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:

Previous
From: Denise McGrath
Date:
Subject: Re: 126 Error Connecting With psqlODBC 32 bit
Next
From: Heikki Linnakangas
Date:
Subject: Re: Mentioning CPU for Windows build in docs