Thread: RV: no updateable recordset

RV: no updateable recordset

From

Manuel Martínez Valls

Date:

16 June 2001, 10:19:51

Hi,

    I'm trying to access to a postgreSQL database via ODBC. I'm able to read
the data without any problem from VB on windows, but I can't update or
insert any record.

    I have NOT checked the 'read only' option of the driver.

    I'm using Postgres 7.1.2 on a SuSE 6.3 Linux, and the 7.01.0005 driver. But
I had the problem previously (with version 7.0.2 and an older version of the
driver).

    I can insert or update a record from 'psql' or via the unixODBC driver
installed on the same machine.

    There's another extrange thing, I'm not able to modify any record, but I
can do a drop table via odbc (so the table is not read only). I'm using the
visual basic sample VisData to do the test. I've read something about a
msysconf file or table, in the logs I see the following:
>> Relation 'msysconf' does not exist'

>>conn=152710432,
SQLDriverConnect(out)='DSN=test;DATABASE=test;SERVER=server;
>>PORT=5432;UID=postgres;PWD=XXXX;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;
>>SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
>>conn=152710432,query='BEGIN'
>>conn=152710432, query='declare SQL_CUR095D0040 cursor for SELECT Config,
nValue FROM MSysConf'
>>ERROR from backend during send_query: 'ERROR:  Relation 'msysconf' does
not exist'
>>conn=152710432, query='ABORT'
>>STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error while
executing the query (non-fatal)'

    Does this mean that my databases should contain any tables to allow odbc
(write)connections? Can anybody help me?


Thanks in advance,

    Manuel Martínez Valls

Re: RV: no updateable recordset

From

"Paja"

Date:

16 June 2001, 22:52:44

----- Original Message -----
From: Manuel Martínez Valls <mmartinezv@sanostra.es>
To: <pgsql-odbc@postgresql.org>
Sent: Saturday, June 16, 2001 1:19 PM
Subject: [ODBC] RV: no updateable recordset


>
> Hi,
>
> I'm trying to access to a postgreSQL database via ODBC. I'm able to read
> the data without any problem from VB on windows, but I can't update or
> insert any record.
>
> I have NOT checked the 'read only' option of the driver.
>
> I'm using Postgres 7.1.2 on a SuSE 6.3 Linux, and the 7.01.0005 driver.
But
> I had the problem previously (with version 7.0.2 and an older version of
the
> driver).
>
> I can insert or update a record from 'psql' or via the unixODBC driver
> installed on the same machine.
>
> There's another extrange thing, I'm not able to modify any record, but I
> can do a drop table via odbc (so the table is not read only). I'm using
the
> visual basic sample VisData to do the test. I've read something about a
> msysconf file or table, in the logs I see the following:
> >> Relation 'msysconf' does not exist'
>
> >>conn=152710432,
> SQLDriverConnect(out)='DSN=test;DATABASE=test;SERVER=server;
> >>PORT=5432;UID=postgres;PWD=XXXX;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;
> >>SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
> >>conn=152710432,query='BEGIN'
> >>conn=152710432, query='declare SQL_CUR095D0040 cursor for SELECT Config,
> nValue FROM MSysConf'
> >>ERROR from backend during send_query: 'ERROR:  Relation 'msysconf' does
> not exist'
> >>conn=152710432, query='ABORT'
> >>STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error
while
> executing the query (non-fatal)'
>
> Does this mean that my databases should contain any tables to allow odbc
> (write)connections? Can anybody help me?
>
>
> Thanks in advance,
>
> Manuel Martínez Valls

1. MS JET database engine reads some ODBC settings from MSysConf table:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);
REVOKE ALL on "msysconf" from PUBLIC;
GRANT SELECT on "msysconf" to GROUP "odbcusers";
All ODBC users must have permission to use the SELECT statement on this
table. For correct settings look at DAO reference.
2. You need unique index to update table. Look around Recognize Unique
Indexes or Fake Index in PostgreSQL ODBC FAQ.
Regards,
Zlatko Talic.

RE: RV: no updateable recordset

From

Manuel Martínez Valls

Date:

20 June 2001, 18:41:15

Hi again,

I've followed your advice and I've created this table, after that reading
the PostgreSQL ODBC FAQ I've realized that they recommend to install the
pgAdmin utility, so that's what I've done.

Now I have created the msysconf table as you told me, I've the following
values:

config=101, nvalue=1, comments='Allow local storage of passwords in
attachments'
config=102, nvalue=10, comments='Background population delay'
config=103, nvalue=100, comments='Background population size'

Now, I can't see any error on the logs, but I still having the same
no-uptdateable recordsets.

Can you told me which are the magic values?

Thanks for your help,

    Manuel Martínez Valls

------------------------------
1. MS JET database engine reads some ODBC settings from MSysConf table:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);
REVOKE ALL on "msysconf" from PUBLIC;
GRANT SELECT on "msysconf" to GROUP "odbcusers";
All ODBC users must have permission to use the SELECT statement on this
table. For correct settings look at DAO reference.
2. You need unique index to update table. Look around Recognize Unique
Indexes or Fake Index in PostgreSQL ODBC FAQ.
Regards,
Zlatko Talic.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

