Prepared statements with multiple values and ODBC

Trafodion supports multiple input rows as rowset arrays that can be provided
to a prepared statement from a jdbc or odbc app.
But it cannot be provided from command line interfaces like trafci or sqlci.
Forwarding this question to connectivity group for details on what apis are
to be used to provide rowset input.

Hi Anoop,

Requested Aruna to provide rowwise rowset example we use in our regressions. Also regarding the invalid text being return in error messages... we need to add AppUnicodeType=utf8 in the config file:

[Trafodion]
   Description = Trafodion ODBC driver
   Driver = libtrafodbc_drvr64.so
   UsageCount = 1
   AppUnicodeType=utf8

You can sue columnwise rowset from the sample code in MSDN. Below is the full sample program I executed against Trafodion, there are a few syntax errors in the MDSN code snippet. Remember to change the datasource name in SQLConnect call.

Hope this helps.
-Aruna

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

#define DESC_LEN 51
#define ARRAY_SIZE 10

SQLUINTEGER PartIDArray[ARRAY_SIZE];
SQLCHAR DescArray[ARRAY_SIZE][DESC_LEN];
SQLREAL PriceArray[ARRAY_SIZE];
SQLLEN PartIDIndArray[ARRAY_SIZE], DescLenOrIndArray[ARRAY_SIZE], PriceIndArray[ARRAY_SIZE];

SQLCHAR * Statement = (SQLCHAR *)"INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)";
SQLUSMALLINT i, ParamStatusArray[ARRAY_SIZE];
SQLULEN ParamsProcessed;

SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLRETURN retcode;
SQLHSTMT hstmt = NULL, hstmt1 = NULL, hstmt2 = NULL;

void GetNewValues(SQLUINTEGER *PartID, SQLCHAR *Desc, SQLREAL *Price)
{
 *PartID = 1000;
 strcpy ((char *)Desc, "ABCDE");
 *Price = 123.45;
 return;
}

int main(int argc, char *argv[])
{
 retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);

 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

 retcode = SQLConnect(hdbc, (SQLCHAR*) "oc", SQL_NTS, (SQLCHAR*) "xyz", SQL_NTS, (SQLCHAR*)"abc", SQL_NTS);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLConnect Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
     SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
    do
     {
         ret = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, ++i, state, &native, text, sizeof(text), &len );
         if (SQL_SUCCEEDED(ret))
          printf("%s:%ld:%ld:%s\n", state, i, native, text);
  }
      while( ret == SQL_SUCCESS );
  exit(0);
 }

 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLAllocHandle hstmt Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
     SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
    do
     {
         ret = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, ++i, state, &native, text, sizeof(text), &len );
         if (SQL_SUCCEEDED(ret))
          printf("%s:%ld:%ld:%s\n", state, i, native, text);
  }
     while( ret == SQL_SUCCESS );
  exit(0);
 }

 memset(DescLenOrIndArray, 0, sizeof(DescLenOrIndArray));
 memset(PartIDIndArray, 0, sizeof(PartIDIndArray));
 memset(PriceIndArray, 0, sizeof(PriceIndArray));

 // Set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to use column-wise binding.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0);

 // Specify the number of elements in each parameter array.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (void*)ARRAY_SIZE, 0);

 // Specify an array in which to return the status of each set of parameters.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);

 // Specify an SQLUINTEGER value in which to return the number of sets of parameters processed.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);

 // Bind the parameters in column-wise fashion.
 SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0, PartIDArray, 0, PartIDIndArray);
 SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0, DescArray, DESC_LEN, DescLenOrIndArray);
 SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0, PriceArray, 0, PriceIndArray);

 // Set part ID, description, and price.
 for (i = 0; i < ARRAY_SIZE; i++) {
  GetNewValues(&PartIDArray[i], DescArray[i], &PriceArray[i]);
  PartIDIndArray[i] = 0;
  DescLenOrIndArray[i] = SQL_NTS;
  PriceIndArray[i] = 0;
 }

 // Execute the statement.
 retcode = SQLExecDirect(hstmt, Statement, SQL_NTS);
 printf ("ParamsProcessed = %d\n", ParamsProcessed);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLExecDirect Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
  SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
  do
     {
   ret = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, ++i, state, &native, text, sizeof(text), &len );
   if (SQL_SUCCEEDED(ret))
    printf("%s:%ld:%ld:%s\n", state, i, native, text);
   }
   while( ret == SQL_SUCCESS );
   exit(0);
  }

 // Check to see which sets of parameters were processed successfully.
 for (i = 0; i < ParamsProcessed; i++) {
    printf("Parameter Set Status\n");
    printf("------------- -------------\n");
    switch (ParamStatusArray[i]) {
       case SQL_PARAM_SUCCESS:
       case SQL_PARAM_SUCCESS_WITH_INFO:
          printf("%13d Success\n", i);
          break;

       case SQL_PARAM_ERROR:
          printf("%13d Error\n", i);
          break;

       case SQL_PARAM_UNUSED:
          printf("%13d Not processed\n", i);
          break;

       case SQL_PARAM_DIAG_UNAVAILABLE:
   printf("%13d Unknown\n", i);
         break;
  }
 }
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
 SQLDisconnect(hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, henv);
 printf("done\n");
}

