By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

VBS return value from stored procedure

P: n/a
Hi All:

I am attemting to write some Visual Basic Scripts that will call a
DB2 stored procedure and get the return value.

The test scripts are shown below. Not sure why I cannot get the
returned value. I know the procedure is running as I had put in a line
to insert into a table and it worked fine.

TIA

Ron

===================
== DB2 Procedure ==
===================

CREATE PROCEDURE MDR.TP1 ( IN pFullName VARCHAR(200),
IN pDBType VARCHAR(20),
OUT retValue VARCHAR(50) )
SPECIFIC MDR.TP1
DYNAMIC RESULT SETS 1
--------------------------
-- SQL Stored Procedure
-- pFullName
-- pDBType
-- retValue
--------------------------
P1: BEGIN
-- Declare variable
DECLARE retValue_TMP VARCHAR(50) DEFAULT 'TESTING';
SET retValue = retValue_TMP;
END P1

======================
== VBS Code Snippet ==
======================

dsn = "MDR"
'
' Open the Connection to the metadata repository
'
set cn = Createobject("adodb.connection")
cn.Open dsn

'
' Set up the call to the function
'
set cmd = CreateObject("adodb.command")
set cmd.ActiveConnection = cn
cmd.CommandType = 4
cmd.CommandText = "MDR.TP1"

set param = cmd.Parameters
param.Append cmd.CreateParameter("PFULLNAME",200,1,200,"Param1" )
param.Append cmd.CreateParameter("PDBTYPE",200,1,20,"Param2")
param.Append cmd.CreateParameter("RETVALUE",200,2,50)

set rs = cmd.execute

msgbox cmd.Parameters.Item(2).Value

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Try to set ParameterDirection property of your third paramter object to
ParameterDirecion.Output.

HTH
Adrian

Nov 12 '05 #2

P: n/a
Is it not already defined as an output param?

param.Append cmd.CreateParameter("RETVALUE",200,2,50)
param.Append cmd.CreateParameter(Name,Type,Direction,Size)

where 2 = Output Parameter

Nov 12 '05 #3

P: n/a
Sorry, I missed that you are using adodb....

Try to close connection before get value from an output parameter:
....
set rs = cmd.execute
cn.Close
'rs.Close
v1 = cmd.Parameters.Item(2).Value
msgbox v1
....
If you would like to use rs.Close instead of cn.Close, your SQL procedure
have to return an open cursor:
....
DECLARE c1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR SELECT 1 FROM
sysibm.Sysdummy1;
SET retValue = retValue_TMP;
OPEN c1;
END

If you ommit cn.Close before getting an output parameter value, a proper
value will be assigned to parameter
....(part of tracefile)..
SQLExecDirectW( hStmt=1:1, pszSqlStr="{ call MDR.TP1(?, ?, ?) }",
cbSqlStr=-3 )
---> Time elapsed - +7,405000E-003 seconds
( StmtOut="CALL MDR.TP1(?,?,? )" )
( Package="SYSSH200 ", Section=4 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="Param1" - x'506172616D31',
pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="Param2" - x'506172616D32',
pcbValue=6, piIndicatorPtr=6 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="TESTING" - x'54455354494E47',
pcbValue=7, piIndicatorPtr=7 )
( return=0 )
( COMMIT REQUESTED=1 )
( COMMIT REPLY RECEIVED=1 )
....

but next, if recordset (or connection) is still open, adodb tries to get
next recordset, and clears the output parameters.

Below entry will appear in trace, if you don't open any cursor in the SP:
( Unretrieved error message="[IBM][CLI Driver][DB2/NT] SQL0100W Nie
znaleziono wiersza dla FETCH, UPDATE lub DELETE, albo rezultatem zapytania
jest pusta tabela. SQLSTATE=02000.
Cheers
Adrian
Nov 12 '05 #4

P: n/a
Thank You Adrian.
it worked.
Ron

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.