Solved

Pass parameter to DB2 stored procedure

Posted on 2010-08-21
2
966 Views
Last Modified: 2012-05-10
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
Comment
Question by:MilburnDrysdale
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 33493430
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
 

Author Closing Comment

by:MilburnDrysdale
ID: 33497736
momi - I changed my call statement to this and it worked;

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

Thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question