UPDATE with data at exec and CURRENT OF question

Hi,

after all my tries I ran into trouble with this statement:

UPDATE "Kontakte" set "Notiz" = ? WHERE CURRENT OF SQL_CUR0x1321f670

I see that my parameter has been passed. And the driver has build up
the query. But I do not understand, why
there is a syntax error near the 'OF' keyword.

Does PostgreSQL not support this syntax ?

Thanks, Lothar

Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=68, stmt='UPDATE "Kontakte" set "Notiz" = ? WHERE
CURRENT OF SQL_CUR0x1321f670'
ResolveOneParam: from(fcType)=-2, to(fSqlType)=-4
SQL_VARBINARY: about to call convert_to_pgbinary, used = 4
convert_to_pgbinary: in[0] = 76, L
convert_to_pgbinary: in[1] = 97, a
convert_to_pgbinary: in[2] = 108, l
convert_to_pgbinary: in[3] = 97, a
convert_to_pgbinary: returning 4, out='Lala'
stmt_with_params = 'UPDATE "Kontakte" set "Notiz" = 'Lala' WHERE
CURRENT OF SQL_CUR0x1321f670'
it's NOT a select statement: stmt=321250656
send_query(): conn=25601024, query='UPDATE "Kontakte" set "Notiz" =
'Lala' WHERE CURRENT OF SQL_CUR0x1321f670'
send_query: done sending query


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: UPDATE with data at exec and CURRENT OF question

"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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: UPDATE with data at exec and CURRENT OF question

Tom Lane schrieb:
> "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);


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.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.

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: UPDATE with data at exec and CURRENT OF question

On 9 Aug., 10:43, z-sa...@guitar.ocn.ne.jp ("Hiroshi Saito") wrote:
> Hi.
>
> Sorry, very late reaction.
> > 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.
>

My application and my ODBC wrapper class relates to full cursor
functionality (first,previous,next,last). So I only know about 7.3.2xx
drivers.

> > What's wrong ?
>
> However, I can't immediately check the following code...
> Slight time is needed. I appreciate your perseverance.
>

Now I have a better working code. It is able to put data in 1000 byte
pieces, but the driver
(SQLParamData(...)) does not indicate, that it needs more data. Also
the getBinaryData
function returns no more data, that the size of the buffer (here
5000).

I could increase the buffer to the buffer in my value object, but I
probably could not go
ahead the maximum of available memory.

So I tried all to put the data in pieces of, say, 1000 or 5000 bytes,
both functions do not
behave as expected from Microsoft documentation from here:

http://msdn2.microsoft.com/en-us/library/ms713824.aspx

I have tried to ignore SQL_NEED_DATA and have calculated a remaining
size to be handled
until it decreases to the piece size to put the rest. But then the
result behaves like storing rubbish.

The lb_I_BinaryData class is a container where I could append pieces
and I think it is unnesesary
to copy that code too.

I have tried all I can get from documentation. I have reached the end
of my brain :-)

Hope you will see any wrong API usage or parameters. But I assume,
there is a bug in
the 07.03.0200 driver.

Actually I do no more use CURRENT OF , because PostgreSQL
doesn't support
that syntax (at least in my old 7.4 database).

I do a trick by first bind the binary column and put a unique marker
to that column for later update
filtering. I hope the cursorname is unique. I probably have to make
this function thread save !

Sorry for the big code.

Thanks, Lothar

