Cannot update or delete rows

Hi,

I am in the process of modifying existing programs closely bound to Informi=
x to
make them use ODBC with unixODBC on SPARC Sun Solaris.=20

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 fig=
ure
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=3D1 in odbc.ini

Here is the odbc.ini part for pgodbc:

[lt]
Driver=3D/home/pascal/ODBC/libpsqlodbcw.so
Description=3DPostgreSQL ODBC DRIVER
ServerName=3Dmbench
Database=3Dlt
Port=3D5432
UserName=3Dpostgres
Password=3Dpostgres
ReadOnly=3D0
RowVersioning=3D1
ShowSystemTables=3D0
ShowOidColumn=3D1
FakeOidIndex=3D0
UpdatableCursors=3D1
UseDeclareFetch=3D1
CommLog=3D1
Debug=3D1
Trace=3DYes
TraceFile=3D/tmp/pgsql.log


My concern is I cannot change the type of the cursor of the SELECT statemen=
t.
When calling SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)
SQL_CURSOR_KEYSET_DRIVEN, 0); - as seen in an other thread - I get the retu=
rn
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 n=
ow I
am completely stuck.

Any help would be greatly appreciated.

Thanks in advance.

Pascal



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: 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 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

RE : Cannot update or delete rows

Hi Luf,

> -----Message d'origine-----
> De=A0: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresq=
l.org]
> De la part de Ludek Finstrle
> Envoyé : July 31, 2006 00:05
> À : Pascal Viandier
> Cc=A0: pgsql-odbc@postgresql.org
> Objet=A0: Re: [ODBC] Cannot update or delete rows
>=20
> > I use PostgreSQL 8.1.4 and psqlodbc-08.01.0200.
>=20
> 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.
>=20
> Please try latest CVS from pgfoundry.org. It should do what you want.
>=20
I just tried the CVS release (psqlodbc-08.02.0002).
It is a lot better: SQLSetPos() does not return the previous error for upda=
te 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=3D'08.02.0002', fetch=3D100, socket=3D4096, unknown=
_sizes=3D0,
max_varchar_size=3D254, max_longvarchar_size=3D8190
disable_optimizer=3D1, ksqo=3D1, unique_index=3D1, use_decl=
arefetch=3D1
text_as_longvarchar=3D1, unknowns_as_longvarchar=3D0,
bools_as_char=3D1 NAMEDATALEN=3D64
extra_systable_prefixes=3D'dd_;', conn_settings=3D''
conn_encoding=3D'OTHER'
[ PostgreSQL version string =3D '8.1.4' ]
[ PostgreSQL version number =3D '8.1' ]
conn=3D68390, query=3D'select oid, typbasetype from pg_type where typname =
=3D 'lo''
[ fetched 0 rows ]
[ Large Object oid =3D -999 ]
[ Client encoding =3D 'SQL_ASCII' (code =3D 0) ]
conn=3D68390,
PGAPI_DriverConnect(out)=3D'DSN=3Dlt;DATABASE=3Dlt;SERVER=3D mbench;PORT=3D5=
432;UID=3Dpostgre
s;PWD=3Dxxxxxxxx;SSLmode=3Ddisable;ReadOnly=3D0;Protocol=3D7 .4;FakeOidIndex=
=3D0;ShowOidCol
umn=3D0;RowVersioning=3D1;ShowSystemTables=3D0;ConnSettings= 3D;Fetch=3D100;=
Socket=3D4096;Unk
nownSizes=3D0;MaxVarcharSize=3D254;MaxLongVarcharSize=3D8190 ;Debug=3D1;Comm=
Log=3D1;Optimiz
er=3D1;Ksqo=3D1;UseDeclareFetch=3D1;TextAsLongVarchar=3D1;Un knownsAsLongVar=
char=3D0;BoolsA
sChar=3D1;Parse=3D0;CancelAsFreeStmt=3D0;ExtraSysTablePrefix es=3Ddd_;;LFCon=
version=3D0;Upd
atableCursors=3D1;DisallowPremature=3D0;TrueIsMinus1=3D0;BI= 3D0;ByteaAsLong=
VarBinary=3D0;U
seServerSidePrepare=3D0;LowerCaseIdentifier=3D0;'
CONN ERROR: func=3DPGAPI_GetInfo30, desc=3D'', errnum=3D209, errmsg=3D'Unre=
cognized key
passed to SQLGetInfo30.'
------------------------------------------------------------
henv=3D64210, conn=3D68390, status=3D1, num_stmts=3D16
sock=3D6ae98, stmts=3D6cf08, lobj_type=3D-999
---------------- Socket Info -------------------------------
socket=3D6, reverse=3D0, errornumber=3D0, errormsg=3D'(NULL)'
buffer_in=3D438008, buffer_out=3D442112
buffer_filled_in=3D77, buffer_filled_out=3D0, buffer_read_in=3D=
77

