MySQL Bugs: #43831: SQLPutData doesn't work for MEDIUMTEXT

Bug #43831	SQLPutData doesn't work for MEDIUMTEXT
Submitted:	24 Mar 2009 13:26	Modified:	18 Nov 2009 15:59
Reporter:	Gerhard Lutz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.27 and 5.01.05	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
I try to write a MEDIUMTEXT column with the C-API [ODBC]. With MyODBC 3.51.27 I get a "Function sequence error" after executing ::SQLPutData (in synchronous mode) and with MyODBC 5.01.05 the MYODBC5.DLL crashed.

Database table:
---------------

CREATE TABLE mytable(id INTEGER, descr MEDIUMTEXT);

C pseudo code:
--------------

SQLRETURN rcode = ::SQLAllocHandle(SQL_HANDLE_STMT, m_hDBConnection, &m_hStmt);

rcode = ::SQLPrepare(m_hStmt, "UPDATE mytable SET descr=? WHERE id=5", SQL_NTS);

SQLINTEGER cbIndicator;
rcode = ::SQLBindParameter(m_hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_WLONGVARCHAR, 0, 0, (SQLPOINTER) 1, 0, &cbIndicator);

cbIndicator = SQL_LEN_DATA_AT_EXEC(0);

rcode = ::SQLExecute(m_hStmt);

SQLPOINTER pToken;
SQLRETURN rcode = ::SQLParamData(m_hStmt, &pToken);

do
{
    rcode = ::SQLPutData(m_hStmt, (void*)szMyString, nSize);
    // Bug: Return code is -1 with "Function sequence error" in 3.51 or crash in 5.01
...
}
while (...);

The same thing happens if I try to write a LONGBLOB. Can you help? Do you need any additional information?

How to repeat:
Please see above.

Hi Gerhard and thanks for your report.

I believe there is an error in your code, probably something like forgetting     SQLFreeStmt(hstmt, ... or similar. Can you please post complete, self-sufficient test case that fails each time it is run.

There can be problems with fields longer than 2^32 as Microsoft does not have those so be sure to include "Limit column size to 32bit signed integer" option.

Hi Tonci.
Thank you for your quick response.
I don't think that I forgot to free a statement handle, because this code worked for other ODBC drivers like MS SQL Server, Oracle or PostgreSQL.

I also tried OPTION=134217728 for long data in my connection string but it made no difference.
Now I coded a small sample C program and unfortunately (or fortunately) it worked. The error message was not reproduceable there. And I don't know the reason for that.

But now I know more about my problem: The ::SQLExecute command returns SQL_NEED_DATA as expected, but the following ::SQLParamData returns SQL_SUCCESS with no change in the parameter 'pToken' (I also expected SQL_NEED_DATA here and pToken should be set to 1).

Do you know some cases when this could happen?

(In my opinion, the crash with v5.01 is still a bug in the MyODBC Connector. I will atttach a Mini Dump for that!)

Mini Dump for the crash with MyODBC Connector 5.01

Attachment: Dump00.dmp (application/octet-stream, text), 48.58 KiB.

Sample code in C that worked

Attachment: CLOBTest.cpp (text/plain), 6.05 KiB.

Gerhard, I'm having troubles reading your minidump as I'm on W2K8x64 server... Will try in XP VM.

Now, what I could read is that you're having exception c0000005 which is (presuming you're on Intel CPU):
0 #DE—Divide Error DIV and IDIV instructions.
13 #GP—General Protection Any memory reference and other protection checks.

Apart from this, I'll try reproducing with your test case.

Gerhard,
What is the program that causes the crash? Can you include an ODBC trace log?

Stack:

