Skip site navigation (1) Skip section navigation (2)

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

From: <noreply(at)pgfoundry(dot)org>
To: noreply(at)pgfoundry(dot)org
Subject: [ psqlodbc-Bugs-1000660 ] Problem with ADODB.Recordset.Open in a table with numeric and bytea or text fields
Date: 2006-06-07 06:33:44
Message-ID: 20060607063344.A57DB86C51F@pgfoundry.org (view raw or flat)
Thread:
Lists: pgsql-odbc
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.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;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 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

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

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

pgsql-odbc by date

Next:From: noreplyDate: 2006-06-07 06:39:43
Subject: [ psqlodbc-Bugs-1000660 ] Problem with ADODB.Recordset.Open in a table with numeric and bytea or text fields
Previous:From: noreplyDate: 2006-06-06 19:10:13
Subject: [ psqlodbc-Bugs-1000467 ] Can't use (b)lobs with Omnis Studio v4

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