conn=3D68390, query=3D'declare "SQL_CUR74468" scroll cursor with hold for S=
ELECT * ,
"ctid", "oid" FROM address WHERE CLEADDR LIKE(E'004304%') ORDER BY CLEADDR'
conn=3D68390, query=3D'SAVEPOINT _EXEC_SVP_00074468'
conn=3D68390, query=3D'fetch 100 in "SQL_CUR74468"'
[ fetched 1 rows ]
conn=3D68390, query=3D'RELEASE _EXEC_SVP_00074468'
conn=3D68390, query=3D'COMMIT'
conn=3D68390, query=3D'MOVE 0 in "SQL_CUR74468"'
STATEMENT ERROR: func=3DSC_pos_update, desc=3D'', errnum=3D15, errmsg=3D'up=
date list
null'
----------------------------------------------------------=
--
hdbc=3D68390, stmt=3D74468, result=3Da51b8
prepare=3D3, internal=3D0
bindings=3D73c00, bindings_allocated=3D20
parameters=3D63810, parameters_allocated=3D1
statement_type=3D0, statement=3D'SELECT * FROM address WHE=
RE
CLEADDR LIKE(?) ORDER BY CLEADDR'
stmt_with_params=3D'declare "SQL_CUR74468" scroll cursor w=
ith
hold for SELECT * , "ctid", "oid" FROM address WHERE CLEADDR LIKE(E'004304%=
')
ORDER BY CLEADDR'
data_at_exec=3D-1, current_exec_param=3D-1, put_data=3D0
currTuple=3D0, current_col=3D-1, lobj_fd=3D-1
maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=
=3D3,
scroll_concurrency=3D3
cursor_name=3D'SQL_CUR74468'
----------------QResult Info -----------------------------=
--
fields=3D67648, backend_tuples=3Da94f0, tupleField=3D69348=
8,
conn=3D68390
fetch_count=3D1, num_total_rows=3D1, num_fields=3D18,
cursor=3D'SQL_CUR74468'
message=3D'(NULL)', command=3D'FETCH', notice=3D'(NULL)'
status=3D100, inTuples=3D0
CONN ERROR: func=3DSC_pos_update, desc=3D'', errnum=3D0, errmsg=3D'(NULL)'
------------------------------------------------------------
henv=3D64210, conn=3D68390, status=3D1, num_stmts=3D16
sock=3D6ae98, stmts=3D6cf08, lobj_type=3D-999
---------------- Socket Info -------------------------------
socket=3D6, reverse=3D0, errornumber=3D0, errormsg=3D'(NULL)'
buffer_in=3D438008, buffer_out=3D442112
buffer_filled_in=3D18, buffer_filled_out=3D0, buffer_read_in=3D=
18
conn=3D68390, query=3D'close "SQL_CUR74468"'

And a snippet around the "delete" error:
conn=3D68390, query=3D'declare "SQL_CURb2ff0" scroll cursor with hold for S=
ELECT * ,
"ctid", "oid" FROM tstidx WHERE CHP1 LIKE(E'FFF%') ORDER BY CHP1'
conn=3D68390, query=3D'fetch 100 in "SQL_CURb2ff0"'
[ fetched 1 rows ]
conn=3D68390, query=3D'COMMIT'
conn=3D68390, query=3D'MOVE 0 in "SQL_CURb2ff0"'
conn=3D68390, query=3D'delete from "public"."tstidx" where ctid =3D '(1, 31=
)' and
"oid" =3D 3782366'
conn=3D68390, query=3D'SELECT * , "ctid", "oid" FROM tstidx WHERE CHP1 LIKE=
(E'FFF%')
ORDER BY CHP1 where ctid =3D currtid2('"public"."tstidx"', '(1, 31)') and =
"oid" =3D
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=3D68390, query=3D'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.
>=20
> Regards,
>=20
> Luf
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: RE : Cannot update or delete rows

Pascal Viandier wrote:
> Hi Luf,
>=20
>> -----Message d'origine-----
>> De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgres=
ql.org]
>> De la part de Ludek Finstrle
>> Envoy=E9 : July 31, 2006 00:05
>> =C0 : 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly