[QUOTE=aducom;32702]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.[/QUOTE]
Thanks for you answer. I have spent some time in investigating this issue and I have found this fix.
Hope this helps someone else out there using IBM DB2 like me.
Background:
SC automatically escapes the apostrophe, before any update and insert into the database, by changing the field value and duplicating the apostrophe.
Example:
Field {NAME} = “O’Neil” becomes {NAME} = “O’'Neil” before being entered into the database.
This is the correct way to escape the apostrophe in DB2 and SC+AdoDB works correctly so far.
After the insert/update , the field {NAME} is restored back to a single apostrophe, by putting back the original form field value into the field variable.
You can see how this mechanism works in details by playing with OnBeforeUpdate and OnAfterUpdate events, and by echoing, on each event, the value for {NAME}, and also snooping into the code generated for the application form_PEOPLE_apl.php
Before updating the database, SC generates this code:
$this->name = substr($this->Db->qstr($this->name), 1, -1);
qstr is the AdoDB function to escape the apostrohe; now {NAME} has two apostrophes ( O’'Neil )
After updting the database, SC generates this code:
if (isset($NM_val_form) && isset($NM_val_form[‘name’])) { $this->name = $NM_val_form[‘name’]; }
elseif (isset($this->name)) { $this->nm_limpa_alfa($this->name); }
which basically sets back {NAME} to his original value. Now {NAME} has his original value ( O’Neil )
And this is exactly where the BUG is !
If {NAME} is of SQLTYPE CHAR, then the code to set back the original value is correctly generated, but if it is of type SQLTYPE STRING, then the code is NOT generated and the apostrophe is left duplicated.
The temporary fix I have found is to modify the FetchField function in the AdoDb code; in the function adodb-db2.inc.php, I forced the return type to be CHAR instead of STRING.
// returns the field object
function FetchField($offset = -1)
{
$o= new ADOFieldObject();
$o->name = @db2_field_name($this->_queryID,$offset);
$o->type = @db2_field_type($this->_queryID,$offset);
// temporary fix, SQL Char type should be reported as CHAR and not as STRING otherwise SC does not generate proper code and there are issues with the apostrophe
if ($o->type == "string") $o->type = "char";
$o->max_length = db2_field_width($this->_queryID,$offset);
if (ADODB_ASSOC_CASE == 0) $o->name = strtolower($o->name);
else if (ADODB_ASSOC_CASE == 1) $o->name = strtoupper($o->name);
return $o;
}
In this way SC, after a resync of the table, assumes {NAME} to be a SQLTYPE CHAR and not a SQLTYPE STRING, and correctly generates the code to refresh the orignal value for {NAME}.
I am not sure if this is a bug of AdoDb , which is considered as a third party library by SC, or it’s a bug of SC.
Indeed the return value of FetchField is not consistent between different databases: MySql and SQLite return CHAR, but DB2 returns STRING, despite the database type is always CHAR and there is no type STRING in DB2.