ADO.net using ODBC and oracle

Hi,
I am trying to execute a simple oracle stored procedure that has an
output parameter, using odbc and ado.net. Here is the code

cSqlOdbcCmd = objConnect.CreateCommand()
cSqlOdbcCmd.CommandType = CommandType.StoredProcedure
cSqlOdbcCmd.CommandText = "packagename.Procedurename"

cSqlOdbcCmd.Parameters.Add("outResult",
OdbcType.Numeric).Direction
= ParameterDirection.Output


objConnect.Open()
cSqlOdbcCmd.ExecuteNonQuery()

The oracle client version is 8.05 and the server version is 8i
(release 8.1.5). I keep getting an invalid sql statement. The same
runs in vb6 ado (with similar call) just fine. I checked for
packagename and procedure name mismatches but there were none. I have
odbc set up propery along with net 8 client. Everytime it goes to the
executenonquery statement it gives an invalid sql statement error. I
also tried to execute simple text sql statments (not stored
procedures) which did not have any problems.

What could be wrong?


Thanks

Roy
Sat May 15 08:33:26 CDT 2004

Shania

Try changing the CommandType to Text, and using an anonymous pl/sql block.

Here is one attempt (albeit in C#)

string cnxtstr = "Data Source=xxx;User ID=xxxx;Password=xxxx;";
string cmdtxt = "begin packagename.Procedurename(:outval);end;";
object myValue = null;
using(OdbcConnection oCnxion = new OdbcConnection(cnxtstr)){
oCnxion.Open();
using(OdbcCommand cSqlOdbcCmd = new OdbcCommand(cmdtxt,oCnxion)){
cSqlOdbcCmd.CommandType = CommandType.Text;
OdbcParameter oParm1 =
cSqlOdbcCmd.Parameters.Add("outval",OdbcType.Decimal);
oParm1.Direction = ParameterDirection.Output;
cSqlOdbcCmd.ExecuteNonQuery();
myValue = oParm1.Value;
}
}

regards
roy fine


"shania" <uramnath@hotmail.com> wrote in message
news:795a47.0405140659.5a944c13@posting.google.com...
> Hi,
> I am trying to execute a simple oracle stored procedure that has an
> output parameter, using odbc and ado.net. Here is the code
>
> cSqlOdbcCmd = objConnect.CreateCommand()
> cSqlOdbcCmd.CommandType = CommandType.StoredProcedure
> cSqlOdbcCmd.CommandText = "packagename.Procedurename"
>
> cSqlOdbcCmd.Parameters.Add("outResult",
> OdbcType.Numeric).Direction
> = ParameterDirection.Output
>
>
> objConnect.Open()
> cSqlOdbcCmd.ExecuteNonQuery()
>
> The oracle client version is 8.05 and the server version is 8i
> (release 8.1.5). I keep getting an invalid sql statement. The same
> runs in vb6 ado (with similar call) just fine. I checked for
> packagename and procedure name mismatches but there were none. I have
> odbc set up propery along with net 8 client. Everytime it goes to the
> executenonquery statement it gives an invalid sql statement error. I
> also tried to execute simple text sql statments (not stored
> procedures) which did not have any problems.
>
> What could be wrong?
>
>
> Thanks

uramnath
Mon May 17 12:10:31 CDT 2004

Thank you for your reply. I will try it out and go from there.
It does however work if I use the following syntax
{call packagename.procedurename(?)}
the ? being the output variable.


Thanks


"Roy Fine" <rlfine@twt.obfuscate.net> wrote in message news:<#tOk#GoOEHA.1276@TK2MSFTNGP11.phx.gbl>...
> Shania
>
> Try changing the CommandType to Text, and using an anonymous pl/sql block.
>
> Here is one attempt (albeit in C#)
>
> string cnxtstr = "Data Source=xxx;User ID=xxxx;Password=xxxx;";
> string cmdtxt = "begin packagename.Procedurename(:outval);end;";
> object myValue = null;
> using(OdbcConnection oCnxion = new OdbcConnection(cnxtstr)){
> oCnxion.Open();
> using(OdbcCommand cSqlOdbcCmd = new OdbcCommand(cmdtxt,oCnxion)){
> cSqlOdbcCmd.CommandType = CommandType.Text;
> OdbcParameter oParm1 =
> cSqlOdbcCmd.Parameters.Add("outval",OdbcType.Decimal);
> oParm1.Direction = ParameterDirection.Output;
> cSqlOdbcCmd.ExecuteNonQuery();
> myValue = oParm1.Value;
> }
> }
>
> regards
> roy fine
>
>
> "shania" <uramnath@hotmail.com> wrote in message
> news:795a47.0405140659.5a944c13@posting.google.com...
> > Hi,
> > I am trying to execute a simple oracle stored procedure that has an
> > output parameter, using odbc and ado.net. Here is the code
> >
> > cSqlOdbcCmd = objConnect.CreateCommand()
> > cSqlOdbcCmd.CommandType = CommandType.StoredProcedure
> > cSqlOdbcCmd.CommandText = "packagename.Procedurename"
> >
> > cSqlOdbcCmd.Parameters.Add("outResult",
> > OdbcType.Numeric).Direction
> > = ParameterDirection.Output
> >
> >
> > objConnect.Open()
> > cSqlOdbcCmd.ExecuteNonQuery()
> >
> > The oracle client version is 8.05 and the server version is 8i
> > (release 8.1.5). I keep getting an invalid sql statement. The same
> > runs in vb6 ado (with similar call) just fine. I checked for
> > packagename and procedure name mismatches but there were none. I have
> > odbc set up propery along with net 8 client. Everytime it goes to the
> > executenonquery statement it gives an invalid sql statement error. I
> > also tried to execute simple text sql statments (not stored
> > procedures) which did not have any problems.
> >
> > What could be wrong?
> >
> >
> > Thanks

Roy
Mon May 17 20:40:05 CDT 2004


"shania" <uramnath@hotmail.com> wrote in message
news:795a47.0405170910.61ab3a6c@posting.google.com...
> Thank you for your reply. I will try it out and go from there.
> It does however work if I use the following syntax
> {call packagename.procedurename(?)}
> the ? being the output variable.
>

That's the ODBC call syntax - with the {} -, and that works as well...

regards
roy fine


>
> Thanks
>
>
> "Roy Fine" <rlfine@twt.obfuscate.net> wrote in message
news:<#tOk#GoOEHA.1276@TK2MSFTNGP11.phx.gbl>...
> > Shania
> >
> > Try changing the CommandType to Text, and using an anonymous pl/sql
block.
> >
> > Here is one attempt (albeit in C#)
> >
> > string cnxtstr = "Data Source=xxx;User ID=xxxx;Password=xxxx;";
> > string cmdtxt = "begin packagename.Procedurename(:outval);end;";
> > object myValue = null;
> > using(OdbcConnection oCnxion = new OdbcConnection(cnxtstr)){
> > oCnxion.Open();
> > using(OdbcCommand cSqlOdbcCmd = new OdbcCommand(cmdtxt,oCnxion)){
> > cSqlOdbcCmd.CommandType = CommandType.Text;
> > OdbcParameter oParm1 =
> > cSqlOdbcCmd.Parameters.Add("outval",OdbcType.Decimal);
> > oParm1.Direction = ParameterDirection.Output;
> > cSqlOdbcCmd.ExecuteNonQuery();
> > myValue = oParm1.Value;
> > }
> > }
> >
> > regards
> > roy fine
> >
> >
> > "shania" <uramnath@hotmail.com> wrote in message
> > news:795a47.0405140659.5a944c13@posting.google.com...
> > > Hi,
> > > I am trying to execute a simple oracle stored procedure that has an
> > > output parameter, using odbc and ado.net. Here is the code
> > >
> > > cSqlOdbcCmd = objConnect.CreateCommand()
> > > cSqlOdbcCmd.CommandType = CommandType.StoredProcedure
> > > cSqlOdbcCmd.CommandText = "packagename.Procedurename"
> > >
> > > cSqlOdbcCmd.Parameters.Add("outResult",
> > > OdbcType.Numeric).Direction
> > > = ParameterDirection.Output
> > >
> > >
> > > objConnect.Open()
> > > cSqlOdbcCmd.ExecuteNonQuery()
> > >
> > > The oracle client version is 8.05 and the server version is 8i
> > > (release 8.1.5). I keep getting an invalid sql statement. The same
> > > runs in vb6 ado (with similar call) just fine. I checked for
> > > packagename and procedure name mismatches but there were none. I have
> > > odbc set up propery along with net 8 client. Everytime it goes to the
> > > executenonquery statement it gives an invalid sql statement error. I
> > > also tried to execute simple text sql statments (not stored
> > > procedures) which did not have any problems.
> > >
> > > What could be wrong?
> > >
> > >
> > > Thanks