part1: Business Objects stored procedure(DB2 UDB V6.1 for 390 ) parameter passing prob

Kadir Guray Meric

part1: Business Objects stored procedure(DB2 UDB V6.1 for 390 ) parameter passing prob
Hi to everyone in the list.This is the first part of my stored procedure passing parameter problem with business objects.
I have parameter passing problem in using DB2 UDB V6.1 for 390 Stored Procedures with Business Objects.
In Business Objects, When generating a new report, I choose Generate a standart report, others. In
others option, if I choose "stored procedures" option in others combo box,
and if stored procedure has input and output parameters, you will get
error :
"Error during SQL execution : DA000
[IBM][CLI Driver][DB2] SQL0440N No function by the name "DSN8ES3" having
compatible arguments was found in the function path. SQLSTATE=42884
:-440
If there are no parameters in stored procedure, it works,there are no errors.I think, This error is due to Business Objects' internal ADO connection type.
My stored procedure source code and bind,grant options are below:
CREATE PROCEDURE DDB2.DSN8ES3 (IN MYINPUT CHAR(8))
LANGUAGE SQL MODIFIES SQL DATA COLLID CDDB299 EXTERNAL NAME 'DSN8ES3'
NO WLM ENVIRONMENT ASUTIME NO LIMIT RESULT SETS 1
BEGIN
DECLARE C2 CURSOR WITH RETURN FOR SELECT NAME, DBNAME, TSNAME
FROM SYSIBM.SYSTABLES WHERE CREATOR = MYINPUT;
OPEN C2;
END
BIND PACKAGE(CDDB299) QUALIFIER(DDB2) PATH(DDB2) MEMBER(DSN8ES3) ACT(REP)
ISO(CS) EXPLAIN(YES)
GRANT BIND, EXECUTE ON PACKAGE CDDB299.DSN8ES3 TO PUBLIC;
GRANT EXECUTE ON PROCEDURE DDB2.DSN8ES3 TO PUBLIC;
The correct and working way to pass input and output parameters is : Generating a standart
report, others. In others combo box, choosing "visual basic stored
procedures", clicking finish. In "use the following subroutine", I write a sub
name, for example "DSSResult", press create, in VB code screen, paste the
following code:
Public Sub DSSResult(dpInterface As DpVBAInterface)
Dim myArr(100, 2) As Variant
' TO RETURN RESULT SET WITH 3 COLUMN NAMES. SINCE WE CANT GET THE NUMBER OF RECORDS
'WITH ADO_RS.RecordCount (it always return -1), WE ASSUME STORED PROC. RETURNS AT MOST 100 ROWS
Dim myArr(100, 2) As Variant
Dim ADO_Conn As ADODB.Connection
Dim ADO_Comm As ADODB.Command
Dim ADO_RS As ADODB.Recordset
Dim ADO_Parm1 As ADODB.Parameter
Dim Conn As String
Dim CONN_STRING, CONN_USER, CONN_PASS As String
'On Error GoTo AdoError
CONN_STRING = "DB2DB3TS" 'name of DSN in your computer, to connect to
DB2 for OS/390 V 6.1
CONN_USER = "myusername"
CONN_PASS = "mypassword"
myinputvar = inputbox("PLEASE ENTER THE CREATOR NAME OF TABLES TO SELECT FROM SYSIBM.SYSTABLES")
Set ADO_Conn = New ADODB.Connection
ADO_Conn.ConnectionString = Conn
ADO_Conn.Open CONN_STRING, CONN_USER, CONN_PASS
Set ADO_Comm = New ADODB.Command
Set ADO_Parm1 = ADO_Comm.CreateParameter("parm1", adChar, adParamInput,8, myinputvar)
ADO_Comm.ActiveConnection = ADO_Conn
ADO_Comm.CommandText = "DDB2.DSN8ES3"
ADO_Comm.CommandType = adCmdStoredProc
ADO_Comm.Parameters.Append ADO_Parm1
ADO_Comm.CommandTimeout = 180
Set ADO_RS = ADO_Comm.Execute
Dim i,x As Integer
x = ADO_RS.RecordCount 'it always returns -1,never gets the correct number of records!
While Not ADO_RS.EOF And i <= 100
i = i + 1
myArr(i, 0) = ADO_RS.Fields(0).Value 'NAME
myArr(i, 1) = ADO_RS.Fields(1).Value 'TBNAME
myArr(i, 2) = ADO_RS.Fields(1).Value 'DBNAME
ADO_RS.MoveNext
Wend
ADO_RS.Close
ADO_Conn.Close
This source code and the other info regarding this problem will be continued on part 2. Please take a look at part2.Regards.