Example of Cobol call to an SQL stored procedure

5 pts.
Tags:
COBOL
IBM iSeries
SQL stored procedures
Stored Procedures
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • NullFields
    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".
    880 pointsBadges:
    report
  • TomLiotta
    Yes, the variables defined in COBOL must match the attributes of the stored proc parameters. That's why the variable used for :DateOfBirth 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 :DateOfBirth is a stored proc DATE parameter, it might take some research and/or experimentation to get the COBOL variable definition to match. Tom
    125,585 pointsBadges:
    report
  • NullFields
    You can define the date host variable as something like this: For *YMD job date format 01 :DateOfBirth FORMAT DATE '@Y/%m/%d'. for *ISO job date format 01 :DateOfBirth FORMAT DATE '@Y-%m-%d'. for *MDY job date format 01 :DateOfBirth FORMAT DATE '%m-%d-@Y'.
    880 pointsBadges:
    report
  • TomLiotta
    01 :DateOfBirth FORMAT DATE ‘@Y/%m/%d’.
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following