[ psqlodbc-Bugs-1000660 ] Problem with ADODB.Recordset.Open in a table with numeric and bytea or tex

Bugs item #1000660, was opened at 2006-06-07 08:33
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 60&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: István Nagy (nagyi)
Assigned to: Nobody (None)
Summary: Problem with ADODB.Recordset.Open in a table with numeric and bytea or text fields

Initial Comment:
If you do a select on a table containing a numeric field and a bytea or text field, the results you get with a recordset.Open depends on the order of the fields in the query. Steps to reproduce behaviour:

CREATE TABLE test
(
textfield text,
id int4 NOT NULL DEFAULT 0,
byteafield bytea,
numfield numeric(6,2) DEFAULT 0,
CONSTRAINT test_pk PRIMARY KEY (id)
)
Insert at least one records in this table. id an numfield should have not NULL values.

In Visual Basic make a connection, with client side cursor and following connection string:

dim c as Connection
set c = new Connection
c.CursorLocation = adUseClient
c.ConnectionString="Provider=MSDASQL.1;Extended Properties=""DRIVER={PostgreSQL};SERVER=server;PORT=5432;UID =user;PWD=password;DATABASE=db;ByteaAsLongVarBinary=1;TextAs LongVarchar=1;Parse=1;MaxLongVarcharSize=-4;Optimizer=1;Bool sAsChar=0;Ksqo=1;LFConversion=0;RowVersioning=0;UnknownsAsLo ngVarchar=1;"""

then open a recordset:

dim rs as Recordset

set rs=new Recordset
rs.Open "SELECT numfield, textfield, byteafield FROM test", c.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
debug.print rs.eof 'false - ok, we have at least one record in the table
rs.close


Now try it with other field order in the select query:

rs.Open "SELECT textfield, byteafield, numfield FROM test", c.Connection, adOpenForwardOnly, adLockReadOnly, adCmdText
debug.print rs.eof 'true ??? no records???
rs.close

I've tried some combinations and got following result: if a numeric field will be selected after a bytea or text type field, you'll get no records. In this case an rs.AddNew will fail with an error E_FAIL in provider or other service. You must open the recordset with adLockPessimistic for this.

If you use a server side cursor, the queries above will not fail, but you cannot use AppendChunk for bytea-fields (invalid string or buffer length executing rs.Update).

With Visual Studio 2005, ODBCConnection and DataReader is everything ok.

Used environment:
Client PC Windows XP SP2 + all WUs
psqlODBC 8.2 (psqlodbc35w.dll 8.2.0.2)
PostgreSql server 8.1.0 on i486-pc-linux-gnu, Debian 4.0.2-4
Visual Basic 6
ADO 2.8

Let me know, if you need more information. Thanks in advance,

István

------------------------------------------------------------ ----------

>Comment By: Ludek Finstrle (luf)
Date: 2006-06-07 08:39

Message:
Please could you try the latest dll
at http://www.geocities.jp/inocchichichi/psqlodbc/index.html
at first? There are some fixes (not sure if it helps).

Thanks,

Luf

------------------------------------------------------------ ----------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 60&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match