Example of Cobol call to an SQL stored procedure
I am new to the iSeries and need to know how to use a Cobol program to call an SQL stored procedure. Someone has already created the stored procedure for me, but I am not sure how to call stored procedures in Cobol on the iSeries. The passed data looks like: FIND_CLIENT ( IN LastName CHAR(35),
IN FirstName CHAR(25),
IN DateOfBirth DATE,
IN RoleName CHAR(20),
IN AreaCode INTEGER,
IN PhoneNumber INTEGER,
IN Extension INTEGER,
IN Address1 CHAR(55),
IN Address2 CHAR(55),
IN City CHAR(30),
IN State CHAR(2),
IN ZipCode INTEGER,
IN ZipSuffixCode INTEGER,
OUT ClientNumber INTEGER )
I could use examples of what needs to be included in the program and how to call the procedure.
Software/Hardware used:
iSeries
IN FirstName CHAR(25),
IN DateOfBirth DATE,
IN RoleName CHAR(20),
IN AreaCode INTEGER,
IN PhoneNumber INTEGER,
IN Extension INTEGER,
IN Address1 CHAR(55),
IN Address2 CHAR(55),
IN City CHAR(30),
IN State CHAR(2),
IN ZipCode INTEGER,
IN ZipSuffixCode INTEGER,
OUT ClientNumber INTEGER )
I could use examples of what needs to be included in the program and how to call the procedure.
Software/Hardware used:
iSeries
ASKED:
November 24, 2010 6:05 PM
UPDATED:
December 3, 2010 2:24 AM
RELATED QUESTIONS
- Set DataTable name in returned data set
- Calling a stored procedure in COBOL
- Calling C ILE Procedure from COBOL 400 Program
- How can I get the name of a "String" variable/object returned to a usable string in my program ?
- iSeries SQL function in a SQL procedure - error: Function Name "in *LIBL type *N not found."
Answer Wiki:
Without knowing a little more, it's hard to say. There is a minimum set of statements needed. The first set would be somewhere in your Working-Storage section:<pre>
EXEC SQL
INCLUDE SQLCA
END-EXEC.</pre>
You will need the SQLCA to be included to give access to SQL error info, etc. You put it wherever seems best for your organization.
Then, of course, you must actually CALL the stored procedure:<pre>
EXEC SQL
CALL my_stor_proc (
:LastName ,
:FirstName ,
:DateOfBirth ,
:RoleName ,
:AreaCode ,
:PhoneNumber ,
:Extension ,
:Address1 ,
:Address2 ,
:City ,
:State ,
:ZipCode ,
:ZipSuffixCode ,
:ClientNumber )
END-EXEC</pre>
I don't know the stored proc name nor the names of any :host variables, so I just used names that you can recognize. The :host variables will need to be defined somewhere in the Working-Storage or Linkage sections with compatible attributes.
It looks like all but :DateOfBirth will be trivial. Your definition of a :DateOfBirth :host variable might depend on other options. You might start with PIC X(10) to see what errors show up and go from there.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Also, if the host variable data types are not compatible with how they are defined in the stored proc, you’ll receive an error stating something like “Stored Procedure named xxxx not found”.
Yes, the variables defined in COBOL must match the attributes of the stored proc parameters. That’s why the variable used for
ateOfBirth would be a potentially tricky one.
A common cause of a mismatch is a remote call to a stored proc that has a CHAR parameter, for example defined as CHAR (10). But the CALL might use a literal constant such as ‘ABCDEFGHIJ’. Even though the value has ten characters, clients will often create a variable definition that is a VARCHAR rather than a CHAR.
When the database searches for a matching stored proc, it uses a combination of the stored proc name and the parameter attributes to decide if a match exists. Stored proc definitions can be over-loaded with different parameter lists. You can have one stored proc that expects a VARCHAR and another that expects a CHAR, and both of stored procs can have the same name.
In this case, if
ateOfBirth is a stored proc DATE parameter, it might take some research and/or experimentation to get the COBOL variable definition to match.
Tom
You can define the date host variable as something like this:
For *YMD job date format
ateOfBirth FORMAT DATE ‘@Y/%m/%d’.
01
for *ISO job date format
ateOfBirth FORMAT DATE ‘@Y-%m-%d’.
01
for *MDY job date format
ateOfBirth FORMAT DATE ‘%m-%d-@Y’.
01
Be aware that this refers to ILE COBOL and V5R4 or later, but not to COBOL/400. The ILE COBOL compiler should be used, so it shouldn’t be a problem.
Tom