Re: SQLBulkOperations - Mailing list pgsql-odbc

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