1. Sivaprakash BKR
  2. PowerBuilder
  3. Tuesday, 24 December 2019 08:44 AM UTC

Hello, 

Using PB 2017 R3, Postgresql 11

Want to display better error message(s) to users, when error(s) returned by database.   All users can't understand message(s) like Primary key violation, check constraint error, Unique key violation etc..

Currently I used to display the error message and error code, returned by the database.  It starts with 

SQLState = P0001
ERROR:  Partially Paid.  Cannot Delete...; Error while executing the query;

No Changes Made to Database;

Update test_table SET Code = '100' where Code = '200'

This error message says something, as it's raised using RAISE EXCEPTION in a trigger.  For Primary Key Violations, Unique Key Violation.. and for others, we may not get such messages.  

How such messages could be translated that's understandable to the users?  

Currently I display it in the DBError event itself.  But this blocks other users, in multi-user environment, from proceeding till this messagebox is closed.  

Would like to have your advice regarding the displaying the error messages in a better way and the place (event) where it could be displayed.

Happiness Always
BKR Sivaprakash

 

Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 4 January 2020 10:49 AM UTC
  2. PowerBuilder
  3. # 1

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.  

 

Comment
  1. Michael Kramer
  2. Saturday, 4 January 2020 12:26 PM UTC
SNC returning both SQLDBCode and SQLState is great! I'm not sure all DB drivers and all DBMS versions deliver SQLSTATE as part of the error message for every single error condition. My recommendation >>

Decide up front which set of error codes you want to handle -- and make sure PB-DB-interface + DB-driver + DBMS deliver that set of error codes!

Using SQLState for the time being requires you extract it from SQLErrText. My function is example of how one can do that.

I expect we won't see SQLState in Transaction object until PB 2019 R3 - or more probably next major version of PB.
  1. Helpful
  1. Sivaprakash BKR
  2. Saturday, 4 January 2020 12:53 PM UTC
I agree, it should not affect existing applications in any way. Let's see what Appeon suggests. Meanwhile, as I told earlier, I'm proceeding with the extracted SQLState code from sqlErrText.
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Friday, 3 January 2020 08:46 AM UTC
  2. PowerBuilder
  3. # 2

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
...
Comment
  1. Sivaprakash BKR
  2. Saturday, 4 January 2020 10:31 AM UTC
What help says about SQLDBCOde



Long by value. A database-specific error code.

See your DBMS documentation for information on the meaning of the code.

When there is no error code from the DBMS, sqldbcode contains one of these values:

-1 -- Cannot connect to the database because of missing values in the transaction object.

-2 -- Cannot connect to the database.

-3 -- The key specified in an Update or Retrieve no longer matches an existing row. This can happen when another user has changed the row after you retrieved it.

-4 -- Writing a blob to the database failed.



Your code need to return SQLDBCode instead of NULL when there is no SQLState in SQLErrText.
  1. Helpful
  1. Michael Kramer
  2. Saturday, 4 January 2020 10:46 AM UTC
The decision to not add SQLState dates back to Sybase. ANSI-SQL 92 is ~25 years old.

Adding SQLState is potentially breaking change. Also, this change has limited value for DB drivers and DBMS already using SQLDBCode. I expect Appeon deems this an enhancement request, not a bug. And therefore I expect enhancement maybe in next major PB release (not PB 2017, nor PB 2019).

Will SQLState remain as char(5)? I expect so. Revisions to the standard probably adds new codes without changing datatype.

/Michael
  1. Helpful
  1. Ken Guo @Appeon
  2. Monday, 6 January 2020 01:44 AM UTC
Hi Sivaprakash,



As Michael said, it is not recommended to use SQLCode in the PostGreSQL database.

Currently I suggest you use the method Michael provided to work around the issue you encountered in the DBError event.

In the meantime, the Appeon developers plan to do a detailed analysis of this limited value in PB 2019 R3.



Regards,

Ken

  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 2 January 2020 10:57 AM UTC
  2. PowerBuilder
  3. # 3

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

 

Comment
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Wednesday, 1 January 2020 02:07 AM UTC
  2. PowerBuilder
  3. # 4

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

 

Comment
  1. Michael Kramer
  2. Thursday, 2 January 2020 07:46 AM UTC
Without ODBC layer there is no SQLSTATE. For all other drivers you rely on SQLDBCode. To me, PostgreSQL is the first DB driver exposing no native error codes. I had to dive into ODBC standard docs to understand what was going on with the ODBC driver for PostgreSQL.
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Wednesday, 1 January 2020 00:32 AM UTC
  2. PowerBuilder
  3. # 5

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.

Comment
  1. Sivaprakash BKR
  2. Wednesday, 1 January 2020 02:08 AM UTC
