Re: UPDATE with data at exec and CURRENT OF question

Hi.

Sorry, very late reaction.

First.
> What version is capable of data_at_exec using SQLSetPos and also
> support full cursor support ?
> (not only forward cursors)
>
> I am still using 07.03.0200 on Mac OS X. I didn't get compiled the
> 07.03.0260 version due to missing
> pg_config.

Can't you use the series of 08.02.xx, either?
I think It should be cleared if there are some problems.

>
> Tom Lane schrieb:
>> "lothar(dot)behrens(at)lollisoft(dot)de" <lothar(dot)behrens(at)lollisoft(dot)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.

Yes , It is the function supported.

>
> What's wrong ?

However, I can't immediately check the following code...
Slight time is needed. I appreciate your perseverance.

Regards,
Hiroshi Saito

>
> 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);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org