Re: TEXT datatype and ODBC?

All,

This is my first posting to this list - I posted a similar message to
the general list, and an individual by the name of Richard Huxton
suggested I try here. I appologize if this issue has already been
covered, however I didn't see anything in the archive that seemed to
address my problem.

Basically, I have a legacy VB app that I am converting over to use
PostgreSQL. Prior to this, the VB app used DAO to update an Access 97
database. Certain tables on this database had fields which were of the
Memo datatype. I converted these on the PG side to TEXT fields. That is
when I noticed a problem, which seems to only be an issue with TEXT type
fields.

When I go to do an "add" (via the AddNew method in DAO/VB), and I update
a field twice before committing the change (via the .update method in
DAO/VB), I get an error of "can't perform this operation" on the second
change of the field. My test code is as follows:

Private Sub cmdTest_Click()
'
Dim ws As Workspace
Dim cn As Connection
Dim db As Database
Dim rs As Recordset
'
Set ws = CreateWorkspace("WS", "", "", dbUseODBC)
'
ws.DefaultCursorDriver = dbUseODBCCursor
'
Set cn = ws.OpenConnection("", dbDriverComplete, False,
"ODBC;driver={PostgreSQL};server=mysvr;port=5433;database=mydb;uid=3Daayers;pwd=;ksqo=;")
Set db = cn.Database
'
Set rs = db.OpenRecordset("SELECT * FROM zzz_dummy", dbOpenDynaset,
dbExecDirect, dbOptimisticValue) ' Fails as "cant perform this operation"
'Set rs = db.OpenRecordset("SELECT * FROM zzz_dummy", dbOpenDynaset)
' Fails as "read only"
'Set rs = db.OpenRecordset("zzz_dummy", dbOpenDynaset) ' Fails as
"read only"
'
With rs
'
If .RecordCount > 0 Then
'
.MoveLast
.MoveFirst
'
End If
'
.AddNew
'
![a] = 1
![a] = 2
![b] = "test1"
![b] = "test2"
'
.Update
'
End With
'
rs.Close
db.Close
cn.Close
ws.Close
'
End Sub

The table can be recreated in PostgreSQL via:

CREATE TABLE zzz_dummy(a int4, b text, PRIMARY KEY (a));

I am using DAO 3.51 methods and functions in this app. I have found that
if I do the same with any other PG datatype (in this case, the field
"a"), it works as expected - only on the TEXT datatype does it fail with
the error message. I am using the latest (7.3.200?) ODBC driver under
Windows XP Pro.

Richard found that using a linked table from Access (he was using 2000 -
I am converting from 97), that the code would work OK. He wrote me saying:

"Now with the code above "testA" is your style and "testB" is using a
linked table from Access 2000 to PG 7.3.2 - the linked table works, but
the "direct" connection doesn't.

I think the problem is that the direct recordset isn't updatable. The
set of parameters "dbExecDirect" etc just means that this isn't noticed
until the "rs.Update".

I'm guessing (and I must emphasise it's a guess) that it's because the
connection doesn't know which fields are primary keys or somesuch -
looking at the ODBC log shows the linked table case just using a cursor
over the primary key (a)."

---

Currently, the way I am getting around this issue (because in many areas
I need the large character fields for very long descriptions and such)
is by using big VARCHAR() fields (ie, VARCHAR(80000) or so). The problem
with these though is that doing any kind of select the accesses them in
any way (either as part of the select or of the WHERE clause), slows
down the select waaaaay down - a select that should take seconds takes
minutes (BTW - this is only via the ODBC connection - under psql the
same select is very quick).

There should be other messages by me in the PostgreSQL General mailing
list archives regarding this error, but they basically say the same as
what I am posting here. Still, they may shed further light on the issue.

Thank you all for any help you may be able to provide. I have a feeling
it is in the ODBC driver, but I don't know why. I suppose it could also
be my setup - but what this issue is, I don't really know.

Sincerely,

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to who it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.