Re: Prepared statement error with UseServerSidePrepare=1

Hi Lev,

Could you please try the attahced patch?

regards,
Hiroshi Inoue

(2014/05/28 17:52), lev(dot)bukovski(at)teliasonera(dot)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