MilburnDrysdale
asked on
Pass parameter to DB2 stored procedure
Hello - I've created a stored procedure that returns a result set to a web page (asp, vbscript). It has worked fine until I attempted to add an IN parameter. Here is the stored procedure and the asp/vbscript code: I am getting the following error;
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0312 - Variable CTERM not defined or not usable.
What am I missing here? Thanks!
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0312 - Variable CTERM not defined or not usable.
What am I missing here? Thanks!
ASP/VBSCRIPT
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Server.ScriptTimeout = 3600 %>
<!--#include file="../Connections/LTL400TAFUPD.asp" -->
<%
Dim vterm
vterm="LAX"
Dim cterm
cterm=vterm
If (Request.Form("terminal") <> "") Then
cterm = Request.Form("terminal")
End If
%>
<%
Dim rs1
Dim rs1_numRows
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.ActiveConnection = MM_LTL400TAFUPD_STRING
rs1.Source = "{call LTL400TAF1.CONTTRACK (:cterm)}"
rs1.CursorType = 0
rs1.CursorLocation = 2
rs1.LockType = 1
rs1.Open()
rs1_numRows = 0
%>
etc...
DB2 Stored Procedure
CREATE PROCEDURE LTL400TAF1/CONTTRACK (IN cterm CHAR(3))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITHOUT HOLD FOR
SELECT T1.FKXREF AS CONTNUM, T1.FKPRO AS PRO,
DATE(WDYSEQ+722814) AS DATE,
T2.FKXREF AS CHASRCVDAT, FHOT AS ORIGTERM, FHSNM AS SHIPNAM, FHDT AS DESTTERM,
FHCNM AS CNEENAM, T3.FKXREF AS DATDUEBACK, T4.FKXREF AS ACTRTRNDAT,
VUSTAT AS TRLRSTAT, VULTID AS LASTLOC,
CAST((CASE WHEN VUSTAT='RET' THEN 'R' ELSE 'O' END) AS CHAR(1) CCSID 37)
AS TYPE
FROM AFP001 T1, DSP070, FRP002, AAP050, (((FRP001 LEFT OUTER JOIN
AFP001 T2 ON T2.FKPRO=FHPRO AND T2.FKTYPE='CRD') LEFT OUTER JOIN
AFP001 T3 ON T3.FKPRO=FHPRO AND T3.FKTYPE='DDB') LEFT OUTER JOIN
AFP001 T4 ON T4.FKPRO=FHPRO AND T4.FKTYPE='ART')
WHERE T1.FKXREF=VUUNIT AND T1.FKTYPE='OCNT' AND T1.FKPRO=FHPRO
AND FHBILV IN (' ', 'C') AND FHPRO=FDPRO AND FDCMCL IN
('C20', 'C40', 'R20', 'R40') AND FHPDAT=WDDATE AND
FHPDAT>=1100601 AND FHOT=cterm;
OPEN c1;
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"{call LTL400TAF1.CONTTRACK (" & "'" & cterm & "'" & ")}"
Thanks!