Request to return SQL State of 23505 instead of 23000 by the PostgreSQL Wire Protocol driver when primary key constraint is violated during insert

Mar 13, 2014
Knowledge

Title

URL Name

000047730

Article Number

000168403

Environment

Product: Connect for ODBC PostgreSQL Wire Protocol driver Version: 7.1 OS: All supported platforms Database: PostgreSQL Application: ODBC Test, Example

Question/Problem Description

Request to return SQL State of 23505 instead of 23000 by the PostgreSQL Wire Protocol driver when primary key constraint is violated during insert. The PostgreSQL Wire Protocol driver returns a SQLState 23000 when inserting duplicate data in a primary key column. The Native PostgreSQL ODBC and JDBC drivers return a SQLState of 23505.

Steps to Reproduce

Clarifying Information

Steps to reproduce issue:-
1) Connect to ODBC Test using the DataDirect PostgreSQL Wire Protocol driver.
2) SQLExecDirect:

CREATE TABLE pktest (

id integer,

name varchar(10),

CONSTRAINT pktest_id PRIMARY KEY(id)

)

3) SQLExecDirect: insert into pktest values (1,'ABC')
4) SQLExecDirect: insert into pktest values (1,'ABC')

5) Repeat steps 1 and 4 using the Native PostgreSQL driver.

Observed Results:-

DataDirect PostgreSQL Wire Protocol driver returns SQLState 23000 while the Native PostgreSQL driver returns SQLState 23505.

DataDirect PostgreSQL Wire Protocol driver output:-

In: hstmt = 0x007E3248, szSqlStr = "insert into pktest values (1,'ABC')", cbSqlStr = -3

Return: SQL_ERROR=-1

stmt: szSqlState = "23000", *pfNativeError = 3505685, *pcbErrorMsg = 218, *ColumnNumber = -1, *RowNumber = 1

MessageText = "[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: duplicate key value violates unique constraint "pktest_id"(Detail Key (id)=(1) already exists.;File nbtinsert.c;Line 397;Routine _bt_check_unique;)"

Native PostgreSQL driver output:-

In: hstmt = 0x0477DBA0, szSqlStr = "insert into pktest values (1,'ABC')", cbSqlStr = -3

Return: SQL_ERROR=-1

stmt: szSqlState = "23505", *pfNativeError = 7, *pcbErrorMsg = 129, *ColumnNumber = -2, *RowNumber = -2

MessageText = "ERROR: duplicate key value violates unique constraint "pktest_id" Key (id)=(1) already exists.; Error while executing the query"

Error Message

stmt: szSqlState = "23000", *pfNativeError = 3505685, *pcbErrorMsg = 218, *ColumnNumber = -1, *RowNumber = 1 MessageText = "[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: duplicate key value violates unique constraint "pktest_id"(Detail Key (id)=(1) already exists.;File nbtinsert.c;Line 397;Routine _bt_check_unique;)"

Defect Number

Enhancement Number

Cause

Per the ODBC specification, the DataDirect PostgreSQL Wire Protocol driver is returning the correct SQLState of 23000.

Resolution

It was determined that the Progress Product is functioning as designed.

An enhancement to the product can be requested through the Progress Community via an Ideas submission. Customer feedback is valuable and Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release. Once an Idea is submitted the Progress Software Community will have the opportunity to comment on and vote for the Idea.

For detailed information on how to submit an Idea, please refer to knowledgebase article "How to submit an idea for a Progress product enhancement".

Workaround

Native error code 3505685 (in base 10) is 23505 in base 36 which can be used as a workaround for this issue.

Notes

Keyword Phrase

Last Modified Date

11/20/2020 7:33 AM

Request to return SQL State of 23505 instead of 23000 by the PostgreSQL Wire Protocol driver when primary key constraint is violated during insert

Files(0)

Was this article helpful?

Was this article helpful?

Request to return SQL State of 23505 instead of 23000 by the PostgreSQL Wire Protocol driver when primary key constraint is violated during insert

Information

Related Articles