Solved

Pass parameter to DB2 stored procedure

Posted on 2010-08-21
2
973 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 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