Need VB example of call DB2 stored procedure without recordset 
Author Message
 Need VB example of call DB2 stored procedure without recordset

We are attempting to call DB2 stored procedure that do NOT return a
recordset.  The stored procedures take a couple of input parameters
and return values in several output parameters.

Our environment consist of VB6 with service pack 3, DB2 Connect 7.2
with FixPak 6, DB2 version 6, ADO 2.6.  Stored procedures are written
in Cobol and have RecordSet=0 in their definition.

When we attempt to call our DB2 stored procedure without recordset,
our VB program get nulls for output parameter.  However calling the
same stored procedure from Cobol on the mainframe or from the Stored
Procedure Builder on the PC, the output parameters have expected
values.

I expect that I am missing a setting in ADO or this is a bug in the
DB2 ODBC driver.

We are working around this problem by returning a dummy recordset and
ignoring the recordset in VB.  Although this works, it is kludgy and
is a minor performance issue.

Thanks!
Chris Adams
Contractor at Northeast Utilities in Berlin, CT
BLC Consulting



Sun, 06 Feb 2005 22:28:14 GMT
 Need VB example of call DB2 stored procedure without recordset

Hi,

If you are able to get return values with recordset, then it means that your
ADO code is fine. I think problem here is in ODBC driver. Try to use another
ODBC driver or OLEDB provider (which is preferable). See next link for
different connection strings

http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForDB2

--
Val Mazur
Microsoft MVP


Quote:
> We are attempting to call DB2 stored procedure that do NOT return a
> recordset.  The stored procedures take a couple of input parameters
> and return values in several output parameters.

> Our environment consist of VB6 with service pack 3, DB2 Connect 7.2
> with FixPak 6, DB2 version 6, ADO 2.6.  Stored procedures are written
> in Cobol and have RecordSet=0 in their definition.

> When we attempt to call our DB2 stored procedure without recordset,
> our VB program get nulls for output parameter.  However calling the
> same stored procedure from Cobol on the mainframe or from the Stored
> Procedure Builder on the PC, the output parameters have expected
> values.

> I expect that I am missing a setting in ADO or this is a bug in the
> DB2 ODBC driver.

> We are working around this problem by returning a dummy recordset and
> ignoring the recordset in VB.  Although this works, it is kludgy and
> is a minor performance issue.

> Thanks!
> Chris Adams
> Contractor at Northeast Utilities in Berlin, CT
> BLC Consulting




Sun, 06 Feb 2005 22:36:49 GMT
 Need VB example of call DB2 stored procedure without recordset
Hi,

Quote:
> ADO code is fine. I think problem here is in ODBC driver. Try to use

another

I had troubles (another Database, another ODBC Driver) when the INOUT
parameters
was not properly initialized (I had the problems with VARCHARs ..., not
with numeric
variables).

When initializing the INOUT parameters with spaces or any other string of
the correct
length, the programm was running.



Mon, 07 Feb 2005 01:37:18 GMT
 Need VB example of call DB2 stored procedure without recordset
I think these are common issues with ADO/DB2.  We use the IBM ODBC
provider for OLE DB, and my stored procedure paramters give me fits
unless all are adChar
types.  Thinking Microsoft offers a similar driver - you might try
that and
see how it compares.  Since both parties have great stakes and
strengths in
such interoperablity, I hope they work out a common ground as the
combination could be dynamite!

Phil J.

Quote:

> Hi,

> > ADO code is fine. I think problem here is in ODBC driver. Try to use
> another

> I had troubles (another Database, another ODBC Driver) when the INOUT
> parameters
> was not properly initialized (I had the problems with VARCHARs ..., not
> with numeric
> variables).

> When initializing the INOUT parameters with spaces or any other string of
> the correct
> length, the programm was running.



Mon, 07 Feb 2005 11:14:47 GMT
 Need VB example of call DB2 stored procedure without recordset
The solution to this problem is in ADO.

Executing a stored procedure with:

        Dim cmd As New ADODB.Command
        Dim oRs As Recordset
        Set oRs = cmd.Execute

Will return without output parameter values if no recordset is
returned from the stored procedure.

However, executing the same stored procedure with:

        Dim cmd As New ADODB.Command
        cmd.Execute

Will return with output parameter values when on recordset is returned
from the stored procedure.

All examples of calling a stored procedure that I had found had the
first form of cmd.Execute.  I have never seen the second case, but it
works!

