Re: UPDATE with data at exec and CURRENT OF question - Mailing list pgsql-odbc

From lothar.behrens@lollisoft.de
Subject Re: UPDATE with data at exec and CURRENT OF question
Date
Msg-id 1186645291.049698.75020@g4g2000hsf.googlegroups.com
Whole thread Raw
In response to Re: UPDATE with data at exec and CURRENT OF question  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-odbc
Tom Lane schrieb:
> "lothar.behrens@lollisoft.de" <lothar.behrens@lollisoft.de> writes:
> > UPDATE "Kontakte" set "Notiz" = ? WHERE CURRENT OF SQL_CUR0x1321f670
>
> > Does PostgreSQL not support this syntax ?
>
> No.
>
> 8.3 will, FWIW.

So then I tried the following code, but I get SQLSTATE=01001 and not
the expected retval=SQL_NEED_DATA.

I tried to use SQL_FETCH_ABSOLUTE to the current cursor position, but
that seems not to help.

What's wrong ?

Thanks, Lothar

#define LB_BLOCKSIZE 10 // Forcing usage of SQLPutData

       // hstmt is initialized
    SQLRETURN     rc, retcode;
    SQLCHAR       BinaryPtr[LB_BLOCKSIZE];
    void*          tempBuffer;
    long          remainingsize;
    SQLINTEGER    BinaryLenOrIndCurrentOf;
    SQLINTEGER    BinaryLenOrInd;

    retcode = SQLAllocStmt(hdbc, &hupdatestmt); /* Statement handle */
    retcode = SQLSetStmtOption(hupdatestmt, SQL_ATTR_CONCURRENCY,
SQL_CONCUR_ROWVER);
    retcode = SQLSetStmtOption(hupdatestmt, SQL_CURSOR_TYPE,
SQL_CURSOR_KEYSET_DRIVEN);

       value = "Hallo duda."; // 12 characters including 0

    remainingsize = 12;
    BinaryLenOrInd = 12;

    BinaryLenOrIndCurrentOf = strlen("SQL_CURS0x012345678")+1;

    rc = SQLBindCol(hstmt, column, SQL_C_BINARY, (void *)BinaryPtr, 0,
&BinaryLenOrIndCurrentOf);
    memcpy(BinaryPtr, cursorname, BinaryLenOrIndCurrentOf);

    retcode = SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);
    retcode = SQLBindCol(hstmt, column, SQL_C_BINARY, NULL, 0, 0);

       // Force to reread current cursor data
       retcode = SQLExtendedFetch(hstmt, SQL_FETCH_ABSOLUTE, pos,
&RowsFetched, &RowStat[0]);

       tempBuffer = value;
    BinaryLenOrInd = 12;
    remainingsize = 12;

    if (value->getSize() <= LB_BLOCKSIZE) {
        memcpy(BinaryPtr, tempBuffer, value->getSize());
    } else {
        memcpy(BinaryPtr, tempBuffer, LB_BLOCKSIZE);
    }


    char* update_query = "UPDATE \"Kontakte\" SET \"Note\" = ? WHERE
\"Note\" LIKE 'SQL_CURS0x012345678%'";

    retcode = SQLPrepare(hupdatestmt, update_query, SQL_NTS);

    retcode = SQLBindParameter(hupdatestmt, 1, SQL_PARAM_INPUT,
                  SQL_C_BINARY, SQL_LONGVARBINARY,
                  0, 0, (SQLPOINTER) &BinaryPtr, 0, &BinaryLenOrInd);

    retcode = SQLExecute(hupdatestmt);

    long iteration = 0;

    if ((retcode != SQL_SUCCESS) && (retcode != SQL_NEED_DATA)) {
        printf("Execute query failed.\n"); // <<== Happens (SQLSTATE=01001)
    }

    if (retcode == SQL_NEED_DATA)
    {
        retcode = SQLParamData(hupdatestmt, (void **)  &BinaryPtr);
        while(retcode == SQL_NEED_DATA)
        {
            tempBuffer += LB_BLOCKSIZE;
            remainingsize -= LB_BLOCKSIZE;

            if (remainingsize <= LB_BLOCKSIZE) memcpy(BinaryPtr, tempBuffer,
remainingsize);
            else memcpy(BinaryPtr, tempBuffer, LB_BLOCKSIZE);

            retcode = SQLPutData(hupdatestmt, BinaryPtr, SQL_NTS);
            retcode = SQLParamData(hupdatestmt, (void **)  &BinaryPtr);
        }
    }

    SQLFreeStmt(hupdatestmt, SQL_DROP);


pgsql-odbc by date:

Previous
From: Paul Lambert
Date:
Subject: Re: ODBC driver adding extra characters to table names.
Next
From: "Hiroshi Saito"
Date:
Subject: Re: ODBC driver adding extra characters to table names.