Skip to main content

Error Handling for SQL

  • January 13, 2021
  • 12 replies
  • 0 views
Alexander 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.

    12 replies

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • January 13, 2021

    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.

    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!

    Alexander Castro
    • Alexander Castro
    • Author
    • Participating Frequently
    • January 13, 2021

    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!

    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. 

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • January 13, 2021

    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. 

    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!

    Alexander Castro
    • Alexander Castro
    • Author
    • Participating Frequently
    • January 13, 2021

    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!

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

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • January 13, 2021

    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!

    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-

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • January 13, 2021

    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-

    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 Buchanan

    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.

    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

     

     

    Alexander Castro
    • Alexander Castro
    • Author
    • Participating Frequently
    • January 15, 2021

    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

     

     

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

    Claude Greiner
    • Claude Greiner
    • Participating Frequently
    • January 15, 2021

    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

     

     

    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 Buchanan
    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

    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.

    Alexander Castro
    • Alexander Castro
    • Author
    • Participating Frequently
    • January 15, 2021
    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
    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.
    D

    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!

    Dear Mr. greiner,
    I feel quite fortunate to have come across you in this discussion. Would you kindly assist me in finding a resolution?
    I require notification logs whenever the SQL server has success or problems.
    many thanks

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement