Connecting to a MySQL database from CG applications.

loocas | 3ds Max,maxscript,Maya,Python,technical | Saturday, August 9th, 2008

I’ve been recently doing some R&D on MySQL databases and connection through Python in Maya as well as Python in Max (through blurPython library), but I couldn’t seem to have found a way to connect to a MySQL database via ODBC. The problem lied in OLE methods as they’re not both much documented in MAXScript reference and they’re tied to the operating system, not Max directly. But thankfully, I bumped into a solution today, out of a blue :)

To save you some time, don’t bother trying to connect to the database (db for short) via the standard example:
DogConn=createOLEObject "ADODB.Connection"
DogConn.Open "driver={SQL Server}; server=localhost; database=test"

output:

-- Runtime error: OLEObject method call failed:
Error Message 1: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
method: open()

This concrete driver is a Microsoft SQL Server driver, so obviously, this doesn’t make much senese to use this method for connecting to a MySQL database. Since there are numerous ways of setting up the connection via different providers or drivers I was lost. But thankfully , Sun provides its own ODBC connectors for various operating systems, including 64bit versions for Windows. So download the one that fits your OS and after installing it, just check what name the driver has assigned in the Data Sources(ODBC) under Administrative Tools. It should read “MySQL ODBC 5.1 Driver”, which is exactly what you need for successful connecting to a MySQL database. Easily enough, from the comprehensive list of example connection strings found at ConnectionStrings.com I was finally able to write this code:

DogConn=createOLEObject "ADODB.Connection"
DogConn.Open "Driver={MySQL ODBC 5.1 Driver};Server=serverName;Database=dbName;User=userName; Password=password"

Even though it returns “undefined”, don’t be alarmed, this is a good thing. So, now you’re good to go and start fully appretiating the whole database purpose and design!

As for dotNET. It’s much easier (as it’s Microsoft’s next “big thing”) to code for and also you’re less likely to bump into similar problems. Again, download the latest .NET connector from Sun and try executing this snippet in Max 9+:

dotNet.loadAssembly "C:\\Program Files (x86)\\MySQL\\MySQL Connector Net 5.2.2\\Binaries\\.NET 2.0\\MySql.Data.dll"
DBConnection = dotNetObject "MySql.Data.MySqlClient.MySqlConnection"
DBConnection.ConnectionString = "Server=192.168.1.14;Database=test;Uid=root;Pwd=root"
DBConnection.open()

dotNET works in a slightly different way. Instead of loading up a provider or service, it loads up an “assembly”, which is a dll, in this case Sun’s .NET connector, and then allows you to work with its methods. So I first loaded the dll up, then fired the dotNetObject function, then I specified the connection string and finally opened up the connection. Then you’re good to go!

Even simplier is the Python way of things, as usual ;) All you need is a MySQLdb module available at SourceForge.net, install it for your version of Python and then try running this code:

import MySQLdb as db
conn = db.connect("serverName","userName","password","databaseName")

Now you’re ready for some serious querying! ;) It really is this simple and that’s why I’m really loving Python. Anyways, these were just a few examples of how to connect to a MySQL database via ODBC, .NET and Python, which is really all you’ll ever need for connecting to MySQL from 3ds Max, Maya or XSI.

1 Comment »

  1. Hello,

    Thank you for this post. I am fairly new to the Maya programming environment and I am trying to make a connection between Maya and a MySQL database using python. I saw your post on that and it seems pretty straight forward. Its just that I am finding it difficult to customize my Maya Python properly so that Maya Python can successfully load the Python DB connector. I am not even sure where to install the DB connector files that I have downloaded from SourceForge. I know this might sound like a stupid question, but I would really appreciate your help.

    Thanks and hope to hear from you soon.

    P.S. I am using Mac OS

    Anas

    Comment by Anas Nader — February 28, 2009 @ 22:10

RSS feed for comments on this post. TrackBack URI

Leave a comment

Powered by WordPress | Theme by Roy Tanck