Solved

Pass parameter to DB2 stored procedure

Posted on 2010-08-21
2
963 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now