When there is a primary key violation in SQL Server 2012, it returns the following error

SQLDBCode = 2601

SQLSTATE = 23000

We get both SQLDBCode and SQLState in SQL Server also.  

Hi Siva,

I have now installed PostgreSQL and tested ODBC behavior. I also dove into PostgreSQL's online docs. My setup is "new version of all":

• PB 2019 R2 #2203 (recent beta)
• PostgreSQL ODBC 11
• PostgreSQL 12.1

I find exactly same behavior as you and Chris!

- HOWEVER - This is not an error in PowerBuilder; nor in PostgreSQL's ODBC driver.
This is instead a deliberate design decision in PostgreSQL according to online docs (link => 35.8.3 SQLSTATE vs. SQLCODE):

... SQLCODE is deprecated ... new applications are strongly encouraged to use SQLSTATE.

Same encouragement for every PostgreSQL version since 7.4, released 04-Oct-2010. I therefore don't see that decision changing.

Conclusion

For PostgreSQL via ODBC, your app must rely on SQLSTATE.
SQLSTATE's value is part of SQLErrText (last 5 characters on first text line).
It is impossible to convert it to SQLDBCode in any generic way.

HTH /Michael

SAMPLE CODE - Example of how to extract SQLSTATE from SQLErrText.

// FUNCTION of_GetSQLState( readonly string as_sqlErrText ): string
//
// Extracts SQLSTATE's value from the text.
// Returns NULL if text doesn't refer to SQLSTATE.
constant string SQLSTATE_LABEL = "SQLSTATE = "
constant int    SQLSTATE_LIMIT = 5

long ll_start
string ls_sqlState

// Check whether text contains SQLSTATE value
if not Match(as_sqlErrText, (SQLSTATE_LABEL + Fill(".", SQLSTATE_LIMIT))) then
   SetNull(ls_sqlState)
   return ls_sqlState // No SQLSTATE => ABORT
end if

// Extract SQLSTATE value from text
ll_start = Pos(as_sqlErrText, SQLSTATE_LABEL) + Len(SQLSTATE_LABEL)
ls_sqlState = Mid(as_sqlErrText, ll_start, SQLSTATE_LIMIT)
return ls_sqlState // DONE
// -----------------------------------------------------------------
// How to call from DBError event:
this.il_Latest_SQLDBCode  = code
this.is_Latest_SQLState   = of_GetSQLState(sqlErrorText)
this.is_Latest_SQLErrText = sqlErrorText
...

Reported this to the support, Bug ID: 3831 

https://www.appeon.com/standardsupport/search/view?id=3831

Let's see what support people says.

Happiness Always
BKR Sivaprakash

Michael Kramer

Yes, SQLSTATE gives the right postgresql error codes and I modified the program to take that value instead of sqldbcode.   

Not sure whether it will work the same way when I connect SQL Server using either OLE-DB or Native driver.  Should I take SQLDBCODE or (extracted) SQLSTATE code ?

Happiness Always
BKR Sivaprakash

You said that blocking occurs when the error message is displayed. This should not occur. You should perform the ROLLBACK before displaying the error message. The ROLLBACK will clear the error information from SQLCA so on inline SQL statements you have to save the error number/text in local variables first.

Siva, Chris, et al

