• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 985
  • Last Modified:

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

0
MilburnDrysdale
Asked:
MilburnDrysdale
1 Solution
 
momi_sabagCommented:
the variable :cterm is not recognized by db2
you have 2 options:
1) change the name :cterm with the actual value using string concatenation
2) )use parameter markers (that is, in the command text you use ? instead of :cterm and then before execution you set the value of the parameter to the actual value of cterm
0
 
MilburnDrysdaleAuthor Commented:
momi - I changed my call statement to this and it worked;

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

Thanks!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now