Thread: Cannot update PGSQL databes via ODBC
When using the latest version of PSQLODBC to connect to a linux machine running postgresql 6.5.2, I keep getting read-only mode. I have the privs wide open on the linux side and I even turned off the read-only flag in the DSN's advanced driver settings. When using VB6 to attempt updating a table, I get the error telling me that update is not allowed and that select is the only allowed operations. BTW, I can do a select with it and that part works fine. I can successfully update these tables just fine over in the linux side from the same user-id that I connect the ODBC with. The log file created by the ODBC driver also indicates that read-only is = 1 even though I have that checkbox unchecked. I think others have experienced a similar problem but I never could find any solutions. I posted a message or two recently to this list but got no responses. Is there anywhere I can turn for help if this list can't do it? I really need to figure out something or I will have to set up microsoft access database and I really don't want to have to go there if I can help it.:) Thanks for any help if possible. -- <Steve> Holmes Tempe, Arizona USA
One possible cause of this is not having a unique index or primary key defined on the table which requires updating. To be able to insert/update/delete there'll need to be at least one of the above. And threats to go to Access are ultimately only going to hurt yourself :) Regards, Joe Steve Holmes wrote: > > When using the latest version of PSQLODBC to connect to a linux > machine running postgresql 6.5.2, I keep getting read-only mode. I > have the privs wide open on the linux side and I even turned off the > read-only flag in the DSN's advanced driver settings. When using VB6 > to attempt updating a table, I get the error telling me that update is > not allowed and that select is the only allowed operations. BTW, I > can do a select with it and that part works fine. > > I can successfully update these tables just fine over in the linux > side from the same user-id that I connect the ODBC with. The log file > created by the ODBC driver also indicates that read-only is = 1 even > though I have that checkbox unchecked. > > I think others have experienced a similar problem but I never could > find any solutions. I posted a message or two recently to this list > but got no responses. Is there anywhere I can turn for help if this > list can't do it? > > I really need to figure out something or I will have to set up > microsoft access database and I really don't want to have to go there > if I can help it.:) > > Thanks for any help if possible. > -- > <Steve> Holmes > Tempe, Arizona USA
> I think others have experienced a similar problem but I never could
> find any solutions. I posted a message or two recently to this list
> but got no responses. Is there anywhere I can turn for help if this
> list can't do it?
I don't use those products, but at least some of these kinds of
problems seem to be related to M$ and other vendors requiring a
"primary key" or at least an index on any table considered writable.
Not sure why.
> I really need to figure out something or I will have to set up
> microsoft access database and I really don't want to have to go there
> if I can help it.:)
You'll be back ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> -----Original Message----- > From: Steve Holmes [mailto:sholmes@primenet.com] > Sent: 11 May 2000 05:14 > To: pgsql-interfaces@postgresql.org > Subject: [INTERFACES] Cannot update PGSQL databes via ODBC > > > When using the latest version of PSQLODBC to connect to a linux > machine running postgresql 6.5.2, I keep getting read-only mode. I > have the privs wide open on the linux side and I even turned off the > read-only flag in the DSN's advanced driver settings. When using VB6 > to attempt updating a table, I get the error telling me that update is > not allowed and that select is the only allowed operations. BTW, I > can do a select with it and that part works fine. In the Datasource options of the ODBC driver, try setting Show Column and Fake Index under OID Options to 'checked'. This will make VB think that there is a unique index on every table (the OID is always unique so it's not far wrong!). Another 'gotcha' with ADO is that the inclusion of a semi-colon at the end of a query (as you would use in psql) will make any resultant recordset read-only eg. (rs = ADO Recordset object, cn = ADO Connection object) rs.Open "SELECT * FROM pg_class;", cn will create a read-only recordset whereas: rs.Open "SELECT * FROM pg_class", cn won't. Hope this helps. Regards, Dave. -- NASA uses Windows? Oh great. If Apollo 13 went off course today the manual would just tell them to open the airlock, flush the astronauts out, and re-install new ones! - Kibo in the SDM. http://www.vale-housing.co.uk/ - http://www.pgadmin.freeserve.co.uk/
Thank you all so much for the replies. Guess my previous attempts didn't make it to the list as I was just signing up. I am guilty of all the above:) I had no primary keys or index built yet and I used a ";" on my query strings from VB. So to start with, I will probably try the options outlined below and later get more proper and build an index for this main table. Thanks again for the help. On Thu, 11 May 2000 08:27:11 -0000, Dave Page <dpage@vale-housing.co.uk> wrote: > > >> -----Original Message----- >> From: Steve Holmes [mailto:sholmes@primenet.com] >> Sent: 11 May 2000 05:14 >> To: pgsql-interfaces@postgresql.org >> Subject: [INTERFACES] Cannot update PGSQL databes via ODBC >> >> >> When using the latest version of PSQLODBC to connect to a linux >> machine running postgresql 6.5.2, I keep getting read-only mode. I >> have the privs wide open on the linux side and I even turned off the >> read-only flag in the DSN's advanced driver settings. When using VB6 >> to attempt updating a table, I get the error telling me that update is >> not allowed and that select is the only allowed operations. BTW, I >> can do a select with it and that part works fine. > >In the Datasource options of the ODBC driver, try setting Show Column and >Fake Index under OID Options to 'checked'. This will make VB think that >there is a unique index on every table (the OID is always unique so it's not >far wrong!). > >Another 'gotcha' with ADO is that the inclusion of a semi-colon at the end >of a query (as you would use in psql) will make any resultant recordset >read-only eg. (rs = ADO Recordset object, cn = ADO Connection object) > >rs.Open "SELECT * FROM pg_class;", cn > >will create a read-only recordset whereas: > >rs.Open "SELECT * FROM pg_class", cn > >won't. Hope this helps. > >Regards, > >Dave. > -- <Steve> Holmes Tempe, Arizona USA