RE : Cannot update or delete rows - Mailing list pgsql-odbc

Hi Luf,

> -----Message d'origine-----
> De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]
> De la part de Ludek Finstrle
> Envoyé : July 31, 2006 00:05
> À : Pascal Viandier
> Cc : pgsql-odbc@postgresql.org
> Objet : Re: [ODBC] Cannot update or delete rows
>
> > I use PostgreSQL 8.1.4 and psqlodbc-08.01.0200.
>
> I'm sorry but psqlodbc-08.01.XXXX doesn't support such feature
> (updateable cursor). I know there is no info about it. But 08.01
> uses libpq for communication with server and there are some
> breakage in updateable code.
>
> Please try latest CVS from pgfoundry.org. It should do what you want.
>
I just tried the CVS release (psqlodbc-08.02.0002).
It is a lot better: SQLSetPos() does not return the previous error for update or
delete but it still does not work.
The update returns:
SQLSTATE: 07005
NATIVE CODE: 15
MESSAGE: update list null
The delete returns:
SQLSTATE: 42601
NATIVE CODE: 7
MESSAGE: syntax error at or near "where";

Here is the beginning and a snippet of the psqlodbc log around the "update"
error:

Global Options: Version='08.02.0002', 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=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
    [ PostgreSQL version string = '8.1.4' ]
    [ PostgreSQL version number = '8.1' ]
conn=68390, query='select oid, typbasetype from pg_type where typname = 'lo''
    [ fetched 0 rows ]
    [ Large Object oid = -999 ]
    [ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=68390,
PGAPI_DriverConnect(out)='DSN=lt;DATABASE=lt;SERVER=mbench;PORT=5432;UID=postgre
s;PWD=xxxxxxxx;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidCol
umn=0;RowVersioning=1;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;Unk
nownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;Optimiz
er=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsA
sChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=0;Upd
atableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;U
seServerSidePrepare=0;LowerCaseIdentifier=0;'
CONN ERROR: func=PGAPI_GetInfo30, desc='', errnum=209, errmsg='Unrecognized key
passed to SQLGetInfo30.'
            ------------------------------------------------------------
            henv=64210, conn=68390, status=1, num_stmts=16
            sock=6ae98, stmts=6cf08, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=6, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=438008, buffer_out=442112
            buffer_filled_in=77, buffer_filled_out=0, buffer_read_in=77
<snip>
conn=68390, query='declare "SQL_CUR74468" scroll cursor with hold for SELECT * ,
"ctid", "oid" FROM address WHERE CLEADDR LIKE(E'004304%') ORDER BY CLEADDR'
conn=68390, query='SAVEPOINT _EXEC_SVP_00074468'
conn=68390, query='fetch 100 in "SQL_CUR74468"'
    [ fetched 1 rows ]
conn=68390, query='RELEASE _EXEC_SVP_00074468'
conn=68390, query='COMMIT'
conn=68390, query='MOVE 0 in "SQL_CUR74468"'
STATEMENT ERROR: func=SC_pos_update, desc='', errnum=15, errmsg='update list
null'
                 ------------------------------------------------------------
                 hdbc=68390, stmt=74468, result=a51b8
                 prepare=3, internal=0
                 bindings=73c00, bindings_allocated=20
                 parameters=63810, parameters_allocated=1
                 statement_type=0, statement='SELECT * FROM address WHERE
CLEADDR LIKE(?) ORDER BY CLEADDR'
                 stmt_with_params='declare "SQL_CUR74468" scroll cursor with
hold for SELECT * , "ctid", "oid" FROM address WHERE CLEADDR LIKE(E'004304%')
ORDER BY CLEADDR'
                 data_at_exec=-1, current_exec_param=-1, put_data=0
                 currTuple=0, current_col=-1, lobj_fd=-1
                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=3,
scroll_concurrency=3
                 cursor_name='SQL_CUR74468'
                 ----------------QResult Info -------------------------------
                 fields=67648, backend_tuples=a94f0, tupleField=693488,
conn=68390
                 fetch_count=1, num_total_rows=1, num_fields=18,
cursor='SQL_CUR74468'
                 message='(NULL)', command='FETCH', notice='(NULL)'
                 status=100, inTuples=0
CONN ERROR: func=SC_pos_update, desc='', errnum=0, errmsg='(NULL)'
            ------------------------------------------------------------
            henv=64210, conn=68390, status=1, num_stmts=16
            sock=6ae98, stmts=6cf08, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=6, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=438008, buffer_out=442112
            buffer_filled_in=18, buffer_filled_out=0, buffer_read_in=18
conn=68390, query='close "SQL_CUR74468"'
<snip>
And a snippet around the "delete" error:
conn=68390, query='declare "SQL_CURb2ff0" scroll cursor with hold for SELECT * ,
"ctid", "oid" FROM tstidx WHERE CHP1 LIKE(E'FFF%') ORDER BY CHP1'
conn=68390, query='fetch 100 in "SQL_CURb2ff0"'
    [ fetched 1 rows ]
conn=68390, query='COMMIT'
conn=68390, query='MOVE 0 in "SQL_CURb2ff0"'
conn=68390, query='delete from "public"."tstidx" where ctid = '(1, 31)' and
"oid" = 3782366'
conn=68390, query='SELECT * , "ctid", "oid" FROM tstidx WHERE CHP1 LIKE(E'FFF%')
ORDER BY CHP1 where ctid = currtid2('"public"."tstidx"', '(1, 31)')  and "oid" =
3782366'
ERROR from backend during send_query: 'SERROR'
ERROR from backend during send_query: 'C42601'
ERROR from backend during send_query: 'Msyntax error at or near "where"'
ERROR from backend during send_query: 'P77'
ERROR from backend during send_query: 'Fscan.l'
ERROR from backend during send_query: 'L770'
ERROR from backend during send_query: 'Ryyerror'
conn=68390, query='COMMIT'

I have also the "mylog" trace I can send if needed.

Help!

Regards,

Pascal

> I see two posts from you I hope it's enough to respond to on of them
> only.
>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings