Connecting to MySQL with ODBC on Mono
Originally published at The Pædantic Programmer. Please leave any comments there.
We had a visitor on #mono today who needed help with his homework. It seems that Reggie is happy to have forgotten everything about using ODBC to connect to MySQL. I was curious and feeling helpful, so I figured it out.
Install the MySQL ODBC driver
$ sudo apt-get install libmyodbc
You can also grab the package directly from MySQL
Configure the MySQL ODBC driver
ODBC looks in /etc/odbcinst.ini for driver configuration. In order to let it know about the MySQL ODBC libraries, append the following to your /etc/odbcinst.ini file:
[MySQL] Description = MySQL driver Driver = /usr/lib/odbc/libmyodbc.so Setup = /usr/lib/odbc/libodbcmyS.so CPTimeout = CPReuse = Configure your DSN
If you expect that you will be using this database connection often, you may want to create a short name for it, so you don’t have to enter all of the parameters every time you want to connect. ODBC uses something called DSNs (Data Source Names, if I recall correctly) to make it easier on the user.
If you want to create a DSN, append something like the following to your /etc/odbc.ini file:
[myodbc3] Driver = MySQL Description = MySQL ODBC 3.51 Driver DSN Server = mysql Port = User = testuser Password = password Database = test Option = 3 Socket =
You will also need to update the ODBC Data Sources list near the top of the file to mention the new DSN:
[ODBC Data Sources] myodbc3 = MySQL ODBC 3.51 Driver DSN C♯ ODBC connection example
Here is some example code to get you connected:
using System; using System.Data; using System.Data.Odbc; class ODBCTest { public static void Main(String[] args) { // Connection string using explicit parameters string ConStr = String.Format( "DRIVER={0};SERVER={1};DATABASE={2};UID={3};PWD={4}", "{MySQL}","mysql","test","testuser","password" ); // Connection string using DSN // string ConStr = String.Format("DRIVER={0};DSN={1}", // "{MySQL}","myodbc3"); //Create the connection object var OdbcCon = new OdbcConnection( ConStr ); try { Console.Write("Opening connection... "); //open the database connection if (OdbcCon.State == ConnectionState.Closed) OdbcCon.Open(); Console.WriteLine("connection opened!"); } catch (OdbcException Ex) { Console.WriteLine(Ex.Message); } finally { //close the database connection OdbcCon.Close(); } } }