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

Bugs item #1000660, was opened at 2006-06-07 06:33
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000660&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.Opendepends 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;TextAsLongVarchar=1;Parse=1;MaxLongVarcharSize=-4;Optimizer=1;BoolsAsChar=0;Ksqo=1;LFConversion=0;RowVersioning=0;UnknownsAsLongVarchar=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
mustopen 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
(invalidstring 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

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

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