Thread: 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
Re: BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
From
"Gevik Babakhani"
Date:
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 >
Re: BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
From
Vincent D'Haene
Date:
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 bo= und to BYTEA data in the WHERE clause of the SQL statement. So in my case: "SELECT x FROM T WHERE BinData =3D ?" 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 =3D ? does not work= if Param is of type bytea > Date: Sun, 10 Feb 2008 23:09:16 +0100 >=20 > perhaps this helps >=20 > http://www.webservertalk.com/archive308-2007-3-1836413.html >=20=20 >=20 > > -----Original Message----- > > From: pgsql-bugs-owner@postgresql.org=20 > > [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 =3D ? does=20 > > not work if Param is of type bytea > >=20 > >=20 > > The following bug has been logged online: > >=20 > > 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 =3D ? does not work=20 > > if Param is of > > type bytea > > Details:=20 > >=20 > > I have a table with one of the columns of type BYTEA=20 > > containing GUIDs (16 bytes of data). > >=20 > > The data of the column is specified using=20 > > SQLBindParameter(hstmt, nCol, SQL_PARAM_INPUT, SQL_C_BINARY,=20 > > SQL_BINARY, 16, 0, theGUID, 16, &ptr). > >=20 > > This works fine to insert the data using "INSERT ... VALUES=20 > > ?", but retrieving the data using "SELECT ... WHERE Param =3D=20 > > ?" doesn't work: nothing is returned. > >=20 > > Am I doing something wrong or is this a bug? > >=20 > > BTW. This code works without any problem on MS SQL 2000, MS=20 > > SQL 2005, Oracle and MySQL. > >=20 > >=20 > > vha > >=20 > > ---------------------------(end of=20 > > broadcast)--------------------------- > > TIP 1: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org=20 > > so that your > > message can get through to the mailing list cleanly > >=20 >=20 _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
Re: BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
From
Tom Lane
Date:
"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
theSQL 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
oftype 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
Re: BUG #3951: SELECT ... WHERE Param = ? does not work if Param is of type bytea
From
Vincent D'Haene
Date:
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, a= lthough 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 IndPt= r 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 =3D ? does not work= if Param is of type bytea=20 > Date: Thu, 21 Feb 2008 13:30:02 -0500 > From: tgl@sss.pgh.pa.us >=20 > "Vincent D'Haene" <vincent_dhaene@hotmail.com> writes: > > My problem is that it seems that in postgresql you can't use a paramete= r bound to BYTEA data in the WHERE clause of the SQL statement. >=20 > The above claim is nonsense. >=20 > > So in my case: "SELECT x FROM T WHERE BinData =3D ?" does not work (ret= urn code -1) if the parameter bound to the ? is of type BYTEA. >=20 > 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. >=20 > regards, tom lane _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=