Prepared statement error with UseServerSidePrepare=1

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

Responses

Browse pgsql-odbc by date

  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