myodbc5!my_wildcmp_unicode+0x322 [f:\build\mysql-5.0.56sp1-winbuild\mysql-enterprise-gpl-5.0.56sp1-build\strings\ctype-utf8.c @ 1952]
myodbc5!my_wildcmp_unicode+0xeaa [f:\build\mysql-5.0.56sp1-winbuild\mysql-enterprise-gpl-5.0.56sp1-build\strings\ctype-utf8.c @ 2610]
myodbc5!escape_string_for_mysql+0x61 [f:\build\mysql-5.0.56sp1-winbuild\mysql-enterprise-gpl-5.0.56sp1-build\mysys\charset.c @ 633]
myodbc5!mysql_real_escape_string+0x37 [f:\build\mysql-5.0.56sp1-winbuild\mysql-enterprise-gpl-5.0.56sp1-build\libmysql\libmysql.c @ 1617]
myodbc5!insert_param+0x885 [c:\cygwin\home\mysqldev\odbc-guffert\src\mysql-connector-odbc-5.1.5r1144\driver\execute.c @ 590]
myodbc5!insert_params+0x21f [c:\cygwin\home\mysqldev\odbc-guffert\src\mysql-connector-odbc-5.1.5r1144\driver\execute.c @ 202]
myodbc5!SQLParamData+0x6a [c:\cygwin\home\mysqldev\odbc-guffert\src\mysql-connector-odbc-5.1.5r1144\driver\execute.c @ 790]
odbc32+0x260be
DBODBC+0x24eb25
DBODBC+0x262853
DBODBC+0x253c6b
DBODBC+0x25ff38
DBODBC+0x2d3659
DBODBC+0x15e8c2
DBODBC+0x16f9a6
DBODBC+0x170c81
tm+0x18609b
mfc90d+0xf7072
mfc90d+0xf77ba
mfc90d+0xcd5f3
mfc90d+0x909d4
mfc90d+0x8f649
mfc90d+0x8f592
mfc90d+0x8ca10
mfc90d+0x8cfd6
tm+0x39b765
user32+0x8734
user32+0x8816
user32+0xb89b
user32+0xb903
comctl32+0x27344
comctl32+0x27426
comctl32+0x2972b
user32+0x8734
user32+0x8816
user32+0xc63f
user32+0xf65d
mfc90d+0x8e444
mfc90d+0x8f5b2
mfc90d+0x8ca10
mfc90d+0x8cfd6
tm+0x39b765
user32+0x8734
user32+0x8816
user32+0x89cd
user32+0x8a10
user32+0x1d99d
user32+0x2c69b
mfc90d+0xf4d53
mfc90d+0x9639e
mfc90d+0xcd5bf
tm+0x17b855
tm+0x307e08
TA+0x755f2
mfc90d+0x11d795
mfc90d+0x11d5d1
mfc90d+0x11ebae
mfc90d+0x11d621
mfc90d+0x96587
mfc90d+0xce64c
TA+0x5d820
mfc90d+0xf7072
mfc90d+0xf77ba
mfc90d+0x9b435
mfc90d+0x909d4
mfc90d+0x99f0e
TA+0x58f5b
mfc90d+0x8f649
mfc90d+0x8f592
mfc90d+0x8ca10
mfc90d+0x8cfd6
mfc90d+0x8879b
user32+0x8734
user32+0x8816
user32+0x89cd

call stack

Attachment: bug43831.txt (text/plain), 7.62 KiB.

Tonci, that's right, I'm on Intel CPU.
Jess, in the MiniDump I can see that my DBODBC.dll of my application TA.exe calls odbc32.dll and myodbc5.dll. The crash happens in myodbc5.dll.
Now I attached an ODBC Trace.log and a new corresponding MiniDump.

ODBC Trace Log

Attachment: ODBC_SQL.LOG (application/octet-stream, text), 145.94 KiB.

New corresponding Mini Dump

Attachment: Dump01.dmp (application/octet-stream, text), 53.84 KiB.

I tried the same again with MyODBC v3.51. I can't understand why SQLExecute returns SQL_NEED_DATA
and the following SQLParamData returns SQL_SUCCESS. I expected SQL_NEED_DATA, too.

Here's the ODBC Trace with MyODBC v3.51:

TA              ce4-f84	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           01672908
		SQLHANDLE *         0012C7CC

TA              ce4-f84	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           01672908
		SQLHANDLE *         0x0012C7CC ( 0x01673048)

TA              ce4-f84	ENTER SQLPrepare 
		HSTMT               01673048
		UCHAR *             0x01359644 [      -3] "UPDATE testdata SET description_l=? WHERE id=23\ 0"
		SDWORD                    -3

TA              ce4-f84	EXIT  SQLPrepare  with return code 0 (SQL_SUCCESS)
		HSTMT               01673048
		UCHAR *             0x01359644 [      -3] "UPDATE testdata SET description_l=? WHERE id=23\ 0"
		SDWORD                    -3

