Bug #8559 SQL_ATTR_ROW_BIND_TYPE ignored with SQLBulkOperations
Submitted: 16 Feb 2005 22:12 Modified: 16 Nov 2005 12:03
Reporter: Gabriele Giuseppini Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.11 OS:Windows (Windows)
Assigned to: Peter Harvey CPU Architecture:Any

[16 Feb 2005 22:12] Gabriele Giuseppini 
Description:
After performing a SQLBulkOperations to add 10 identical integer values in a table, the values in the table do not match the values stored with SQLBulkOperations; it seems that SQLBulkOperations inserts a value *and* the following length\NULL indicator.

The code performing the SQLBulkOperations call is as follows:

unsigned char pBuffer[2048];
SQLPOINTER cbUnitSize=(SQLPOINTER)(4 + 4); // value + length\NULL indicator
SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROW_BIND_TYPE, cbUnitSize, 0);
SQLBindCol(m_hstmt, 1, (SQLSMALLINT)SQL_C_SLONG, pBuffer, 4, (SQLLEN *)(pBuffer + 4));

[...]
int myValue = 1;
int cbValue = sizeof(int);
memmove(pBuffer, &myValue, 4);
memmove(pBuffer + 4, &cbValue, 4); // length\NULL indicator
pBuffer += 8;
// Repeat 10 times, storing 1,4,1,4,1,4,1,4,1,4,....

[...]

SQLPOINTER cbRecords = (SQLPOINTER)10;
SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROW_ARRAY_SIZE, cbRecords, 0);
DWORD dwBindOffset = 0;
SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROW_BIND_OFFSET_PTR, &dwBindOffset, 0);

SQLBulkOperations(m_hstmt, SQL_ADD);

The table shows the following data:
mysql> select * from tblevents2;
+---------+
| MyField |
+---------+
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
+---------+

Here's the ODBC trace file:

logparser "SELE ae8-e98	ENTER SQLAllocHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00000000
		SQLHANDLE *         00AF92C4

logparser "SELE ae8-e98	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00000000
		SQLHANDLE *         0x00AF92C4 ( 0x00841788)

logparser "SELE ae8-e98	ENTER SQLSetEnvAttr 
		SQLHENV             00841788
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER          0x00000003
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
		SQLHENV             00841788
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER          0x00000003 (BADMEM)
		SQLINTEGER                   0 

logparser "SELE ae8-e98	ENTER SQLAllocHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           00841788
		SQLHANDLE *         00AF92C8

logparser "SELE ae8-e98	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           00841788
		SQLHANDLE *         0x00AF92C8 ( 0x00841830)

logparser "SELE ae8-e98	ENTER SQLSetConnectAttrW 
		SQLHDBC             00841830
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER          0x0000000F
		SQLINTEGER                  -6 

logparser "SELE ae8-e98	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             00841830
		SQLINTEGER                 103 <SQL_ATTR_LOGIN_TIMEOUT>
		SQLPOINTER          0x0000000F (BADMEM)
		SQLINTEGER                  -6 

logparser "SELE ae8-e98	ENTER SQLSetConnectAttrW 
		SQLHDBC             00841830
		SQLINTEGER                1204 <unknown>
		SQLPOINTER          [Unknown attribute 1204]
		SQLINTEGER                  -6 

logparser "SELE ae8-e98	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             00841830
		SQLINTEGER                1204 <unknown>
		SQLPOINTER          [Unknown attribute 1204]
		SQLINTEGER                  -6 

logparser "SELE ae8-e98	ENTER SQLDriverConnectW 
		HDBC                00841830
		HWND                00000000
		WCHAR *             0x74329A38 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x74329A38 
		SWORD                        2 
		SWORD *             0x00000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

logparser "SELE ae8-e98	EXIT  SQLDriverConnectW  with return code 0 (SQL_SUCCESS)
		HDBC                00841830
		HWND                00000000
		WCHAR *             0x74329A38 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x74329A38 
		SWORD                        2 
		SWORD *             0x00000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

logparser "SELE ae8-e98	ENTER SQLSetConnectAttrW 
		SQLHDBC             00841830
		SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
		SQLPOINTER          0x00000001
		SQLINTEGER                  -5 

logparser "SELE ae8-e98	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             00841830
		SQLINTEGER                 102 <SQL_ATTR_AUTOCOMMIT>
		SQLPOINTER          0x00000001 (BADMEM)
		SQLINTEGER                  -5 

logparser "SELE ae8-e98	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841830
		SQLHANDLE *         00AF92CC

logparser "SELE ae8-e98	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841830
		SQLHANDLE *         0x00AF92CC ( 0x00841c60)

