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 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
 
 [ 6 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. Calling a Stored proc within a stored proc

7. How do I call a stored proc from inside another stored proc

8. Calling stored proc from stored proc

9. Calling Stored Proc from other Stored Proc

10. Using a stored proc to call another stored proc

11. Calling Stored Proc from another Stored Proc

12. Stored Proc Call in Stored Proc


 
Powered by phpBB® Forum Software