Yes Roland, modified the program to dispaly DB Errors at the end of the transactions, using (extracted) SQLSTATE value and displaying the generic error message(s) from a table.

  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 31 December 2019 20:32 PM UTC
  2. PowerBuilder
  3. # 6

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

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 31 December 2019 17:22 PM UTC
  2. PowerBuilder
  3. # 7

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) ...

 

Comment
  1. Sivaprakash BKR
  2. Wednesday, 1 January 2020 01:58 AM UTC
Chris,

SQLDBCode is a long value, where as SQLSTATE could return alphanumeric values, for Postgresql. You could check at

https://www.postgresql.org/docs/11/errcodes-appendix.html



  1. Helpful
  1. Sivaprakash BKR
  2. Wednesday, 1 January 2020 02:30 AM UTC
Can we expect an alphanumeric value from a long variable ?
  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 1 January 2020 21:34 PM UTC
Hi BKR;

That is why I suggested opening a Support Ticket for this issue. The SQLCA.SQLDBCode in the ANSI/ISO 89 standard was replaced in the ANSI/ISO-SQL 92 standard by SQLSTATE. Neither PB, IM or PS have kept up to this change. FYI: https://docs.oracle.com/cd/B28359_01/appdev.111/b31231/chapter8.htm#CEGDFHIJ

We should be able to code ... SQLCA.SQLState ...

Regards ... Chris
  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 31 December 2019 13:44 PM UTC
  2. PowerBuilder
  3. # 8

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

 

Comment
  1. Michael Kramer
  2. Tuesday, 31 December 2019 15:40 PM UTC
Hi Siva, not having PostgreSQL on my laptop makes repro hard. Transaction object itself has several status info properties:

Generic properties: long SQLCode + long SQLNRows

DBMS dependent: long SQLDBCode + string SQLErrText + string SQLReturnData.

Any non-numeric statuses are probably mapped by the ODBC driver or some other PostgreSQL component. My personal experience with PostgreSQL is brief and a decade ago so I can't tell you specific details.

NOTE - "Row changed between retrieve and update"

DataWindow/DataStore reports this outcome when SQLCode = 0 and SQLNRows = 0. So the SQL is valid but DW engine generated the SQL expecting it to impact exactly one row. Seen from the database this is not an error condition.Therefore the "DW error code". Online help for "DBError event (DataWindows)" has more details.

I may install PostgreSQL soon and try it with PB since my current contract ended today. Might as well refresh my PostgreSQL experience while looking for next PowerBuilder project.

Happy New Year! /Michael
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 29 December 2019 05:22 AM UTC
  2. PowerBuilder
  3. # 9

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

Comment
  1. Sivaprakash BKR
  2. Tuesday, 31 December 2019 13:26 PM UTC
Yes. Capture the error code in DB error event, display it after rollback of the transaction.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 27 December 2019 16:42 PM UTC
  2. PowerBuilder
  3. # 10

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

Comment
  1. Sivaprakash BKR
  2. Tuesday, 31 December 2019 13:24 PM UTC
Prolonged display of error messages from DB Error event by one user, blocks other user(s). The first user should close those message box sooner.
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Wednesday, 25 December 2019 14:37 PM UTC
  2. PowerBuilder
  3. # 11

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.

Comment
  1. Sivaprakash BKR
  2. Thursday, 26 December 2019 05:02 AM UTC
So far not recorded any error logs. Will consider Roland Smith.
  1. Helpful
  1. Sivaprakash BKR
  2. Tuesday, 31 December 2019 13:25 PM UTC
Now proceeding that way.
  1. Helpful
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Tuesday, 24 December 2019 10:07 AM UTC
  2. PowerBuilder
  3. # 12

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

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 24 December 2019 15:38 PM UTC
In addition to what Marco said in the DB Error event, I would ...

1) Display a more friendly user message like "A database error has occurred. Please contact application support".

2) Log the actual DB error number and message to an Application log file

3) Allow the user to decide the flow ... "Continue Yes/No?".

4) Suppress the DB Error event like Marco suggests.

  1. Helpful
  1. Sivaprakash BKR
  2. Thursday, 26 December 2019 04:59 AM UTC
Chris Pollach,

Will a general message be sufficient ? Won't it be better to have a message that describes the error ?



Well, I would like to know where / when this error could be displayed ? Say if user A is getting is this error and we display the message at DB error Event, user B and C could get locked, if they get some / same DB error. This lock is there till user A dismisses the error message.



Since the whole things being in a transaction, with Autocommit set to False, as neither commit or rollback happens at DB error event, other users need to wait.



Would like to know whether this wait could be avoided, by placing this information in any other (better) event ?

  1. Helpful
  1. Sivaprakash BKR
  2. Thursday, 26 December 2019 05:01 AM UTC
Marco Meoni

Yes currently those messages get displayed at DB error event only. Searching a better place to avoid the locking in some circumstances.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.