469,306 Members | 1,987 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

VBS return value from stored procedure

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
4 7791
Try to set ParameterDirection property of your third paramter object to
ParameterDirecion.Output.

HTH
Adrian

Nov 12 '05 #2
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
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
Thank You Adrian.
it worked.
Ron

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Ravindranath Gummadidala | last post: by
2 posts views Thread by Dino L. | last post: by
5 posts views Thread by Sandy | last post: by
1 post views Thread by csgraham74 | last post: by
9 posts views Thread by fniles | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.