Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-276

Update of Binary data containing 0x00 does not work

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 3.1.6
    • 3.1.7
    • General
    • 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]=

      {0}

      ;
      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;
      }
      }}

      Attachments

        Activity

          People

            Lawrin Lawrin Novitsky
            stevem Steve Millington
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.