TA              ce4-f84	ENTER SQLBindParameter 
		HSTMT               01673048
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                        1 <SQL_C_CHAR>
		SWORD                      -10 <SQL_WLONGVARCHAR>
		SQLULEN                    0
		SWORD                        0 
		PTR                0x00000001
		SQLLEN                     0
		SQLLEN *            0x0012C498

TA              ce4-f84	EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
		HSTMT               01673048
		UWORD                        1 
		SWORD                        1 <SQL_PARAM_INPUT>
		SWORD                        1 <SQL_C_CHAR>
		SWORD                      -10 <SQL_WLONGVARCHAR>
		SQLULEN                    0
		SWORD                        0 
		PTR                0x00000001
		SQLLEN                     0
		SQLLEN *            0x0012C498 (-858993460)

TA              ce4-f84	ENTER SQLExecute 
		HSTMT               01673048

TA              ce4-f84	EXIT  SQLExecute  with return code 99 (SQL_NEED_DATA)
		HSTMT               01673048

TA              ce4-f84	ENTER SQLParamData 
		HSTMT               01673048
		PTR *              0x0012C65C

TA              ce4-f84	EXIT  SQLParamData  with return code 0 (SQL_SUCCESS)
		HSTMT               01673048
		PTR *              0x0012C65C

TA              ce4-f84	ENTER SQLFreeStmt 
		HSTMT               01673048
		UWORD                        0 <SQL_CLOSE>

TA              ce4-f84	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               01673048
		UWORD                        0 <SQL_CLOSE>

TA              ce4-f84	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           01673048

TA              ce4-f84	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           01673048

Gerhard, looking into specs I do not think it's a problem...
Common causes involving SQL_NEED_DATA:

Function sequence error

(DM) An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.

(DM) SQLExecute, SQLExecDirect, SQLBulkOperations, or SQLSetPos was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.

(DM) The StatementHandle was not prepared.

Invalid string or buffer length

A parameter value, set with SQLBindParameter, was a null pointer, and the parameter length value was not 0, SQL_NULL_DATA, SQL_DATA_AT_EXEC, SQL_DEFAULT_PARAM, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET.

A parameter value, set with SQLBindParameter, was not a null pointer; the C data type was SQL_C_BINARY or SQL_C_CHAR; and the parameter length value was less than 0 but was not SQL_NTS, SQL_NULL_DATA, SQL_DEFAULT_PARAM, or SQL_DATA_AT_EXEC, or less than or equal to SQL_LEN_DATA_AT_EXEC_OFFSET.

A parameter length value bound by SQLBindParameter was set to SQL_DATA_AT_EXEC; the SQL type was either SQL_LONGVARCHAR, SQL_LONGVARBINARY, or a long data source–specific data type; and the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo was "Y".

If SQLExecute encounters a data-at-execution parameter, it returns SQL_NEED_DATA. The application sends the data using SQLParamData and SQLPutData. But (http://msdn.microsoft.com/en-us/library/ms716238(VS.85).aspx) says:
An application can actually send any type of data at execution time with SQLPutData, although only character and binary data can be sent in parts. However, if the data is small enough to fit in a single buffer, there is generally no reason to use SQLPutData. It is much easier to bind the buffer and let the driver retrieve the data from the buffer.

I guess I should try and make your test fail...

Gerhard, no luck, "Return code is 0. Press <ENTER>..." each time I run your test case...

Now, I used c/ODBC 5.1 cause of wide functions you have in code and I added Id as PRIMARY KEY in table definition (id INTEGER NOT NULL PRIMARY KEY).
The rest is just copy/paste of your test.

Ideas?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Gerhard,
Please follow up if you have more information on how to reproduce this.

Tonci,
how would you program a function that writes long data to a MEDIUMTEXT column using the MyODBC connnector? Can you add a short example in C code?

Do you have any further ideas how to find the bug that caused the crash in MyODBC v5.1.5? Do I have further debugging possibilities?
Best regards, Gerhard

Gerhard, You have already provided an example in C++ code at [26 Mar 9:37]. What is the issue with this program?

I'm not sure if I used the functions ::SQLBindParameter, ::SQLParamData and ::SQLPutData with its parameters in the correct way.

Hello,
I found the bug in the source code of my application. The indicator variable of the ::SQLBindParameter call (last parameter) was not valid any more when I called ::SQLParamData.

I'm sorry for the inconvenience. You can close this issue now.
Best regards,
Gerhard

Gerhard, no problem.