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 07:41:31
Message-ID: 1186645291.049698.75020@g4g2000hsf.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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.

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);


In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi SaitoDate: 2007-08-09 08:02:51
Subject: Re: ODBC driver adding extra characters to table names.
Previous:From: Paul LambertDate: 2007-08-09 06:57:40
Subject: Re: ODBC driver adding extra characters to table names.

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