ODBC Compatibility - MySQL

Required knowledge before using ODBC in Vectorworks:

Vectorworks 2011 introduced the ability to use an ODBC (Open Database Connectivity) interface to communicate with external databases. To use this functionality; ODBC drivers and an ODBC manager are required.

Windows:

There is an integrated ODBC manager and the ‘Microsoft ODBC Driver Pack’ is available with the OS. To use databases like FileMaker Pro, MySQL, Oracle, FoxPro, etc., additional driver packages must be installed. The ODBC manager is available in Control Panel\Administrative Tools\ and it is called ‘Data Sources (ODBC)’. Take into consideration that in 64bit Windows there are two applications – one 32bit and one 64bit. They work with different ODBC drivers. The ‘Microsoft ODBC Driver Pack’ is not available in 64bit version and by default the list of drivers is empty. So, use the 32bit version of the ODBC manager for common purposes.

The 32bit version is located at \Windows\System32\odbcad32.exe and the 64bit version is located at \Windows\System32\odbcad32.exe.

Mac OSX:

There is no integrated ODBC manager, nor drivers. Additional packages must be installed. Here is an article about ODBC drivers and managers: http://kbase.vectorworks.net/questions/858/ODBC+Driver+Information

Before you start using Vectorworks a DSNs (Data Source Name) must be created. It is the name that applications use to request a connection to an ODBC Data Source. In other words, it is a symbolic name that represents the ODBC connection. It stores the connection details like database name, directory, database driver, UserID, password, etc. when making a connection to the ODBC.

There are various types of ODBC Data Source Types such as System DSN, User DSN, FIle DSN. The differences between is in the store location and user privileges. Some drivers may not support all types of DSN, so be careful when create one.

On Mac some keywords Establishing ODBC connection in Vectorworks consists of three steps:

1) Connect to DSN: Tools > Database > Manage Databases. On Macintosh use username ‘admin’ with no password if you don’t have one.

2) Create Record Format Database Connection: Tools > Database > Create Record Format Connection.

3) Create Object Database Connection: Tools > Database > Object Connection.

Windows - Using ODBC to connect to MySQL files:

MySQL server and MySQL driver are required. For purposes of this article a sample database ‘test’ was created with one table ‘testdatabase’:

odbc mysql win1.png

Start ODBC Administrator:

- 32bit version: \Windows\SysWOW64\odbcad32.exe;

- 64bit versiom: \Windows\System32\odbcad32.exe.

On the ‘System DSN’ tab click on ‘Add…’ and a dialog ‘Create New Data Source’ will open. Choose ‘MySQL ODBC 5.1 Driver’ or other version of MySQL Driver and click ‘Finish’. A dialog will open and enter Data Source Name, e.g. ‘MySQL Test’, enter the IP of the server. If the server is localhost, enter 127.0.0.1 and port 3306. Select the database ‘test’ and test the connection.

If the test succeeded close the dialog and ODBC Administrator with OK. Start Vectorworks and go to Tools > Database > Manage Databases. Click on Connect button and choose the DSN ‘MySQL Test’ from the popup. Close the dialog with OK and the connection is established. Mark a column as a key and close the dialog with OK.

odbc mysql win3.png

Create the following record format and map its fields to the database columns:

odbc mysql win4.png

Close the dialog with OK and draw a rectangle. Go to data pane in the OIP and attach record Space to it. Go to Tools > Database > Object Connection and select a row from the table:

odbc mysql win5.png

Close the dialog with OK and execute Tools > Database > Update Vectorworks Document and look at the data pane. The record instance is updated. Use Tools > Database > Update External Database to update data in the external database.

odbc mysql win6.png

Mac OSX - Using ODBC to connect to MySQL files:

MySQL server and MySQL driver are required. For the purposes of this article MySQL server is running on remote machine with address 192.168.180.45 in the network. A sample database ‘test’ was created with one table ‘testdatabase’:

odbc mysql mac1.png

If you need to install MySQL Driver on MacOS download and install http://mirrors.cogentco.com/pub/mysql/Connector-ODBC/5.1/mysql-connector-odbc-5.1.7-osx10.6-x86-32bit.dmg. Start ODBC Manager and go to Drivers tab. If there isn't MySQL ODBC 5.1 Driver click on 'Add...' and type driver name 'MySQL ODBC Driver 5.1 Driver', type in 'Driver File' field /usr/local/lib/libmyodbc5.so and for 'Setup File' /usr/local/lib/libmyodbc3S.so. Close the dialog with OK.

odbc mysql mac2.png

To create a DSN start ODBC Manager and on System DSN click 'Add...', choose MySQL ODBC 5.1 Driver and click OK. If 'Connector/ODBC' dialog appear type data source name, e.g. 'MySQL Test', server 192.168.180.45 in our case, 127.0.0.1 for localhost, user and password and the database you are going to use. In our case 'test'. Test the connection and if it is successful close the dialog with OK.

odbc mysql mac3.png

If a standard dialog appears type a name for the datasource, e.g. 'MySQL Test' and add the following keywords:

SERVER= (in our case 192.168.180.45, 127.0.0.1 for localhost)

PORT=3306

DATABASE= (in our case 'test')

odbc mysql mac4.png

Close the dialog with OK and start Vectorworks. Go to Tools > Database > Manage Databases. Click on Connect button and choose the DSN ‘MySQL Test’ from the popup, enter username and password. Close the dialog with OK and the connection is established. Mark a column as a key and close the dialog with OK.

odbc mysql mac5.png

Create the following record format and map its fields to the database columns:

odbc mysql mac6.png

Close the dialog with OK and draw a rectangle. Go to data pane in the OIP and attach record Space to it. Go to Tools > Database > Object Connection and select a row from the table:

odbc mysql mac7.png