Обсуждение: 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!