So, I’ve been working a lot with Visual Studio, and although it doesn’t do everything perfectly, I tend to like it. One of the things I tend to like about it is its ability to group together lots of seemingly disparate technologies into a single solution file so that you can have all the information about your “solution” right at your fingertips.

Today, in preparation for porting our technology to MySQL, I decided to try to attempt to make Visual Studio database projects and MySQL work nicely together. Believe it or not, this is insanely hard without purchasing an OLE DB provider, and I couldn’t find a single site through Google that told me how to get Visual Studio database projects working with MySQL. Even MySQL’s Visual Studio plugin doesn’t actually support everything you think it should (you can’t run scripts for example, the major piece of the puzzle I wanted). So what’s a programmer to do? In this case, I found a kind of solution that doesn’t give you all of the features of the MySql plugin in the Server Explorer, but it does give you the run feature, which I feel is much more important.

For those of you that are curious, here’s the solution. First, download and install the MySQL Connector/ODBC libraries. In my case, I used the 5.0 libraries because we’re developing against beta software anyway (the 5.1 release of MySQL), but I have a feeling the 3.51 release will work just as well. Next, open your server explorer and add a data connection. You’ll be presented with this window (click for the full image):

Click “Change” next to the data source and select “Microsoft ODBC Data Source” and the add connection window will change to this:

Now, you can go through the trouble of attempting to make system or user data sources here, but you’re better off just making a connection string, so select “Use Connection String” and type in your ODBC connection string. For me, this was similar to:

Driver={MySQL Connector/ODBC v5};server=localhost;database=Database;

and add the user name and password below (note that if you’re using the older version, I believe the driver needs to be set to “mySQL ODBC 3.51 Driver”, though I’m not actually sure). Test the connection, and you’re all set. Multi-line scripts should now run just fine and you’re all set to develop as if you were working on SQL Server.

Edit: Although this works for executing multi-statement queries, it doesn’t work for doing things like creating procedures. MySQL chokes on the semi-colon being used inside multi-statement procedures, and there’s no way of using the delimiter command to change the end of statement delimiter. In my opinion, this is a huge problem, and the whole concept of requiring that you change delimiters feels like a hack. I’m not sure if there’s a way around this, but you can be sure I’ll write about it if there is.