What is the difference between SQL TYPE CHAR and STRING ? Apostrophe issue.

Scriptcase 9 General
, , ,
#1

Hy,

I am looking for the advise of some expert in understanding how SC maps database column to its internal types system.

I have one very simple table:

CREATE TABLE PEOPLE (ID INTEGER, NAME CHAR(30)).

If I connect to this table using a generic ODBC driver, then SC maps column NAME as SQLTYPE CHAR.

But if I connect to this same table using the native IBM DB2 driver, then SC maps column NAME as SQLTYPE STRING.

What is the difference between these two types in SC ?
There is no type STRING in DB2, so why NAME is mapped differently depending on the driver used to connect to the database ?

Please see map1.png and map2.png attached.

Moreover I have discovered a very strange behaviour , which I think is a bug, in all form applications using text fields mapped as SQLTYPE STRING.

If the field contains a value with an apostrophe (like in O’Connor) then the apostrophe is duplicated each time the form is saved.
This does not happen if the field is mapped as SQLTYPE CHAR.

Is there someone willing to explain me this strange behaviour ?

Thanks, Max

map1.png

map2.png

#2

I can’t answer that question fully. But regarding the last issue: if you know that you will have quotes in your string (and quotes can corrupt your sql statement) then i advise you to use the phpfunction addslashes in the onvalidate or onbeforeinsert/update. That will prevent your issue.

regarding the sql datatypes, it’s a reaction on inquiries to the database engine and I guess that odbc reports it as an array of char and db2 as a string. For php it’s just a string. So I guess it’s a difference like : myfield : char(x) to myfield varchar(x). I don’t think this is a sc issue but a database driver issue.