Re: UPDATE with data at exec and CURRENT OF question

On 9 Aug., 10:43, z-sa(dot)(dot)(dot)(at)guitar(dot)ocn(dot)ne(dot)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 <cursor name>, 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;
}