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

From: Nilesh (Nilesh_at_discussions.microsoft.com)
Date: 11/05/04 

Date: Fri, 5 Nov 2004 11:23:03 -0800

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

Relevant Pages

  • Re: ADO.NET and ODBC { CALL ..... } Syntax
    ... > Coonecting to Oracle 8i DB running on Unix ... > OleDB connection with provider as MSDAORA. ... > the same way as it works in ADO arena. ... You should really only do this at design-time, or with a command you will ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.NET and ODBC { CALL ..... } Syntax
    ... > Coonecting to Oracle 8i DB running on Unix ... > OleDB connection with provider as MSDAORA. ... > the same way as it works in ADO arena. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OO4O 2.1 vs MDAC
    ... We're planning to move VB3 using OO4O 2.1 ... Now I must distinguish and list out some of the features that ADO / ... (OLE DB or ODBC) ... Also you don't mention the Oracle version there are differences between ...
    (microsoft.public.data.ado)
  • Re: Data modelling tool - Seeking for recommendations
    ... I tried using ODBC, ADO, and several drivers to import into the ORACLE8 target. ... Your best bet on reverse engineering Oracle would be to extract ... SQL script into file and then reverse engineer the script. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: TADO table open exception
    ... > design time that the data can be loaded to a dbgrid by opening the TADO ... Why are you using ODBC and ADO together? ... Oracle has its own OLE DB provider which AFAICR is ...
    (borland.public.delphi.database.ado)