Prepared statement error with UseServerSidePrepare=1

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