lb_I_BinaryData* LB_STDCALL lbQuery::getBinaryData(int column) {
// Declare a binary buffer to retrieve 5000 bytes of data at a time.
SQLCHAR BinaryPtr[5000];
SQLUINTEGER PartID;
SQLINTEGER PartIDInd, BinaryLenOrInd, NumBytes;
SQLRETURN rc, retcode;
SQLHSTMT hstmt_blob;

// Make an instance of my binarydata container class.
UAP_REQUEST(getModuleInstance(), lb_I_BinaryData, binarydata)

if (boundColumns != NULL) {
if (boundColumns->isBound(column)) {

// My log mechanism
_LOG << "Error: binary column " << column << "shouldn't be bound!"
LOG_
binarydata->append((void*) "", 1);

// Referencecounting. Avoid cleanup at this scope.
binarydata++;
return binarydata.getPtr();
} else {
while ((rc = SQLGetData(hstmt, column, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr), &BinaryLenOrInd)) != SQL_NO_DATA) {
NumBytes = (BinaryLenOrInd > 5000) || (BinaryLenOrInd ==
SQL_NO_TOTAL) ? 5000 : BinaryLenOrInd;
if (BinaryLenOrInd == SQL_NULL_DATA) {
binarydata->append("", 1);
binarydata++;
return binarydata.getPtr();
}
if (BinaryLenOrInd == 0) {
binarydata->append("", 1);
binarydata++;
return binarydata.getPtr();
}
binarydata->append(BinaryPtr, NumBytes-1);
}
}
}

binarydata->append((void*) "", 1);

binarydata++;
return binarydata.getPtr();
}

lb_I_BinaryData* LB_STDCALL lbQuery::getBinaryData(const char* column)
{
if (boundColumns != NULL) {
int i = boundColumns->getColumnIndex(column);
return getBinaryData(i);
}
_LOG << "lbQuery::getBinaryData('" << column << "') Error: No bound
columns!" LOG_
UAP_REQUEST(getModuleInstance(), lb_I_BinaryData, binary)
binary++;
binary->append("", 1);
return binary.getPtr();
}

lbErrCodes LB_STDCALL lbQuery::setBinaryData(int column,
lb_I_BinaryData* value) {
#define LB_BLOCKSIZE 1000
#define USE_CURRENT_OF

#ifdef USE_CURRENT_OF
#undef USE_CURRENT_OF
UAP_REQUEST(getModuleInstance(), lb_I_String, update_query)

SQLRETURN rc, retcode;
SQLCHAR* BinaryPtr;
SQLCHAR BinaryPtrCur[100];
long realBufferSize;
void* tempBuffer;
long remainingsize;
SQLINTEGER BinaryLenOrIndCurrentOf;
SQLINTEGER BinaryLenOrInd;
SQLINTEGER PutDataSize;

retcode = SQLAllocStmt(hdbc, &hupdatestmt); /* Statement handle */

if (retcode != SQL_SUCCESS)
{
_LOG << "lbDatabase::getQuery() failed due to statement
allocation." LOG_
return ERR_DB_ALLOCSTATEMENT;
}

retcode = SQLSetStmtOption(hupdatestmt, SQL_ATTR_CONCURRENCY,
SQL_CONCUR_ROWVER);

retcode = SQLSetStmtOption(hupdatestmt, SQL_CURSOR_TYPE,
SQL_CURSOR_KEYSET_DRIVEN);

if (retcode == SQL_SUCCESS_WITH_INFO) {
_LOG << "lbDatabase::getQuery() failed due to setting cursor type."
LOG_
} else
if (retcode != SQL_SUCCESS)
{
_LOG << "lbDatabase::getQuery() failed due to setting
cursor type." LOG_
return ERR_DB_ALLOCSTATEMENT;
}

SQLINTEGER size = 1;

if (retcode != SQL_SUCCESS) {
_LOG << "lbDatabase::getQuery() failed due to set statement
attributes." LOG_
return ERR_DB_ALLOCSTATEMENT;
}

remainingsize = value->getSize();
BinaryLenOrInd = value->getSize();

BinaryLenOrIndCurrentOf = strlen(cursorname)+1;

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

update();

retcode = SQLBindCol(hstmt, column, SQL_C_BINARY, NULL, 0, 0);

tempBuffer = value->getData();
BinaryLenOrInd = value->getSize();
remainingsize = value->getSize();

*update_query = "UPDATE \"";
*update_query += getTableName(getColumnName(column));
*update_query += "\" SET \"";
*update_query += getColumnName(column);
*update_query += "\" = ? WHERE \"";
*update_query += getColumnName(column);
*update_query += "\" LIKE '";
*update_query += cursorname;
*update_query += "%'";

_LOG << "Prepare positioned BLOB update: '" << update_query-
>charrep() << "' with length of data = " << BinaryLenOrInd LOG_

retcode = SQLPrepare(hupdatestmt, update_query->charrep(), SQL_NTS);

if (retcode != SQL_SUCCESS) {
_LOG << "Preparing update statement failed." LOG_
}

if (remainingsize > LB_BLOCKSIZE) {
//BinaryLenOrInd = SQL_LEN_DATA_AT_EXEC(value->getSize());
BinaryLenOrInd = SQL_LEN_DATA_AT_EXEC(value->getSize());

realBufferSize = LB_BLOCKSIZE;
BinaryPtr = malloc(realBufferSize);


_LOG << "Call SQLBindParameter with a length indicator value of " <<
BinaryLenOrInd << "." LOG_

retcode = SQLBindParameter(hupdatestmt, 1, SQL_PARAM_INPUT,
SQL_C_BINARY, SQL_LONGVARBINARY,
value->getSize(), 0, (SQLPOINTER) 1, LB_BLOCKSIZE,
&BinaryLenOrInd);

} else {
realBufferSize = remainingsize;
BinaryLenOrInd = remainingsize;
BinaryPtr = malloc(remainingsize);
retcode = SQLBindParameter(hupdatestmt, 1, SQL_PARAM_INPUT,
SQL_C_BINARY, SQL_LONGVARBINARY,
0, 0, (SQLPOINTER) &BinaryPtr, BinaryLenOrInd,
&BinaryLenOrInd);
}


if (retcode != SQL_SUCCESS) {
_LOG << "Binding update parameter failed." LOG_
}

_LOG << "Executing positioned BLOB update: '" << update_query-
>charrep() << "' with length of data = " << BinaryLenOrInd LOG_

retcode = SQLSetPos(hstmt, 1, SQL_REFRESH, SQL_LOCK_NO_CHANGE);
//retcode = SQLSetPos(hupdatestmt, 1, SQL_REFRESH,
SQL_LOCK_NO_CHANGE);
retcode = SQLExecute(hupdatestmt);

