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
-1131.
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.
file-control.
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.
00000-main-rtn.
exec sql connect to noadmidb user dbmssi using dbmssi
end-exec
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)
end-exec
if sqlcode not= 0
display 'error in sp = ' sqlcode
else
display "policy number : " lnk-policy
display "plancode : " lnk-plan
end-if
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.
file-control.
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.
00000-main-rtn.
move "XXX" to lnk-plan
goback.
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
results.
I then created the procedure with the CREATE PROCEDURE command as
follows:
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)
cmCallSP.Execute
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.