Sorry for the typo... corrected below:

[Trafodion]
    Description = Trafodion ODBC driver
    Driver = libtrafodbc_drvr64.so
    UsageCount = 1
    AppUnicodeType=utf16

That's interesting, thank you. If I compile that test program with e.g.
  g++ -g test.c -L/usr/lib64 -I/usr/include/odbc -ltrafodbc64 -o test
then it compiles and successfully inserts 10 rows into the table when run with
  LD_LIBRARY_PATH=/usr/lib64/ ./test

That's static linked against the trafodbc64 library though, and not trafodbc_drvr64.so according to
  LD_LIBRARY_PATH=/usr/lib64 ldd test
  ...
 libtrafodbc_l64.so => /usr/lib64/libtrafodbc_l64.so (0x00007f0619b5f000)

I'm still getting the '[rfdo DCDie]UAL OCNETT AASUC.TEASCAINSRIEETYI O ON REPY ' error in my unixodbc program if I use trafodbc_drvr64.so even with AppUnicodeType=utf16 in my odbcinst.conf.

[Trafodion]
    Description = Trafodion ODBC driver
    Driver = libtrafodbc_drvr64.so
    UsageCount = 1
    AppUnicodeType=utf16

Hi Jin,

The rowwise sample is working now with the code changed as below, but it has the same issue as columnwise, it does not work with select clause. I will file a bug for this, meanwhile please use the workaround suggested by Rao.

Regards.
-Aruna

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <iostream>
#include <sql.h>
#include <sqlext.h>
#include <time.h>
#include <sys/types.h>
#include <sys/timeb.h>

#define DESC_LEN 51
#define ARRAY_SIZE 1

typedef struct {
   SQLREAL Price;
   SQLUINTEGER PartID;
   SQLCHAR Desc[DESC_LEN];
   SQLINTEGER PriceInd;
   SQLINTEGER PartIDInd;
   SQLINTEGER DescLenOrInd;
} PartStruct;

PartStruct *PartArray;
SQLCHAR * Statement = (SQLCHAR *)"INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)";
SQLUSMALLINT i, ParamStatusArray[ARRAY_SIZE];
SQLULEN ParamsProcessed;

SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLRETURN retcode;
SQLHSTMT hstmt = NULL, hstmt1 = NULL, hstmt2 = NULL;

void GetNewValues(SQLUINTEGER *PartID, SQLCHAR *Desc, SQLREAL *Price)
{
 *PartID = 2000;
 strcpy ((char *)Desc, "ABCDE");
 *Price = 123.45;
 return;
}

int main(int argc, char *argv[])
{
 retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);

 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

 retcode = SQLConnect(hdbc, (SQLCHAR*) "oc", SQL_NTS, (SQLCHAR*) "xxx", SQL_NTS, (SQLCHAR*)"yyy", SQL_NTS);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLConnect Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
     SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
    do
     {
         ret = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, ++i, state, &native, text, sizeof(text), &len );
         if (SQL_SUCCEEDED(ret))
          printf("%s:%ld:%ld:%s\n", state, i, native, text);
  }
      while( ret == SQL_SUCCESS );
  exit(0);
 }

 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLAllocHandle hstmt Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
     SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
    do
     {
         ret = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, ++i, state, &native, text, sizeof(text), &len );
         if (SQL_SUCCEEDED(ret))
          printf("%s:%ld:%ld:%s\n", state, i, native, text);
  }
     while( ret == SQL_SUCCESS );
  exit(0);
 }

 PartArray = (PartStruct *)malloc (ARRAY_SIZE * sizeof (PartStruct));
    memset( PartArray, 0, ARRAY_SIZE * sizeof (PartStruct));

 // Set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to use column-wise binding.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE, (void*)sizeof(PartStruct), 0);

 // Specify the number of elements in each parameter array.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (void*)ARRAY_SIZE, 0);

 // Specify an array in which to return the status of each set of parameters.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);

 // Specify an SQLUINTEGER value in which to return the number of sets of parameters processed.
 SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);

 // Bind the parameters in row-wise fashion.
 SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0, &PartArray[0].PartID, 0, (SQLLEN*)PartArray[0].PartIDInd);
 SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0, PartArray[0].Desc, DESC_LEN, (SQLLEN*)PartArray[0].DescLenOrInd);
 SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0, &PartArray[0].Price, 0, (SQLLEN*)PartArray[0].PriceInd);

 // Set part ID, description, and price.
 for (i = 0; i < ARRAY_SIZE; i++) {
    GetNewValues(&PartArray[i].PartID, PartArray[i].Desc, &PartArray[i].Price);
    PartArray[i].PartIDInd = 0;
    PartArray[i].DescLenOrInd = SQL_NTS;
    PartArray[i].PriceInd = 0;
 }

 // Execute the statement.
 retcode = SQLExecDirect(hstmt, Statement, SQL_NTS);
 printf ("ParamsProcessed = %d\n", ParamsProcessed);
 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
 {
  printf("SQLExecDirect Failed\n");
  int i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
  SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;
  do
     {
   ret = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, ++i, state, &native, text, sizeof(text), &len );
   if (SQL_SUCCEEDED(ret))
    printf("%s:%ld:%ld:%s\n", state, i, native, text);
   }
   while( ret == SQL_SUCCESS );
   exit(0);
  }

 // Check to see which sets of parameters were processed successfully.
 for (i = 0; i < ParamsProcessed; i++) {
    printf("Parameter Set Status\n");
    printf("------------- -------------\n");
    switch (ParamStatusArray[i]) {
       case SQL_PARAM_SUCCESS:
       case SQL_PARAM_SUCCESS_WITH_INFO:
          printf("%13d Success\n", i);
          break;

       case SQL_PARAM_ERROR:
          printf("%13d Error\n", i);
          break;

       case SQL_PARAM_UNUSED:
          printf("%13d Not processed\n", i);
          break;

       case SQL_PARAM_DIAG_UNAVAILABLE:
   printf("%13d Unknown\n", i);
         break;
  }
 }
 free (PartArray);
 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
 SQLDisconnect(hdbc);
 SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
 SQLFreeHandle(SQL_HANDLE_ENV, henv);
 printf("done\n");
}

Copying the mail conversion:

=====================================================================================================

Hi Jin,

If this is an application that you are starting from the shell, then could you also try exporting the same (export AppUnicodeType=utf16) before starting the application. That way we can see the correct error message.

Daniel will be able to confirm regarding odbcinst.ini. One issue I did notice was the double entry for UsageCount - code should handle it though.

Regards
Arvind

Hi Arvind,

Thank you! Exporting it as an environmental variable gives a much clearer error:
  04/09/2014 18:43:00 [0] 70-Error: SQL Database Connection error: 08001, [unixODBC][Trafodion ODBC Driver] UNABLE TO CONNECT TO DATA SOURCE. THE ASSOCIATION SERVICE ENTRY IS NOT FOUND OR EMPTY.

Once that was done, copying my /etc/odbc/TRAFDSN to /etc/odbc.ini (was previously blank) seems to make general SELECTs and INSERTs work with 'libtrafodbc_drvr64.so'.

Is it possible to do SELECT to get the SYSKEY from an INSERT when using multiple values in ODBC? I tried experimenting with the Trafodion regression test code; changed it to use char * instead of integers to make it more similar to my odbc test case.

With these changes, the original
  INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?) still worked, but
  select SYSKEY from (INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)) x didn't work.

Thanks,
Jin

=====================================================================================================

Thank you, adding AppUnicodeType=utf16 into the [ODBC] section of odbc.ini file does make it work without having to set it as an environment variable.

Both 'Driver' or 'Driver64' in /etc/odbcinst.ini seem to work once AppUnicodeType is added. Using Driver64 alone without the [ODBC] AppUnicodeType=utf16 in /etc/odbc.ini doesn't work, so I'm not sure what difference it makes.

I assume this AppUnicodeType is a universal setting which affects all ODBC programs? To see if it was possible to scope it to just that one driver, I tried moving it to just the odbcinst.ini [Trafodion] section, or just the ODBC app connection string, or just the odbc.ini [Default_Datasource] section, but these three give the same error.

My current setup now has the ODBC app connection string:
  ConnectionString=Driver=Trafodion;DSN=Default_DataSource;UID=trafodion;PWD=traf123;
