Tags: mysql

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();
    }
  }
}

Well, that was an eventful day!

Originally published at The Pædantic Programmer. Please leave any comments there.

*whew* I did a bunch of things yesterday. We took our kindergärtner to her first Friday at her new school (and were about 10 minutes tardy. oops). We then took our toddler to a nearby playground with swings and slides and let her expend some energy. After she had been sufficiently exercised, we walked back home, stopping at a coffee shop on the way. The baristo (you call male baristas “baristos,” right? :) ) recognized my MC Frontalot shirt and asked whether I had caught him the previous weekend at PAX. Unfortunately, I have not attended PAX since 2006, but I *did* purchase the tee directly from The Front himself ;)

When we got home, I worked a bit on an English Language parser implementation and then went to the University of Washington to meet with Emily Bender about getting in to the Professional Master’s program in Computational Linguistics. It all looks good, and I even got the good news that the GRE is no longer required!

After the meeting, I headed home and poked at the parser for a little while longer. I then picked Scarlet up from after-school care and brought her home. I then hopped in the car and drove toward Bellevue to meet up with Monty while he’s in town. I over-estimated the amount of time traffic would steal on my way to Bellevue, and had an extra hour to blow. So I dropped by building 41 and shot the IronPython bull with Dino. It turns out he’s got an android phone, too. I told him it was possible to put a debian chroot on it and that he should even be able to ‘apt-get install ironpython’ to his phone soon ;) We talked briefly about the CodePlex Foundation and Sam Ramji’s departure from The Evil Empire. Dino seems skeptical about the project. I don’t have enough information to have much of an opinion. However, it sounds like some folks I trust are involved, so I’m hopeful.

I left MS just in time to make it to the wrong address at the specified time. My phone had just enough juice to call Monty to get the right address and then use the navigation system to find my way there. I wasn’t able to make reservations at the place we intended to go for dinner until 8:15, so we went to the Barnes & Noble for a bit. They only had one NLP book in stock and the examples are all in Python. I should learn that language one of these days… As we were leaving the Pacific Place, Monty mentioned to me that he is on the advisory board for the CodePlex Foundation, and that they have been responsive enough to his input that they changed the Mission statement, at his recommendation, just one day before the Foundation was publicized. He feels that this is a very good direction for Microsoft to be heading.

My brother Chris was kind enough to watch the kids while we went out to dinner. Quick note: he recently graduated from UW with a BA in Electrical Engineering and is looking for work using his acquired knowledge, in case anyone needs one of those ;)

We met up with my wife, Hannah and our friends, Mike & Cynthia at our place. Monty graciously avoided mentioning the terrible state in which our apartment has recently found itself. The kids were super cute and polite and said hi/bye.

Over dinner we discussed building an android app (Monty has one, too ;) ) to automate the process of creating bounties for apps and getting folks to implement them. We also talked about MySQL and MariaDB, of course. Hannah and I recalled my time working for MySQL, Inc. on the MaxDB project and some subtle cultural differences we noticed while traveling. It was interesting getting the inside scoop about the Sun acquisition and some of the recent goings-on in the MySQL/Sun/Oracle world. I wasn’t aware, for instance, that the EU is balking on the merger because of monopoly concerns.

Now that I write blog software, I never post anything...

Oh well. Anyway...

I went to LinuxFest Northwest yesterday with fuzzyredmittens and scarletwilde. I hung out at the Mono Project table and gave away some books and t-shirts that Miguel sent over for the occasion. Coincidentally, the Mono Project booth was set up right next to the MySQL booth, which was manned by none other than Gerry and a couple of his kiddos. Gerry and I manned the MySQL booth together in 2006, and it was good to be sitting next to him again this year.

Tim Maher, retired president for life of the Seattle Perl Users' Group and recipient of the White Camel, was selling copies of his Minimal Perl in the exhibition hall. I bought a copy and Tim even signed it for me. Whee! This is the first book that I've got an acknowledgement in, so I had to have a copy.

Quite a few fun folks stopped by the booth including Paul Bartell, brad, Allison, chromatic and lots of folks with questions, comments, rants and concerns about the Mono project.

We've got a shower for baby Zelda today, so we're not going to be attending the second day of the conference. I hope I'm forgiven... :)

Although Hannah offered to let me sleep in this morning, I couldn't fall back asleep after about 5:15. So I'm writing down some the thoughts that kept me up...

1) Moose helps perl devs create classes that are nearly CLS compliant. It would be nice to either patch Moose to fully implement CLS compliant classes or create a CLI:: namespace with classes that are CLS compliant. OMG! Strongly typed Perl!!!

2) It would be nice to target Perl to the CIL virtual machine. What is the process of adding another architecture to Perl's vast target suite?

3) Module::Starter::Moose, Module::Starter::DotNet, Class::Mop, Class::CLS ECMA335::*

blah blah blah :)