Thread: SQLBulkOperations
Is there a problem with the SQLBulkOperations function? When I call SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does not support this function". I'm probably missing something simple. Help please? John
On Fri, Jun 6, 2014 at 12:32 PM, John C. Frickson <john@frickson.us> wrote: > Is there a problem with the SQLBulkOperations function? When I call > SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does > not support this function". > > I'm probably missing something simple. Help please? What is the version of Postgres ODBC and ODBC lib used? the OS? I am seeing in the code that SQLBulkOperations is used if odbc version protocol is newer than 0x0300. -- Michael
On Fri, 2014-06-06 at 13:37 +0900, Michael Paquier wrote: > On Fri, Jun 6, 2014 at 12:32 PM, John C. Frickson <john@frickson.us> wrote: > > Is there a problem with the SQLBulkOperations function? When I call > > SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does > > not support this function". > > > > I'm probably missing something simple. Help please? > What is the version of Postgres ODBC and ODBC lib used? the OS? I am > seeing in the code that SQLBulkOperations is used if odbc version > protocol is newer than 0x0300. Versions: OpenSUSE 13.1 x86_64 PostgreSQL 9.2.7 psqlODBC 08.03.0200 unixODBC 2.3.1
How are you setting UpdatableCursors option? You have to set the option=1 when you use SQLBulkOperations(). regards, Hiroshi Inoue (2014/06/06 20:57), John C. Frickson wrote: > On Fri, 2014-06-06 at 13:37 +0900, Michael Paquier wrote: >> On Fri, Jun 6, 2014 at 12:32 PM, John C. Frickson <john@frickson.us> wrote: >>> Is there a problem with the SQLBulkOperations function? When I call >>> SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does >>> not support this function". >>> >>> I'm probably missing something simple. Help please? >> What is the version of Postgres ODBC and ODBC lib used? the OS? I am >> seeing in the code that SQLBulkOperations is used if odbc version >> protocol is newer than 0x0300. > > Versions: > > OpenSUSE 13.1 x86_64 > PostgreSQL 9.2.7 > psqlODBC 08.03.0200 > unixODBC 2.3.1
/etc/unixODBC/odbcinst.ini: [PSQL] Description = PostgreSQL Driver64 = /usr/lib64/psqlodbcw.so UsageCount = 1 I set attributes like this: SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); SQLSetConnectAttr(hdbc,SQL_ATTR_ODBC_CURSORS,(SQLPOINTER)SQL_CUR_USE_ODBC,0); SQLSetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY, SQL_CONCUR_ROWVER, 0); SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN,0); Connection is DSN-less using connection string: DRIVER={PGSQL};SERVER=xx.xxx.xx.x;UID=user;PWD=pass;DATABASE=MyDB;UseServerSidePrepare=1; I'm doing a read on a record and binding the columns. If found, update some columns and call SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE) to do the update, which works fine. If the record is not found, I set the values of all bound columns, then call SQLBulkOperations(hstmt, SQL_ADD) at which point I get the error. The SQLSetPos for update had been failing until I changed the concurrency to SQL_CONCUR_ROWVER and type to SQL_CURSOR_KEYSET_DRIVEN. I'm not sure what you mean about setting "option=1"?? On Fri, 2014-06-06 at 21:13 +0900, Inoue, Hiroshi wrote: > How are you setting UpdatableCursors option? > You have to set the option=1 when you use SQLBulkOperations(). > > regards, > Hiroshi Inoue > > (2014/06/06 20:57), John C. Frickson wrote: > > On Fri, 2014-06-06 at 13:37 +0900, Michael Paquier wrote: > >> On Fri, Jun 6, 2014 at 12:32 PM, John C. Frickson <john@frickson.us> wrote: > >>> Is there a problem with the SQLBulkOperations function? When I call > >>> SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does > >>> not support this function". > >>> > >>> I'm probably missing something simple. Help please? > >> What is the version of Postgres ODBC and ODBC lib used? the OS? I am > >> seeing in the code that SQLBulkOperations is used if odbc version > >> protocol is newer than 0x0300. > > > > Versions: > > > > OpenSUSE 13.1 x86_64 > > PostgreSQL 9.2.7 > > psqlODBC 08.03.0200 > > unixODBC 2.3.1
Ok, I boiled my code down to the following sample. The comments show the errors that get printed. ----------8<----------8<----------8<----------8<----------8<----------8<---------- #include <stdio.h> #include <string.h> #include <sql.h> #include <sqlext.h> void printerr(int); SQLHANDLE henv, hdbc, hstmt; int main() { SQLLEN length_or_ind[3]; char connOut[2048], colVal[3][32]; const char *query = "SELECT * FROM pvsystem.loginfails WHERE userid='foo'"; const char *cstr = "DRIVER={PSQL};SERVER=server.frickson.pvt;UID=pvwebuser;" "PWD=Zjgg2WuNwO52XGVT;UseServerSidePrepare=1;DATABASE=PortVantage;"; int i, rc; colVal[0][0] = colVal[1][0] = colVal[2][0] = 0; rc = SQLSetEnvAttr(NULL, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, 0); if (rc) printerr(rc); rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); if (rc) printerr(rc); rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); if (rc) printerr(rc); rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); rc = SQLSetConnectAttr(hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_ODBC, 0); if (rc) printerr(rc); rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)cstr, strlen(cstr), (SQLCHAR*)connOut, 2048, NULL, SQL_DRIVER_NOPROMPT); if (rc) printerr(rc); rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if (rc) printerr(rc); rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, SQL_IS_INTEGER); if (rc) printerr(rc); rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0); // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed if (rc) printerr(rc); rc = SQLSetStmtAttr(hstmt, SQL_ATTR_MAX_ROWS, 0, SQL_IS_INTEGER); if (rc) printerr(rc); rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0); // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed if (rc) printerr(rc); rc = SQLPrepare(hstmt, (UCHAR*)query, strlen(query)); if (rc) printerr(rc); rc = SQLExecute(hstmt); if (rc) printerr(rc); for (i = 0; i < 3; ++i) { length_or_ind[i] = SQL_NTS; rc = SQLBindCol(hstmt, (SQLUSMALLINT)i+1, SQL_C_CHAR, colVal[i], 32, &length_or_ind[i]); if (rc) printerr(rc); } rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0); // rc=100 - Record Not Found if (rc) printerr(rc); strcpy(colVal[0], "foo"); length_or_ind[0] = 3; strcpy(colVal[1], "1"); length_or_ind[1] = 1; strcpy(colVal[2], "2014-06-06 10:15:00"); length_or_ind[2] = 19; rc = SQLBulkOperations(hstmt, SQL_ADD); // rc=-1 State=IM001 Native=0 Message=[unixODBC][Driver Manager]Driver does not support this function if (rc) printerr(rc); rc = 0; } void printerr(int rc) { SQLINTEGER native; SQLSMALLINT rec = 1, sz; char state[16], msg[1024]; if (rc == 100) puts("rc=100 - Record Not Found"); while (true) { if (SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, rec++, (SQLCHAR*)state, &native, (SQLCHAR*)msg, sizeof msg, &sz)) { break; } printf("rc=%d State=%s Native=%d Message=%s\n", rc, state, native, msg); } sz = 0; } ----------8<----------8<----------8<----------8<----------8<----------8<---------- On Fri, 2014-06-06 at 07:55 -0500, John C. Frickson wrote: > /etc/unixODBC/odbcinst.ini: > [PSQL] > Description = PostgreSQL > Driver64 = /usr/lib64/psqlodbcw.so > UsageCount = 1 > > I set attributes like this: > > SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); > SQLSetConnectAttr(hdbc,SQL_ATTR_ODBC_CURSORS,(SQLPOINTER)SQL_CUR_USE_ODBC,0); > SQLSetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY, SQL_CONCUR_ROWVER, 0); > SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN,0); > > Connection is DSN-less using connection string: > DRIVER={PGSQL};SERVER=xx.xxx.xx.x;UID=user;PWD=pass;DATABASE=MyDB;UseServerSidePrepare=1; > > I'm doing a read on a record and binding the columns. If found, update > some columns and call > SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE) > to do the update, which works fine. > > If the record is not found, I set the values of all bound columns, > then call > SQLBulkOperations(hstmt, SQL_ADD) > at which point I get the error. > > The SQLSetPos for update had been failing until I changed the > concurrency to SQL_CONCUR_ROWVER and type to SQL_CURSOR_KEYSET_DRIVEN. > > I'm not sure what you mean about setting "option=1"?? > > > > On Fri, 2014-06-06 at 21:13 +0900, Inoue, Hiroshi wrote: > > How are you setting UpdatableCursors option? > > You have to set the option=1 when you use SQLBulkOperations(). > > > > regards, > > Hiroshi Inoue > > > > (2014/06/06 20:57), John C. Frickson wrote: > > > On Fri, 2014-06-06 at 13:37 +0900, Michael Paquier wrote: > > >> On Fri, Jun 6, 2014 at 12:32 PM, John C. Frickson <john@frickson.us> wrote: > > >>> Is there a problem with the SQLBulkOperations function? When I call > > >>> SQLBulkOperations(hstmt, SQL_ADD) I get an error: "[IM001] Driver does > > >>> not support this function". > > >>> > > >>> I'm probably missing something simple. Help please? > > >> What is the version of Postgres ODBC and ODBC lib used? the OS? I am > > >> seeing in the code that SQLBulkOperations is used if odbc version > > >> protocol is newer than 0x0300. > > > > > > Versions: > > > > > > OpenSUSE 13.1 x86_64 > > > PostgreSQL 9.2.7 > > > psqlODBC 08.03.0200 > > > unixODBC 2.3.1
(2014/06/06 21:55), John C. Frickson wrote: > /etc/unixODBC/odbcinst.ini: > [PSQL] > Description = PostgreSQL > Driver64 = /usr/lib64/psqlodbcw.so > UsageCount = 1 > > I set attributes like this: > > SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); > SQLSetConnectAttr(hdbc,SQL_ATTR_ODBC_CURSORS,(SQLPOINTER)SQL_CUR_USE_ODBC,0); > SQLSetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY, SQL_CONCUR_ROWVER, 0); > SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN,0); > > Connection is DSN-less using connection string: > DRIVER={PGSQL};SERVER=xx.xxx.xx.x;UID=user;PWD=pass;DATABASE=MyDB;UseServerSidePrepare=1; Please try to add UpdatabelCursors=1 to your connection string. BTW is the version of the driver 8.3.0200 not 9.3.0200? regards, Hiroshi Inoue
On Sat, 2014-06-07 at 06:13 +0900, Inoue, Hiroshi wrote: > > (2014/06/06 21:55), John C. Frickson wrote: > > /etc/unixODBC/odbcinst.ini: > > [PSQL] > > Description = PostgreSQL > > Driver64 = /usr/lib64/psqlodbcw.so > > UsageCount = 1 > > > > I set attributes like this: > > > > SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); > > SQLSetConnectAttr(hdbc,SQL_ATTR_ODBC_CURSORS,(SQLPOINTER)SQL_CUR_USE_ODBC,0); > > SQLSetStmtAttr(hstmt,SQL_ATTR_CONCURRENCY, SQL_CONCUR_ROWVER, 0); > > SQLSetStmtAttr(hstmt,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN,0); > > > > Connection is DSN-less using connection string: > > DRIVER={PGSQL};SERVER=xx.xxx.xx.x;UID=user;PWD=pass;DATABASE=MyDB;UseServerSidePrepare=1; > > Please try to add UpdatabelCursors=1 to your connection string. > BTW is the version of the driver 8.3.0200 not 9.3.0200? > > regards, > Hiroshi Inoue Added "UpdatableCursors=1" to the connection string. No difference. Yes, OpenSUSE has 8.3.0200. Why? I don't know. I downloaded, compiled and installed version 9.2.0100. Still get the error.
(2014/06/07 1:14), John C. Frickson wrote: > Ok, I boiled my code down to the following sample. The comments show > the errors that get printed. > > ----------8<----------8<----------8<----------8<----------8<----------8<---------- > #include <stdio.h> > #include <string.h> > #include <sql.h> > #include <sqlext.h> > > void printerr(int); > > SQLHANDLE henv, hdbc, hstmt; > > int main() > { > SQLLEN length_or_ind[3]; > char connOut[2048], colVal[3][32]; > const char *query = "SELECT * FROM pvsystem.loginfails WHERE userid='foo'"; > const char *cstr = "DRIVER={PSQL};SERVER=server.frickson.pvt;UID=pvwebuser;" > "PWD=Zjgg2WuNwO52XGVT;UseServerSidePrepare=1;DATABASE=PortVantage;"; > int i, rc; > > colVal[0][0] = colVal[1][0] = colVal[2][0] = 0; > > rc = SQLSetEnvAttr(NULL, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, 0); > if (rc) printerr(rc); > rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); > if (rc) printerr(rc); > rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); > if (rc) printerr(rc); > rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); > rc = SQLSetConnectAttr(hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_ODBC, 0); > if (rc) printerr(rc); Please try to skip the above SQLSetConnectAttr operation. > rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)cstr, strlen(cstr), > (SQLCHAR*)connOut, 2048, NULL, SQL_DRIVER_NOPROMPT); > if (rc) printerr(rc); > rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); > if (rc) printerr(rc); > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, SQL_IS_INTEGER); > if (rc) printerr(rc); > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0); > // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed > if (rc) printerr(rc); > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_MAX_ROWS, 0, SQL_IS_INTEGER); > if (rc) printerr(rc); > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0); > // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed > if (rc) printerr(rc); > rc = SQLPrepare(hstmt, (UCHAR*)query, strlen(query)); > if (rc) printerr(rc); > rc = SQLExecute(hstmt); > if (rc) printerr(rc); > for (i = 0; i < 3; ++i) { > length_or_ind[i] = SQL_NTS; > rc = SQLBindCol(hstmt, (SQLUSMALLINT)i+1, SQL_C_CHAR, colVal[i], 32, &length_or_ind[i]); > if (rc) printerr(rc); > } > rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0); > // rc=100 - Record Not Found > if (rc) printerr(rc); > strcpy(colVal[0], "foo"); > length_or_ind[0] = 3; > strcpy(colVal[1], "1"); > length_or_ind[1] = 1; > strcpy(colVal[2], "2014-06-06 10:15:00"); > length_or_ind[2] = 19; > rc = SQLBulkOperations(hstmt, SQL_ADD); > // rc=-1 State=IM001 Native=0 Message=[unixODBC][Driver Manager]Driver does not support this function > if (rc) printerr(rc); > rc = 0; > }
On Sat, 2014-06-07 at 14:59 +0900, Hiroshi Inoue wrote: > (2014/06/07 1:14), John C. Frickson wrote: > > Ok, I boiled my code down to the following sample. The comments show > > the errors that get printed. > > > > ----------8<----------8<----------8<----------8<----------8<----------8<---------- > > #include <stdio.h> > > #include <string.h> > > #include <sql.h> > > #include <sqlext.h> > > > > void printerr(int); > > > > SQLHANDLE henv, hdbc, hstmt; > > > > int main() > > { > > SQLLEN length_or_ind[3]; > > char connOut[2048], colVal[3][32]; > > const char *query = "SELECT * FROM pvsystem.loginfails WHERE userid='foo'"; > > const char *cstr = "DRIVER={PSQL};SERVER=server.frickson.pvt;UID=pvwebuser;" > > "PWD=Zjgg2WuNwO52XGVT;UseServerSidePrepare=1;DATABASE=PortVantage;"; > > int i, rc; > > > > colVal[0][0] = colVal[1][0] = colVal[2][0] = 0; > > > > rc = SQLSetEnvAttr(NULL, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV, 0); > > if (rc) printerr(rc); > > rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); > > if (rc) printerr(rc); > > rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0); > > if (rc) printerr(rc); > > rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); > > > rc = SQLSetConnectAttr(hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_ODBC, 0); > > if (rc) printerr(rc); > > Please try to skip the above SQLSetConnectAttr operation. > > > rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)cstr, strlen(cstr), > > (SQLCHAR*)connOut, 2048, NULL, SQL_DRIVER_NOPROMPT); > > if (rc) printerr(rc); > > rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); > > if (rc) printerr(rc); > > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, SQL_IS_INTEGER); > > if (rc) printerr(rc); > > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0); > > // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed > > if (rc) printerr(rc); > > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_MAX_ROWS, 0, SQL_IS_INTEGER); > > if (rc) printerr(rc); > > rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)SQL_CONCUR_ROWVER, 0); > > // rc=1 State=01S02 Native=0 Message=[unixODBC][Driver Manager]Option value changed > > if (rc) printerr(rc); > > rc = SQLPrepare(hstmt, (UCHAR*)query, strlen(query)); > > if (rc) printerr(rc); > > rc = SQLExecute(hstmt); > > if (rc) printerr(rc); > > for (i = 0; i < 3; ++i) { > > length_or_ind[i] = SQL_NTS; > > rc = SQLBindCol(hstmt, (SQLUSMALLINT)i+1, SQL_C_CHAR, colVal[i], 32, &length_or_ind[i]); > > if (rc) printerr(rc); > > } > > rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0); > > // rc=100 - Record Not Found > > if (rc) printerr(rc); > > strcpy(colVal[0], "foo"); > > length_or_ind[0] = 3; > > strcpy(colVal[1], "1"); > > length_or_ind[1] = 1; > > strcpy(colVal[2], "2014-06-06 10:15:00"); > > length_or_ind[2] = 19; > > rc = SQLBulkOperations(hstmt, SQL_ADD); > > // rc=-1 State=IM001 Native=0 Message=[unixODBC][Driver Manager]Driver does not support this function > > if (rc) printerr(rc); > > rc = 0; > > } > It looks like that's going to work. I'll do some testing and let you know if I run into any problems. Thanks, Hiroshi!