and /etc/odbc.ini :
  [ODBC]
  traceFlags = ERROR
  TraceStart = 0
  TraceFile = tracefile.log
  MaxTraceFileSize = 1024
  AppUnicodeType=utf16

  [Default_DataSource]
  Description = Default Data Source
  Catalog = TRAFODION
  Schema = SEABASE
  DataLang = 0
  FetchBufferSize = SYSTEM_DEFAULT
  Server = TCP:hadoop-trafodion2:37800
  SQL_ATTR_CONNECTION_TIMEOUT = SYSTEM_DEFAULT
  SQL_LOGIN_TIMEOUT = SYSTEM_DEFAULT
  SQL_QUERY_TIMEOUT = NO_TIMEOUT
and /etc/odbcinst.ini:
  [Trafodion]
  Description = Trafodion ODBC driver
  Driver64 = libtrafodbc_drvr64.so
  UsageCount = 1
  Debug = 1
  CommLog = 1

Thanks,
Jin

From Daniel:

Could you try to put AppUnicodeType=utf16 into [ODBC] section of odbc.ini file?

Meanwhile, change Driver = libtrafodbc_drvr64.so to Driver64 = libtrafodbc_drvr64.so

=====================================================================================================
SYSKEY conversation:
=====================================================================================================

To get a clusterwide unique number id, one can do:
  select unique_id() from (values(1)) x(a); This will return a 16 byte long global unique number.

This statement is evaluated in memory and doesn't do any disk or message IO.
One can prepare it once and then execute it whenever a unique number is needed.

Using this function instead of a SYSKEY would let you create a table with user primary key instead of a table with SYSKEY as part of the primary key. It will have better performance.

Once the rowset issue is fixed, you can use this function instead of SYSKEY in your query to get an array of unique numbers after insert is done.

Until then, you can execute the unique_id query with a values clause containing N values where N is equal to the number of rowset entries.
 something like:
    select unique_id() from (values (0), (0), (0)) x;

To look at what this unique_id looks like, one can do:
  select converttohex(unique_id()) from (values(1)) x(a); This will convert each byte into a hex number.

As you pointed out, we are adding support for sequence numbers and identity columns but that is not part of our product yet.

let us know if this works out for you.

anoop

Just as background, the reason for the SELECT SYSKEY is to get dynamically generated identifiers for the rows which have just been inserted, since I need some kind of unique identifier for them. This was the closest thing I could find to 'identity columns' at the time A ticket was recently added to add identity column support at https://blueprints.launchpad.net/trafodion/+spec/identity-column , which sounds useful; in that case we'd want to return the autogenerated values of the identity columns instead.

Thanks,
Jin

Hi,

Aruna, This should work... The one we DON'T support is multiple result sets like "select * from t1 where c1 = ?", where you give array values for c1. Looks like server is rejecting when it sees the statement as SELECT with array of rows... we need to investigate the code. Can you file a RFE for this one...

Jin, till we fix it you may have to do insert followed by select statements separately.

Thanks
Rao

Hi Jin,

Rao/Arvind can weigh in here, but I don’t think you can do a rowset operation with a SELECT stmt, will have to be singleton operation.

Regards.
-Aruna

Hi Aruna,

The error response it gave was:
  tungj@tungj-notebook:~/trafodion/test_sample_rao$ cd ~/trafodion/test_sample_rao; LD_LIBRARY_PATH=/usr/lib64/ ./test
  ParamsProcessed = 3
  Parameter Set Status
  ------------- -------------
              0 Error
  Parameter Set Status
  ------------- -------------
              1 Error
  Parameter Set Status
  ------------- -------------
              2 Error
  done

This was with the 'select SYSKEY from (INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)) x' query. I also checked that there weren't any new rows added to the 'Parts' table in `trafci` using 'SELECT * from Parts'.

For comparison, when using the 'INSERT INTO Parts (PartID, Description, Price) VALUES (?, ?, ?)' query, it succeeds with
  tungj@tungj-notebook:~/trafodion/test_sample_rao$ cd ~/trafodion/test_sample_rao; LD_LIBRARY_PATH=/usr/lib64/ ./testParamsProcessed = 3
  Parameter Set Status
  ------------- -------------
              0 Success
  Parameter Set Status
  ------------- -------------
              1 Success
  Parameter Set Status
  ------------- -------------
              2 Success
  done
and the documents do appear in the 'Parts' table in `trafci`.

I've not tried it in ODBC, but multi-value static INSERTs with SELECT do work in `trafci` e.g.
  SELECT SYSKEY FROM (INSERT INTO event (docuid, typeid, sourceid, "timestamp") VALUES ('2', 2, 55212861821880210, 2), ('3', 3, 55212861821880210, 3)) y ; from the initial question at https://answers.launchpad.net/trafodion/+question/253796 .

Thanks,
Jin

Hi Jin,

What error did the select syskey stmt return? I was able to execute it successfully from odbctest with static values.

Regards.
-Aruna