IBM Support

Client Access ODBC: Common External Stored Procedure Errors

Troubleshooting


Problem

This document describes common errors associated with external stored procedures. Most errors involve an incorrect or missing procedure definition.

Resolving The Problem

Overview

Stored procedures can be used to improve the performance and functionality of an ODBC application. Almost any operating system program written in any programming language can act as a stored procedure. Client Access ODBC stored procedures support input, input/output, and output parameters. The stored procedure program can return a result set by specifying one or more cursors to return (from an embedded SQL statement) or by specifying an array of values. The ODBC 3.0 procedure return value parameter ( {[?=] Call ...}) is supported with V5R1M0 Client Access Express and the V5R1M0 operating system and later.

Procedures usually need to be defined. The definition informs IBM DB2 how to call the external program and how to map between SQL data types and native data types. This document discusses common errors due to missing or incorrect procedure definitions.

    • - Creating a procedure definition

      ] Stored procedures that contain parameter markers (required for output or input/output parameters) must be defined to DB2/400. This is done by using the CREATE PROCEDURE SQL statement. This SQL statement creates catalog information describing the stored procedure and its parameters. Because this information is stored in the catalog tables, the create only needs to be performed once for the lifetime of the stored procedure. Most errors occur because of missing or invalid procedure definitions. Although the definition is not always required, we strongly recommended that you define every procedure with a CREATE PROCEDURE statement. If you choose not to create the procedure definition you must also follow the rules outlined in the SQL Programming Concepts manual. Failure to follow these rules could result in corrupted data or severity 40 errors during the call. The following is taken from the V4R5M0 SQL Programming Concepts manual:

      The following rules pertain to the processing of a dynamic CALL statement when there is no CREATE PROCEDURE definition: o All arguments are treated as IN type parameters. o The CALL type is GENERAL (no indicator argument is passed). o The program to call is determined based on the procedure name specified on the CALL and the naming convention. o The language of the program to call is determined based on information retrieved from the system about the program. ...the length of the expected argument in the program must be kept in mind. If program MYLIB.P3 expected an argument of only 5 characters, [and seven are passed] the last 2 characters of the constant specified in the example would be lost to the program. For numeric constants passed on a CALL statement, the following rules apply: o All integer constants are passed as fullword binary integers. o All decimal constants are passed as packed decimal values. Precision and scale are determined based on the constant value. For instance, a value of 123.45 is passed as a packed decimal(5,2). Likewise, a value of 001.01 is also passed with a precision and scale of 5 and 2, respectively. o All floating point constants are passed as double-precision floating point.
      Note: The V3R1M0 operating system did not originally support the CREATE PROCEDURE statement. The CREATE PROCEDURE SQL statement supersedes the DECLARE PROCEDURE SQL statement. Although DECLARE PROCEDURE can still be used, it is not recommended. DECLARE PROCEDURE stores parameter information in the ODBC extended dynamic SQL package rather than the catalog tables. The application must carefully manage the SQL package to maintain its integrity. The ODBC catalog APIs cannot be used to retrieve stored procedure information that was created using this method. See References below for further information and sample programs on ODBC Stored Procedures. [


    • - Error: [DB2/400 SQL]SQL0444 - External Program &A in &B Not Found

      ] Message SQL0444 is generated on an execute or execute direct when the database server is unable to locate the program object defined for an external procedure. Note that this is not the procedure name but the program object used to implement the procedure. The database first resolves a default collection for the procedure name if one wasn't specified. It looks up this procedure in QSYS2/SYSPROCS and attempts a call on the "external_program" defined there. The library for the program name may be a specific library or *LIBL depending on the naming convention in affect at the time the CREATE PROCEDURE was run. Run the following query to view the program name defined for the procedure: select specific_schema, specific_name, external_name from qsys2.sysprocs where specific_name = '' /* note the name is case sensitive */ For further information see document N1010026, Client Access ODBC: Default Libraries Setting, for further information on setting the default collection. To link to N1010026 immediately, click here Client Access ODBC: Default Libraries Setting [


      • - Error: Incorrect Data Returned on OUTPUT and INPUT_OUTPUT Parameters

        ] An ODBC trace and communications trace is useful for troubleshooting problems involving parameter markers. The ODBC sql log can be used to verify the parameter direction was set correctly. The communication trace can be used to check the values returned.

        o The programmer incorrectly declared a parameter as IN on the CREATE PROCEDURE or DECLARE PROCEDURE.
        o The ODBC SQLBindParameter API incorrectly specified fParamType as SQL_PARAM_INPUT.
        o The procedure was defined using both DECLARE PROCEDURE and CREATE PROCEDURE and the declare procedure has it defined as input. Declare procedure takes precedence over create procedure.
        o The procedure was called with literals instead of parameters: Call sp1 ('aaa', 12.1) instead of Call sp1(?,?). Note that this is sometimes caused by the programming tool being used without the programmer being aware of it.
        o The stored procedure program incorrectly returned the parameter. This is seen more often in C where a copy of a inout parameter may have been inadvertently modified rather then the actual value on the call stack.
        o An application error corrupted the output. This is a common error with ADO, DAO and older versions of Powerbuilder (3.0 and 4.0). When using ADO you must use the syntax that forces ADO to use a catalog lookup or else manually set the parameter direction property. See the ADO documentation for details.

        		When using Visual Basic to make direct ODBC API calls, the programmer must verify that Visual Basic has not changed the address of the parameter that was set on the SQLBindParameter API. This usually occurs when binding a String data type. It can results in corrupted data or a program exception. When using DAO, check the version of DAO. DAO 3.0 and earlier did not support input/output or output parameters. ADO, RDO or DAO 3.5 or later must be used. Powerbuilder (3.0 and 4.0) supported only input parameters on stored procedures. Remote Program Call (RPC) from Powerbuilder must be used to work with input/output or output parameters.
        [


      • - Data mapping errors and incorrect data on INPUT parameters

        ] Input parameter problems related to incorrect data, MCH1202 decimal data error, and other application errors usually involve incorrect or missing procedure definitions. DB2/400 uses the procedure definition to convert data between the SQL data type and native programming language data type. The definition also specifies how the parameters are passed on the stack. See SQL Programming Concepts and SQL Reference for further information on calling conventions and parameter mapping. Programmers should also check SQL Programming with Host Languages (available in iSeries Information Center) to verify that they selected a compatible data type for the language they are using. The manual contains specific information for each OS/400 or i5/OS programming language (RPG, COBOL, C, Java, BASIC, CL, PL/I, and so on). [

        • - Procedure receives data from the previous call to the procedure when passed a null input parameter

          ] This error can happen if a stored procedure allows null values for parameters; the procedure is called more than one time during the same connection to the database server; and a null value is set on the later call. The data buffer for the parameter passed to the stored procedure external program may not be reinitialized between calls when the parameter is set to a null value. Programmers should check the null indicators before attempting to access the parameter value. If the indicator indicates a null value the parameter's data buffer is undefined and should be ignored. Assuming that the data buffer is reset to some "null" value or attempting an operation on that data (such as a string/numeric conversion) may result in an application error. When a procedure is defined as allowing null values for parameters, the null information is passed to the procedure program as separate parameters called null indicators. There is one indicator variable for every parameter in a stored procedure. The indicator variables are integer variables which indicate if the parameter value is null or if the parameter data buffer contains the value. The exact method used to pass the indicator to the procedure's external program may vary depending on the calling convention used on the CREATE PROCEDURE statement. For more information and samples, refer to the DB2 UDB for AS/400 SQL Programming Concepts manual located at the InfoCenter web site. Also, keep in the mind the distinction between null and an empty string. This can be confusing in ODBC. If the ODBC application passes a valid data buffer of length zero (rgbValue is a pointer to null and pcbValue is Null or pointer to SQL_NTS or pcbValue is pointer to 0) then this is defined as an empty string (VARCHAR field with length 0). The ODBC application must set the pcbValue to pointer to SQL_NULL_DATA to actually set the null value. [


          • - Compile Error: SQL5011 Position Host Structure Array Not Defined or Not Usable

            ] The "Coding SQL Statements in C Applications" chapter of the SQL Programmers Manual states that Character host variables must be: Single-character form; NUL-terminated character form; or VARCHAR structured form. You must use the VARCHAR form (unless the length is 1) when returning an array result set or the Message SQL5011 error is returned. [


            • - Changes Made by Stored Procedure are Lost When Job Disconnects

              ] The V4R4M0 operating system with the latest database group PTF and all later releases include code changes to support autocommit. The autocommit support resolves the problem. The problem affects earlier operating system releases where the stored procedure is compiled with an isolation level (commit level) other than *NONE, and the ODBC job sets autocommit to True (isolation level of *NONE). These versions of DB2/400 do not support autocomit. The work done by the procedure is not committed and is rolled back when the job ends. To circumvent the problem, upgrade the operating system or use matching isolation levels in the stored procedure and ODBC program. [

              • - APARs That Added New Function

                ] SA67577 (V4R1M0) Divide by zero SA71201 (V4R2M0) 0 Row array result set [

                • - Error: [DB2/400 SQL]SQL0204 - in type *N not found

                  ] This error is generated when preparing or running a stored procedure if the call uses parameter markers and the server is unable to find a valid declaration of the stored procedure. As mentioned in "defining stored procedures" section, if a stored procedure call uses parameter markers then the procedure must be defined. This applies even if the application does not describe the parameter. If CREATE PROCEDURE was used, run a query over QSYS2/SYSPROCS to verify the create procedure was run correctly. SPECIFIC_SCHEMA and ROUTINE_SCHEMA must match the library used on the call in the PC application. EXTERNAL_NAME must resolve to the actual program name. The parameter descriptions must also be correct. The SQLProcedures and SQLProcedureColumns ODBC APIs can also be used to retrieve the catalog information. If the older DECLARE PROCEDURE was used, then you must use extended dynamic support to return output or input output parameters. The call of the stored procedure must be stored in the active package. Use PRTSQLINF on the package to verify its contents. Delete the SQL package after making any change to declare procedure. Parameter descriptions are stored in the package as part of the call entry, not the declare entry. DECLARE PROCEDURE takes precedence over CREATE PROCEDURE. Delete the package if the problem persists. Changes to the SQL PATH job setting at V4R5M0 and V5R1M0, specifically changes to how unqualified SQL CALL statements are resolved can also result in this error. Note: With earlier versions of OS/400 and Client Access this error was reported as "Internal Driver Error". [


                  • - Save Restore Considerations

                    ] The procedure definitions are stored in the system catalog tables. The data in these tables is not saved or restored during a restore of the operating system. Rather, the data is rebuilt/updated as the program objects are restored. Although it is possible for the procedure definition to be saved with the program object there are a few significant restrictions. We recommend that you maintain a source file or SQL script that contains all the create procedure statements. This script can be executed to rebuild the definitions if needed. See the SQL Reference for detailed information on the limitations of saving and restoring external procedures. [


                    [{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0"}]

                    Historical Number

                    10191429

                    Document Information

                    Modified date:
                    18 December 2019

                    UID

                    nas8N1019720

                    Page Feedback