Link to home
Start Free TrialLog in
Avatar of MilburnDrysdale
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!

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MilburnDrysdale
MilburnDrysdale

ASKER

momi - I changed my call statement to this and it worked;

"{call LTL400TAF1.CONTTRACK (" & "'" & cterm & "'" & ")}"

Thanks!