Regression with INSERT/UPDATE...RETURNING statements in driver version 13 - Mailing list pgsql-odbc
Hi,
Since we've upgraded to PSQLODBC 13 we've noticed inconsistency with what SQLFetch returns when INSERT/UPDATE statement with a RETURNING clause is executed consecutively.
We're using PostgreSQL ANSI Driver with Postgres 13.
The sequence of events as follows, with the results we're seeing in the logs.
We've also attached the complete log files for reference.
1. PREPARE ins_invhisthead statement.
PREPARE upd_invhisthead statement.
2. EXECUTE "BEGIN"
3. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #1)
(UPDATE Statement) SQLExecute with return code 100 (SQL_NO_DATA_FOUND)
(Expected)
4. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #1)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
5. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #2)
(UPDATE Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(This should have returns code 100 (SQL_NO_DATA_FOUND))
(Retrieve RETURNING value) SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
(The UPDATE statement should have returns code 0, and the SQLFetch shouldn't be executing here as there is no value to retrieve)
6. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #2)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
7. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #3)
(UPDATE Statement) SQLExecute with return code 100 (SQL_NO_DATA_FOUND)
(Expected)
8. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #3)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
9. EXECUTE upd_invhisthead INTO .... USING .... (Update Existing Record #2)
(UPDATE Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
(This should have returns the invhisthead_id with return code 0)
10. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #2)
(INSERT Statement) SQLExecute with return code -1 (SQL_ERROR) ERROR: duplicate key value violates unique constraint "invhead_company_branch_date_cusno_invno_udx
(Expected)
11. EXECUTE "COMMIT"
Since we've upgraded to PSQLODBC 13 we've noticed inconsistency with what SQLFetch returns when INSERT/UPDATE statement with a RETURNING clause is executed consecutively.
We're using PostgreSQL ANSI Driver with Postgres 13.
The sequence of events as follows, with the results we're seeing in the logs.
We've also attached the complete log files for reference.
1. PREPARE ins_invhisthead statement.
PREPARE upd_invhisthead statement.
2. EXECUTE "BEGIN"
3. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #1)
(UPDATE Statement) SQLExecute with return code 100 (SQL_NO_DATA_FOUND)
(Expected)
4. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #1)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
5. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #2)
(UPDATE Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(This should have returns code 100 (SQL_NO_DATA_FOUND))
(Retrieve RETURNING value) SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
(The UPDATE statement should have returns code 0, and the SQLFetch shouldn't be executing here as there is no value to retrieve)
6. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #2)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
7. EXECUTE upd_invhisthead INTO .... USING .... (Update non-existed Record #3)
(UPDATE Statement) SQLExecute with return code 100 (SQL_NO_DATA_FOUND)
(Expected)
8. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #3)
(INSERT Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 0 (SQL_SUCCESS)
(Expected)
9. EXECUTE upd_invhisthead INTO .... USING .... (Update Existing Record #2)
(UPDATE Statement) SQLExecute with return code 0 (SQL_SUCCESS)
(Expected)
(Retrieve RETURNING value) SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
(This should have returns the invhisthead_id with return code 0)
10. EXECUTE ins_invhisthead INTO .... USING .... (Insert Record #2)
(INSERT Statement) SQLExecute with return code -1 (SQL_ERROR) ERROR: duplicate key value violates unique constraint "invhead_company_branch_date_cusno_invno_udx
(Expected)
11. EXECUTE "COMMIT"
--
body, .SiteBody { background-color:#FFFFFF; font-weight: normal; color:#000000; font-size: 14px; font-family: Arial; line-height: 15px; }
.name { font-size: 18px; font-weight: 900; color:#000099; font-family: Helvetica; line-height: 19px; }
.disclaimer { color:#999999; font-size: 9px; line-height: 10px; }
This email message, including any attachments, is for the intended recipients only and may contain information that is privileged, confidential, subject to copyright and/or exempt from disclosure under applicable law. Using, disclosing, copying or distributing such information by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, or are obviously not one of the intended recipients, please notify the sender immediately by reply email and delete this email message together with any attachments.
Patrick Cheung
780-483-2727
1-888-8amador (1-888-826-2367)
www.amador.ca
This email message, including any attachments, is for the intended recipients only and may contain information that is privileged, confidential, subject to copyright and/or exempt from disclosure under applicable law. Using, disclosing, copying or distributing such information by anyone other than the intended recipient is strictly prohibited. If you have received this message in error, or are obviously not one of the intended recipients, please notify the sender immediately by reply email and delete this email message together with any attachments.
Attachment
pgsql-odbc by date: