Re: Prepared statement error with UseServerSidePrepare=1 - Mailing list pgsql-odbc
From | |
---|---|
Subject | Re: Prepared statement error with UseServerSidePrepare=1 |
Date | |
Msg-id | 619ABD714EEDD84E8E582E993559DB36142AFEB6@EXMB12TSTRZ2.tcad.telia.se Whole thread Raw |
In response to | Re: Prepared statement error with UseServerSidePrepare=1 (Hiroshi Inoue <inoue@tpf.co.jp>) |
List | pgsql-odbc |
Hi Hiroshi, I applied your patch on psqlodbc 9.3.3 (NOT on the head branch). Now I can't even get to insert operations, because I get error while application configuration fetch. I have this CONFIG table, from where I load configuration parameters (SELECT VALUE2, VALUE3 FROM CONFIG WHERE VALUE1 = ?) Each execute of the SQL returns one row. So the first execute success, but second returns with 'Null statement result inPGAPI_ExtendedFetch' error. psqlodbc and mylog files are in attechaments. Lev Bukovski -----Original Message----- From: Hiroshi Inoue [mailto:inoue@tpf.co.jp] Sent: 2. kesäkuuta 2014 6:59 To: Bukovski, Lev /External; pgsql-odbc@postgresql.org Subject: Re: [ODBC] Prepared statement error with UseServerSidePrepare=1 Hi Lev, Could you please try the attahced patch? regards, Hiroshi Inoue (2014/05/28 17:52), lev.bukovski@teliasonera.com wrote: > Hi, > > I found some strangeness in psqlodbc's behavior. May be you can > explain to me, do I do something wrong or there is a bug. > > Tested on psqlodbc 9.3.3 and postgresql 9.3.4. > > I have department tree table named KEY11 with 2 indexes on that: > > CREATE TABLE Key11 ( > > ID INTEGER not null, > > Disp VARCHAR (64) not null, > > Search VARCHAR (64) not null, > > CustomerID INTEGER not null, > > ParentID INTEGER not null, > > primary KEY (Search,Disp,CustomerID,ParentID) > > ); > > create unique index key11_idx on Key11 (ID); > > create unique index key11_disp_idx on Key11(Disp,CustomerID,ParentID); > > So I can't insert same named department for same customer and on same > organization level (ParentID). > > I do login to the database and prepare all SQL statements only once. > Afterward prepared SQL statements are reused with different parameters. > > So I do insert new department to KEY11 table: > > SQLPrepare - INSERT INTO KEY11 (ID, DISP, SEARCH, CUSTOMERID, > PARENTID) VALUES (?,?,?,?,?) > > SQLExecute with values for example (6022, 'New department', 'NEW > DEPARTMENT', 2, 6021) > > Everything fine. > > Now I make another insert with same parameters: > > SQLExecute with values for example (6023, 'New department', 'NEW > DEPARTMENT', 2, 6021) > > Fine, I get an error: duplicate key value violates unique constraint > "key11_disp_idx" > > Now I rename department 6022 "New Department" -> "New Department 2": > > SQLPrepare - UPDATE KEY11 SET DISP = ?, SEARCH = ? WHERE ID = ? AND > CUSTOMERID = ? > > SQLExecute with values for example ('New department 2', 'NEW > DEPARTMENT', 6022, 2) > > Fine again. Department's name is renamed, so I can try to insert new > department again. Error while executing the query > > Third insert with new department in parameters: > > SQLExecute with values for example (6024, 'New department', 'NEW > DEPARTMENT', 2, 6021) > > Still an error, but only: "Error while executing the query" ? > > But if I query now the KEY11 table, I see that New department was > added despite of error: > > select * from key11 where parentid = 6021; > > id | disp | search | > customerid | parentid > > ------+-----------------------------+-----------------------------+------------+---------- > > 6022 | New department 2 | NEW DEPARTMENT 2 > | 2 | 6021 > > 6024 | New department | NEW DEPARTMENT > | 2 | 6021 > > (3 rows) > > I noticed the behavior on psqlodbc 9.2.1. In that version third insert > returned same error, but didn't really insert the data. > > If I disable UseServerSidePrepare=0, then third insert executes > successfully. > > Thanks in advance, > > Lev Bukovski
Attachment
pgsql-odbc by date: