SCM

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

View Trackers | Bugs | Download .csv | Monitor

Date:
2006-06-07 06:33
Priority:
3
State:
Open
Submitted by:
István Nagy (nagyi)
Assigned to:
Hiroshi Inoue (hinoue)
Category:
Group:
Resolution:
None
Category:
Group:
Resolution:
None
Summary:
Problem with ADODB.Recordset.Open in a table with numeric and bytea or text fields

Detailed description
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

Followup

Message
Date: 2006-06-23 07:41
Sender: Nobody


Date: 2006-06-16 08:04
Sender: István Nagy

I wanted to try the ansi version, but the zip-file on the
geocieties page is unchanged.
Date: 2006-06-13 16:09
Sender: István Nagy

I must first create a test case with random records, 'cause
the mylog contains sensible data. It will take certainly a
while, until I get that error reproduced... Could you help
me in searching for errors in mylog (what should I search
for)? If I could find something of interest for you, I could
send only that part.
Date: 2006-06-13 14:32
Sender: Hiroshi Inoue

> I've done further testing with the unicode
> version. By some complicated queries over 3
> tables I get an error, if I want
> Recordset.RecordCount. Recordset.EOF is true,
> allthough I do have hundreds of records.
> The MyLog file contains the complete recordset
> and is over 1,4 MB in size.

In zip ?
Could you send me the Mylog output ?

regards,
Hiroshi Inoue
Date: 2006-06-13 14:29
Sender: Hiroshi Inoue

> I cannot connect with the ansi version, still
> getting FATAL: Umwandlung zwischen LATIN9 und
> LATIN1 wird nicht unterstützt
> (conversion latin9<->latin1 not supported)

Could you retry the dll ?

regards,
Hiroshi Inoue
Date: 2006-06-13 06:51
Sender: István Nagy

I've done further testing with the unicode version. By some
complicated queries over 3 tables I get an error, if I want
Recordset.RecordCount. Recordset.EOF is true, allthough I do
have hundreds of records. The MyLog file contains the
complete recordset and is over 1,4 MB in size. I've search
for fail, error and warning in it. No search results for
fail, for error only at connecting, and a lots for warning
like this:

**** PGAPI_GetData: icol = 12, target_type = -9999,
field_type = 25, value = 'false'
[1860]copy_and_convert: field_type = 25, fctype = -9999,
value = 'false', cbValueMax=0
[1860]STATEMENT WARNING: func=PGAPI_GetData, desc='',
errnum=-2, errmsg='The buffer was too small for the GetData.'
[1860]len_needed=5
[1860]PGAPI_GetData: enter, stmt=7e5bb78
[1860] num_rows = 30
[1860] value = 'false'

Date: 2006-06-13 06:13
Sender: István Nagy

I cannot connect with the ansi version, still getting
FATAL: Umwandlung zwischen LATIN9 und LATIN1 wird nicht
unterstützt
(conversion latin9<->latin1 not supported)
I'm not using a dns to connect, the connection string is the
same, as with the unicode driver.
Date: 2006-06-12 16:08
Sender: Hiroshi Inoue

Please try the Ansi version at the same place,
though I'm not sure about Win98 at all.

regards,
Hiroshi Inoue
Date: 2006-06-12 15:17
Sender: István Nagy

Now it seems to be ok. What about the ANSI version?
Date: 2006-06-12 14:55
Sender: Hiroshi Inoue

I've just uploaded a new driver(7.3.0728).
Please try it.

regards,
Hiroshi Inoue
Date: 2006-06-12 10:51
Sender: István Nagy

I forgot the declarations:

Dim Buff() as byte
Dim OrigText as string
Dim rs as recordset

c.connection is a ADODB.Connection.
Date: 2006-06-12 10:48
Sender: István Nagy

This is the VB code:

rs.Open "SELECT ID, byteafield FROM test WHERE ID = -1",
c.Connection, adOpenKeyset, adLockPessimistic, adCmdText
If rs.EOF Then
rs.AddNew
rs(0) = -1
End If
If rs(1).ActualSize > 0 Then
Buff = rs(1).GetChunk(rs(1).ActualSize)
OrigText = Buff
End If
Buff = OrigText + "Some Text"
rs(1).AppendChunk Buff
rs.Update
rs.Close

