Thread: INSERT but no UPDATE or DELETE from windows

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,
  I have Postgres 7.3.3 and ODBC driver 7.03.0100 and as the subject says, I
can INSERT but can't UPDATE nor DELETE. I can also create new tables. The
user has permision to do this as the psql \dp show the correct privileges and
lets me UPDATE without problems using the same user as the ODBC driver.

  I open the database as Dynaset (from VB and Visdata) but when I try to
UPDATE or .Edit a record it says something like 'should use an updatable
query' (translated from spanish).

  The odbc connection is not, of course, read only.

Any ideas of what's going on here? This worked well a couple of weeks ago and
the only thing I can think I could have modified is the PostgreSQL version
(probably updated from 7.2.x to 7.3.3). Could this be the cause? How could I
work arround this?

Thanks in advance!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/DBfSTK7ZP4pDOHcRAiswAJ9yJa6MCBgFnszjWaBdMwdJKF4ApACghwC3
eX/pkUfoKyM46Xlc/Ez846s=
=I91X
-----END PGP SIGNATURE-----

On Wednesday 09 Jul 2003 2:25 pm, Albert Cervera Areny wrote:
> Hi,
>   I have Postgres 7.3.3 and ODBC driver 7.03.0100 and as the subject says,
> I can INSERT but can't UPDATE nor DELETE. I can also create new tables. The
> user has permision to do this as the psql \dp show the correct privileges
> and lets me UPDATE without problems using the same user as the ODBC driver.
>
>   I open the database as Dynaset (from VB and Visdata) but when I try to
> UPDATE or .Edit a record it says something like 'should use an updatable
> query' (translated from spanish).
>
>   The odbc connection is not, of course, read only.

Can you give us the options ticked/not in the odbc driver settings and the
options used when creating the cursor?

--
  Richard Huxton

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Dimecres 09 Juliol 2003 22:39, Richard Huxton va escriure:
> On Wednesday 09 Jul 2003 2:25 pm, Albert Cervera Areny wrote:
> > Hi,
> >   I have Postgres 7.3.3 and ODBC driver 7.03.0100 and as the subject
> > says, I can INSERT but can't UPDATE nor DELETE. I can also create new
> > tables. The user has permision to do this as the psql \dp show the
> > correct privileges and lets me UPDATE without problems using the same
> > user as the ODBC driver.
> >
> >   I open the database as Dynaset (from VB and Visdata) but when I try to
> > UPDATE or .Edit a record it says something like 'should use an updatable
> > query' (translated from spanish).
> >
> >   The odbc connection is not, of course, read only.
>
> Can you give us the options ticked/not in the odbc driver settings and the
> options used when creating the cursor?

*** ODBC Driver: (C=Checked, U=Unchecked)
- ------Page 1------
C    Disable genetic optimizer
C    KSQO (Keyset Query Optimization)
C    Recognize unique indexes
U    Use declare/fetch

U    CommLog
U    Parse Statements
U    Cancel as freestmt (Exp)
U    Mylog

Unknown sizes: Maximum

C    Text as LongVarChar
U    Unknowns as LongVarChar
C    Bools as char

Max Varchar=254
Cache Size=100
Max LongVarChar=8190
Systable prefixes=dd_;


- ------Page 2------
U    Read only
U    Show system tables
C    LF<->CR/LF conversion
C    Updatable cursors
U    bytea as LO

U    Row versioning
U    Disallow premature
U    True is -1
U    Server side prepare

Int8 As Default

Protocol: 7.X,6.4+

U    Show columns

*** VB Example

    Dim dbT As Database
    Dim rsT As Recordset

      Set dbT = OpenDatabase("comunica", dbDriverNoPrompt,
False,"ODBC;DATABASE=comon;UID=comon;PWD=comon;DSN=Comunica")
     dbT.Execute "INSERT INTO articles VALUES(1,1,1,'BBB')"
    dbT.Execute "UPDATE articles SET unitats=300"
    dbT.Close

*** This example will crush when trying the update but will insert the record.
The same if I open a recordset (dynaset) and try to .AddNew or .Edit (the
latter will crush)
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/DRpXTK7ZP4pDOHcRAuv5AKCBwpqz5IxRVheFIBlCeMhOBqHyXQCghpH/
ckbHL8pPKaAXLgLhbiOM34g=
=pEbR
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Dimecres 09 Juliol 2003 22:39, Richard Huxton va escriure:
> On Wednesday 09 Jul 2003 2:25 pm, Albert Cervera Areny wrote:
> > Hi,
> >   I have Postgres 7.3.3 and ODBC driver 7.03.0100 and as the subject
> > says, I can INSERT but can't UPDATE nor DELETE. I can also create new
> > tables. The user has permision to do this as the psql \dp show the
> > correct privileges and lets me UPDATE without problems using the same
> > user as the ODBC driver.
> >
> >   I open the database as Dynaset (from VB and Visdata) but when I try to
> > UPDATE or .Edit a record it says something like 'should use an updatable
> > query' (translated from spanish).
> >
> >   The odbc connection is not, of course, read only.
>
> Can you give us the options ticked/not in the odbc driver settings and the
> options used when creating the cursor?

Now, I've been able to us UPDATE using the dbSQLPassthrough option parameter,
but can't update using a recordset. With Openrecordset, the dbSQLPassthrough
parameter is only valid for dbOpensnapshot and I can't update using any of
dbOpenDynaset, dbOpenDynamic, dbOpenTable either.

Any suggestions? Thanks for your help.

PS: I use the simple PostgreSQL driver, not the UTF beta one.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/DSKLTK7ZP4pDOHcRAgMcAJ9uWpzmDwafNfYPX4gUI4x/S7rJTACgjXnK
7jBroXNhK0bSswfQkK62EW4=
=7iCE
-----END PGP SIGNATURE-----