From: | <lev(dot)bukovski(at)teliasonera(dot)com> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Prepared statement error with UseServerSidePrepare=1 |
Date: | 2014-05-28 08:52:26 |
Message-ID: | 619ABD714EEDD84E8E582E993559DB36142ADA8C@EXMB12TSTRZ2.tcad.telia.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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 | Content-Type | Size |
---|---|---|
mylog_merex28757.log | application/octet-stream | 211.4 KB |
psqlodbc_merex28757.log | application/octet-stream | 7.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-05-28 10:50:20 | Re: Failing tests on Fedora |
Previous Message | Michael Paquier | 2014-05-27 23:30:50 | Re: Failing tests on Fedora |