calling Stored Proc with InputOutput params - help please! 
Author Message
 calling Stored Proc with InputOutput params - help please!

I have posted a couple of times already about this issue, but after an
entire weekend of working on this, I still am stumped.
I have simplified my problem and now know exactly when it doesn't work:

Here's my test proc:
CREATE OR REPLACE PROCEDURE MY_PROC
   (P_PART_NO        IN NUMBER,
    P_PART_REV       IN VARCHAR2,
    R_PART_NO       IN OUT NUMBER,
    R_PART_REV       IN OUT VARCHAR2)
IS

BEGIN

  R_PART_NO := P_PART_NO;
  R_PART_REV := P_PART_REV;

END MY_PROC;

In order for my vb code to work, I can't assign a value to the IN OUT
params, so basically I have to treat them like OUT params. Otherwise, on the
Command.Execute method, I get an unhelpful error message, "errors have
occurred".

Here's my code:
------
    Dim adoConn As ADODB.Connection
    Dim cmdSQL As New ADODB.Command

    Set adoConn = New ADODB.Connection
    sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

    With adoConn
       .ConnectionString = sConnect
       .CursorLocation = adUseClient
       .Open
    End With

    With cmdSQL
        .CommandTimeout = 120
        .ActiveConnection = adoConn
        .CommandText = "MY_PROC"
        .CommandType = adCmdStoredProc
    End With

    Dim pPartNo As Integer
    Dim pPartRev As String
    Dim rPartNo As Integer
    Dim rPartRev As String

    cmdSQL.Parameters.Refresh
    MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

    pPartNo = 23813
    cmdSQL.Parameters(0).Value = pPartNo
    pPartRev = "mypartrev"
    cmdSQL.Parameters(1).Value = pPartRev

    ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
    ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
    ' **** COMMAND.EXECUTE METHOD BOMBS.
    'cmdSQL.Parameters(2).Value = rPartNo
    'cmdSQL.Parameters(3).Value = rPartRev

    ' << used to confirm the param vals, type and direction. works fine.
    Dim s As String
    Dim t As String
    Dim d As String
    Dim iCountParams As Integer
    For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
        s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
cmdSQL.Parameters(iCountParams).Value & vbCrLf
        t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
cmdSQL.Parameters(iCountParams).Type & vbCrLf
        d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
cmdSQL.Parameters(iCountParams).Direction & vbCrLf
    Next iCountParams

    MsgBox s
    MsgBox t
    MsgBox d

    ' >>

    ' this cmdSQL.Execute method just gives me a vb error - "errors have
occurred"
    ' unless, of course, I comment the 2 lines above that assign values to
the in/out variables.
    cmdSQL.Execute

    MsgBox cmdSQL.Parameters(2).Value & "  -  " & cmdSQL.Parameters(3).Value

    adoConn.Close
    Set adoConn = Nothing
----



Fri, 08 Apr 2005 23:45:32 GMT
 calling Stored Proc with InputOutput params - help please!

I for one replied to your earlier message.

Irrespective of the procedure itself, and it doesn't look like a SQL stored
procedure, so I'd guess oracle or sybase, that isn't the issue.

With a stored procedure in SQL, I'd build thestring textually, so for
example "MyStoredProcedure" required four pass parameters, the string mught
look like this:=

strSQL = "MyStoredProcedure '" & strParam1 & "','" & strParam2 & "','" &
strParam3 & "','" & strParam4 & "'"

open a connection to the database as you have, fine:=

     Dim adoConn As ADODB.Connection
     Dim cmdSQL As New ADODB.Command

     Set adoConn = New ADODB.Connection
     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

     With adoConn
        .ConnectionString = sConnect
        .CursorLocation = adUseClient
        .Open
     End With

Now there are a couple of methods of executimg the SQL command. One is to
use the connections Execute method. This is only useful really when you
don't require a result returned.
A better method obviously is to dim a recordset & execute it.

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, adoConn, adOpenDynamic, adLockOptimistic

the .Open sends the SQL command to the database & will be populated by any
output from the SP

If there are lots data items, then its simple to loop through say a
spreadsheet to pick them up.

HTH

Patrick Molloy
Microsoft Excel MVP

Quote:

> I have posted a couple of times already about this issue, but after an
> entire weekend of working on this, I still am stumped.
> I have simplified my problem and now know exactly when it doesn't work:

> Here's my test proc:
> CREATE OR REPLACE PROCEDURE MY_PROC
>    (P_PART_NO        IN NUMBER,
>     P_PART_REV       IN VARCHAR2,
>     R_PART_NO       IN OUT NUMBER,
>     R_PART_REV       IN OUT VARCHAR2)
> IS

> BEGIN

>   R_PART_NO := P_PART_NO;
>   R_PART_REV := P_PART_REV;

> END MY_PROC;

> In order for my vb code to work, I can't assign a value to the IN OUT
> params, so basically I have to treat them like OUT params. Otherwise, on
the
> Command.Execute method, I get an unhelpful error message, "errors have
> occurred".

> Here's my code:
> ------
>     Dim adoConn As ADODB.Connection
>     Dim cmdSQL As New ADODB.Command

>     Set adoConn = New ADODB.Connection
>     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

>     With adoConn
>        .ConnectionString = sConnect
>        .CursorLocation = adUseClient
>        .Open
>     End With

>     With cmdSQL
>         .CommandTimeout = 120
>         .ActiveConnection = adoConn
>         .CommandText = "MY_PROC"
>         .CommandType = adCmdStoredProc
>     End With

>     Dim pPartNo As Integer
>     Dim pPartRev As String
>     Dim rPartNo As Integer
>     Dim rPartRev As String

>     cmdSQL.Parameters.Refresh
>     MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

>     pPartNo = 23813
>     cmdSQL.Parameters(0).Value = pPartNo
>     pPartRev = "mypartrev"
>     cmdSQL.Parameters(1).Value = pPartRev

>     ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
>     ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
>     ' **** COMMAND.EXECUTE METHOD BOMBS.
>     'cmdSQL.Parameters(2).Value = rPartNo
>     'cmdSQL.Parameters(3).Value = rPartRev

>     ' << used to confirm the param vals, type and direction. works fine.
>     Dim s As String
>     Dim t As String
>     Dim d As String
>     Dim iCountParams As Integer
>     For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
>         s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Value & vbCrLf
>         t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Type & vbCrLf
>         d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Direction & vbCrLf
>     Next iCountParams

>     MsgBox s
>     MsgBox t
>     MsgBox d

>     ' >>

>     ' this cmdSQL.Execute method just gives me a vb error - "errors have
> occurred"
>     ' unless, of course, I comment the 2 lines above that assign values to
> the in/out variables.
>     cmdSQL.Execute

>     MsgBox cmdSQL.Parameters(2).Value & "  -  " &

cmdSQL.Parameters(3).Value

- Show quoted text -

Quote:

>     adoConn.Close
>     Set adoConn = Nothing
> ----



Sat, 09 Apr 2005 02:10:08 GMT
 calling Stored Proc with InputOutput params - help please!
one erratum. in the code above, dim strSQL as STRING ... don't use the line
where you dim it as a command.


Quote:
> I for one replied to your earlier message.

> Irrespective of the procedure itself, and it doesn't look like a SQL
stored
> procedure, so I'd guess oracle or sybase, that isn't the issue.

> With a stored procedure in SQL, I'd build thestring textually, so for
> example "MyStoredProcedure" required four pass parameters, the string
mught
> look like this:=

> strSQL = "MyStoredProcedure '" & strParam1 & "','" & strParam2 & "','" &
> strParam3 & "','" & strParam4 & "'"

> open a connection to the database as you have, fine:=

>      Dim adoConn As ADODB.Connection
>      Dim cmdSQL As New ADODB.Command

>      Set adoConn = New ADODB.Connection
>      sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

>      With adoConn
>         .ConnectionString = sConnect
>         .CursorLocation = adUseClient
>         .Open
>      End With

> Now there are a couple of methods of executimg the SQL command. One is to
> use the connections Execute method. This is only useful really when you
> don't require a result returned.
> A better method obviously is to dim a recordset & execute it.

>     Dim rst As ADODB.Recordset
>     Set rst = New ADODB.Recordset
>     rst.Open strSQL, adoConn, adOpenDynamic, adLockOptimistic

> the .Open sends the SQL command to the database & will be populated by any
> output from the SP

> If there are lots data items, then its simple to loop through say a
> spreadsheet to pick them up.

> HTH

> Patrick Molloy
> Microsoft Excel MVP


> > I have posted a couple of times already about this issue, but after an
> > entire weekend of working on this, I still am stumped.
> > I have simplified my problem and now know exactly when it doesn't work:

> > Here's my test proc:
> > CREATE OR REPLACE PROCEDURE MY_PROC
> >    (P_PART_NO        IN NUMBER,
> >     P_PART_REV       IN VARCHAR2,
> >     R_PART_NO       IN OUT NUMBER,
> >     R_PART_REV       IN OUT VARCHAR2)
> > IS

> > BEGIN

> >   R_PART_NO := P_PART_NO;
> >   R_PART_REV := P_PART_REV;

> > END MY_PROC;

> > In order for my vb code to work, I can't assign a value to the IN OUT
> > params, so basically I have to treat them like OUT params. Otherwise, on
> the
> > Command.Execute method, I get an unhelpful error message, "errors have
> > occurred".

> > Here's my code:
> > ------
> >     Dim adoConn As ADODB.Connection
> >     Dim cmdSQL As New ADODB.Command

> >     Set adoConn = New ADODB.Connection
> >     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

> >     With adoConn
> >        .ConnectionString = sConnect
> >        .CursorLocation = adUseClient
> >        .Open
> >     End With

> >     With cmdSQL
> >         .CommandTimeout = 120
> >         .ActiveConnection = adoConn
> >         .CommandText = "MY_PROC"
> >         .CommandType = adCmdStoredProc
> >     End With

> >     Dim pPartNo As Integer
> >     Dim pPartRev As String
> >     Dim rPartNo As Integer
> >     Dim rPartRev As String

> >     cmdSQL.Parameters.Refresh
> >     MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

> >     pPartNo = 23813
> >     cmdSQL.Parameters(0).Value = pPartNo
> >     pPartRev = "mypartrev"
> >     cmdSQL.Parameters(1).Value = pPartRev

> >     ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
> >     ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
> >     ' **** COMMAND.EXECUTE METHOD BOMBS.
> >     'cmdSQL.Parameters(2).Value = rPartNo
> >     'cmdSQL.Parameters(3).Value = rPartRev

> >     ' << used to confirm the param vals, type and direction. works fine.
> >     Dim s As String
> >     Dim t As String
> >     Dim d As String
> >     Dim iCountParams As Integer
> >     For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
> >         s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Value & vbCrLf
> >         t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Type & vbCrLf
> >         d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Direction & vbCrLf
> >     Next iCountParams

> >     MsgBox s
> >     MsgBox t
> >     MsgBox d

> >     ' >>

> >     ' this cmdSQL.Execute method just gives me a vb error - "errors have
> > occurred"
> >     ' unless, of course, I comment the 2 lines above that assign values
to
> > the in/out variables.
> >     cmdSQL.Execute

> >     MsgBox cmdSQL.Parameters(2).Value & "  -  " &
> cmdSQL.Parameters(3).Value

> >     adoConn.Close
> >     Set adoConn = Nothing
> > ----



Sat, 09 Apr 2005 02:22:23 GMT
 calling Stored Proc with InputOutput params - help please!
Cynic

you have a couple of problems in your code -- irrespective of what any MVP
tell you, use the command object with a parameterized command string and
bind parameters.  you have varchar2 types, and those just do not work well
over time when you build the sql command -- and with out the bind variables,
there is no way to get the output data!  you need to have a look at the
Oracle client OLEDB documentation -- if you have Oracle client installed for
oracle 8i or later, it should be on your computer...

you should be using either the microsoft oledb provider or the oracle
provider - not the odbc provider.

you must not use adCmdStoredProc for an oracle stored procedure -- you have
to use either the odbc syntax or the oracle pl/sql anonymous block syntax:

oCmd.CommandText = "Begin My_Proc(?,?,?,?); end;"
oCmd.CommandType = adCmdText

let me know if you are still having problems...
regards
roy fine

Quote:

> I have posted a couple of times already about this issue, but after an
> entire weekend of working on this, I still am stumped.
> I have simplified my problem and now know exactly when it doesn't work:

> Here's my test proc:
> CREATE OR REPLACE PROCEDURE MY_PROC
>    (P_PART_NO        IN NUMBER,
>     P_PART_REV       IN VARCHAR2,
>     R_PART_NO       IN OUT NUMBER,
>     R_PART_REV       IN OUT VARCHAR2)
> IS

> BEGIN

>   R_PART_NO := P_PART_NO;
>   R_PART_REV := P_PART_REV;

> END MY_PROC;

> In order for my vb code to work, I can't assign a value to the IN OUT
> params, so basically I have to treat them like OUT params. Otherwise, on
the
> Command.Execute method, I get an unhelpful error message, "errors have
> occurred".

> Here's my code:
> ------
>     Dim adoConn As ADODB.Connection
>     Dim cmdSQL As New ADODB.Command

>     Set adoConn = New ADODB.Connection
>     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

>     With adoConn
>        .ConnectionString = sConnect
>        .CursorLocation = adUseClient
>        .Open
>     End With

>     With cmdSQL
>         .CommandTimeout = 120
>         .ActiveConnection = adoConn
>         .CommandText = "MY_PROC"
>         .CommandType = adCmdStoredProc
>     End With

>     Dim pPartNo As Integer
>     Dim pPartRev As String
>     Dim rPartNo As Integer
>     Dim rPartRev As String

>     cmdSQL.Parameters.Refresh
>     MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

>     pPartNo = 23813
>     cmdSQL.Parameters(0).Value = pPartNo
>     pPartRev = "mypartrev"
>     cmdSQL.Parameters(1).Value = pPartRev

>     ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
>     ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
>     ' **** COMMAND.EXECUTE METHOD BOMBS.
>     'cmdSQL.Parameters(2).Value = rPartNo
>     'cmdSQL.Parameters(3).Value = rPartRev

>     ' << used to confirm the param vals, type and direction. works fine.
>     Dim s As String
>     Dim t As String
>     Dim d As String
>     Dim iCountParams As Integer
>     For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
>         s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Value & vbCrLf
>         t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Type & vbCrLf
>         d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
> cmdSQL.Parameters(iCountParams).Direction & vbCrLf
>     Next iCountParams

>     MsgBox s
>     MsgBox t
>     MsgBox d

>     ' >>

>     ' this cmdSQL.Execute method just gives me a vb error - "errors have
> occurred"
>     ' unless, of course, I comment the 2 lines above that assign values to
> the in/out variables.
>     cmdSQL.Execute

>     MsgBox cmdSQL.Parameters(2).Value & "  -  " &

cmdSQL.Parameters(3).Value

- Show quoted text -

Quote:

>     adoConn.Close
>     Set adoConn = Nothing
> ----



Sat, 09 Apr 2005 02:48:27 GMT
 calling Stored Proc with InputOutput params - help please!
I have the docs, but there is absolutely nothing about what to do if I have
a SP that has 2 input params, and 2 inputoutput params.  It's the
inputoutputs that are causing problems.


Quote:
> Cynic

> you have a couple of problems in your code -- irrespective of what any MVP
> tell you, use the command object with a parameterized command string and
> bind parameters.  you have varchar2 types, and those just do not work well
> over time when you build the sql command -- and with out the bind
variables,
> there is no way to get the output data!  you need to have a look at the
> Oracle client OLEDB documentation -- if you have Oracle client installed
for
> oracle 8i or later, it should be on your computer...

> you should be using either the microsoft oledb provider or the oracle
> provider - not the odbc provider.

> you must not use adCmdStoredProc for an oracle stored procedure -- you
have
> to use either the odbc syntax or the oracle pl/sql anonymous block syntax:

> oCmd.CommandText = "Begin My_Proc(?,?,?,?); end;"
> oCmd.CommandType = adCmdText

> let me know if you are still having problems...
> regards
> roy fine


> > I have posted a couple of times already about this issue, but after an
> > entire weekend of working on this, I still am stumped.
> > I have simplified my problem and now know exactly when it doesn't work:

> > Here's my test proc:
> > CREATE OR REPLACE PROCEDURE MY_PROC
> >    (P_PART_NO        IN NUMBER,
> >     P_PART_REV       IN VARCHAR2,
> >     R_PART_NO       IN OUT NUMBER,
> >     R_PART_REV       IN OUT VARCHAR2)
> > IS

> > BEGIN

> >   R_PART_NO := P_PART_NO;
> >   R_PART_REV := P_PART_REV;

> > END MY_PROC;

> > In order for my vb code to work, I can't assign a value to the IN OUT
> > params, so basically I have to treat them like OUT params. Otherwise, on
> the
> > Command.Execute method, I get an unhelpful error message, "errors have
> > occurred".

> > Here's my code:
> > ------
> >     Dim adoConn As ADODB.Connection
> >     Dim cmdSQL As New ADODB.Command

> >     Set adoConn = New ADODB.Connection
> >     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

> >     With adoConn
> >        .ConnectionString = sConnect
> >        .CursorLocation = adUseClient
> >        .Open
> >     End With

> >     With cmdSQL
> >         .CommandTimeout = 120
> >         .ActiveConnection = adoConn
> >         .CommandText = "MY_PROC"
> >         .CommandType = adCmdStoredProc
> >     End With

> >     Dim pPartNo As Integer
> >     Dim pPartRev As String
> >     Dim rPartNo As Integer
> >     Dim rPartRev As String

> >     cmdSQL.Parameters.Refresh
> >     MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

> >     pPartNo = 23813
> >     cmdSQL.Parameters(0).Value = pPartNo
> >     pPartRev = "mypartrev"
> >     cmdSQL.Parameters(1).Value = pPartRev

> >     ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
> >     ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
> >     ' **** COMMAND.EXECUTE METHOD BOMBS.
> >     'cmdSQL.Parameters(2).Value = rPartNo
> >     'cmdSQL.Parameters(3).Value = rPartRev

> >     ' << used to confirm the param vals, type and direction. works fine.
> >     Dim s As String
> >     Dim t As String
> >     Dim d As String
> >     Dim iCountParams As Integer
> >     For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
> >         s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Value & vbCrLf
> >         t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Type & vbCrLf
> >         d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
> > cmdSQL.Parameters(iCountParams).Direction & vbCrLf
> >     Next iCountParams

> >     MsgBox s
> >     MsgBox t
> >     MsgBox d

> >     ' >>

> >     ' this cmdSQL.Execute method just gives me a vb error - "errors have
> > occurred"
> >     ' unless, of course, I comment the 2 lines above that assign values
to
> > the in/out variables.
> >     cmdSQL.Execute

> >     MsgBox cmdSQL.Parameters(2).Value & "  -  " &
> cmdSQL.Parameters(3).Value

> >     adoConn.Close
> >     Set adoConn = Nothing
> > ----



Sat, 09 Apr 2005 05:27:00 GMT
 calling Stored Proc with InputOutput params - help please!
Cynic --

I too have suffered the pain of Oracle I/O parameters with
the Oracle provider for .NET ... one thing that seemed to
make my problems go away was setting the I/O parameters'
data types to "OracleType.Int32" rather
than "OracleType.Number" for numeric parameters.  I don't
know WHY, but this just seemed to work.

Good luck!

Kurt Mang

Quote:
>-----Original Message-----
>I have posted a couple of times already about this issue,
but after an
>entire weekend of working on this, I still am stumped.
>I have simplified my problem and now know exactly when it
doesn't work:

>Here's my test proc:
>CREATE OR REPLACE PROCEDURE MY_PROC
>   (P_PART_NO        IN NUMBER,
>    P_PART_REV       IN VARCHAR2,
>    R_PART_NO       IN OUT NUMBER,
>    R_PART_REV       IN OUT VARCHAR2)
>IS

>BEGIN

>  R_PART_NO := P_PART_NO;
>  R_PART_REV := P_PART_REV;

>END MY_PROC;

>In order for my vb code to work, I can't assign a value
to the IN OUT
>params, so basically I have to treat them like OUT

params. Otherwise, on the
Quote:
>Command.Execute method, I get an unhelpful error

message, "errors have
Quote:
>occurred".

>Here's my code:
>------
>    Dim adoConn As ADODB.Connection
>    Dim cmdSQL As New ADODB.Command

>    Set adoConn = New ADODB.Connection
>    sConnect = "Data Source='myodbc';User

ID='PROD';Password='PROD';"

- Show quoted text -

Quote:

>    With adoConn
>       .ConnectionString = sConnect
>       .CursorLocation = adUseClient
>       .Open
>    End With

>    With cmdSQL
>        .CommandTimeout = 120
>        .ActiveConnection = adoConn
>        .CommandText = "MY_PROC"
>        .CommandType = adCmdStoredProc
>    End With

>    Dim pPartNo As Integer
>    Dim pPartRev As String
>    Dim rPartNo As Integer
>    Dim rPartRev As String

>    cmdSQL.Parameters.Refresh
>    MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

>    pPartNo = 23813
>    cmdSQL.Parameters(0).Value = pPartNo
>    pPartRev = "mypartrev"
>    cmdSQL.Parameters(1).Value = pPartRev

>    ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN
THOUGH
>    ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE,
THE
>    ' **** COMMAND.EXECUTE METHOD BOMBS.
>    'cmdSQL.Parameters(2).Value = rPartNo
>    'cmdSQL.Parameters(3).Value = rPartRev

>    ' << used to confirm the param vals, type and

direction. works fine.

- Show quoted text -

Quote:
>    Dim s As String
>    Dim t As String
>    Dim d As String
>    Dim iCountParams As Integer
>    For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
>        s = s & cmdSQL.Parameters(iCountParams).Name & "
= " &
>cmdSQL.Parameters(iCountParams).Value & vbCrLf
>        t = t & cmdSQL.Parameters(iCountParams).Name & "
= " &
>cmdSQL.Parameters(iCountParams).Type & vbCrLf
>        d = d & cmdSQL.Parameters(iCountParams).Name & "
= " &
>cmdSQL.Parameters(iCountParams).Direction & vbCrLf
>    Next iCountParams

>    MsgBox s
>    MsgBox t
>    MsgBox d

>    ' >>

>    ' this cmdSQL.Execute method just gives me a vb

error - "errors have
Quote:
>occurred"
>    ' unless, of course, I comment the 2 lines above that
assign values to
>the in/out variables.
>    cmdSQL.Execute

>    MsgBox cmdSQL.Parameters(2).Value & "  -  " &

cmdSQL.Parameters(3).Value

- Show quoted text -

Quote:

>    adoConn.Close
>    Set adoConn = Nothing
>----

>.



Sat, 09 Apr 2005 06:19:05 GMT
 calling Stored Proc with InputOutput params - help please!
Cynic,

I have used ADO, the Command object, and the IN/OUT parameter for Oracle
without any problems.

Inserted below is but one sample that I put together to demonstrate such.

It is implemented in VC++ (that is my native language)...  I hope it is of
use to you:

Best regards
roy fine

-- *****************************************
Procedure         TEST_INOUT ( param1 IN number,
                               param2 IN OUT number,
                               param3 out number) IS
BEGIN
param3 := param2 + param1;
param2 := param1*2;
END; -- Procedure TEST_INOUT
-- *****************************************

_bstr_t bstrCnxt(L"Provider=OraOLEDB.Oracle;Data Source=Ecom1.ssi.org");
/* ** */
/* ****************************************************** */
/* ** */
int main(int argc, char* argv[]){
printf("Hello World!\n");
::CoInitialize(NULL);

_ConnectionPtr oCnxt(__uuidof(Connection));
_RecordsetPtr oRst(__uuidof(Recordset));
_CommandPtr oCmd(__uuidof(Command));
_ParameterPtr oParm1,oParm2,oParm3;
oCnxt->CursorLocation = adUseServer;
try{
 oCnxt->Open(bstrCnxt,L"smaster",L"smaster",adConnectUnspecified);
 oCmd->PutRefActiveConnection(oCnxt);
 _bstr_t bstrCmd(L"begin smaster.TEST_INOUT(?,?,?);end;");
 oCmd->PutCommandText(bstrCmd);
 oParm1 = oCmd->CreateParameter(L"PARM1",adInteger,adParamInput,0);
 oParm2 = oCmd->CreateParameter(L"PARM2",adInteger,adParamInputOutput,0);
 oParm3 = oCmd->CreateParameter(L"PARM3",adInteger,adParamOutput,0);
 oCmd->Parameters->Append(oParm1);
 oCmd->Parameters->Append(oParm2);
 oCmd->Parameters->Append(oParm3);
 oParm1->Value = _variant_t((long)2);
 oParm2->Value = _variant_t((long)3);
 oCmd->Execute(NULL,NULL,adCmdText);
 int parmCount = oCmd->Parameters->GetCount();
 int inVal = (long)oParm1->Value;
 int inoutVal = (long)oParm2->Value;
 int outVal = (long)oParm3->Value;
 printf("\nin Parameter : %d",inVal);
 printf("\nin/out Parameter : %d",inoutVal);
 printf("\nout Parameter : %d",outVal);
 }
catch(_com_error &e){
 _bstr_t errd(e.Description());
 _bstr_t errm(e.ErrorMessage());
 }

oCnxt->Close();
oCnxt.Release();
printf("\n\nDone!\n");
return 0;

Quote:
}

/* ** */
/* ****************************************************** */
/* ** */

/* here is the output from the above program */
===============================
Hello World!

in Parameter : 2
in/out Parameter : 4
out Parameter : 5

Done!
Press any key to continue
===============================

Quote:

> I have the docs, but there is absolutely nothing about what to do if I
have
> a SP that has 2 input params, and 2 inputoutput params.  It's the
> inputoutputs that are causing problems.



> > Cynic

> > you have a couple of problems in your code -- irrespective of what any
MVP
> > tell you, use the command object with a parameterized command string and
> > bind parameters.  you have varchar2 types, and those just do not work
well
> > over time when you build the sql command -- and with out the bind
> variables,
> > there is no way to get the output data!  you need to have a look at the
> > Oracle client OLEDB documentation -- if you have Oracle client installed
> for
> > oracle 8i or later, it should be on your computer...

> > you should be using either the microsoft oledb provider or the oracle
> > provider - not the odbc provider.

> > you must not use adCmdStoredProc for an oracle stored procedure -- you
> have
> > to use either the odbc syntax or the oracle pl/sql anonymous block
syntax:

> > oCmd.CommandText = "Begin My_Proc(?,?,?,?); end;"
> > oCmd.CommandType = adCmdText

> > let me know if you are still having problems...
> > regards
> > roy fine


> > > I have posted a couple of times already about this issue, but after an
> > > entire weekend of working on this, I still am stumped.
> > > I have simplified my problem and now know exactly when it doesn't
work:

> > > Here's my test proc:
> > > CREATE OR REPLACE PROCEDURE MY_PROC
> > >    (P_PART_NO        IN NUMBER,
> > >     P_PART_REV       IN VARCHAR2,
> > >     R_PART_NO       IN OUT NUMBER,
> > >     R_PART_REV       IN OUT VARCHAR2)
> > > IS

> > > BEGIN

> > >   R_PART_NO := P_PART_NO;
> > >   R_PART_REV := P_PART_REV;

> > > END MY_PROC;

> > > In order for my vb code to work, I can't assign a value to the IN OUT
> > > params, so basically I have to treat them like OUT params. Otherwise,
on
> > the
> > > Command.Execute method, I get an unhelpful error message, "errors have
> > > occurred".

> > > Here's my code:
> > > ------
> > >     Dim adoConn As ADODB.Connection
> > >     Dim cmdSQL As New ADODB.Command

> > >     Set adoConn = New ADODB.Connection
> > >     sConnect = "Data Source='myodbc';User ID='PROD';Password='PROD';"

> > >     With adoConn
> > >        .ConnectionString = sConnect
> > >        .CursorLocation = adUseClient
> > >        .Open
> > >     End With

> > >     With cmdSQL
> > >         .CommandTimeout = 120
> > >         .ActiveConnection = adoConn
> > >         .CommandText = "MY_PROC"
> > >         .CommandType = adCmdStoredProc
> > >     End With

> > >     Dim pPartNo As Integer
> > >     Dim pPartRev As String
> > >     Dim rPartNo As Integer
> > >     Dim rPartRev As String

> > >     cmdSQL.Parameters.Refresh
> > >     MsgBox cmdSQL.Parameters.Count     ' confirms 4 params

> > >     pPartNo = 23813
> > >     cmdSQL.Parameters(0).Value = pPartNo
> > >     pPartRev = "mypartrev"
> > >     cmdSQL.Parameters(1).Value = pPartRev

> > >     ' **** NOTE: HERE IS WHERE I RUN INTO PROBLEMS. EVEN THOUGH
> > >     ' **** THESE PARAMS ARE IN/OUT, IF I UNCOMMENT THESE, THE
> > >     ' **** COMMAND.EXECUTE METHOD BOMBS.
> > >     'cmdSQL.Parameters(2).Value = rPartNo
> > >     'cmdSQL.Parameters(3).Value = rPartRev

> > >     ' << used to confirm the param vals, type and direction. works
fine.
> > >     Dim s As String
> > >     Dim t As String
> > >     Dim d As String
> > >     Dim iCountParams As Integer
> > >     For iCountParams = 0 To (cmdSQL.Parameters.Count - 1)
> > >         s = s & cmdSQL.Parameters(iCountParams).Name & " = " &
> > > cmdSQL.Parameters(iCountParams).Value & vbCrLf
> > >         t = t & cmdSQL.Parameters(iCountParams).Name & " = " &
> > > cmdSQL.Parameters(iCountParams).Type & vbCrLf
> > >         d = d & cmdSQL.Parameters(iCountParams).Name & " = " &
> > > cmdSQL.Parameters(iCountParams).Direction & vbCrLf
> > >     Next iCountParams

> > >     MsgBox s
> > >     MsgBox t
> > >     MsgBox d

> > >     ' >>

> > >     ' this cmdSQL.Execute method just gives me a vb error - "errors
have
> > > occurred"
> > >     ' unless, of course, I comment the 2 lines above that assign
values
> to
> > > the in/out variables.
> > >     cmdSQL.Execute

> > >     MsgBox cmdSQL.Parameters(2).Value & "  -  " &
> > cmdSQL.Parameters(3).Value

> > >     adoConn.Close
> > >     Set adoConn = Nothing
> > > ----



Sun, 10 Apr 2005 02:55:48 GMT
 calling Stored Proc with InputOutput params - help please!
Cynic,

It must be frustrating that you still have the problem despite all the
experts.
I know I'm going to be criticised for what I am about to say, but I'll say
it ( again ) anyway!
I have been told in the past never to use OUTPUT Parameters ( See the "IN
OUT Parameters" thread )
I use the SELECT statement to return values.  Others would argue that this
is "inefficient", I disagree for the following reason:

Using result sets is more consistent & reliable and the additional effort to
use output parameters for a very small performance gain is not justified.

Ignore any claim of dramatic improvements. In a real application with many
users and large databases the actual data access is likely to take longer
than either type of call so any gain on the VB side will be almost
irrelevant. Also, both methods ( OUTPUT Parameters Vs SELECT statement )
probably have about the same size of data over the network so the real gain
will be minimal.

Since the client application will only be retrieving data intermittently as
the user enters or edits data the performance issue is moot.

To conclude, you have a perfect right to disagree with me.  I am only
suggesting another option
for you to try.  You have spent a whole weekend on this problem and it ain't
fixed.  At this stage
it mightn't be a bad idea to look at all your options, ie, If IN OUT
Parameters are not working for you
then look at a different way to achieve the same thing.

Good luck with the problem

Regards

Ross
Not an MVP!

For a more detailed

Quote:
> [followups trimmed to microsoft.public.data.ado]


> >CREATE OR REPLACE PROCEDURE MY_PROC
> >   (P_PART_NO        IN NUMBER,
> >    P_PART_REV       IN VARCHAR2,
> >    R_PART_NO       IN OUT NUMBER,
> >    R_PART_REV       IN OUT VARCHAR2)
> >IS

> >BEGIN

> >  R_PART_NO := P_PART_NO;
> >  R_PART_REV := P_PART_REV;

> >END MY_PROC;

> Not strictly required, but I'd specify the size of your VARCHAR2
> parameters. In the VB code below, I'm assuming a size of 50 for a
> PART_REV. I'm assuming that your PART_NO is a 32-bit integer; if not,
> then change the data type from adInteger as appropriate.

> I'm also assuming that your IN OUT params in the final procedure truly
> need to input and output, not just output. If this is so, then you only
> need to specify as OUT in the procedure and adParamOutput in VB, and
> omit the value in CreateParameter.

> Try this:

> Dim adoConn As ADODB.Connection
> Dim cmdSQL As ADODB.Command
> Dim oPartNo As ADODB.Parameter
> Dim oPartRev As ADODB.Parameter

> Set adoConn = New ADODB.Connection
> adoConn.Open "Data Source='myodbc';User ID='PROD';Password='PROD';"

> Set cmdSQL = CreateObject("ADODB.Command")
> Set cmdSQL.ActiveConnection = adoConn
> cmdSQL.CommandType = adCmdStoredProc
> cmdSQL.CommandText = "MY_PROC"
> Set params = cmdSQL.Parameters

> ' input parameters
> params.Append cmdSQL.CreateParameter(, adInteger, adParamInput, pPartNo)
> params.Append cmdSQL.CreateParameter(, adVarChar, adParamInput, 50, _
> pPartRev)

> ' input / output parameters; NB: we want access to these after execution
> Set oPartNo = cmdSQL.CreateParameter(, adInteger, adParamInputOutput, _
> pPartNo)
> params.Append oPartNo
> Set oPartRev = cmdSQL.CreateParameter(, adVarChar, adParamInputOutput, _
> 50, pPartRev)
> params.Append oPartNo

> ' execute the command
> cmd.Execute , , adCmdStoredProc + adExecuteNoRecords

> ' retrieve the values from output parameters; NB: add NULL handling!
> pPartNo = oPartNo.value
> pPartRev = oPartRev.value

> --
> Ross McKay, WebAware Pty Ltd
> "I got to think less. I had thought that, actually."
> - John Cusack, "Pushing Tin"



Mon, 11 Apr 2005 05:54:00 GMT
 calling Stored Proc with InputOutput params - help please!
On Wed, 23 Oct 2002 22:54:00 +0100, "Ross McLoughlin"

Quote:

>Cynic,

>It must be frustrating that you still have the problem despite all the
>experts.
>I know I'm going to be criticised for what I am about to say, but I'll say
>it ( again ) anyway!
>I have been told in the past never to use OUTPUT Parameters ( See the "IN
>OUT Parameters" thread )
>I use the SELECT statement to return values.  Others would argue that this
>is "inefficient", I disagree for the following reason:

>Using result sets is more consistent & reliable and the additional effort to
>use output parameters for a very small performance gain is not justified.

Depends...

Quote:
>Ignore any claim of dramatic improvements. In a real application with many
>users and large databases the actual data access is likely to take longer
>than either type of call so any gain on the VB side will be almost
>irrelevant. Also, both methods ( OUTPUT Parameters Vs SELECT statement )
>probably have about the same size of data over the network so the real gain
>will be minimal.

>Since the client application will only be retrieving data intermittently as
>the user enters or edits data the performance issue is moot.

Whilst this may be true for client/server applications on fast networks,
it is not true for client/server applications on WANs or for web
applications on high-load servers.

Returning results in a cursor means that ADO must construct a recordset
and retrieve meta-data about each column returned. This is additional
work (bad on a high-load web server) and network traffic (bad on a slow
network).

Quote:
>To conclude, you have a perfect right to disagree with me.  I am only
>suggesting another option
>for you to try.  You have spent a whole weekend on this problem and it ain't
>fixed.  At this stage
>it mightn't be a bad idea to look at all your options, ie, If IN OUT
>Parameters are not working for you
>then look at a different way to achieve the same thing.

Always a reasonable approach, in my book - first, get the problem
solved; second, make it faster.

Quote:
>Ross
>Not an MVP!

Ross, also not an MVP :-)
--
Ross McKay, WebAware Pty Ltd
"I got to think less. I had thought that, actually."
- John Cusack, "Pushing Tin"


Mon, 11 Apr 2005 06:54:51 GMT
 calling Stored Proc with InputOutput params - help please!


Quote:

> You have spent a whole weekend on this problem and it ain't
> fixed.  At this stage
> it mightn't be a bad idea to look at all your options, ie, If IN OUT

Spoken like a true 'been there, done that" programmer person... :)

Quote:

> Ross
> Not an MVP!

rlf
same as ross


Mon, 11 Apr 2005 09:47:14 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. calling Stored Proc with InputOutput params - help please!

2. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

3. Ora ODBC Driver: VARCHAR2 out params in proc calls / proc unknown

4. !!!! PLEASE HELP ::stored proc call dies with ODBC

5. Can you help with: Stored Proc w/ 2 params

6. Stored Proc Calling Another Stored Proc

7. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

8. calling stored proc from stored proc via variable

9. Calling a stored proc within a stored proc

10. Calling Stored Proc from other Stored Proc

11. a stored proc calling another stored proc

12. Calling another stored proc within a stored proc


 
Powered by phpBB® Forum Software