Обсуждение: Prepared statement error with UseServerSidePrepare=1

On 05/28/2014 01:52 AM, 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 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.

I would tail the Postgres log and see what it says when you go through
the sequence. My suspicion is that it is a transaction rollback not
being handled.

>
> Thanks in advance,
>
> Lev Bukovski
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/30/2014 05:12 AM, lev.bukovski@teliasonera.com wrote:
> I do rollback after any error and commit even after rollback :)

So what does the Postgres log show during this?

You have mentioned errors, but you have not said exactly what errors.

So what are the actual errors?

>
> bool RollBack()
> {
>      RETCODE rc=SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);
>      if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
>      {
>        return false;
>
>      }
>      return true;
> }
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/01/2014 12:19 PM, lev.bukovski@teliasonera.com wrote:
> Rollback is successfull, no problems on that.
>
> Log files in the original message have exact error code and description,
> but like I wrote
>
> on wrong insert I get logical error "duplicate key value violates unique
> constraint "key11_disp_idx". Error while executing the query", but when
> I afterward try to make correct insert, I still get an error from
> psqlodbc "Error while executing the query", which is like a last part of
> the first error.
>

Hmm, not sure what is going on.

I would say you will need to show the full code of what you are doing in
the Prepare/bind/Execute cycle. Hopefully others can see if there is
anything out of place. Would not hurt to crank the psqlodbc logging and
capture that also.


--
Adrian Klaver
adrian.klaver@aklaver.com

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

(2014/06/02 22:11), lev.bukovski@teliasonera.com wrote:
> 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.

Thanks for the report.
Please apply the attached patch also.

It's the same as the one attached in the thread
  [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
.

regards,
Hiroshi Inoue

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

Hi,
The patches together are working. All problems I noticed in psqlodbc 9.3.3 are gone. Thank you very much.
Now, I guess, I have to test my application all over again.
When can I expect a new release of the psqlodbc driver?

Lev Bukovski

-----Original Message-----
From: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
Sent: 2. kesäkuuta 2014 18:17
To: Bukovski, Lev /External; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Prepared statement error with UseServerSidePrepare=1

(2014/06/02 22:11), lev.bukovski@teliasonera.com wrote:
> 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 in PGAPI_ExtendedFetch' error. 
>
> psqlodbc and mylog files are in attechaments.

Thanks for the report.
Please apply the attached patch also.

It's the same as the one attached in the thread
  [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause .

regards,
Hiroshi Inoue