ADO.NET and ODBC { CALL ..... } Syntax

[Guest]

Environment:
Web server IIS 5 running on Windows 2000 (SP2)
Oracle Client 8.1.7
Coonecting to Oracle 8i DB running on Unix
ADO is used as connecting mechanism (MSDAORA)

Synopsis:
Curently we are using ADO (MDAC 2.5) to connect to Oracle 8i (Oracle client
8.1.7).
We are using heavily ODBC call syntax { CALL ,…} and so on. We are using
OleDB connection with provider as MSDAORA.
Especially the using ODBC Call syntax to fetch the data either via Stored
Proc, Function or REF Cursor. It is working seamlessly!

Lately we are trying to migrate to ADO.NET. I was trying to test the same
concept of ODBC call Notation with Microsoft ADO.NET Oracle provider (came
with .Net 1.1) System.Data.OracleClient as well as System.Oledb .NET
Provider. However to my surprise I found that this technique does not work
the same way as it works in ADO arena.

To be specific, in ADO world for Command Object, once I set the CommandType
and CommandText property then after setting the ActiveCoonection property,
ADO intelligently populates the Parameter Collection seamlessly based on '?'
notation in the provided CommandText property. Parameter collection is
populated with Input and Output parameters, data type is set properly. This
is automatically done by ADO.

Does ADO.Net (System.OracleClinet) supports this behavior? However MSDAORA
OleDB provider supports above OBDC call syntax.

Secondly System.OleDB not being 100% managed provider, still uses MSDAORA
OleDB provider underneath. However my finding shows that it does not populate
the Prameter collection automatically. One has to add the Items to parameter
collection, set the type and direction and so on.

I would like know am I missing anything out here? Any other way to have
identical behavior with System.OracleClient.

Can anybody please provide me with expert advise from your side as to what
needs to be done in ADO.NET world to have the same behavior as ADO world as
far as
Calling Oracle SPs/Functions with ODBC Call notation?

I am using the technique given by you and Microsoft for using Arrays and ADO
to retrieve Recordset from Oracle Stored Proc.
http://support.microsoft.com/kb/229919/EN-US/

I am also using the technique documented by Microsoft for REF Cursor Usage.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q255043.

Thanks in advance.
Nilesh

 

[David Browne]

Environment:
Web server IIS 5 running on Windows 2000 (SP2)
Oracle Client 8.1.7
Coonecting to Oracle 8i DB running on Unix
ADO is used as connecting mechanism (MSDAORA)

Synopsis:
Curently we are using ADO (MDAC 2.5) to connect to Oracle 8i (Oracle
client
8.1.7).
We are using heavily ODBC call syntax { CALL ,.} and so on. We are using
OleDB connection with provider as MSDAORA.
Especially the using ODBC Call syntax to fetch the data either via Stored
Proc, Function or REF Cursor. It is working seamlessly!

Lately we are trying to migrate to ADO.NET. I was trying to test the same
concept of ODBC call Notation with Microsoft ADO.NET Oracle provider (came
with .Net 1.1) System.Data.OracleClient as well as System.Oledb .NET
Provider. However to my surprise I found that this technique does not work
the same way as it works in ADO arena.

To be specific, in ADO world for Command Object, once I set the
CommandType
and CommandText property then after setting the ActiveCoonection property,
ADO intelligently populates the Parameter Collection seamlessly based on
'?'

An expensive round trip to Oracle.

notation in the provided CommandText property. Parameter collection is
populated with Input and Output parameters, data type is set properly.
This
is automatically done by ADO.

Does ADO.Net (System.OracleClinet) supports this behavior? However MSDAORA
OleDB provider supports above OBDC call syntax.

Secondly System.OleDB not being 100% managed provider, still uses MSDAORA
OleDB provider underneath. However my finding shows that it does not
populate
the Prameter collection automatically. One has to add the Items to
parameter
collection, set the type and direction and so on.

I would like know am I missing anything out here? Any other way to have
identical behavior with System.OracleClient.

Can anybody please provide me with expert advise from your side as to what
needs to be done in ADO.NET world to have the same behavior as ADO world
as
far as
Calling Oracle SPs/Functions with ODBC Call notation?

Forget ODBC call notation. The equivilent in ADO.NET is to set your
CommandType to StoredProcedure, set your CommandText to the name of the
stored procedure and run
OracleCommandBuilder.DeriveParameters(OracleCommand) to go to the server and
figure out what the parameters should be.

You should really only do this at design-time, or with a command you will
execute many times, since querying Oracle for the procedure parameters is
not free.

David

 

[Kingsley Idehen]

Environment:
Web server IIS 5 running on Windows 2000 (SP2)
Oracle Client 8.1.7
Coonecting to Oracle 8i DB running on Unix
ADO is used as connecting mechanism (MSDAORA)

Synopsis:
Curently we are using ADO (MDAC 2.5) to connect to Oracle 8i (Oracle client
8.1.7).
We are using heavily ODBC call syntax { CALL ,…} and so on. We are using
OleDB connection with provider as MSDAORA.
Especially the using ODBC Call syntax to fetch the data either via Stored
Proc, Function or REF Cursor. It is working seamlessly!

Lately we are trying to migrate to ADO.NET. I was trying to test the same
concept of ODBC call Notation with Microsoft ADO.NET Oracle provider (came
with .Net 1.1) System.Data.OracleClient as well as System.Oledb .NET
Provider. However to my surprise I found that this technique does not work
the same way as it works in ADO arena.

To be specific, in ADO world for Command Object, once I set the CommandType
and CommandText property then after setting the ActiveCoonection property,
ADO intelligently populates the Parameter Collection seamlessly based on '?'
notation in the provided CommandText property. Parameter collection is
populated with Input and Output parameters, data type is set properly. This
is automatically done by ADO.

Does ADO.Net (System.OracleClinet) supports this behavior? However MSDAORA
OleDB provider supports above OBDC call syntax.

Secondly System.OleDB not being 100% managed provider, still uses MSDAORA
OleDB provider underneath. However my finding shows that it does not populate
the Prameter collection automatically. One has to add the Items to parameter
collection, set the type and direction and so on.

I would like know am I missing anything out here? Any other way to have
identical behavior with System.OracleClient.

Can anybody please provide me with expert advise from your side as to what
needs to be done in ADO.NET world to have the same behavior as ADO world as
far as
Calling Oracle SPs/Functions with ODBC Call notation?

I am using the technique given by you and Microsoft for using Arrays and ADO
to retrieve Recordset from Oracle Stored Proc.
http://support.microsoft.com/kb/229919/EN-US/

I am also using the technique documented by Microsoft for REF Cursor Usage.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q255043.

Thanks in advance.
Nilesh

Nilesh,

You can obtain a managed ADO.NET provider for Oracle from OpenLink
Software.

See the following links for details:
http://uda.openlinksw.com/ado.net/

This is the best path for leveraging your existing ODBC knowledge in
the ADO.NET realm or working in pure ADO.NET mode against Oracle.

Regards,
Kingsley Idehen
OpenLink Software http://www.openlinksw.com/blog/~kidehen

 

[David Browne]

.. . .

Nilesh,

You can obtain a managed ADO.NET provider for Oracle from OpenLink
Software.

See the following links for details:
http://uda.openlinksw.com/ado.net/

This is the best path for leveraging your existing ODBC knowledge in
the ADO.NET realm or working in pure ADO.NET mode against Oracle.

No it's not.

The best bath for leveraging your existing ODBC knoledge in the ADO.NET
realm is the System.Data.Odbc client.

The best path for working in pure ADO.NET mode against Oracle is ODP.NET
from Oracle
(http://www.oracle.com/technology/tech/windows/odpnet/index.html)

Both of which are free and supported by the company that owns the underlying
unmanaged library.

David