DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets

Joseph DeMarco

DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
We are coding DB2 COBOL/LE stored procedures that are called from a Visual
Basic 6 client application. In the stored procedure, a cursor is declared
WITH RETURN and left open. In the VB app, we are looping through an ADO
recordset to retrieve the result set. Offsets are used in the recordset
object to reference the returned columns as follows:

While Not rs.EOF
lstResults.AddItem rs.Fields.Item(0) & " - " & rs.Fields.Item(1)
rs.MoveNext
Wend

We would like to reference the DB2 column names directly in the ADO
recordset (as in the function below using a SQL Server stored procedure)
instead of using offsets. Has anyone been successful in doing this in DB2?

Public Function GetSectionName(strSectNo As String) As String

Dim rs As ADODB.Recordset

SQLSrv.QueryString = "Execute si_GetSectionName " & "'" & strSectNo &
"'"
Set rs = SQLSrv.ExecuteSQL

If Not rs.EOF Then
GetSectionName = rs!SECTNAME
Else
GetSectionName = "TITLE UNKNOWN"
End If
SQLSrv.CloseRecordSet rs

End Function

When we try to reference the columns, we receive the following error: ADO
could not find the object in the collection.

We connect to DB2 (DB2 5.1 on OS/390 2.5) using an IBM DB2 ODBC driver 5.2
via the IBM DB2 Connect gateway 5.2.

Thanks.

Joseph DeMarco
[login to unmask email]
Con Edison
Information Resources
Database Administration
4 Irving Place, Room 428
212.460.3392



Steven Camitta

Re: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
(in response to Joseph DeMarco)
Joseph,
In using CLI, we were not able to obtain the column names on the stored
procedure results until we changed our ZPARM to use DESCSTAT=YES. It's
within the DSN6SPRM macro. I'm not familiar with the VB access but making
this change is probably a prerequisite.
Rgds,
Steve

-----Original Message-----
From: DeMarco, Joseph [mailto:[login to unmask email]
Sent: Monday, December 13, 1999 8:32 AM
To: [login to unmask email]
Subject: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets


We are coding DB2 COBOL/LE stored procedures that are called from a Visual
Basic 6 client application. In the stored procedure, a cursor is declared
WITH RETURN and left open. In the VB app, we are looping through an ADO
recordset to retrieve the result set. Offsets are used in the recordset
object to reference the returned columns as follows:

While Not rs.EOF
lstResults.AddItem rs.Fields.Item(0) & " - " & rs.Fields.Item(1)
rs.MoveNext
Wend

We would like to reference the DB2 column names directly in the ADO
recordset (as in the function below using a SQL Server stored procedure)
instead of using offsets. Has anyone been successful in doing this in DB2?

Public Function GetSectionName(strSectNo As String) As String

Dim rs As ADODB.Recordset

SQLSrv.QueryString = "Execute si_GetSectionName " & "'" & strSectNo &
"'"
Set rs = SQLSrv.ExecuteSQL

If Not rs.EOF Then
GetSectionName = rs!SECTNAME
Else
GetSectionName = "TITLE UNKNOWN"
End If
SQLSrv.CloseRecordSet rs

End Function

When we try to reference the columns, we receive the following error: ADO
could not find the object in the collection.

We connect to DB2 (DB2 5.1 on OS/390 2.5) using an IBM DB2 ODBC driver 5.2
via the IBM DB2 Connect gateway 5.2.

Thanks.

Joseph DeMarco
[login to unmask email]
Con Edison
Information Resources
Database Administration
4 Irving Place, Room 428
212.460.3392








Joseph DeMarco

Re: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
(in response to Steven Camitta)
Steve,

Thanks for the reponse, but we changed DESCSTAT=YES and still were not able
to reference the column names.

Joe

> -----Original Message-----
> From: Steven Camitta [SMTP:[login to unmask email]
> Sent: Monday, December 13, 1999 2:35 PM
> To: [login to unmask email]
> Subject: Re: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
>
> Joseph,
> In using CLI, we were not able to obtain the column names on the stored
> procedure results until we changed our ZPARM to use DESCSTAT=YES. It's
> within the DSN6SPRM macro. I'm not familiar with the VB access but making
> this change is probably a prerequisite.
> Rgds,
> Steve
>
> -----Original Message-----
> From: DeMarco, Joseph [mailto:[login to unmask email]
> Sent: Monday, December 13, 1999 8:32 AM
> To: [login to unmask email]
> Subject: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
>
>
> We are coding DB2 COBOL/LE stored procedures that are called from a Visual
> Basic 6 client application. In the stored procedure, a cursor is declared
> WITH RETURN and left open. In the VB app, we are looping through an ADO
> recordset to retrieve the result set. Offsets are used in the recordset
> object to reference the returned columns as follows:
>
> While Not rs.EOF
> lstResults.AddItem rs.Fields.Item(0) & " - " & rs.Fields.Item(1)
> rs.MoveNext
> Wend
>
> We would like to reference the DB2 column names directly in the ADO
> recordset (as in the function below using a SQL Server stored procedure)
> instead of using offsets. Has anyone been successful in doing this in
> DB2?
>
> Public Function GetSectionName(strSectNo As String) As String
>
> Dim rs As ADODB.Recordset
>
> SQLSrv.QueryString = "Execute si_GetSectionName " & "'" & strSectNo &
> "'"
> Set rs = SQLSrv.ExecuteSQL
>
> If Not rs.EOF Then
> GetSectionName = rs!SECTNAME
> Else
> GetSectionName = "TITLE UNKNOWN"
> End If
> SQLSrv.CloseRecordSet rs
>
> End Function
>
> When we try to reference the columns, we receive the following error: ADO
> could not find the object in the collection.
>
> We connect to DB2 (DB2 5.1 on OS/390 2.5) using an IBM DB2 ODBC driver 5.2
> via the IBM DB2 Connect gateway 5.2.
>
> Thanks.
>
> Joseph DeMarco
> [login to unmask email]
> Con Edison
> Information Resources
> Database Administration
> 4 Irving Place, Room 428
> 212.460.3392
>
>
>
> the
>
>
>
>
>
>
>
>



Joseph DeMarco

Re: DB2 5.1, OS/390 2.5, VB 6 - Retrieving Result Sets
(in response to Joseph DeMarco)
Thanks for the input Steven and Tim.

The DESCSTAT parameter was changed to YES, but I neglected to rebind the
package for the stored procedure. Once I rebound, it worked!

Mea culpa.

Joe


Joseph DeMarco
[login to unmask email]
Con Edison
Information Resources
Database Administration
4 Irving Place, Room 428
212.460.3392