long iteration = 0;

if ((retcode != SQL_SUCCESS) && (retcode != SQL_NEED_DATA)) {
_LOG << "Execute query failed." LOG_
//_dbError_STMT("Executing positioned BLOB update failed.",
hupdatestmt);
}

if (retcode == SQL_NEED_DATA)
{
SQLPOINTER putDataBuffer;
retcode = SQLParamData(hupdatestmt, (void **) &putDataBuffer);
while(retcode == SQL_NEED_DATA)
{
_LOG << "lbQuery::setBinaryData() Needs more data ..." <<
remainingsize LOG_
if (remainingsize <= realBufferSize) {
_LOG << "Copy lesser memory piece of " << remainingsize << "
bytes." LOG_
memcpy(BinaryPtr, tempBuffer, remainingsize);
PutDataSize = remainingsize;
retcode = SQLPutData(hupdatestmt, BinaryPtr, PutDataSize);
retcode = SQLParamData(hupdatestmt, (void **) &putDataBuffer);
tempBuffer += realBufferSize;
remainingsize -= realBufferSize;
} else {

//
************************************************************ ********************
// Force multible calls to SQLPutData and
SQLParamData doesn't help
//
************************************************************ ********************

// while (remainingsize > realBufferSize) {
_LOG << "Copy maximum memory piece of " << realBufferSize << "
bytes." LOG_
memcpy(BinaryPtr, tempBuffer, realBufferSize);
PutDataSize = realBufferSize;
retcode = SQLPutData(hupdatestmt, BinaryPtr, PutDataSize);
retcode = SQLParamData(hupdatestmt, (void **) &putDataBuffer);
tempBuffer += realBufferSize;
remainingsize -= realBufferSize;
// }
}
}

}
retcode = SQLSetPos(hstmt, 1, SQL_REFRESH, SQL_LOCK_NO_CHANGE);

