Vectorworks, MySQL, ODBC, your Mac and You

Here’s some guidance in getting Vectorworks to talk to MySQL on a Mac. Brace yourself: this will probably hurt a bit, and involve more than a few acronyms.

Should I care?

Getting and setting key-value data is basic to any interesting idea in using a computer to help manage the firehose of data that is an architectural project. Vectorworks has had the ability to assign key-value data to any object since forever, using Records.

But getting at this data is finicky.

Worse–like so much of Vectorworks–it’s very one-document-one-database. We run teams of six architects on some projects, and have to split up our drawings into multiple files. I’ve tried all sorts of horrible workgroup-reference hacks involving worksheets or layer referencing to collect this data–examples are sheet numbers, and door & room finish scheduling information–with varying degrees of success, workflow compromise and hair-pulling.

It would be better for Vectorworks to store the data in an external project database. Any document in the project can get and set to the external database. Other processes (web servers, cron jobs, Ruby/Python scripts, you name it) can also get and set to the database without having to resort to brittle Rube-Goldbergesque automations of the Vectorworks UI using Applescript and/or Vectorscript.

Since version 16 (aka Vectorworks 2011), Vectorworks has had the ability to talk to such external databases through ODBC. I’m sure ODBC is lovely if you’re on Windows, where it originated. But it’s a horrible bag of hurt on a Mac.

ODBwhat?

ODBC is a shim standard that’s supposed to abstract away any need for application developers to care about the idiosyncracies of talking to any particular database server.¹ If your database (Filemaker, MySQL, PostgreSQL, Excel, whatever) has an ODBC driver, then–in theory–Vectorworks can talk to it through the shim.²

Here’s my superficial, as-much-as-I-needed-to-know understanding of how the moving parts are communicating:

1. Vectorworks is hard-wired to talk to your Mac’s built-in ODBC manager. On Mac OS 10.6 and 10.7, this is iODBC and gets installed as part of the OS.

2. Your Mac’s ODBC manager is hard-wired to go looking for its configuration files in /Library/ODBC/ and ~/Library/ODBC/, to figure out what data sources are available and how to get at them. A data source is, more or less, the name of a driver saying what database server program to use, the name of the database file itself, and the necessary addresses and credentials for finding and connecting to it.

3. The ODBC manager translates Vectorworks’s requests to get and set data from generic ODBCese into MySQLese on the fly, using the driver we told it about in Step 2.

4. The ODBC driver pushes the request and pulls the response through a socket or TCP port to the running MySQL process. In our demo, we’ll be using a MySQL server running on our local machine. Using a TCP port would mean that the MySQL server could be anywhere on your local network or the internet.

These points are numbered in red on this diagram; we’ll need to build the parts shown in yellow:

Getting down to work

Here are the steps we’ll follow, finishing with Vectorworks being able to connect to our database. Once we’re there, you should be able to start using Nemetschek’s documentation and play around with the connection on your own.

1. Go install Xcode and Homebrew
2. Set up the database server and an ODBC driver
3. Set up a test database
4. Try a test query through iodbctest
5. Connect from within Vectorworks to the MySQL data source

1. Go install Xcode and Homebrew

You’ll need a working C/C++ compiler. Macs don’t have them installed by default, but Xcode from the Mac App Store is just a few clicks and a hefty download away.

Homebrew is system that takes much of the pain away from installing Unix stuff (like MySQL) on your Mac. Highly recommended. You’ll need it to keep the steps below from devolving into a terrible, uninteresting discussion about how to compile programs from scratch.

2. Set up the database server and an ODBC driver

Open /Applications/Utilities/Terminal.app and type in this line below:

brew install mysql --universal

Follow the instructions you see on screen for getting the MySQL server process up and listening on your machine.

Next, type:

brew install mysql-connector-odbc --universal

This installs the ODBC driver.

Everything is living under the directory /usr/local, nicely isolated from the rest of the system. Make sure to add --universal to each line. If not, you’ll get strange-looking errors when you go to connect in Vectorworks.³

3. Set up a test database

I’ve put up a project at Github to help you set up a test database and the ODBC configuration. In Terminal.app, cd yourself to ~/Desktop or wherever you’d like to keep this, and type each line below:

git clone git@github.com:rgm/vectorworks-mysql.git
cd vectorworks-mysql
script/bootstrap

You’ll see some log output. What just happened:

• There is now a sample database in MySQL with the same name as your Mac OS X username.
• That database has a couple of tables of sample data in it, so we have something to see when this all works.
• Configuration files got copied to ~/Library/ODBC so that Vectorworks knows that we’re using MySQL.
• In those configuration files, we set up a data source named mysql-vw-test for Vectorworks to look at, pointed at the sample database.

Longer-term, you’ll probably want a tool like Navicat to poke around the database, unless you really like command line interfaces.

4. Try a test query through iodbctest

This step is optional, but it does demonstrate how something that isn’t Vectorworks can be talking to the database at the same time. iodbctest is a command-line utility to test that the drivers are working correctly.

Again, from Terminal.app, type:

iodbctest

What follows is a transcript of what it should look like, if everything is working. You’ll need to type the ?, the DSN=mysql-vw-test, and the select * from sheets; when you see the prompts.

$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list): ?

DSN                              | Driver
-----------------------------------------------------------
mysql-vw-test                    | mysql-odbc-connector

Enter ODBC connect string (? shows list): DSN=mysql-vw-test
Driver: 05.01.0011 (libmyodbc5.so)

SQL>select * from sheets;

id         |sheet_number   |sheet_name
-----------+---------------+-------------------------------
1          |A101           |L1 Plan
2          |A401           |L1 Reflected Ceiling Plan
3          |A102           |L2 Plan
4          |A201           |Sections
5          |A301           |Elevations

 result set 1 returned 5 rows.


SQL>quit

Have a nice day.

This step is useful if you’re trying to figure out if a connection error is happening in your Vectorworks configuration or your setup of ODBC.

5. Connect from within Vectorworks to the MySQL data source.

The moment of truth. There are two sorts of connection you can make:

1. an ad-hoc connection from within scripts.

2. a persistent connection at the document level.

Connecting from a script is the more flexible system: executing arbitrary SQL queries from within a Vectorscript. Sky’s the limit.

I’ve supplied a test Vectorscript in the Github project directory. Go to the menu Tools > Scripts > Run VectorScript... and select odbc_test.vss from wherever you cloned the Github project.

Success is getting a few alert dialogs that look like this:

You can also establish a connection that stays with the document. It’s meant for tying in Records so that changes to the record data are “automatically” synchronized with the external database.

I won’t go too much into how this works, since you can read more by going to the menu Help > Vectorworks Help... and searching for “ODBC”.⁴

1. Start a new document, and go to the menu Tools > Database > Manage Databases.

2. Hit the Connect... button.

3. Under the Use Data Source Name pulldown, select mysql-vw-test, enter root as the ‘User Name’ and leave ‘Password’ blank.

The connection dialog should look like this before you hit “OK”:

A successful connection is going to look like this (notice Vectorworks has used ODBC to figure out the table schema):

C’est tout

Congratulations. We’re done (but hopefully you’re not). Getting this working at all used to be the deep end. Now it isn’t. Go do something cool with your new arbitrary key-value pair assigning powers.

Wed 20 Jun 2012