RE: RV: no updateable recordset

From

Dave Page

Date:

21 June 2001, 07:01:50


> -----Original Message-----
> From: Manuel Martínez Valls [mailto:mmartinezv@sanostra.es]
> Sent: 18 June 2001 09:43
> To: Paja; pgsql-odbc@postgresql.org
> Subject: RE: [ODBC] RV: no updateable recordset
>
>
> Hi again,
>
> I've followed your advice and I've created this table, after
> that reading the PostgreSQL ODBC FAQ I've realized that they
> recommend to install the pgAdmin utility, so that's what I've done.
>
> Now I have created the msysconf table as you told me, I've
> the following
> values:
>
> config=101, nvalue=1, comments='Allow local storage of
> passwords in attachments' config=102, nvalue=10,
> comments='Background population delay' config=103,
> nvalue=100, comments='Background population size'
>
> Now, I can't see any error on the logs, but I still having
> the same no-uptdateable recordsets.
>

The msysconf table won't fix this problem though it will make the error
message go away. Some things to check:

1) Read only should not be set in the driver. Be careful, there are 2 boxes
that could be checked.
2) Make sure you have unique index/primary key on your table (and it's
included in your SELECT).
3) Make sure 'Recognize Unique Indexes' is switched on in the driver.
4) Don't include a trailing ; on your queries. This makes ADO recordsets
read only and may affect DAO as well.

HTH, regards, Dave.

RE: RV: no updateable recordset

From

Manuel Martínez Valls

Date:

21 June 2001, 07:13:46

Sorry, I've already solved the problem. The solution is just to create the
MSysConf table but... I made a mistake, I was using the 'snapshot' option on
visdata and the data were obviously 'Not Updateable'.

Thanks for your help,

    Manuel Martínez Valls



-----Mensaje original-----
De: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]En nombre de Manuel Martínez
Valls
Enviado el: lunes 18 de junio de 2001 10:43
Para: Paja; pgsql-odbc@postgresql.org
Asunto: RE: [ODBC] RV: no updateable recordset


Hi again,

I've followed your advice and I've created this table, after that reading
the PostgreSQL ODBC FAQ I've realized that they recommend to install the
pgAdmin utility, so that's what I've done.

Now I have created the msysconf table as you told me, I've the following
values:

config=101, nvalue=1, comments='Allow local storage of passwords in
attachments'
config=102, nvalue=10, comments='Background population delay'
config=103, nvalue=100, comments='Background population size'

Now, I can't see any error on the logs, but I still having the same
no-uptdateable recordsets.

Can you told me which are the magic values?

Thanks for your help,

    Manuel Martínez Valls

------------------------------
1. MS JET database engine reads some ODBC settings from MSysConf table:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);
REVOKE ALL on "msysconf" from PUBLIC;
GRANT SELECT on "msysconf" to GROUP "odbcusers";
All ODBC users must have permission to use the SELECT statement on this
table. For correct settings look at DAO reference.
2. You need unique index to update table. Look around Recognize Unique
Indexes or Fake Index in PostgreSQL ODBC FAQ.
Regards,
Zlatko Talic.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

RE: RV: no updateable recordset

From

"Henshall, Stuart - WCP"

Date:

21 June 2001, 07:23:08

Are you using MS Access 2K? IIRC it uses ADO as default. There was a report
that ADO in MS Access couldn't be updated without OLE DB and MS DataShape
(which surprise, surprise SQL Server supports). If this is the case use DAO
instead. Make sure both read only flags are unset.
- Stuart

> -----Original Message-----
> From:    Manuel Martínez Valls [SMTP:mmartinezv@sanostra.es]
> Sent:    Monday, June 18, 2001 9:43 AM
> To:    Paja; pgsql-odbc@postgresql.org
> Subject:    RE: RV: no updateable recordset
>
> Hi again,
>
> I've followed your advice and I've created this table, after that reading
> the PostgreSQL ODBC FAQ I've realized that they recommend to install the
> pgAdmin utility, so that's what I've done.
>
> Now I have created the msysconf table as you told me, I've the following
> values:
>
> config=101, nvalue=1, comments='Allow local storage of passwords in
> attachments'
> config=102, nvalue=10, comments='Background population delay'
> config=103, nvalue=100, comments='Background population size'
>
> Now, I can't see any error on the logs, but I still having the same
> no-uptdateable recordsets.
>
> Can you told me which are the magic values?
>
> Thanks for your help,
>
>     Manuel Martínez Valls
>
> ------------------------------
> 1. MS JET database engine reads some ODBC settings from MSysConf table:
> CREATE TABLE "msysconf" (
> "config" int2 NOT NULL,
> "chvalue" character varying,
> "nvalue" int4,
> "comments" character varying);
> REVOKE ALL on "msysconf" from PUBLIC;
> GRANT SELECT on "msysconf" to GROUP "odbcusers";
> All ODBC users must have permission to use the SELECT statement on this
> table. For correct settings look at DAO reference.
> 2. You need unique index to update table. Look around Recognize Unique
> Indexes or Fake Index in PostgreSQL ODBC FAQ.
> Regards,
> Zlatko Talic.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>