467,915 Members | 1,710 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

COBOL stored procedure

I am trying to learn how to use stored procedures written in COBOL so
I wrote 2 small programs to test it out: the stored procedure and the
the calling program. I have no problems compiling them but when the
calling program enters the SP, it either hangs or gives me sqlcode

We are on AIX 5.2 (I think) running DB2 UDB ver 7.2 and MF COBOL 4.1.

Below are the programs I wrote:

Calling program (spcaller.sqb)
identification division.
program-id. spcaller.
environment division.
input-output section.

working-storage section.
exec sql begin declare section end-exec.

01 work-area.
05 lnk-stored-proc pic x(12).
05 lnk-policy pic x(10).
05 lnk-plan pic x(3).

exec sql end declare section end-exec.

exec sql include sqlca end-exec.
exec sql include sqlda end-exec.

procedure division.

exec sql connect to noadmidb user dbmssi using dbmssi
exec sql set schema dbipas end-exec
initialize work-area
accept lnk-policy from command-line
move "spprog" to lnk-stored-proc
display "calling spprog"
exec sql
call :lnk-stored-proc
(:lnk-policy, :lnk-plan)
if sqlcode not= 0
display 'error in sp = ' sqlcode
display "policy number : " lnk-policy
display "plancode : " lnk-plan
exec sql connect reset end-exec
stop run.

The database name si "noadmidb" and the schema is "dbipas" but the
account running the program is "dbmssi". Our DBA says this account has
all privileges of the instance except for commands that alter the
database/tables such as DROP.

SP program: (spprog.cbl)
identification division.
program-id. spprog.
environment division.
input-output section.

data division.
working-storage section.

linkage section.
01 lnk-policy pic x(10).
01 lnk-plan pic x(3).

procedure division using lnk-policy lnk-plan.

move "XXX" to lnk-plan

This used to be a little longer and had embedded SQL commands in it
but I removed them to try and figure out where the problem was.

I compiled the sp program using the commands:
cob -c -x spprog.cbl
cob -x -o spprog spprog.o -Q -bnoentry -Q -bE:noadmi.exp -Q -bI:
$DB2PATH/lib/db2g.imp -L$DB2PATH/lib -ldb2 -ldb2gmf

....and copied the executable file spprog to the $INSTHOME/sqllib/
function directory. I then prepped/bound/compiled the calling program
using the commands:
db2 prep spcaller.sqb bindfile target ibmcob
db2 bind spcaller.bnd
cob -c -x spcaller.cbl
cob -x -o spcaller spcaller.o -ldb2 -ldb2gmf -L$DB2PATH/lib

I also tried using "target mfcob" for db2 prep but with the same

I then created the procedure with the CREATE PROCEDURE command as
db2 "create procedure dbipas.spprog (in policy char(10), out plan
char(3)) dynamic result sets 0 no sql language cobol external name
'spprog' parameter style general program type sub"

My co-worker also wrote a VB program to run the SP but it also hangs
when it tries to call it:
Option Explicit

Private Sub cmdOK_Click()
txtPlan.Text = ExecSP(txtPolNo.Text)
End Sub

Private Function ExecSP(ByVal sPolno As String) As String
Dim cmCallSP As Command
Dim parmSP As Parameter
Dim sSQLState As String
Dim g_sConnection As String

g_sConnection = "DSN=NONADMI;UID=dbmssi;PWD=dbmssi"
Screen.MousePointer = vbHourglass
Set cmCallSP = New Command
cmCallSP.CommandText = "spprog"
cmCallSP.CommandType = adCmdStoredProc
cmCallSP.ActiveConnection = g_sConnection

Set parmSP = cmCallSP.CreateParameter("policy", adChar,
adParamInput, 10, sPolno)
Call cmCallSP.Parameters.Append(parmSP)
Set parmSP = cmCallSP.CreateParameter("plan", adChar,
adParamOutput, 3)
Call cmCallSP.Parameters.Append(parmSP)


ExecSP = cmCallSP.Parameters("plan").Value
End Function

Can anyone tell me what I'm doing wrong? We have tons of COBOL
programs that I would like to use as stored procs so they can be
called by VB6 programs running on Windows XP or 2000.

Thanks in advance for your help.

Apr 3 '07 #1
  • viewed: 5340

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

By using this site, you agree to our Privacy Policy and Terms of Use.