as400 stored procedure using classic ASP

globalterminal
globalterminal used Ask the Experts™
on
i need to call an AS400 DB2 stored procedure from a classic ASP page.  
using the following:
     Dim termcode
     Dim chassisnumber
     Dim StrProc
     Dim strProcCMD
     Dim RST

      chassisnumber            = "APLZ330211"
      termcode                   = "GBLDEV"

     Set STRProc = Server.Createobject("adodb.connection")
     STRProc.open "test","gblodbc","odbcgbl"
     Set strProcCMD = server.createobject("adodb.command")
     Set strProcCMD.ActiveConnection = STRProc
     strProcCMD.CommandText = "call getOpenDvirByChassis(termcode, chassisnumber);"
      Set RST = strProcCMD.Execute

then i should be able to iterate thur the recordset.

i get the this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token TERMCODE was not valid. Valid tokens: :.

what is wrong with the parameters.
I think i need to use a different method.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can't use
 strProcCMD.CommandText = "call getOpenDvirByChassis(termcode, chassisnumber);"

since termcode is a variable in your asp, which is unknown to db2
either use string concatenation to replace the variable name with the actual value or use parameter markers (?)
and pass the values through parameters

Author

Commented:
momi. I am not sure i understand.
this is the documentation of the procedeure...do u think u can help me with the parameters using the following info?
-------------------------------------------------------------------------------------------------------------------------
 created the stored procedure getOpenDvirByChassis which will return a record set listing the open DVIRs for the specified chassis.

The stored procedure is called using the following syntax:

CALL getOpenDvirByChassis(term-code, chassis-number);

Where:
term-code    :A 6-character input only field identifying the terminal.
    This is a required parameter.
    Valid values:
    GBLDEV - Global Development
    GLOBAL - Global Production
    NYCDEV - NYCT Development
    NYCT - NYCT Production
chassis-number    :A 12-character input-only field identifying the chassis number for which DVIRs are to be retrieved
    This is a required parameter.

The result set will include all DVIR header and detail fields as well as the description of the DVIR reason code.  The columns returned are:
Column Type Description
IRDVIR CHAR(15) DVIR
IRCHNO CHAR(12) CHASSIS NUMBER
IRIEPS CHAR(4) IEP SCAC
IRTRKR CHAR(4) CARRIER SCAC
IRDRVR CHAR(15) DRIVER PIN
IRHOLD CHAR(2) HOLD CODE
IRGIDT DATE GATE-IN DATE
IRGITM TIME GATE-IN TIME
IRICTL DECIMAL(14 0) ISA CONTROL
IRGCTL DECIMAL(14 0) GS CONTROL
IRTCTL DECIMAL(14 0) ST CONTROL
IRIDTE DATE ISA DATE
IRITIM TIME ISA TIME
IRCRDT DATE CREATE DATE
IRCRTM TIME CREATE TIME
IRSTAT CHAR(1) STATUS
IRCHGD DATE CHANGE DATE
IRCHGT TIME CHANGE TIME
IRUSER CHAR(10) CHANGE USER
IRRESN CHAR(2) REASON
IRRDESC VARCHAR(30) REASON DESCRIPTION


Gary PattersonVP Technology / Senior Consultant
Commented:
strProcCMD.CommandText  = "call getOpenDvirByChassis(termcode,  chassisnumber);"

When you do this, you are sending the LITERAL value "termcode" and the literal value "chassisnumber" as part of the command.  You want to pass the string values referenced by the variables.

strProcCMD.CommandText = "call getOpenDvirByChassis(?,?)"

http://www.redbooks.ibm.com/redbooks/pdfs/sg245183.pdf

- Gary Patterson



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial