Thread: DAO VB ODBC "read only problems

DAO VB ODBC "read only problems

From

"Miller, Sam"

Date:

29 October 2001, 15:39:11

Folks,

I am having great difficulty getting a VB script (Excel) to talk to a linux
based PostgreSQL server.  I can read data just fine but get the following
error when I try to edit:

Next line produces error:
Run-time error '3027';
Connot update. Database or object is read-only

I have done the following so far:

1. Created a table with a "PRIMARY KEY" as:
CREATE TABLE data2 (x integer,y integer,symbol varchar(15),pin varchar(15) primary key); 

2. Made sure that the select statement does not end in a semi-colon.

3. Created the following table (don't think this is necessary).. but I saw
something that mentioned it:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);

4. Verified that the "read only" check box is not checked in the PostgreSQL
ODBC Advanced Options

5. Checked both "show columns" and "fake index" in the OID options section
of the Data Source advanced options. 

6. Tried dbOpenSnapshot option (??? static cursor) on the OpenRecordset
method (also tried without).

I don't know what else to try, 
Anyone have any ideas? (VB code below)

Sam.


-----------------------------------
Sub checkit()
Dim wrkODBC As Workspace
Dim conSource As Connection

Set wrkODBC = CreateWorkspace("TestWorkspace", "sgmiller", "", dbUseODBC)
Set conSource = wrkODBC.OpenConnection("template1", , ,
"ODBC;Database=template1;DSN=PostgreSQL")
Set rs = conSource.OpenRecordset("Select * From data2", dbOpenSnapshot)

Do While Not rs.EOF Debug.Print rs.Fields("pin").Value
 If rs.Fields("pin").Value = "C19" Then

'Next line produces error:
' Run-time error '3027';
' Connot update. Database or object is read-only
     rs.Edit     rs.Fields("symbol").Value = "Hello"     rs.Update End If rs.MoveNext
Loop

rs.Close
wrkODBC.Close

End Sub

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

Global Options: Version='07.01.0007', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=0               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1               extra_systable_prefixes='dd_;', conn_settings=''
conn=69037888, query=' '
conn=69037888, query='set DateStyle to 'ISO''
conn=69037888, query='set geqo to 'OFF''
conn=69037888, query='set ksqo to 'ON''
conn=69037888, query='select oid from pg_type where typname='lo''   [ fetched 0 rows ]
conn=69037888, query='select version()'   [ fetched 1 rows ]   [ PostgreSQL version string = 'PostgreSQL 7.0.2 on
i686-pc-linux-gnu,
compiled by gcc 2.96' ]   [ PostgreSQL version number = '7.0' ]
conn=69037888,
PGAPI_DriverConnect(out)='DSN=PostgreSQL;DATABASE=template1;SERVER=bbfarm1;P
ORT=5432;UID=sgmiller;PWD=;A0=0;A1=6.4;A2=1;A3=1;A4=0;A5=0;A6=;A7=100;A8=409
6;A9=0;B0=254;B1=8190;B2=0;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=d
d_;'
conn=69037888, query='Select * From data2'   [ fetched 3 rows ]
conn=69037888, PGAPI_Disconnect

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

Re: DAO VB ODBC "read only problems

From

"Ross J. Reedstrom"

Date:

29 October 2001, 17:12:38

On Sun, Oct 28, 2001 at 10:01:52PM -0800, Miller, Sam wrote:
> Folks,
> 
> 
> 4. Verified that the "read only" check box is not checked in the PostgreSQL
> ODBC Advanced Options
> 

Both of them? There's one for the Driver as a whole, as well as one for each
instance/db/whatever_they_call_it

Ross

Re: DAO VB ODBC "read only problems

From

"Ross J. Reedstrom"

Date:

29 October 2001, 21:42:00

On Mon, Oct 29, 2001 at 01:44:07PM -0800, Miller, Sam wrote:
> Ross,
> 
> - The read only attribute under driver options (advanced) is greyed out w/
> it unselected. (I couldn't enable it if I wanted to)
> 
> - Under the data source options, it is also unchecked.
> 
> Does this sound correct?
> Any other ideas?

Hmm, I haven't used that in a long time. It seems odd to me that the driver
option is greyed out: are you logged in as an administrator?

Sorry, fresh out of ideas.

Ross

-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

Re: DAO VB ODBC "read only problems

From

Hiroshi Inoue

Date:

29 October 2001, 22:52:04

"Miller, Sam" wrote:
> 
> Folks,
> 
> I am having great difficulty getting a VB script (Excel) to talk to a linux
> based PostgreSQL server.  I can read data just fine but get the following
> error when I try to edit:
> 
> Next line produces error:
> Run-time error '3027';
> Connot update. Database or object is read-only
> 
> I have done the following so far:
> 
> 1. Created a table with a "PRIMARY KEY" as:
> CREATE TABLE data2 (
>  x integer,
>  y integer,
>  symbol varchar(15),
>  pin varchar(15) primary key);
> 
> 2. Made sure that the select statement does not end in a semi-colon.
> 
> 3. Created the following table (don't think this is necessary).. but I saw
> something that mentioned it:
> CREATE TABLE "msysconf" (
> "config" int2 NOT NULL,
> "chvalue" character varying,
> "nvalue" int4,
> "comments" character varying);
> 
> 4. Verified that the "read only" check box is not checked in the PostgreSQL
> ODBC Advanced Options
> 
> 5. Checked both "show columns" and "fake index" in the OID options section
> of the Data Source advanced options.
> 
> 6. Tried dbOpenSnapshot option (??? static cursor) on the OpenRecordset
> method (also tried without).
> 
> I don't know what else to try,
> Anyone have any ideas? (VB code below)
> 

Unfortunately psqlodbc driver doesn't implement updatable
cursors. Probably you have to set DefaultCursorDriver property
of the ODBCDirect workspace as dbUseODBCCursor.

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

Hiroshi Inoue

Date:

30 October 2001, 05:01:55

"Miller, Sam" wrote:
> 
> Hiroshi and others,
> 
> I added:
> wrkODBC.DefaultCursorDriver=dbUseODBCCursor
> after the "Set wrkODBC....." line.
> 
> Still doesn't work.  Any more ideas?
> 
> I discovered I am using postgresql-7.0.2-17, could that be the problem?
> (The ODBC driver is at 7.01.0007)

How about unchecking *Text As LongVarChar* ?

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

Hiroshi Inoue

Date:

30 October 2001, 05:51:50

"Miller, Sam" wrote:
> 
> Hiroshi and others,
> 
> 
> -Any wisdom on verifying the logs?
> the only thing I can see the looks a little ominous is where it executes
> "select oid from pg_type where typname='lo'"
> and it returns 0 rows.  I have no idea what this is...

Could you send me the mylog output ?
You could enable it by chekcing Mylog driver option.
Note that mylog is pretty big.

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

DI Hasenöhrl

Date:

30 October 2001, 06:56:09

Hi Sam,

AFAIK, in MsAccess the dbopenSnapshot format is read-only. Try dbOpenDynaset and it should work.

HTH,

Irina

----- Original Message -----
From: Miller, Sam
To: pgsql-interfaces@postgresql.org
Sent: Monday, October 29, 2001 7:01 AM
Subject: [INTERFACES] DAO VB ODBC "read only problems

Folks,

I am having great difficulty getting a VB script (Excel) to talk to a linux
based PostgreSQL server. I can read data just fine but get the following
error when I try to edit:

Next line produces error:
Run-time error '3027';
Connot update. Database or object is read-only

I have done the following so far:

1. Created a table with a "PRIMARY KEY" as:
CREATE TABLE data2 (
x integer,
y integer,
symbol varchar(15),
pin varchar(15) primary key);

2. Made sure that the select statement does not end in a semi-colon.

3. Created the following table (don't think this is necessary).. but I saw
something that mentioned it:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);

4. Verified that the "read only" check box is not checked in the PostgreSQL
ODBC Advanced Options

5. Checked both "show columns" and "fake index" in the OID options section
of the Data Source advanced options.

6. Tried dbOpenSnapshot option (??? static cursor) on the OpenRecordset
method (also tried without).

I don't know what else to try,
Anyone have any ideas? (VB code below)

Sam.

-----------------------------------
Sub checkit()
Dim wrkODBC As Workspace
Dim conSource As Connection

Set wrkODBC = CreateWorkspace("TestWorkspace", "sgmiller", "", dbUseODBC)
Set conSource = wrkODBC.OpenConnection("template1", , ,
"ODBC;Database=template1;DSN=PostgreSQL")
Set rs = conSource.OpenRecordset("Select * From data2", dbOpenSnapshot)

Do While Not rs.EOF
Debug.Print rs.Fields("pin").Value

If rs.Fields("pin").Value = "C19" Then

'Next line produces error:
' Run-time error '3027';
' Connot update. Database or object is read-only

      rs.Edit
      rs.Fields("symbol").Value = "Hello"
      rs.Update
End If
rs.MoveNext
Loop

rs.Close
wrkODBC.Close

End Sub

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

Global Options: Version='07.01.0007', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1
                extra_systable_prefixes='dd_;', conn_settings=''
conn=69037888, query=' '
conn=69037888, query='set DateStyle to 'ISO''
conn=69037888, query='set geqo to 'OFF''
conn=69037888, query='set ksqo to 'ON''
conn=69037888, query='select oid from pg_type where typname='lo''
    [ fetched 0 rows ]
conn=69037888, query='select version()'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.0.2 on i686-pc-linux-gnu,
compiled by gcc 2.96' ]
    [ PostgreSQL version number = '7.0' ]
conn=69037888,
PGAPI_DriverConnect(out)='DSN=PostgreSQL;DATABASE=template1;SERVER=bbfarm1;P
ORT=5432;UID=sgmiller;PWD=;A0=0;A1=6.4;A2=1;A3=1;A4=0;A5=0;A6=;A7=100;A8=409
6;A9=0;B0=254;B1=8190;B2=0;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=d
d_;'
conn=69037888, query='Select * From data2'
    [ fetched 3 rows ]
conn=69037888, PGAPI_Disconnect

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: DAO VB ODBC "read only problems

From

"Miller, Sam"

Date:

30 October 2001, 17:27:37

Ross,

- The read only attribute under driver options (advanced) is greyed out w/
it unselected. (I couldn't enable it if I wanted to)

- Under the data source options, it is also unchecked.

Does this sound correct?
Any other ideas?

Thanks for the help,

Sam

-----Original Message-----
From: Ross J. Reedstrom [mailto:reedstrm@rice.edu]
Sent: Monday, October 29, 2001 10:33 AM
To: Miller, Sam
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] DAO VB ODBC "read only problems


On Sun, Oct 28, 2001 at 10:01:52PM -0800, Miller, Sam wrote:
> Folks,
> 
> 
> 4. Verified that the "read only" check box is not checked in the
PostgreSQL
> ODBC Advanced Options
> 

Both of them? There's one for the Driver as a whole, as well as one for each
instance/db/whatever_they_call_it

Ross

Re: DAO VB ODBC "read only problems

From

"Miller, Sam"

Date:

30 October 2001, 18:40:26

Hiroshi and others,

Nope....

Also tried (randomly):
-Disabling KSQO
-Enabling "Unknowns as LongVarChar"
-Enabling Parse Statements
-Enabling Show System Tables
-Switching to 6.3 Protocol

-Tried to get pgadminII running as an independent test but ran into 7.1
dependency problems.  Upgrading the system is a task for tomorrow.

-pgaccess works fine.

-Any other "known good" applications that could be used to verify
functionality?

-Any wisdom on verifying the logs?
the only thing I can see the looks a little ominous is where it executes
"select oid from pg_type where typname='lo'"
and it returns 0 rows.  I have no idea what this is...

Thanks for all the help,
Wearing out,

Sam

-----Original Message-----
From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
Sent: Monday, October 29, 2001 10:56 PM
To: Miller, Sam
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] DAO VB ODBC "read only problems


"Miller, Sam" wrote:
> 
> Hiroshi and others,
> 
> I added:
> wrkODBC.DefaultCursorDriver=dbUseODBCCursor
> after the "Set wrkODBC....." line.
> 
> Still doesn't work.  Any more ideas?
> 
> I discovered I am using postgresql-7.0.2-17, could that be the problem?
> (The ODBC driver is at 7.01.0007)

How about unchecking *Text As LongVarChar* ?

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

"Miller, Sam"

Date:

30 October 2001, 18:40:39

Hiroshi and others,

I added:
wrkODBC.DefaultCursorDriver=dbUseODBCCursor
after the "Set wrkODBC....." line.

Still doesn't work.  Any more ideas?

I discovered I am using postgresql-7.0.2-17, could that be the problem?
(The ODBC driver is at 7.01.0007)

Getting desperate,

Sam

-----Original Message-----
From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
Sent: Monday, October 29, 2001 4:40 PM
To: Miller, Sam
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] DAO VB ODBC "read only problems


"Miller, Sam" wrote:
> 
> Folks,
> 
> I am having great difficulty getting a VB script (Excel) to talk to a
linux
> based PostgreSQL server.  I can read data just fine but get the following
> error when I try to edit:
> 
> Next line produces error:
> Run-time error '3027';
> Connot update. Database or object is read-only
> 
> I have done the following so far:
> 
> 1. Created a table with a "PRIMARY KEY" as:
> CREATE TABLE data2 (
>  x integer,
>  y integer,
>  symbol varchar(15),
>  pin varchar(15) primary key);
> 
> 2. Made sure that the select statement does not end in a semi-colon.
> 
> 3. Created the following table (don't think this is necessary).. but I saw
> something that mentioned it:
> CREATE TABLE "msysconf" (
> "config" int2 NOT NULL,
> "chvalue" character varying,
> "nvalue" int4,
> "comments" character varying);
> 
> 4. Verified that the "read only" check box is not checked in the
PostgreSQL
> ODBC Advanced Options
> 
> 5. Checked both "show columns" and "fake index" in the OID options section
> of the Data Source advanced options.
> 
> 6. Tried dbOpenSnapshot option (??? static cursor) on the OpenRecordset
> method (also tried without).
> 
> I don't know what else to try,
> Anyone have any ideas? (VB code below)
> 

Unfortunately psqlodbc driver doesn't implement updatable
cursors. Probably you have to set DefaultCursorDriver property
of the ODBCDirect workspace as dbUseODBCCursor.

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

Hiroshi Inoue

Date:

30 October 2001, 21:52:06

"Miller, Sam" wrote:
> 
> Hiroshi and others,
> 
> Nope....
> 
> Also tried (randomly):
> -Disabling KSQO
> -Enabling "Unknowns as LongVarChar"
> -Enabling Parse Statements
> -Enabling Show System Tables
> -Switching to 6.3 Protocol
> 

Please try 
Set rs = conSource.OpenRecordset("Select * From data2", dbOpenDynaset, _    dbExecDirect, dbOptimisticValue)
and never forget to set the DefaultCursorDriver property.

regards,
Hiroshi Inoue

Re: DAO VB ODBC "read only problems

From

"Miller, Sam"

Date:

31 October 2001, 17:39:20

Hiroshi,

IT WORKS!!!  Thanks a million.
When understand a little more what I am doing, I guess I should write a
mini-faq.

Sam

>Please try 
>Set rs = conSource.OpenRecordset("Select * From data2", dbOpenDynaset, _
>        dbExecDirect, dbOptimisticValue)
>and never forget to set the DefaultCursorDriver property.
>
>regards,
>Hiroshi Inoue