Quote:

> We are attempting to call DB2 stored procedure that do NOT return a
> recordset.  The stored procedures take a couple of input parameters
> and return values in several output parameters.

> Our environment consist of VB6 with service pack 3, DB2 Connect 7.2
> with FixPak 6, DB2 version 6, ADO 2.6.  Stored procedures are written
> in Cobol and have RecordSet=0 in their definition.

> When we attempt to call our DB2 stored procedure without recordset,
> our VB program get nulls for output parameter.  However calling the
> same stored procedure from Cobol on the mainframe or from the Stored
> Procedure Builder on the PC, the output parameters have expected
> values.

> I expect that I am missing a setting in ADO or this is a bug in the
> DB2 ODBC driver.

> We are working around this problem by returning a dummy recordset and
> ignoring the recordset in VB.  Although this works, it is kludgy and
> is a minor performance issue.

> Thanks!
> Chris Adams
> Contractor at Northeast Utilities in Berlin, CT
> BLC Consulting




Sun, 20 Feb 2005 19:25:37 GMT
 Need VB example of call DB2 stored procedure without recordset
Here's an  example of using VB to call a JAVA stored proc with an
input parm and receives an output parm (no result set). The example
here uses a strored proc to generate a primary key for inserts :

 Dim rstSequence As ADODB.Recordset
 Dim cTmp As New ADODB.Connection
 Dim lNewKey As Long
 Dim sSeq As String
 Dim cmd As New ADODB.Command
 Dim parm1 As New ADODB.Parameter
 Dim parm2 As New ADODB.Parameter

 lNewKey = 0
 cTmp.ConnectionString = sConnectString
 cTmp.CursorLocation = adUseClient
 cTmp.Open

 cmd.ActiveConnection = cTmp
 cmd.CommandText = "KWOOD.GETNEWKEY"
 cmd.CommandType = adCmdStoredProc

 Set parm1 = cmd.CreateParameter("Sseq", adVarChar, adParamInput, 30,
sTableName)
   cmd.Parameters.Append parm1

   Set parm2 = cmd.CreateParameter("lKeyValue", adBigInt,
adParamOutput, 8)
   cmd.Parameters.Append parm2

   Set rstSequence = cmd.Execute
   Set rstSequence = Nothing

lNewKey = cmd(1).Value          ' Here is the returned value

parm1 references the strored proc input variable 'Sseq'
parm2 referneces the stored proc outrput variable 'IKeyValue'

The key to hgetting this to work was to set the recordset to nothing
BEFORE trying to retrieve the output parms.


Quote:
>We are attempting to call DB2 stored procedure that do NOT return a
>recordset.  The stored procedures take a couple of input parameters
>and return values in several output parameters.

>Our environment consist of VB6 with service pack 3, DB2 Connect 7.2
>with FixPak 6, DB2 version 6, ADO 2.6.  Stored procedures are written
>in Cobol and have RecordSet=0 in their definition.

>When we attempt to call our DB2 stored procedure without recordset,
>our VB program get nulls for output parameter.  However calling the
>same stored procedure from Cobol on the mainframe or from the Stored
>Procedure Builder on the PC, the output parameters have expected
>values.

>I expect that I am missing a setting in ADO or this is a bug in the
>DB2 ODBC driver.

>We are working around this problem by returning a dummy recordset and
>ignoring the recordset in VB.  Although this works, it is kludgy and
>is a minor performance issue.

>Thanks!
>Chris Adams
>Contractor at Northeast Utilities in Berlin, CT
>BLC Consulting




Mon, 21 Feb 2005 20:05:07 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Need VB example of call DB2 stored procedure without recordset

2. Need example: Visual Cafe calling Oracle stored procedure.

3. Need examples of recordset from stored procedure with parame

4. Need examples of recordset from stored procedure with parameters

5. Call DB2 Stored Procedure from VB and DAO

6. One way to call DB2 stored procedures through VB 6 with ADO/RDO

7. Getting a recordset from a DB2 Stored Procedure with ADO in VB 6

8. Calling Stored Procedure that calls Stored Procedure in ASP

9. Simple Example of Calling Stored Procedure from ASP

10. Pro*C calling stored procedures examples

11. DB2 SQL stored procedures - Examples

12. Calling a Java Stored Procedure from another Java Stored Stored Procedure


 
Powered by phpBB® Forum Software