Error Handling for SQL

[Alex_Castro]

I am trying to write an error-handling routine in my COBOL program for connecting, reading, inserting and deleting tables in my SQL database and would like to know where I can find a list that contains all the possible errors that can potentially be generated along with their code and description. The error codes and their description are assigned to the following variables:

05  SQLCODE         PIC S9(9) COMP-5 VALUE 0.

 05  SQLERRM.

               49  SQLERRML    PIC S9(4) COMP-5.

               49  SQLERRMC    PIC X(70).

These variables are part of the copybook located at : “ C:\Program Files (x86)\Micro Focus\Visual COBOL\cpylib\sqlca.cpy”

As an example, a table row containing a null field returns and assigns the error code “0000000001”  to the SQLCODE variable and the description of  “NULL value returned but no indicator variable supplied.[12]” to the variable SQLERRMC. Having a list of all possible error codes and their description in advance would make it easier to write an error handling routine rather than trying to manually generate errors in order to obtain their description and code number for error handling. Hopefully this makes sense. Thanks in advance.

[greiner]

You Must Show the errons Code and messages by mysql internet side.

code 1 is Not Heavy, you can Write:

if sqlcode = 0 or 1

    Continue

else

  Display ...

end-if

you Must use null-Field By any Fields.

publish your Code and i will help you, i have experience with Mandy sql databases as MySQL, Oracle, MySQL, sqlite and Many others!

[Alex_Castro]

It seems you did not understand my inquiry. It is easy to write code for an error if one knows the error code in advance. I do not know all the possible errors and their codes that can potentially arise. I want to know if there is a list of all the possible errors that can arise so that I may write the code to handle them. 

[greiner]

I have given you a correct answer, but you don’t anderstand it.

yes, there is a list of error codes depending on the used sql database and not depending from cobol error codes. You must show this in the description of error codes from MySQL!
Please search over your internet browser!

[Alex_Castro]

You are correct in that I do not understand your response. Perhaps someone else can clarify it for me. 

[greiner]

see link https://dev.mysql.com/doc/refman/8.0/en/error-message-elements.html

Error Code Ranges

The set of error codes used in error messages is partitioned into distinct ranges, each with its own purpose:

• 1 to 999: Global error codes. This error code range is called “global” because it is a shared range that is used by the server as well as by clients.

  When an error in this range originates on the server side, the server writes it to the error log, padding the error code with leading zeros to six digits and adding a prefix of MY-.

  When an error in this range originates on the client side, the client library makes it available to the client program with no zero-padding or prefix.

• 1,000 to 1,999: Server error codes reserved for messages sent to clients.

• 2,000 to 2,999: Client error codes reserved for use by the client library.

• 3,000 to 4,999: Server error codes reserved for messages sent to clients.

• 5,000 to 5,999: Error codes reserved for use by X Plugin for messages sent to clients.

• 10,000 to 49,999: Server error codes reserved for messages to be written to the error log (not sent to clients).

  When an error in this range occurs, the server writes it to the error log, padding the error code with leading zeros to six digits and adding a prefix of MY-.

• 50,000 to 51,999: Error codes reserved for use by third parties.

The server handles error messages written to the error log differently from error messages sent to clients:

• When the server writes a message to the error log, it pads the error code with leading zeros to six digits and adds a prefix of MY-

[greiner]

Any SQL codes for better anderstanding!

exec sql include sqlca end-exec.
exec sql begin declare section end-exec
exec sql end declare section end-exec
01 sql-connectstring pic x(255).
01 mfsqlmessagetext pic x(250).
01 sqlcode pic s9(05).
01 sql-felder.
05 sql-vert-nr pic 9(10).
05 sql-vert-nr-null pic s9(04) comp-5. PERFORM UNTIL SQLCODE < 0 OR SQLCODE = +100
EXEC SQL
FETCH CSR165 INTO
:sql-kunden-nr
,:sql-branchen-nr
,:sql-vert-nr
,:sql-kd-name1
,:sql-kd-name2
,:sql-kd-plz
,:sql-kd-ort
,:sql-branchen-bez
,:sql-vert-name:sql-vert-name-null
END-EXEC
IF SQLCODE = 0
.....
END-IF

* OpenESQL-Handling, SQL-Fehlermeldungen ausgeben!
perform openesql-error-handling
/
OpenESQL-Error Section.
display 'sqlerror ' sqlcode ' encountered'
display 'sqlstate ' sqlstate
display 'sql message text ' sqlerrmc
display 'sql message message ' sqlerrml
display 'expanded text ' mfsqlmessagetext
display 'Processing terminated '
perform sql-disconnect-all
stop run.
* 0 = The statement ran without error
* 1 = The statement ran, but a warning was generated, see SQLWARN flags
* <0= The statement did not run due to an application, database,
* system, or network error
* -1= Autoconnect failure
*100= Data matching the query was not found or the end of the
* results set has been reached. No rows were processed
*-10000= An ODBC error occurred, but no more details are available
*-19085= This is caused by invalid parameters to a QUERY ODBC
* statement
*-19101= Statement too long
*-19199= ESQL Keyword(s) detected in PREPARE/EXECUTE IMMEDIATE
* statement
*-19313= Too few host variables
*-19413= Data overflow occurred during decimal data conversion
*-19501= No cursor declared
*-19514= Cursor is not prepared
*-19701= NULL connection name
*-19702= Connection name not found
*-19703= Could not make connection
*-19707= Duplicate connection name
*-19708= OpenESQL failed to get details of the connection
*-19822= Improperly initialized User SQLDA
*-19957= Statement text not found or empty
*-20000= Unimplemented embedded SQL feature The COBOL compiler
* may accept some

[Mark_B]

You can use EXEC SQL WHENEVER syntax together with  MFSQLMESSAGETEXT to handle your errors.  MFSQLMESSAGETEXT is updated with a description of the exception condition whenever SQLCODE is non-zero. This will enable you to retrieve messages greater than 70 bytes provided in SQLERRC. 

You can also look at the SQL (Native OESQL) - Whenever Demo - load the solution from the Visual COBOL Samples Browser.

In addition here is a small code sample you can compile and run:

$SET SQL(DBMAN=ODBC) sourceformat(variable)
WORKING-STORAGE SECTION.

EXEC SQL INCLUDE SQLCA END-EXEC.

*> after an sql error this has the full message text
01 MFSQLMESSAGETEXT PIC X(250).

PROCEDURE DIVISION.

EXEC SQL
WHENEVER SQLERROR perform OpenESQL-Error
END-EXEC
*> try connecting to a non-existent dat source name

EXEC SQL
connect to "NOCONNECTION"
END-EXEC

EXIT PROGRAM.
STOP RUN.

*> Default sql error routine / modify to stop program if needed
OpenESQL-Error Section.

display "SQL Error = " sqlstate " " sqlcode
display MFSQLMESSAGETEXT
exit.

This should display :

SQL Error = IM002 -0000019703

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

[Alex_Castro]

There is a lot here to digest but thanks for the resources.

[greiner]

Do you define a odbc Connection in your os (Windows 10)?
The code is only a extract and not a full running program. you must adapt it!
NOCONNECTION must be replace with your connectstring. Then you can see the result in sqlcode.
if sqlcode = 0
continue
else
Display 'no connection' .....
end-if

[Mark_B]

For the purposes of this example there is no need to define a DSN. The code will compile and run /debug.

This is a simple example to show an alternative way of handling SQL errors and to get meaningful messages.

[Alex_Castro]

I am sorry but this is not relevant to my initial inquiry. Connecting the a database or constructing "if" statements in order to handle SQL errors is not what I need help with nor was it part of my inquiry.