TD5.2-ODBC-MySQL : INSERT 1 of 2 MEDIUMTEXT cols with null

[olvin]

Hello,

I think there's a bug in TD 5.2 with a connection to a MySQL database (though ODBC) when inserting or updating one of two MEDIUMTEXT (or TEXT) cols with null or empty string.
When inserting only one empty long string to one field ==> no problem

Code: Select all

Set lsTestNull = ''
Set lsTest = 'data'
Call SqlPrepareAndExecute( lhSql, "INSERT INTO table (col1) VALUES (:lsTestNull)" )

When inserting two long string with data to two fields ==> no problem

Code: Select all

Call SqlPrepareAndExecute( lhSql, "INSERT INTO table (col1,col2) VALUES (:lsTest,:lsTest)" )

But when inserting two long string (one empty string and one string with data) to two fields ==> crash !

Code: Select all

Call SqlPrepareAndExecute( lhSql, "INSERT INTO table (col1,col2) VALUES (:lsTest,:lsTestNull)" )

It works with TD 5.1 SP6 !
So I see with TD 5.1 SP6 that empty strings ('') were sent to MySQL as it is. But with TD 5.2, empty strings are sent with NULL value.
I have logged the odbc trace with TD 5.1 SP6 and TD 5.2.

You can test it with the sample I have uploaded.

I have already cooperated with Chris Anderson to make the MySQL ODBC router working with TD 5.1 SP6.
So, it would be great if you tell me if there's an option to activate with TD 5.2 to avoid this problem before you can correct the bug in the SP1...
It is very urgent for us to find a solution avoiding to explode all of our queries with mediumtext cols to one update per query !

Thanks,

Olvin

[Jeff Luther]

Hi, Olvin. let me look at this for you. I am working on getting a MySql connection going, but let me make a couple comments:

* would you mind responding with your exact SQL statement that does the CREATE TABLE? I want to be sure I use your exact syntax for the DB table.

* I notice you are not using SqlSetLongBindDatatype(), yet you mention the columns are 'long' types. Have you tried this:

Code: Select all

...SqlPrepare( ... )
SqlSetLongBindDatatype( 1, 22 )        ! sets variable @1 as long text; pass 23 to set as long binary
SqlSetLongBindDatatype( 2, 22 )        ! sets variable @2 as long text; pass 23 to set as long binary
...SqlExecute( ... )

Would you try that in your test and see if this makes a difference? Thanks.

I thought from your description that the problem might be that in TD v5.2 this statement: Set sVar = ''
evaluated as a NULL value now (and in 5.1 it evaluated as an empty string). But I just ran this code in a small test in v5.1 and v5.2:

Code: Select all

On SAM_AppStartup
   Set sVar = ''
   If sVar = STRING_Null
      Call SalMessageBox( 'sVar = STRING_Null', '', 0 )
   If sVar = ''
      Call SalMessageBox( 'sVar = empty string', '', 0 )

and in both versions of TD both of the "If... " statements evaluate TRUE. That is, TD evaluates an empty string ('') = NULL.

* One other thing you might try is to pass an empty string literal as param. #2, like this:
Call SqlPrepareAndExecute( lhSql, "INSERT INTO TEST (test_01,test_02) VALUES (:lsTest, '' )" )
and see what happens.

Since I cannot test with MySql now and don't see a change between 5.1 & 5.2 with my SAM_AppStartup test above, I'm not sure what to recommend. Except, to ask you to run the tests I mention above, using SqlSetLongBindDatatype() and passing a string literal as parm. #2.

[Jeff Luther]

I got MySql v5 working and tried to duplicate your crash. I do not get a crash with running the attached app with 5.2 and it is a variation of what you sent.

I'll attach the 5.1 version -- same code runs in 5.2 -- and added an ID, a couple names changes. You'll see the CREATE TABLE call commented out.

Does this sample run for you in both 5.1 and 5.2? Do you get the same msg. box results?

[olvin]

Hello,

Sorry for the late reply, but I post this message to tell that this problem is corrected with SP1 of TD 5.2.

Thanks,

Olvin