logparser "SELE ae8-e98	ENTER SQLSetStmtAttrW 
		SQLHSTMT            00841C60
		SQLINTEGER                   6 <SQL_ATTR_CURSOR_TYPE>
		SQLPOINTER          0x00000002
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetStmtAttrW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		SQLHSTMT            00841C60
		SQLINTEGER                   6 <SQL_ATTR_CURSOR_TYPE>
		SQLPOINTER          0x00000002 (BADMEM)
		SQLINTEGER                   0 

		DIAG [01S02] Option value changed to default static cursor (502) 

logparser "SELE ae8-e98	ENTER SQLSetStmtAttrW 
		SQLHSTMT            00841C60
		SQLINTEGER                   7 <SQL_ATTR_CONCURRENCY>
		SQLPOINTER          0x00000002
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLINTEGER                   7 <SQL_ATTR_CONCURRENCY>
		SQLPOINTER          0x00000002 (BADMEM)
		SQLINTEGER                   0 

logparser "SELE ae8-e98	ENTER SQLPrepareW 
		HSTMT               00841C60
		WCHAR *             0x0006E804 [      24] "SELECT * FROM tblEvents2"
		SDWORD                    24

logparser "SELE ae8-e98	EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
		HSTMT               00841C60
		WCHAR *             0x0006E804 [      24] "SELECT * FROM tblEvents2"
		SDWORD                    24

logparser "SELE ae8-e98	ENTER SQLExecute 
		HSTMT               00841C60

logparser "SELE ae8-e98	EXIT  SQLExecute  with return code -1 (SQL_ERROR)
		HSTMT               00841C60

		DIAG [42S01] Table 'eventlog.tblevents2' doesn't exist (1146) 

logparser "SELE ae8-e98	ENTER SQLAllocHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841830
		SQLHANDLE *         0006DF0C

logparser "SELE ae8-e98	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841830
		SQLHANDLE *         0x0006DF0C ( 0x00842538)

logparser "SELE ae8-e98	ENTER SQLExecDirectW 
		HSTMT               00842538
		WCHAR *             0x00B0E130 [      -3] "CREATE TABLE tblEvents2 ( MyField int)\ 0"
		SDWORD                    -3

logparser "SELE ae8-e98	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               00842538
		WCHAR *             0x00B0E130 [      -3] "CREATE TABLE tblEvents2 ( MyField int)\ 0"
		SDWORD                    -3

logparser "SELE ae8-e98	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00842538

logparser "SELE ae8-e98	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00842538

logparser "SELE ae8-e98	ENTER SQLPrepareW 
		HSTMT               00841C60
		WCHAR *             0x0006E804 [      24] "SELECT * FROM tblEvents2"
		SDWORD                    24

logparser "SELE ae8-e98	EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
		HSTMT               00841C60
		WCHAR *             0x0006E804 [      24] "SELECT * FROM tblEvents2"
		SDWORD                    24

logparser "SELE ae8-e98	ENTER SQLExecute 
		HSTMT               00841C60

logparser "SELE ae8-e98	EXIT  SQLExecute  with return code 0 (SQL_SUCCESS)
		HSTMT               00841C60

logparser "SELE ae8-e98	ENTER SQLNumResultCols 
		HSTMT               00841C60
		SWORD *             0x0006DF9E

logparser "SELE ae8-e98	EXIT  SQLNumResultCols  with return code 0 (SQL_SUCCESS)
		HSTMT               00841C60
		SWORD *             0x0006DF9E (1)

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0006DF68
		SQLPOINTER          [Unknown attribute 1011]

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x0006DF68 (7)
		SQLPOINTER          [Unknown attribute 1011]

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x00AF9540
		SQLSMALLINT                  9 
		SQLSMALLINT *       0x0006DF68
		SQLPOINTER          [Unknown attribute 1011]

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 1 (SQL_SUCCESS_WITH_INFO)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1011 <SQL_DESC_NAME>
		SQLPOINTER         0x00AF9540
		SQLSMALLINT                  9 
		SQLSMALLINT *       0x0006DF68 (14)
		SQLPOINTER          [Unknown attribute 1011]

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1002 <SQL_DESC_TYPE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1002]

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1002 <SQL_DESC_TYPE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1002]

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1008 <SQL_DESC_NULLABLE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1008]

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1008 <SQL_DESC_NULLABLE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1008]

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT                 11 <SQL_DESC_AUTO_UNIQUE_VALUE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          0x0006DFA0

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT                 11 <SQL_DESC_AUTO_UNIQUE_VALUE>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          0x0006DFA0 (0)

