OleDB vs ODBC: does one of them NOT require driver installation FOR ALL Oracle, MySQL, SQL Server?

Question

I want my application to be able to work with multiple db vendors. In other words, depending on the DB infrastructure of the client, the db schema will be deployed on one of Oracle, MySQL, SQL Server. I am between using ODBC and OleDB, and the following key requirements that must be taken into account for the selection:

• the DB schema must be created from within the application (I was told that ODBC may be problematic in this case, is this true?)

• it is strongly desirable that the end users are not required to install any additional software (i.e. Oracle Instant Client etc). So, the driver should be preferably either:

  1. already bundled with Windows. Does Windows have a generic version of ODBC / OleDB?
  2. be able to be bundled in a way with the application. I.E. in Java, I can bundle the JDBC driver for MySQL as a .jar file with my application. Can this be done by including a .dll file?

• it is strongly desirable that the end users are not required to make any external configuration, such as creating ODBC datasources

Cheers!

Answer 1

Both need the database drivers. If not the ODBC layer does not know how to connect to remote database.

Answer 2

Most ODBC/OLEDB drivers are using a "common language" which still requires some kind of native device driver or "client install" provided by the vendor to properly connect to the database.

What you want to look for is a proper ADO.NET driver, which will have all the required libraries built into it, or it may only require a second DLL to go with it that doesn't not require a "client install". This will also allow for easy use of the Connectin String in your app.config file and all the goodness that ADO.NET provides.

Here are some links to the common ones you need:

• MySQL - MySQL Connector Oracle -
• Oracle Data Provider (ODP) SQL
• Server - Already built in (naturally)
• SQLite - SQL.Data.Sqlite (just in case you want that available)

Answer 3

Well, yes but you need neither ODBC or OLEDB to do this. You can get 100% native ADO.NET providers for SQL Server, MySQL and Oracle from here http://www.datadirect.com/products/net/index.ssp.

Answer 4

You do need to have an appropriate, database specific driver installed.

Once this is installed the connection string will also be, to some degree, database dependent.

Using ADO.NET much database interaction in code can be independent by using the common interfaces (e.g. [IDbCommand][1]) rather than the provider specific subclasses, but a provider specific factory will still be needed.

Even then, SQL dialects and datatypes have significant variation.

Expect to need to be very familiar with building your own abstracts, inversion of control (IoC) and debugging each different database. The latter would strongly indicate debugging actively across multiple database platforms from the project start to avoid sudden need for significant porting effort.