At the first time it succeds. If you let it run again, you
get in Buff not the same characters, and the update does not
function.
I'll try to attach the mylog.log
Date: 2006-06-12 09:12
Sender: Hiroshi Inoue

Could you send me the Mylog output ?

regards,
Hiroshi Inoue
Date: 2006-06-12 08:22
Sender: István Nagy

If you mean the option ByteaAsLongVarBinary in the
connection string, yes, it is 1. If not, tell me more
precise, where to find that option. Here is the complete
connection string:

Provider=MSDASQL.1;Extended Properties=
"DRIVER={PostgreSQL ANSI};
DATABASE=test;SERVER=server;PORT=5432;SSLMODE=prefer;UID=user;PWD=secret;
ReadOnly=0;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=8192;
UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=-4;
Debug=0;CommLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=0;
TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;
Parse=1;CancelAsFreeStmt=0;
ExtraSysTablePrefixes=dd_;;LFConversion=0;
UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;
BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;
LowerCaseIdentifier=0"
Date: 2006-06-12 07:49
Sender: Hiroshi Inoue

> I get other errors.
> If I use AppendChunk on a bytea field,
> inserting unicode text in it, i get a
> string not closed error. If I try
> inserting some more characters, I get no
> error. But the contents of the field is not
> that, what I've tried to insert.

Are you turning on the *bytea as lo* option ?

regards,
Hiroshi Inoue
Date: 2006-06-12 06:35
Sender: István Nagy

Ok, I've tried the new version of the unicode driver.
The order of the fields is not a problem anymore and I get
no more GPF's, but...
I get other errors.
If I use AppendChunk on a bytea field, inserting unicode
text in it, i get a string not closed error. If I try
inserting some more characters, I get no error. But the
contents of the field is not that, what I've tried to
insert. If I change the text read back from the field and
try to do an update, a get following error:

could not convert UTF8 character 0x00e2 to ISO8859-1;Error
while executing the query

On the other side, I need a driver that works on Windows 98.
Will this unicode version work? IMHO Windows 98 does not
support unicode. And the ansi version still gives me an
error at connecting.
Date: 2006-06-09 15:55
Sender: Hiroshi Inoue

> I downloaded the two dll-s from the said
> website. I think we are talking about the
> same version.

Probably not the same because I uplodaed
the version today.
Date: 2006-06-09 15:27
Sender: István Nagy

I downloaded the two dll-s from the said website. I think we
are talking about the same version.
The original problem is still there, so I didn't made
another test case. I'll try to post some more queries monday.
Date: 2006-06-09 02:31
Sender: Hiroshi Inoue

> I've tried both the unicode version
> (PSQLODBC35W.DLL (7.3.2.76).

Could you try the newer version(7.3.0276) ?

> Furthermore I get GPF's with the unicode
> driver by some other queries.

By what kind of queries ?

> Another bug: doing a SELECT MAX(timestamp_field
> ) with the 8.2 driver gives as a result
> a field of type string with unprintable
> characters as value.

I couldn't see such behavior here.
Could you show me more concrete example ?
Date: 2006-06-07 08:43
Sender: István Nagy

Thanks for replying

I've tried both the unicode version (PSQLODBC35W.DLL
7.3.2.75) and ansi version(psqlodbc.dll 7.3.2.66).
No changes in the unicode driver's behaviour.
The ansi driver sais: FATAL: Umwandlung zwischen LATIN9 und
LATIN1 wird nicht unterstützt (converting between LATIN9 and
LATIN1 not supported).
Furthermore I get GPF's with the unicode driver by some
other queries.

Another bug: doing a SELECT MAX(timestamp_field) with the
8.2 driver gives as a result a field of type string with
unprintable characters as value.
Date: 2006-06-07 06:39
Sender: Ludek Finstrle

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

Attached Files:

Attachments:
mylog_4036.zip

Changes:

Field Old Value Date By
File Added114: mylog_4036.zip2006-06-12 10:49nagyi
assigned_tonone2006-06-12 07:49hinoue
Powered By FusionForge