logparser "SELE ae8-e98	ENTER SQLColAttributeW 
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1013]

logparser "SELE ae8-e98	EXIT  SQLColAttributeW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLSMALLINT                  1 
		SQLSMALLINT               1013 <SQL_DESC_OCTET_LENGTH>
		SQLPOINTER         0x00000000
		SQLSMALLINT                  0 
		SQLSMALLINT *       0x00000000
		SQLPOINTER          [Unknown attribute 1013]

logparser "SELE ae8-e98	ENTER SQLSetStmtAttrW 
		SQLHSTMT            00841C60
		SQLINTEGER                   5 <SQL_ATTR_ROW_BIND_TYPE>
		SQLPOINTER          0x00000008
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLINTEGER                   5 <SQL_ATTR_ROW_BIND_TYPE>
		SQLPOINTER          0x00000008 (BADMEM)
		SQLINTEGER                   0 

logparser "SELE ae8-e98	ENTER SQLBindCol 
		HSTMT               00841C60
		UWORD                        1 
		SWORD                      -16 <SQL_C_SLONG>
		PTR                0x00F90000
		SQLLEN                     4
		SQLLEN *            0x00F90004

logparser "SELE ae8-e98	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
		HSTMT               00841C60
		UWORD                        1 
		SWORD                      -16 <SQL_C_SLONG>
		PTR                0x00F90000
		SQLLEN                     4
		SQLLEN *            0x00F90004 (0)

logparser "SELE ae8-e98	ENTER SQLSetStmtAttrW 
		SQLHSTMT            00841C60
		SQLINTEGER                  27 <SQL_ATTR_ROW_ARRAY_SIZE>
		SQLPOINTER          0x0000000A
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLINTEGER                  27 <SQL_ATTR_ROW_ARRAY_SIZE>
		SQLPOINTER          0x0000000A (BADMEM)
		SQLINTEGER                   0 

logparser "SELE ae8-e98	ENTER SQLSetStmtAttrW 
		SQLHSTMT            00841C60
		SQLINTEGER                  23 <SQL_ATTR_ROW_BIND_OFFSET_PTR>
		SQLPOINTER          0006F840
		SQLINTEGER                   0 

logparser "SELE ae8-e98	EXIT  SQLSetStmtAttrW  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLINTEGER                  23 <SQL_ATTR_ROW_BIND_OFFSET_PTR>
		SQLPOINTER          0006F840
		SQLINTEGER                   0 

logparser "SELE ae8-e98	ENTER SQLBulkOperations 
		SQLHSTMT            00841C60
		SQLSMALLINT                  4 

logparser "SELE ae8-e98	EXIT  SQLBulkOperations  with return code 0 (SQL_SUCCESS)
		SQLHSTMT            00841C60
		SQLSMALLINT                  4 

logparser "SELE ae8-e98	ENTER SQLFreeHandle 
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841C60

logparser "SELE ae8-e98	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  3 <SQL_HANDLE_STMT>
		SQLHANDLE           00841C60

logparser "SELE ae8-e98	ENTER SQLDisconnect 
		HDBC                00841830

logparser "SELE ae8-e98	EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)
		HDBC                00841830

logparser "SELE ae8-e98	ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           00841830

logparser "SELE ae8-e98	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           00841830

logparser "SELE ae8-e98	ENTER SQLFreeHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00841788

logparser "SELE ae8-e98	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00841788

Here's the table definition:

mysql> desc tblevents2;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| MyField | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
			

How to repeat:
Windows XP SP2;
MySQL 4.1;
MyODBC 3.51.11;
Microsoft Log Parser 2.2 (from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8...);

- Create a MySQL database called "eventlogs";
- Get the Log Parser 2.2 executable and run the following command:

logparser "SELECT top 10 1 AS MyField from system to tblEvents" -o:SQL -server:localhost -driver:"MySQL ODBC 3.51 Driver" -database:eventlog -username:root -password:<your password here> -createtable:ON -clearTable:ON

(the command creates the target table as defined above and inserts 10 integer fields with value = 1)

- Check the newly-created "tblEvents" table:

Expected:
+---------+
| MyField |
+---------+
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
|       1 |
+---------+

Actual:
+---------+
| MyField |
+---------+
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
|       1 |
|       4 |
+---------+
[16 Feb 2005 22:18] Gabriele Giuseppini 
I forgot to add that the same code works fine against other ODBC drivers (SQL Server; Access; Oracle).
[17 Feb 2005 16:39] Gabriele Giuseppini 
Set correct version.
[16 Nov 2005 12:04] Bogdan Degtyariov 
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

fixed in the version 3.51.12