SQLFreeStmt(hupdatestmt, SQL_DROP);
#endif

#ifdef USE_SETPOS
// Declare a binary buffer to retrieve 5000 bytes of data at a time.
SQLCHAR BinaryPtr[LB_BLOCKSIZE];
SQLUINTEGER PartID;
SQLINTEGER PartIDInd, BinaryLenOrInd, NumBytes;
SQLINTEGER longDataLen;
SQLRETURN rc, retcode;
SQLHSTMT hstmt_blob;

void* tempBuffer;
long remainingsize;

tempBuffer = value->getData();

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

remainingsize = value->getSize();

longDataLen = SQL_LEN_DATA_AT_EXEC(value->getSize());

rc = SQLBindCol(hstmt, column, SQL_C_BINARY, (void *)BinaryPtr, 0,
&longDataLen);

retcode = SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);

long iteration = 0;

if (retcode == SQL_NEED_DATA)
{
retcode = SQLParamData(hstmt, (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(hstmt, BinaryPtr, SQL_NTS);
/* check for error here */
retcode = SQLParamData(hstmt, (void **) &BinaryPtr);
}

}

rc = SQLBindCol(hstmt, column, SQL_C_BINARY, NULL, 0, 0);
#endif

return ERR_NONE;
}

lbErrCodes LB_STDCALL lbQuery::setBinaryData(const char* column,
lb_I_BinaryData* value) {
if (boundColumns != NULL) {
int i = boundColumns->getColumnIndex(column);
return setBinaryData(i, value);
}

return ERR_NONE;
}


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: UPDATE with data at exec and CURRENT OF question

Hi.

From:

> On 9 Aug., 10:43, z-sa...@guitar.ocn.ne.jp ("Hiroshi Saito") wrote:
>> Hi.
>>
>> Sorry, very late reaction.
>> > 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.
>>
>
> My application and my ODBC wrapper class relates to full cursor
> functionality (first,previous,next,last). So I only know about 7.3.2xx
> drivers.

Um, We have 08.02.xxx on the extension. Surely it had a time of falling
out by some confusion.... However, the current version supports it.
It should be corrected if there are some problems. and, support is
short-handed up to 07.x past versions.....I want to clear a problem
by the present version as much as possible.

>
>> > What's wrong ?
>>
>> However, I can't immediately check the following code...
>> Slight time is needed. I appreciate your perseverance.
>>
>
> Now I have a better working code. It is able to put data in 1000 byte
> pieces, but the driver
> (SQLParamData(...)) does not indicate, that it needs more data. Also
> the getBinaryData
> function returns no more data, that the size of the buffer (here
> 5000).
>
> I could increase the buffer to the buffer in my value object, but I
> probably could not go
> ahead the maximum of available memory.
>
> So I tried all to put the data in pieces of, say, 1000 or 5000 bytes,
> both functions do not
> behave as expected from Microsoft documentation from here:
>
> http://msdn2.microsoft.com/en-us/library/ms713824.aspx
>
> I have tried to ignore SQL_NEED_DATA and have calculated a remaining
> size to be handled
> until it decreases to the piece size to put the rest. But then the
> result behaves like storing rubbish.
>
> The lb_I_BinaryData class is a container where I could append pieces
> and I think it is unnesesary
> to copy that code too.
>
> I have tried all I can get from documentation. I have reached the end
> of my brain :-)
>
> Hope you will see any wrong API usage or parameters. But I assume,
> there is a bug in
> the 07.03.0200 driver.
>
> Actually I do no more use CURRENT OF , because PostgreSQL
> doesn't support
> that syntax (at least in my old 7.4 database).
>
> I do a trick by first bind the binary column and put a unique marker
> to that column for later update
> filtering. I hope the cursorname is unique. I probably have to make
> this function thread save !

