BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea

perhaps this helps

http://www.webservertalk.com/archive308-2007-3-1836413.html

Gevik,

The link didn't help because that is not the same problem as I have:

My problem is that it seems that in postgresql you can't use a parameter bound to BYTEA data in the WHERE clause of the SQL statement.

So in my case: "SELECT x FROM T WHERE BinData = ?" does not work (return code -1) if the parameter bound to the ? is of type BYTEA.

Vincent

From: pgdev@xs4all.nl
To: vincent_dhaene@hotmail.com; pgsql-bugs@postgresql.org
Subject: RE: [BUGS] BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
Date: Sun, 10 Feb 2008 23:09:16 +0100

perhaps this helps

http://www.webservertalk.com/archive308-2007-3-1836413.html

-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of vha
Sent: Sunday, February 10, 2008 7:55 PM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] BUG #3951: SELECT ... WHERE Param = ? does
not work if Param is of type bytea

The following bug has been logged online:

Bug reference: 3951
Logged by: vha
Email address: vincent_dhaene@hotmail.com
PostgreSQL version: 8.3
Operating system: Windows XP SP2
Description: SELECT ... WHERE Param = ? does not work
if Param is of
type bytea
Details:

I have a table with one of the columns of type BYTEA
containing GUIDs (16 bytes of data).

The data of the column is specified using
SQLBindParameter(hstmt, nCol, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BINARY, 16, 0, theGUID, 16, &ptr).

This works fine to insert the data using "INSERT ... VALUES
?", but retrieving the data using "SELECT ... WHERE Param =
?" doesn't work: nothing is returned.

Am I doing something wrong or is this a bug?

BTW. This code works without any problem on MS SQL 2000, MS
SQL 2005, Oracle and MySQL.

vha

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

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

"Vincent D'Haene" <vincent_dhaene@hotmail.com> writes:

My problem is that it seems that in postgresql you can't use a parameter bound to BYTEA data in the WHERE clause of the SQL statement.

The above claim is nonsense.

So in my case: "SELECT x FROM T WHERE BinData = ?" does not work (return code -1) if the parameter bound to the ? is of type BYTEA.

I'm guessing that you've got an ODBC problem. That could be a bug in
your code, or less likely a bug in the ODBC driver you're using.
Since you haven't specified which driver or which version of the driver
you're using, nor provided a test case to run, it's difficult for anyone
else to investigate it.

regards, tom lane

Hi Tom,

Tx for you really clear answer, it helped a lot.

I just found the problem and it could indeed be seen as a bug in my code, although that very same piece of code works without any problem on MSSQL 2K, MSSQL 2K5, MSSQL Express, Oracle 9i, Oracle 10, MySQL 5.0 and MySQL 5.1.

The problem was the following:

When specifying the following statement:

SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY, 16, 0, ValuePtr, 16, &IndPtr)

and the column bound is of binary type (BYTEA, IMAGE, BLOB, ...), the IndPtr must contain the length of the data passed in ValuePtr.

In my code I had it set to 0 (because I already specified the length of the field in the DataLen parameter), which doesn't seem to work for postgresql.

Regards and have a nice day, Vincent

To: vincent_dhaene@hotmail.com
CC: pgdev@xs4all.nl; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
Date: Thu, 21 Feb 2008 13:30:02 -0500
From: tgl@sss.pgh.pa.us

"Vincent D'Haene" <vincent_dhaene@hotmail.com> writes:

My problem is that it seems that in postgresql you can't use a parameter bound to BYTEA data in the WHERE clause of the SQL statement.

The above claim is nonsense.

So in my case: "SELECT x FROM T WHERE BinData = ?" does not work (return code -1) if the parameter bound to the ? is of type BYTEA.

I'm guessing that you've got an ODBC problem. That could be a bug in
your code, or less likely a bug in the ODBC driver you're using.
Since you haven't specified which driver or which version of the driver
you're using, nor provided a test case to run, it's difficult for anyone
else to investigate it.

regards, tom lane

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/