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

Re: UPDATE with data at exec and CURRENT OF question

From: "lothar(dot)behrens(at)lollisoft(dot)de" <lothar(dot)behrens(at)lollisoft(dot)de>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: UPDATE with data at exec and CURRENT OF question
Date: 2007-08-09 11:50:43
Message-ID: 1186660243.064117.146140@57g2000hsv.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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;
}


In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi SaitoDate: 2007-08-09 14:18:40
Subject: Re: UPDATE with data at exec and CURRENT OF question
Previous:From: Hiroshi SaitoDate: 2007-08-09 08:43:06
Subject: Re: UPDATE with data at exec and CURRENT OF question

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