RE : Cannot update or delete rows
RE : Cannot update or delete rows
От:
"Pascal Viandier" <pascal@accovia.com>
Дата:
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
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"'
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
Cannot update or delete rows
От:
"Pascal Viandier" <pascal@accovia.com>
Дата:
Hi, I am in the process of modifying existing programs closely bound to Informix to make them use ODBC with unixODBC on SPARC Sun Solaris. I setup 3 databases to connect to through ODBC: - Informix ODBC (CLI) - MySQL - PostgreSQL. I use a test program that makes various ODBC calls (Connect, Select, Insert, Update, Delete...) on these databases. It works well with the first two but not with PostgreSQL when it comes to update or delete rows in a table using SQLSetPos(). I use PostgreSQL 8.1.4 and psqlodbc-08.01.0200. I have seen many threads on this subject in this newsgroup but I cannot figure out what to do to make it work. Using the information related to this problem from this newsgroup I already made the following changes to my initial setup - with no success - : - re-created the database with oids since they seem to be essential in this case. - put UpdatableCursors=1 in odbc.ini Here is the odbc.ini part for pgodbc: [lt] Driver=/home/pascal/ODBC/libpsqlodbcw.so Description=PostgreSQL ODBC DRIVER ServerName=mbench Database=lt Port=5432 UserName=postgres Password=postgres ReadOnly=0 RowVersioning=1 ShowSystemTables=0 ShowOidColumn=1 FakeOidIndex=0 UpdatableCursors=1 UseDeclareFetch=1 CommLog=1 Debug=1 Trace=Yes TraceFile=/tmp/pgsql.log My concern is I cannot change the type of the cursor of the SELECT statement. When calling SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0); - as seen in an other thread - I get the return code SQL_SUCCESS_WITH_INFO and SQLDiagRec returns: SQLSTATE:01S02 NATIVE CODE: 16 MESSAGE: Requested value changed. If I call SQLGetStmtAttr(..., SQL_ATTR_CURSOR_TYPE,...); it returns SQL_CURSOR_STATIC. I think this is why I cannot update or delete rows using SQLSetPos() but I do not understand why I cannot change the cursor type. I already spent many hours browsing this newsgroup to find a solution but now I am completely stuck. Any help would be greatly appreciated. Thanks in advance. Pascal
Re: Cannot update or delete rows
От:
Ludek Finstrle <luf@pzkagis.cz>
Дата:
> 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 see two posts from you I hope it's enough to respond to on of them only. Regards, Luf
Re: RE : Cannot update or delete rows
От:
Hiroshi Inoue <inoue@tpf.co.jp>
Дата:
Pascal Viandier wrote: > 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 Are you updating any columns of the row ? > The delete returns: > SQLSTATE: 42601 > NATIVE CODE: 7 > MESSAGE: syntax error at or near "where"; Please try the dll at http://www.geocities.jp/inocchichichi/psqlodbc/index.html ? regards, Hiroshi Inoue
