ODBC Data Provider

• ADO.NET Data Provider for Data Sources that have ODBC support.
• Exists in namespace System.Data.Odbc and assembly System.Data
• Works on Windows and Linux. Should have no problems working on UNIX too.
• Works on Windows via the native Windows odbc32.dll
• Works on Linux via:
  • unixODBC which has commercial support from Easysoft
  • iODBC which has commercial support from OpenLink Software
• List of unixODBC drivers
• List of ODBC Vendors
• ODBC can connect to various databases which has an ODBC driver installed:
  • MySQL
  • PostgreSQL
  • Oracle
  • Interbase
  • Sybase ( via FreeTDS on UNIX)
  • Microsoft SQL Server ( via FreeTDS on UNIX)
  • IBM DB2 Universal Database
  • MS Access (via MDB Tools on UNIX)
• ODBC Provider created by Brian Ritchie.
• Does not support trusted connections

Current Status

• Can Connect on:
  • Windows via native Windows odbc32.dll
  • Linux via:
    • unixODBC's libodbc.so
    • iODBC's libiodbc.so
• Various databases have been tested using their ODBC drivers: MySQL, PostgreSQL, Oracle, IBM DB2, and Microsoft SQL Server
• Can execute non-query commands via ExecuteNonQuery of a OdbcCommand
• Can execute aggreates and retrieve a single row single column result via ExecuteScalar of a OdbcCommand
• Can execute queries via ExecuteReader of a OdbcCommand and retrieve results using an OdbcDataReader
• Can get a DataTable containing schema info via GetSchemaTable() in a OdbcDataReader
• Can Fill a DataTable in a DataSet via an OdbcDataAdapter
• Works in SQL#, but Column names don't show up correctly.
• Bugs with Mono or the data provider should be reported in Mono's Bugzilla here. If you do not have Bugzilla user account, it is free and easy to create one here.

Action Plan

• Fixing bugs
• Testing with other setups

Testing ODBC provider with IBM DB2 Universal Database

• You need a working mono and mcs
• For Unix, you will need unixODBC or iODBC installed
• Have acess to a IBM DB2 Universal Database or you can download from IBM
• Read these web pages about Unix, ODBC, and IBM DB2
  • unixODBC web page about IBM DB2
  • IBM web page about unixODBC and DB2
• The ODBC provider is similar to the IBM DB2 provider.

Testing ODBC provider with MySQL

You can test Mono's ODBC provider System.Data.Odbc with the MySQL ODBC driver MyODBC

• Take a look at OdbcTest.cs in mcs/class/System.Data/Test
• Here is a ConnectionString format if you have a DSN setup:

  "DSN=dataSetName;UID=myuserid;PWD=mypassword"
  

• Here is a ConnectionString format if you do not have a DSN (have not gotten this to work though):

  "DRIVER={MySQL ODBC 3.51 Driver};" +
  "SERVER=localhost;DATABASE=test;" +
  "UID=myuserid;PASSWORD=mypassword;" +
  "OPTION=3";
  
  

• C# Example:

   using System;
   using System.Data;
   using System.Data.Odbc;
   
   public class Test 
   {
      public static void Main(string[] args)
      {
  		// have an ODBC DSN setup named MYSQLDSN
  		// that accesses a MySQL database via
  		// MyODBC driver for ODBC with a
  		// hostname of localhost and database test
         string connectionString = 
            "DSN=MYSQLDSN;" +
            "UID=myuserid;" +
            "PWD=mypassword";
         IDbConnection dbcon;
         dbcon.Open();
         dbcon = new OdbcConnection(connectionString);
         IDbCommand dbcmd = dbcon.CreateCommand();
         // requires a table to be created named employee
         // with columns firstname and lastname
         // such as,
         //        CREATE TABLE employee (
         //           firstname varchar(32),
         //           lastname varchar(32));
         string sql = 
             "SELECT firstname, lastname " +
             "FROM employee";
         dbcmd.CommandText = sql;
         IDataReader reader = dbcmd.ExecuteReader();
         while(reader.Read()) {
              string FirstName = (string) reader["firstname"];
              string LastName = (string) reader["lastname"];
              Console.WriteLine("Name: " + 
                  FirstName + " " + LastName);
         }
         // clean up
         reader.Close();
         reader = null;
         dbcmd.Dispose();
         dbcmd = null;
         dbcon.Close();
         dbcon = null;
      }
   }
  

• Building C# Example:
  • Save the example to a file, such as, TestExample.cs
  • Build on Linux:

    	mcs TestExample.cs -r System.Data.dll
    

  • Build on Windows via Cygwin:

    	mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
    	     TestExample.cs \
    	     -lib:C:/cygwin/home/MyHome/mono/install/lib \
    	     -r System.Data.dll
    

• Running the Example:

  mono TestExample.exe