Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
3.1.6
-
None
-
Linux x64
Description
I am doing a select for update and then SQLSetPos(SQL_UPDATE) on a particular row. Now, my table contains a column that is of type BINARY. The SQLSetPos(SQL_UPDATE) works fine unless the original data (as opposed to the updated data) contains a 0x00 byte. When it does contain such a byte, the SQLSetPos(SQL_UPDATE) returns success, but the row is not actually updated.
It should be noted that other unusual bytes are fine - e.g. 0x01; it is just 0x00 that is the problem I think.
I have create a Unit Test to demonstrate the issue, which I am putting here.
The sequence of events is
Create a table with a 6-byte BINARY column
Add some rows, one of which has a 0x00 as one of the bytes in the BINARY column.
Do a select
Update a row that does not have a 0x00 in it. (a)
Update a row that does have a 0x00 in it. (b)
Select all updated rows from (a) - there is one row.
Select all updated rows from (b) - there are no rows (there should be one).
Source code of the test is here ...
{{
ODBC_TEST(tmysql_setpos_upd_bin)
{
SQLRETURN rc;
SQLINTEGER nData = 500;
SQLLEN nlen;
SQLCHAR szData[255]=
;
SQLULEN pcrow;
SQLUSMALLINT rgfRowStatus;
OK_SIMPLE_STMT(Stmt, "DROP TABLE IF EXISTS tmysql_setpos_bin");
OK_SIMPLE_STMT(Stmt, "create table tmysql_setpos_bin(col1 int, col2 binary(6))");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(100,'MySQL1')");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(300,'MySQL3')");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(200,'My
0QL2')");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(300,'MySQL3')");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(400,'MySQL4')");
OK_SIMPLE_STMT(Stmt, "insert into tmysql_setpos_bin values(300,'MySQL3')");
rc = SQLTransact(NULL, Connection, SQL_COMMIT);
CHECK_DBC_RC(Connection,rc);
CHECK_STMT_RC(Stmt, SQLFreeStmt(Stmt, SQL_CLOSE));
CHECK_STMT_RC(Stmt, SQLSetStmtAttr(Stmt, SQL_ATTR_CURSOR_TYPE,
(SQLPOINTER)SQL_CURSOR_STATIC, 0));
rc = SQLSetCursorName(Stmt, (SQLCHAR *)"venu",SQL_NTS);
OK_SIMPLE_STMT(Stmt, "select * from tmysql_setpos_bin");
rc = SQLBindCol(Stmt,1,SQL_C_LONG,&nData,100,NULL);
CHECK_STMT_RC(Stmt,rc);
rc = SQLBindCol(Stmt,2,SQL_C_BINARY,szData,100,NULL);
CHECK_STMT_RC(Stmt,rc);
// Fetch Row 2 (That does not have a null) and update it
rc = SQLExtendedFetch(Stmt, SQL_FETCH_ABSOLUTE,2,&pcrow,&rgfRowStatus);
CHECK_STMT_RC(Stmt,rc);
diag(" pcrow:%d\n",pcrow);
diag(" row2:%d,%s\n",nData,szData);
rc = SQLSetPos(Stmt,1,SQL_POSITION,SQL_LOCK_NO_CHANGE);
CHECK_STMT_RC(Stmt,rc);
nData = 1000;
strcpy((char *)szData , "updat1");
rc = SQLSetPos(Stmt,1,SQL_UPDATE,SQL_LOCK_NO_CHANGE);
CHECK_STMT_RC(Stmt,rc);
rc = SQLRowCount(Stmt,&nlen);
CHECK_STMT_RC(Stmt,rc);
diag(" rows affected:%d\n",nlen);
// Fetch Row 3 (That does have a null) and update it
rc = SQLExtendedFetch(Stmt, SQL_FETCH_ABSOLUTE,3,&pcrow,&rgfRowStatus);
CHECK_STMT_RC(Stmt,rc);
diag(" pcrow:%d\n",pcrow);
diag(" row3:%d,%s\n",nData,szData);
rc = SQLSetPos(Stmt,1,SQL_POSITION,SQL_LOCK_NO_CHANGE);
CHECK_STMT_RC(Stmt,rc);
nData = 1000;
strcpy((char *)szData , "updat2");
rc = SQLSetPos(Stmt,1,SQL_UPDATE,SQL_LOCK_NO_CHANGE);
CHECK_STMT_RC(Stmt,rc);
rc = SQLRowCount(Stmt,&nlen);
CHECK_STMT_RC(Stmt,rc);
diag(" rows affected:%d\n",nlen);
rc = SQLFreeStmt(Stmt, SQL_UNBIND);
CHECK_STMT_RC(Stmt,rc);
rc = SQLFreeStmt(Stmt, SQL_CLOSE);
CHECK_STMT_RC(Stmt,rc);
OK_SIMPLE_STMT(Stmt, "select * from tmysql_setpos_bin");
myrowcount(Stmt);
rc = SQLFreeStmt(Stmt, SQL_CLOSE);
CHECK_STMT_RC(Stmt,rc);
OK_SIMPLE_STMT(Stmt, "DELETE FROM tmysql_setpos_bin WHERE col2 = 'updat1'");
rc = SQLRowCount(Stmt,&nlen);
CHECK_STMT_RC(Stmt,rc);
diag("\n total rows affected (updat1):%d",nlen);
IS(nlen == 1);
rc = SQLFreeStmt(Stmt,SQL_CLOSE);
CHECK_STMT_RC(Stmt,rc);
OK_SIMPLE_STMT(Stmt, "DELETE FROM tmysql_setpos_bin WHERE col2 = 'updat2'");
rc = SQLRowCount(Stmt,&nlen);
CHECK_STMT_RC(Stmt,rc);
diag("\n total rows affected (updat2):%d",nlen);
IS(nlen == 1);
rc = SQLFreeStmt(Stmt,SQL_CLOSE);
CHECK_STMT_RC(Stmt,rc);
rc = SQLTransact(NULL,Connection,SQL_COMMIT);
CHECK_DBC_RC(Connection,rc);
OK_SIMPLE_STMT(Stmt, "select * from tmysql_setpos_bin");
IS(5 == myrowcount(Stmt));
rc = SQLFreeStmt(Stmt,SQL_CLOSE);
CHECK_STMT_RC(Stmt,rc);
OK_SIMPLE_STMT(Stmt, "DROP TABLE IF EXISTS tmysql_setpos_bin");
return OK;
}
}}