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.
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.1 If your database (Filemaker, MySQL, PostgreSQL, Excel, whatever) has an ODBC driver, then–in theory–Vectorworks can talk to it through the shim.2
Here’s my superficial, as-much-as-I-needed-to-know understanding of how the moving parts are communicating:
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.
Your Mac’s ODBC manager is hard-wired to go looking for its configuration files in
~/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.
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.
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.
- Go install Xcode and Homebrew
- Set up the database server and an ODBC driver
- Set up a test database
- Try a test query through
- 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
/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.
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
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 email@example.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/ODBCso that Vectorworks knows that we’re using MySQL.
- In those configuration files, we set up a data source named
mysql-vw-testfor 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
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:
What follows is a transcript of what it should look like, if everything is working. You’ll need to type 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:
an ad-hoc connection from within scripts.
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”.4
Start a new document, and go to the menu
Tools > Database > Manage Databases.
Use Data Source Namepulldown, 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):
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
In practice, this approach swaps the time of a small group of developers for that of a large group of users, but hey, excruciating-but-possible is still better than impossible. Story for another day.↩
Short version: Vectorworks is still a 32-bit app, unlike most of the rest of Mac OS X these days. It’s easy to leave out 32-bit code by mistake, since the build tools now default to 64-bit. And Vectorworks can’t run 64-bit code.↩
In Mac Vectorworks 2011, at least, I find this system buggy and question its basic design: both its comprehensibility as an end-user feature, and the potential for confusing data loss in having implemented immediate writes to the database but only periodic polling for reads from the database. Another story for another day.↩