Re: UPDATE with data at exec and CURRENT OF question

["Hiroshi Saito" <z-saito(at)guitar(dot)ocn(dot)ne(dot)jp>]

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