Skip site navigation (1) Skip section navigation (2)

Re: UPDATE with data at exec and CURRENT OF question

From: "Hiroshi Saito" <z-saito(at)guitar(dot)ocn(dot)ne(dot)jp>
To: <lothar(dot)behrens(at)lollisoft(dot)de>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: UPDATE with data at exec and CURRENT OF question
Date: 2007-08-09 08:43:06
Message-ID: 048b01c7da61$4eaf6970$c601a8c0@HP22720319231 (view raw or flat)
Thread:
Lists: pgsql-odbc
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

In response to

Responses

pgsql-odbc by date

Next:From: lothar.behrens@lollisoft.deDate: 2007-08-09 11:50:43
Subject: Re: UPDATE with data at exec and CURRENT OF question
Previous:From: Hiroshi SaitoDate: 2007-08-09 08:02:51
Subject: Re: ODBC driver adding extra characters to table names.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group