ODBC standard describes each error message using 3 properties. (I know I'm mixing info regarding SQLGetDiagRec and SQLGetDiagField to deliver overview instead of tech details. See ODBC API Reference for details.

• SQLState (aka. SQL_DIAG_SQLSTATE or short SQLSTATE) - 5 character code
• NativeErrorPtr (aka. SQL_DIAG_NATIVE) - Numeric code
• MessageText (aka. SQL_DIAG_MESSAGE_TEXT) - String containing actual error message

To me your descriptions seem to indicate PostgreSQL driver delivers SQLState and MessageText. However it may leave the native numeric code as a simple 0 vs. 1 = OK vs. error.

SQLState is never returned in SQLDBCode because it is alphanumeric. Example from SQL Anywhere:

• MessageText :: No primary key value for foreign key '%1' in table '%2'
• SQLCODE (native error code) :: -194
• SQLSTATE :: 23503

Error message in SQLCA.SQLErrText includes the SQLSTATE value just like Chris describes for PostgreSQL.

I would consider a function on my transaction class = PUBLIC string of_GetSqlState( ) to extract SQLState from SQLErrText and return it. That could help you write ODBC generic error handling since you base your logic on SQLSTATE instead of "flimsy" native error codes.

HTH /Michael

Hi BKR;

   I just tested the DBError Event handling of the DW Control with PostGreSQL v12.1 and PB2019R2. There is definitely a bug in the PostgreSQL DB handler. I do not see the 23505  DB Error Code in either the DC's DBError event arguments or when introspecting the Transaction Object directly (SQLCA.sqldbcode). These values are always Zero and Empty instead of what they should be.

  The DBError event code in my test was as follows ....

IF  sqldbcode = 23505  THEN
    MessageBox ("Duplicate", "The new Bonus data already exists for that employee and date!" )
else
    MessageBox ( "DB Error", "Code: " + String (SQLDbcode) + ", Msg: " + &
                        SQLErrtext )
END IF
Return +3

The end result was always the ELSE ...

    I am now suspecting that there might be an issue with the DB "Interface" ODBC driver that handles the direct PostgreSQL DBMS interaction. This issue can be replicated in PB versions PB2017Rx through PB2019Rx.

  Please open a Support Ticket on this critical issue!

Regards .... Chris

PS: When there is no code in the DBError event (same in the DB Painter) ...

Michael Kramer,

As you suggested, I moved the error display at the end of the transaction.  Works perfectly.  One more clarification.

Seems sqldbcode (@ dberror) is not returning the same value as the DBMS error codes.  The sqldbcode (long datatype) is 1 for both Primary / Unique key violations and Foreign key violation, but it's -3 when there is a 'row changed between retrieve and update error'.  The equivalent Posgres error code is 23505 (Primary/unique), 23503 (Foreign key) and it returns alpha numeric data.

Help says it's a database-specific error code, but I get only 1 as return value instead of 23505 or 23503.   

Any thing need to be changed for postgres ?   Btw, postgresql error code contains alphabets in its error code.  May be that's the reason ?

Any idea ?

Happiness Always
BKR Sivaprakash

Hi Siva,

I have worked in environments where prolonged locking is a sin and no technical details may be provided to any app user since each such detail exposes a potential attack surface.

When to Display Error

• DataWindow's DBError event defers action to its Transaction object using RETURN 1
• DataStore's DBError event defers action to its Transaction object using RETURN 1
• Transaction's DBError captures current error condition but delays display until transaction has rolled back using POST of_DisplayErrorMessage( code, sqlErrorText, sqlSyntax ).
• Current transaction is rolled back before post'ed function executes.
• Function of_DisplayErrorMessage formats error message and displays it to the user

 What to Display in Error Message

• Every user message is sanitized to avoid exposing any technical details that are valuable to an attacker.
  
  • App ignores sqlErrorText + sqlSyntax.
  • Instead, app maps code into meaningful error description.
  • App also displays user's "action context" (EX: "Find customer data" or "Run report")
• Function of_GetErrorText( code ) converts error number to relevant text.
  This depends on DBMS -- EX: Deadlock detected :
  • Code = 1205 for MS T-SQL
  • Code = -306 for SAP SQL Anywhere
  • Code = 60 for Oracle PL/SQL
  • Code = -911 for IBM DB/2
• Sample user messages:
  • Failed to run report.
    App lost contact to the database.
    Click here to contact helpdesk...
  • Failed to find customer data.
    Please adjust search criteria and try again.
    Click here to contact helpdesk...
  • Failed to save data.
    Check your data entry and try again.
    Click here to contact helpdesk...
• In internal dev environment app may log extensive technical details for developers to investigate all details.

HTH /Michael

Hi Sivaprakash... FWIW: I always use general clear messages in my PB Apps and have never had any application user issues with that.

Perhaps you could have a table in the database with the alternate message text and the error number as the primary key. Then your error handling code would just have to select the error message and display it. If you aren't already, you should have a errorlog table to record errors.

Hi Sivaprakash,

dberror event is the right place to be.

If you do not want error message windows just 

return 1

at the end of the script.

Best,

.m