Um, I want to separate problem. It is the right motion of a cursor action.
Then, data-column is divided and treated by chunk... Probably, we need
the test code. I will work by it... However, I want to solve it by 08.02.0402.

>
> Sorry for the big code.
>
> Thanks, Lothar

Ahh, sorry, It seems that can't reproduce for the environment problem.

P.S)
Please understand that I can't solve early like Inoue-san.

Regards,
Hiroshi Saito

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: UPDATE with data at exec and CURRENT OF question

I'll try my code on Windows next days with current versions of
database and driver.
I only had compile problems on Mac OS X using fink based postgreSQL
installation.

I think, I write a plain C version as for testing purposes and see
what happens with them.

Regards, Lothar


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: UPDATE with data at exec and CURRENT OF question

Hi.

From:

> I'll try my code on Windows next days with current versions of
> database and driver.

Ah thanks, It is the environment which can reproduce me.
Then, I am beginning to make a test code. There is likely to be a problem
of some except for SQL_FETCH_NEXT....

> I only had compile problems on Mac OS X using fink based postgreSQL
> installation.

Um, I do not have Mac OS X..
However, I think psqlODBC is used also that environment.

>
> I think, I write a plain C version as for testing purposes and see
> what happens with them.
>
> Regards, Lothar

Thanks!

P.S)
I'm the schedule of a weekend to vacation..
sorry in the delay of a response.

Regards,
Hiroshi Saito


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: UPDATE with data at exec and CURRENT OF question

"Hiroshi Saito" schrieb:
> Hi.
>
> Ah thanks, It is the environment which can reproduce me.
> Then, I am beginning to make a test code. There is likely to be a problem
> of some except for SQL_FETCH_NEXT....

I have created a small test application only with plain ODBC stuff. I
could reproduce
my problem also on Windows with MS SQL Server 2000 !

You could grab the code here:

http://lbdmf.cvs.sourceforge.net/lbdmf/CPP/RegressionTests/D atabase/Plain-ODBC-Test-Blob/test.cpp?revision=1.4&view=mark up

The relevant function is testBlobUpdate.

On PostgreSQL I did not yet have tested on actual drivers, but when
the MS SQL driver behaves similar, I suppose still an error in my
code.

Regards, Lothar

>
> Um, I do not have Mac OS X..
> However, I think psqlODBC is used also that environment.
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: UPDATE with data at exec and CURRENT OF question

Hi.

----- Original Message -----
From:

>
> "Hiroshi Saito" schrieb:
>> Hi.
>>
>> Ah thanks, It is the environment which can reproduce me.
>> Then, I am beginning to make a test code. There is likely to be a problem
>> of some except for SQL_FETCH_NEXT....
>
> I have created a small test application only with plain ODBC stuff. I
> could reproduce
> my problem also on Windows with MS SQL Server 2000 !
>
> You could grab the code here:
>
> http://lbdmf.cvs.sourceforge.net/lbdmf/CPP/RegressionTests/D atabase/Plain-ODBC-Test-Blob/test.cpp?revision=1.4&view=mark up
>
> The relevant function is testBlobUpdate.
>
> On PostgreSQL I did not yet have tested on actual drivers, but when
> the MS SQL driver behaves similar, I suppose still an error in my
> code.
>
> Regards, Lothar

Uga...Sorry, SQL_CURSOR_KEYSET_DRIVEN cannot be supported yet.:-(
Although I tried it as remembered, it is very difficult correction....
As for the present version, SQL_CURSOR_FORWARD_ONLY and
SQL_CURSOR_STATIC are supported.
To be sure, it needs to exist as TODO.!

Regards,
Hiroshi Saito


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: UPDATE with data at exec and CURRENT OF question

"Hiroshi Saito" schrieb:
> Hi.
>
> ----- Original Message -----
> From:
>
> >
> > "Hiroshi Saito" schrieb:
> >> Hi.
> >>
> >> Ah thanks, It is the environment which can reproduce me.
> >> Then, I am beginning to make a test code. There is likely to be a problem
> >> of some except for SQL_FETCH_NEXT....
> >
> > I have created a small test application only with plain ODBC stuff. I
> > could reproduce
> > my problem also on Windows with MS SQL Server 2000 !
> >
> > You could grab the code here:
> >
> > http://lbdmf.cvs.sourceforge.net/lbdmf/CPP/RegressionTests/D atabase/Plain-ODBC-Test-Blob/test.cpp?revision=1.4&view=mark up
> >
> > The relevant function is testBlobUpdate.
> >
> > On PostgreSQL I did not yet have tested on actual drivers, but when
> > the MS SQL driver behaves similar, I suppose still an error in my
> > code.
> >
> > Regards, Lothar
>
> Uga...Sorry, SQL_CURSOR_KEYSET_DRIVEN cannot be supported yet.:-(
> Although I tried it as remembered, it is very difficult correction....
> As for the present version, SQL_CURSOR_FORWARD_ONLY and
> SQL_CURSOR_STATIC are supported.
> To be sure, it needs to exist as TODO.!
>

I have solved it by binding the binary column as usual. Then I have
set the value of
the column to an unique value - maybe cursorname - and have updated
the database
with that value set.

Then I was able to use a different query (prepared) with a simple
where clause and this was successfull.

So this may be a solution for the TODO ?

I have figured out some problems doing this on MS SQL but this another
issue and propably
only a cast for the where clause because they do strikter type
checking.

Regards,
Lothar

> Regards,
> Hiroshi Saito
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: UPDATE with data at exec and CURRENT OF question

Hi.

From: "Lothar Behrens"

>
> "Hiroshi Saito" schrieb:
>> Hi.
>>
>> ----- Original Message -----
>> From:
>>
>> >
>> > "Hiroshi Saito" schrieb:
>> >> Hi.
>> >>
>> >> Ah thanks, It is the environment which can reproduce me.
>> >> Then, I am beginning to make a test code. There is likely to be a problem
>> >> of some except for SQL_FETCH_NEXT....
>> >
>> > I have created a small test application only with plain ODBC stuff. I
>> > could reproduce
>> > my problem also on Windows with MS SQL Server 2000 !
>> >
>> > You could grab the code here:
>> >
>> > http://lbdmf.cvs.sourceforge.net/lbdmf/CPP/RegressionTests/D atabase/Plain-ODBC-Test-Blob/test.cpp?revision=1.4&view=mark up
>> >
>> > The relevant function is testBlobUpdate.
>> >
>> > On PostgreSQL I did not yet have tested on actual drivers, but when
>> > the MS SQL driver behaves similar, I suppose still an error in my
>> > code.
>> >
>> > Regards, Lothar
>>
>> Uga...Sorry, SQL_CURSOR_KEYSET_DRIVEN cannot be supported yet.:-(
>> Although I tried it as remembered, it is very difficult correction....
>> As for the present version, SQL_CURSOR_FORWARD_ONLY and
>> SQL_CURSOR_STATIC are supported.
>> To be sure, it needs to exist as TODO.!
>>
>
> I have solved it by binding the binary column as usual. Then I have
> set the value of
> the column to an unique value - maybe cursorname - and have updated
> the database
> with that value set.
>
> Then I was able to use a different query (prepared) with a simple
> where clause and this was successfull.
>
> So this may be a solution for the TODO ?
>
> I have figured out some problems doing this on MS SQL but this another
> issue and propably
> only a cast for the where clause because they do strikter type
> checking.

Oh, Cool, It is great solution. I'm sorry in explanation having been lacking.
Sorry, my explanation is insufficient...SQL_CURSOR_KEYSET_DRIVEN
does not become effective together with DECLARE/FETCH options.
It can be used as the option of UPDATE cursor.
Correctly, it should have made it TODO to use with it.

Anyway, I'm sorry confused the user.

Thanks!

Regards,
Hiroshi Saito


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend