3 Replies Latest reply on Dec 2, 2006 12:55 AM by 172604

    Error in assignment (SQL-22005) when using SQLParamOptions and SQL_C_CHAR

    172604
      I am getting "Error in assignment" when I use SQLParamOptions to bulk insert rows to a numeric data type (INTEGER, NUMERIC, REAL, etc) with a C type of SQL_C_CHAR. Based on the ODBC specs it should do the conversion for me, but TimesTen pukes. Oddly it works fine id you are only inserting one row, and it does work fine if you store the value as a SLONG and insert it into an INTEGER.

      Basically, I am working on adding bulk insert into the DBD::TimesTen driver, VARCHAR's work fine.
        • 1. Re: Error in assignment (SQL-22005) when using SQLParamOptions and SQL_C_CHAR
          scheung
          What you are trying to do should work. Can you check to make sure you are setting the parameter correctly?

          When using SQLParamOptions() with SQL_C_CHAR, the cbValueMax parameter to SQLBindParameter() (the second to last parameter) must be set to the length of the char buffer element. As it loops through the rows, it increments the pointer for each column by the associated cbValueMax number of bytes.

          If this doesn't explain your error, please provide your code segment showing the SQLBindParameter calls and the allocation and initialization of the bound buffers. Also the TimesTen version you are using.

          -scheung
          • 2. Re: Error in assignment (SQL-22005) when using SQLParamOptions and SQL_C_CH
            172604
            Doesn't seem to work, here is a test case:

            1. Create table:

            CREATE TABLE tt_test1 (
            tcol1 INTEGER NOT NULL
            );

            2. Save code as bulktc1.c

            /* BEGIN bulktc1.c */
            #include <stdio.h>
            #include <string.h>
            #include <timesten.h>

            /*
            CREATE TABLE tt_test1 (
            tcol1 INTEGER NOT NULL
            );
            */

            int dump_dberror (HENV, HDBC, HSTMT, char *);
            #define SQL_ok(x) (x == SQL_SUCCESS || x == SQL_SUCCESS_WITH_INFO)

            int main (int argc, char **argv)
            {
            HENV henv = SQL_NULL_HENV;
            HDBC hdbc = SQL_NULL_HDBC;
            HSTMT hstmt = SQL_NULL_HSTMT;
            RETCODE rc;
            char szConnStrOut[2048];
            SQLSMALLINT cbConnStrOut;
            char statement[2048];
            #ifdef USE_INTEGER
            SQLINTEGER **values;
            SQLLEN **values_len;
            #else
            char ***values;
            SQLLEN **values_len;
            #endif
            int i, j;

            if (argc < 1)
            {
            fprintf (stderr, "No args.\n");
            exit (1);
            }

            rc = SQLAllocEnv (&henv);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLAllocEnv");
            exit (1);
            }

            rc = SQLAllocConnect (henv, &hdbc);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLAllocConnect");
            SQLFreeEnv (henv);
            exit (1);
            }

            rc = SQLDriverConnect (hdbc, 0, argv[1], strlen(argv[1]), szConnStrOut,
            sizeof (szConnStrOut), &cbConnStrOut,
            SQL_DRIVER_NOPROMPT);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLDriverConnect");
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }
            else if (rc = SQL_SUCCESS_WITH_INFO)
            {
            dump_dberror (henv, hdbc, hstmt, "SQLDriverConnect");
            }

            rc = SQLSetConnectOption (hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLSetConnectOption");
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }

            rc = SQLAllocStmt (hdbc, &hstmt);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLAllocStmt");
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }

            sprintf (statement, "INSERT INTO tt_test1 (tcol1) VALUES (?)");
            rc = SQLPrepare (hstmt, statement, strlen(statement));
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLPrepare");
            SQLFreeStmt (hstmt, SQL_DROP);
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }

            int param_count = 1;
            int exe_count = 50;
            #ifdef USE_INTEGER
            values = (SQLINTEGER **)malloc (param_count * sizeof (SQLINTEGER *));
            memset (values, 0, param_count * sizeof (SQLINTEGER *));
            #else
            values = (char ***)malloc (param_count * sizeof (char **));
            memset (values, 0, param_count * sizeof (char **));
            #endif
            values_len = (SQLLEN **)malloc (param_count * sizeof (SQLLEN *));
            memset (values_len, 0, param_count * sizeof (SQLLEN *));

            for (i=0; i<param_count; i++)
            {
            #ifdef USE_INTEGER
            values[i] = (SQLINTEGER *)malloc (exe_count * sizeof (SQLINTEGER));
            memset (values, 0, exe_count * sizeof (SQLINTEGER));
            #else
            values[i] = (char **)malloc (exe_count * sizeof (char *));
            memset (values[i], 0, exe_count * sizeof (char *));
            #endif
            values_len[i] = (SQLLEN *)malloc (exe_count * sizeof (SQLLEN));
            memset (values_len[i], 0, exe_count * sizeof (SQLLEN));

            for (j=0; j<exe_count; j++)
            {
            #ifdef USE_INTEGER
            values[i][j] = i*j;
            values_len[i][j] = sizeof (SQLLEN);
            #else
            values[i][j] = (char *)malloc (sizeof (char) * 128);
            sprintf (values[i][j], "%d", i*j);
            values_len[i][j] = strlen(values[i][j]);
            #endif
            }

            SQLLEN cbValue = 10;
            SQLLEN cbColDef = 10;
            SQLLEN ibScale = 0;
            #ifdef USE_INTEGER
            rc = SQLBindParameter (hstmt, i+1, SQL_PARAM_INPUT, SQL_C_SLONG,
            SQL_INTEGER, cbColDef, ibScale, values[i], cbValue,
            values_len[i]);
            #else
            rc = SQLBindParameter (hstmt, i+1, SQL_PARAM_INPUT, SQL_C_CHAR,
            SQL_INTEGER, cbColDef, ibScale, values[i], cbValue,
            values_len[i]);
            #endif
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLBindParameter");
            SQLFreeStmt (hstmt, SQL_DROP);
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }
            }

            SQLROWSETSIZE irow;
            rc = SQLParamOptions (hstmt, exe_count, &irow);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLParamOptions");
            SQLFreeStmt (hstmt, SQL_DROP);
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }

            rc = SQLExecute (hstmt);
            if (!SQL_ok (rc))
            {
            dump_dberror (henv, hdbc, hstmt, "SQLExecute");
            SQLFreeStmt (hstmt, SQL_DROP);
            SQLFreeConnect (hdbc);
            SQLFreeEnv (henv);
            exit (1);
            }
            }

            int dump_dberror (HENV henv, HDBC hdbc, HSTMT hstmt, char *msg)
            {
            RETCODE rc;

            UCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
            UCHAR errormsg[SQL_MAX_MESSAGE_LENGTH + 1];
            SWORD errormsglen;
            SDWORD nativeerror;

            while ((rc = SQLError (henv, hdbc, hstmt, sqlstate, &nativeerror, errormsg,
            sizeof (errormsg), &errormsglen)) == SQL_SUCCESS
            || rc == SQL_SUCCESS_WITH_INFO)
            {
            fprintf (stderr, "%s (SQL-%s) %s\n", errormsg, sqlstate, msg);

            if (hstmt != SQL_NULL_HSTMT) hstmt = SQL_NULL_HSTMT;
            else if (hdbc != SQL_NULL_HDBC) hdbc = SQL_NULL_HDBC;
            else henv = SQL_NULL_HENV;
            }
            }
            /* END bulktc1.c */

            3. Save script as runtest.sh:

            #!/bin/sh
            ttisqlcs -connStr $* -e "truncate table tt_test1; quit;"
            echo "Trying with SQL_C_CHAR"
            gcc -o bulktc1 bulktc1.c -I$TT_HOME/include -L$TT_HOME/lib -lttclient -Wl,-rpath,$TT_HOME/lib
            ./bulktc1 $*
            ttisqlcs -connStr $* -e "select count(*) from tt_test1; quit;"
            echo "Trying with SQL_C_SLONG"
            gcc -DUSE_INTEGER -o bulktc1 bulktc1.c -I$TT_HOME/include -L$TT_HOME/lib -lttclient -Wl,-rpath,$TT_HOME/lib
            ./bulktc1 $*
            ttisqlcs -connStr $* -e "select count(*) from tt_test1; quit;"

            4. Set TT_HOME to point to the root install of TimesTen
            5. Run script ./runtest.sh <DSN=....>
            6. Results:


            Copyright (c) 1996-2006, Oracle. All rights reserved.
            Type ? or "help" for help, type "exit" to quit ttIsql.
            All commands must end with a semicolon character.



            connect "DSN=testcs";
            Connection successful: DSN=testcs;TTC_SERVER=LocalHost_tt60;TTC_SERVER_DSN=test;UID=wagnerch;DATASTORE=/u01/app/timesten/TimesTen/tt60/info/DataStore/test;DURABLECOMMITS=1;AUTHENTICATE=0;PERMSIZE=16;
            (Default setting AutoCommit=1)

            truncate table tt_test1;

            quit;
            Disconnecting...
            Done.
            Trying with SQL_C_CHAR
            [TimesTen][TimesTen 6.0.4 ODBC Driver]Error in assignment (SQL-22005) SQLExecute

            Copyright (c) 1996-2006, Oracle. All rights reserved.
            Type ? or "help" for help, type "exit" to quit ttIsql.
            All commands must end with a semicolon character.



            connect "DSN=testcs";
            Connection successful: DSN=testcs;TTC_SERVER=LocalHost_tt60;TTC_SERVER_DSN=test;UID=wagnerch;DATASTORE=/u01/app/timesten/TimesTen/tt60/info/DataStore/test;DURABLECOMMITS=1;AUTHENTICATE=0;PERMSIZE=16;
            (Default setting AutoCommit=1)

            select count(*) from tt_test1;
            < 0 >
            1 row found.

            quit;
            Disconnecting...
            Done.
            Trying with SQL_C_SLONG

            Copyright (c) 1996-2006, Oracle. All rights reserved.
            Type ? or "help" for help, type "exit" to quit ttIsql.
            All commands must end with a semicolon character.



            connect "DSN=testcs";
            Connection successful: DSN=testcs;TTC_SERVER=LocalHost_tt60;TTC_SERVER_DSN=test;UID=wagnerch;DATASTORE=/u01/app/timesten/TimesTen/tt60/info/DataStore/test;DURABLECOMMITS=1;AUTHENTICATE=0;PERMSIZE=16;
            (Default setting AutoCommit=1)

            select count(*) from tt_test1;
            < 50 >
            1 row found.

            quit;
            Disconnecting...
            Done.
            • 3. Re: Error in assignment (SQL-22005) when using SQLParamOptions and SQL_C_CH
              172604
              Ahh, you must have updated your note. I didn't see the comments below the first line.

              I figured it out, it was something stupid! I thought SQLBindParameter was looking for an array of character pointers. It is actually looking for a single character pointer that is structured into a "blob".

              Essentially I tweaked the code to this...

              sprintf (values[i]+(j*128), "%d", (i+1)*(j+1));
              values_len[i][j] = strlen(values[i]+(j*128));

              and it works fine. Where 128 is cbValueMax.