Database

The Vectorworks Design Series products include the ability to communicate with external databases according to Open Database Connectivity (ODBC) standard. Using ODBC, a set of data can be used to automatically update the records attached to objects. Similarly, Vectorworks objects can send object data to a database, updating it. To successfully use ODBC in Vectorworks Design Series products, a database management system is required, and steps need to be followed to set up the connection and manage the data flow.

1. Configure and set up data in the database program of your choice. Database connectivity in Vectorworks software is supported for the following database systems:

Database and/or operating systems typically include a separate ODBC Administrator or Manager application, which defines drivers and data sources. Database drivers may need to be installed. See “ODBC Driver Information” on page 1810.

The installation, setup, and usage of database systems is beyond the scope of this help system and is not described here. Please consult the documentation related to your operating system or database software for information.

* SQLite database files do not require an ODBC manager or server. For information on SQLite, visit http://www.sqlite.org

2. Set up the connection between Vectorworks software and the database system. A “key” column with unique values is designated, to establish and maintain the communication between the database system and Vectorworks record formats.

3. Identify the object instances of each type of object to be connected, and define the link between the database and the record formats of the object types.

4. When connections have been established, determine the settings to update the Vectorworks record formats from the database, and to update the database from the Vectorworks object records. The two-way communication process can be automated.

Database-Vectorworks Communication

The values in a database link to the record fields of an object instance through a series of connections made between the database software and the Vectorworks software (Vectorworks Design Series required).

One or more columns of the database table must have unique values. These Key columns are used by the Vectorworks program to uniquely identify the database rows.

Database setup consists of identifying the key column(s) and setting up each type of connection, and then specifying update settings for the flow of data.

Database Setup

In Vectorworks Design Series products, open the ODBC Data Source Administrator (Windows) or ODBC Manager (Mac). The standard drivers may already be installed for the major database providers; verify this on the Drivers tab. More information on drivers is available in “ODBC Driver Information” on page 1810. Add the database file, providing a name for it. This makes the database available through the ODBC administrator.

SQLite database files do not require an ODBC manager, so setting up the database drivers is not necessary.

Database Connection

In the Vectorworks file, connect the database to the file (Vectorworks Design Series required), and then select a Key identifier for each data source.

1. Open the Vectorworks drawing. Database connection information is saved in the Vectorworks file.

The Manage Databases dialog box opens. Through this dialog box, manage available databases and connect the desired database or databases to the current file.

3. Since the connection has not yet been made, the Available Databases list is empty. Click Connect.

Parameter	Description
Use Data Source Name	Connect to a database source that was previously added to the ODBC manager or ODBC administrator; click Refresh to update the choices
Use Connection String	On Windows, this option allows temporary creation of data sources from files, or can select the known data source name similarly to the Use Data Source Name option. Click Build and select the database from the Machine Data Source tab. The connection string cannot be built from a specified source on the Mac.
Use SQLite File	Click Browse to select and connect to an SQLite database file; this type of database connection is indicated as [sqlite] in database-related dialog boxes. If no SQLite file exists, browse to an appropriate location and enter a file name to automatically create an empty SQLite database file.
User Name/Password	If a database connection requires a user name/password, enter the information

4. Either select the SQLite file, or the named database added to the ODBC manager/administrator earlier, enter a User Name and Password if required, and click OK.

5. The selected database is added to the list of available databases in the Manage Databases dialog box. The tables associated with the Layout database are listed on the left, with the database table columns listed on the right.

6. Select one of the available database tables; its rows display on the right. Select one of the rows to serve as the Key identifier by clicking in the Use as Key column for that row. The Key column uniquely identifies the data rows in the table. By default, this is one of the columns with unique data, used as a row identifier.

The ability to select the Key depends on the database driver. If needed, click Show Table to display the table’s contents and help select an appropriate Key.

7. Select the Key identifier for any other database tables. Each table must have at least one column selected as Use as Key.

8. Click Connect if there are other databases to connect, and identify the Key for each additional table.

9. Once the database tables have been selected and connected, and the Key identifier(s) set for each table, click OK.

If the connection to the database becomes out of date, select the database and click Renew Connection to re-establish the database connection. Table and column information data is updated.

If there are problems connecting to the database, an error message provides troubleshooting information. Click Details to open a log file in the default text editor application. The log file provides further information to help diagnose connection errors.

Record Format Database Connection

Each field of the record format needs to be mapped to the appropriate database table column (Vectorworks Design Series required). The record connections can be saved in an xml file and imported into another file.

1. Select Tools > Database > Record Format Connection. Alternatively, select a record format in the Data tab of the Object Info palette for a selected object with the desired record attached, and then click Record Format Database Connection.

The Record Format Database Connection dialog box opens, showing available record formats and parametric objects in the file. Each record format can be linked to a data source that has already been connected to the file.

Parameter	Description
Record Formats in the Document	Lists the record formats available in the Vectorworks file, as well as the databases and database tables to which they are connected
New	Creates a new record format as described in “Creating Record Formats” on page 258
Edit	Opens the Edit Record Format dialog box, to link the record format to the specified database and table
Delete	Deletes the selected record format from the file
Export as Database Table	Automatically creates database table rows from the selected record format; see “Automatically Creating a Database Table from a Record Format” on page 1805
Manage Databases	Opens the Manage Databases dialog box, to connect to available databases; see “Database Connection” on page 1789
Import Connections	Imports the connection settings from an external .xml file, overriding any existing database connections. This allows the connections specified from another file to be used in this file.
Export Connections	Exports the current connection settings to an .xml file

The Edit Record Format dialog box opens, with additional database controls available due to the presence of the database connection.

3. Select Database Connection to link the record format fields to the selected database and table. This area only displays for record formats when a database is connected in the file.

If needed, verify or change the connected database by clicking Manage Databases, or check the table selected in Table Name to view the table contents.

The Connection Options dialog box opens. Select the actions to occur in the database table when the connected record format, or object with the record attached, is edited. (These changes occur at the time of database update set in “Specifying Update Settings” on page 1809; alerts inform you of any changes to the database.)

Parameter	Description
Add new rows in Database Table when creating Records of this Format	When a new record format is created, automatically adds a database table row to the connected database
Delete the row in the Database Table when removing Records of this Format	When a record format is disconnected from an object, or an object with a connected record format is deleted, automatically deletes the row that was connected to the object in the database table

6. For each record field, assign a database table from the selected database. Select a record field and click Edit.

7. Select the associated table row from the database in DB Table Column, and select the method of database communication in DB Link Type:

• Read/Write: Allows data to move in both directions between the database and the Vectorworks file

• Read Only: Allows database information to be read into the Vectorworks file, but does not allow record data to modify the database

• Write Only: Allows the Vectorworks file data to modify the database, but does not allow the database information to modify the Vectorworks record field

8. Click OK to return to the Edit Record Format dialog box. The dialog box updates to display the associated table data and link type.

Click in a DB Read/Write column to easily change the link type for the selected row.

9. Once the data has been linked for each record field, click OK. The fields in the record format have now been linked to the table data in the specified database, and the link type has been defined. On the Data tab of the Object Info palette, connected record formats display with the name of the connected database and its table.

Object Database Connection

The final step in setting up a database connection (Vectorworks Design Series required) is to connect object instances to the database. Once connected, the connection can be edited. The connection can also be removed.

Single Object Connection

3. Select Tools > Database > Object Connection. Alternatively, click Object Database Connection from the Data tab of the Object Info palette.

The Object Database Connection dialog box opens. If a row was previously selected for the object, it appears highlighted and bolded.

Parameter	Description
Available Records	Lists the record formats available for the selected object
Database Connection	Opens the Record Formats Database Connection dialog box, to connect a record format to a database table
Add New Database Table Row	Inserts a new row in the database table, and associates it with the object
Use Selected Database Table Row	Allows the database table row from the linked database table list to be selected, associating an existing row with the object
Database Table Rows	Selects a table row to associate with the object; this row provides data for the selected object or receives data from the selected object

4. Click Use Selected Database Table Row, and then select the appropriate row for the selected object.

6. The record fields are populated from the database table for the record instance attached to the object.

Multiple Object Connection

When there are multiple similar objects that should be identically connected to database tables through attached record formats, it would be tedious to connect each one. The connection for multiple objects can be made at one time (Vectorworks Design Series required).

1. Select several objects of the same type; for example, select several space objects.

2. Select Tools > Database > Object Connection. Alternatively, click Object Database Connection from the Data tab of the Object Info palette.

The Multiple Objects Database Connection dialog box opens. By selecting a format field and selecting search filter criteria, the table rows associated can be searched.

Parameter	Description
Available Records	Lists the record formats available for the selected objects (only record formats that are common to the selected objects display)
Database Connection	Opens the Record Formats Database Connection dialog box, to connect a record format to a database table
Add New Database Table Rows	Inserts a new row in the database table for each selected object, and associates each row with each object
Find Row by Value (exclude objects with existing object connection)	Only the format fields connected to a database are listed. Selects a format field, and uses the value from the selected objects that are not already connected to a database, to search the database table for a particular row. (Once the row is found, the Key identifier is used to link the table row to the record fields.)
Find Row by Value (for all objects)	Only the format fields connected to a database are listed. Selects a format field, and uses the value from the selected objects to search the database table for a particular row. (Once the row is found, the Key identifier is used to link the table row to the record fields.)
Show Database Table	Opens the Database Table dialog box, to view the database table associated with the record format and help determine which column to use for the search criteria
When this dialog is dismissed	Determines the selection status of resolved objects, or of unresolved objects that may require further attention to connect them. Select do not change selection to make no change to the number of selected objects.

3. Select a Format Field, and then click in the Find by Value column to select the field as a search filter.

4. On the right, the status of resolved objects indicates how many of the selected objects had data found in the database tables based on this search. When objects are resolved, their record field data is properly connected to the database table.

5. Click OK. For each resolved object, the record fields are populated from the database table for the record instance attached to the objects.

Resolved objects are connected to the corresponding rows using the Key identifier, and then each row is associated with the corresponding object.

Editing Object Connections

The read/write queries of an object connection can be viewed and edited for one or several selected objects (Vectorworks Design Series required).

1. Select one or more objects. The objects should already be connected to a database.

The Edit Object Connection dialog box opens. The reading and writing SQL queries for the selected object(s) display and can be edited, either individually for each selected object, or for all the objects at once.

Parameter	Description
Available Records	Lists the record formats available for the selected objects (when multiple objects are selected, only record formats that are common to the selected objects display)
Modify selected objects individually	Edits the reading and writing SQL queries of each selected object individually, by scrolling through each one
Modify selected objects in bulk	Edits the reading and writing SQL queries of all the selected objects at one time using record name identifiers; scrolling through the selected objects is not necessary
Reading Query	Shows the reading SQL query for the selected object(s)
Protocol	Reading SQL queries consist of the following rows: • SELECT: indicates the column names, in brackets separated by commas Example: [id],[room name],[area] • FROM: indicates the name of the database and table, in brackets separated by periods Example: [SpaceInfo].[dbo].[Spaces] • WHERE: indicates the name and value of the record format, with the record format name in brackets and the value (or identifier in angle brackets) separated by an equal sign Example: [area]=30 or [area]=<area> When editing a single query that applies to multiple selected objects (Modify selected objects in bulk is selected), use an identifier within angle brackets for the WHERE record field name, instead of the actual value. Using an identifier in angle brackets allows the query to proceed for all selected objects regardless of each object’s actual value.
Test Query	Tests the current reading SQL query to see if it is valid; this is useful for verifying queries before committing to the changes. An alert indicates whether the test query was successful or invalid
Reset	If the reading SQL query has been modified, returns the query to its original settings
Writing Query	Shows the writing SQL query for the selected object(s)
Protocol	Writing SQL queries consist of the following rows: • UPDATE: indicates the name of the database and table, in brackets separated by periods Example: [SpaceInfo].[dbo].[Spaces] • SET: indicates the name and value of the table column, with the column name in brackets and the value (or record format field name in angle brackets) separated by an equal sign Example: [Room Name]=Kitchen or [Room Name]=<name> • WHERE: indicates the name and value of a search condition, such as column name and value, with the search item name in brackets and the value (or record format field name in angle brackets) separated by an equal sign Example: [id]=1 or [id]=<identifier> When editing a single query that applies to multiple selected objects (Modify selected objects in bulk is selected), use a record format field name within angle brackets for the SET and/or WHERE value, instead of the actual value. Using a record format field name in angle brackets allows the query to proceed for all selected objects regardless of each object’s actual value.
Test Query	Tests the current writing SQL query to see if it is valid; this is useful for verifying queries before committing to the changes. An alert indicates whether the test query was successful or invalid.
Reset	If the writing SQL query has been modified, returns the query to its original settings
< or >	When multiple objects are selected with the Modify selected objects individually option enabled, scrolls among the objects to display each object’s queries for editing
Show Object	Displays the current object in the drawing window for identification

If there are validation problems, an error message provides information. The cursor is automatically placed in the field where the problem exists to help identify the problem.

Removing Object Connections

The connection between an object and a database can be removed (Vectorworks Design Series required).

1. Select one or more objects. The objects should already be connected to a database.

Automating Database Connection Workflows

In the Vectorworks Design Series, the functionality for connecting to a database and then establishing object/record format database linking contains built-in automation features to make the job easier. While it is still necessary to know how to accomplish the steps manually, dynamic updating between the Vectorworks file and the connected database allows record formats to be automatically created from a database table, and database tables to be automatically created from record formats. Connections are automatically established, and remain up to date.

Automatically Creating Record Formats from a Database Table

Record formats can be created from database tables (Vectorworks Design Series required). The imported tables are automatically linked to the new record formats.

Parameter	Description
Database Table Name	Displays the name of the selected database
New Record Format Name	Specifies the name of the new record format to create; the suggested name is based on the database table name, but it can be changed
New Record Format Fields connection	Lists the database columns and the corresponding record field and type; database fields with a check mark in the left column become record fields in the Vectorworks file
Selection	Specifies the parameters and link type for the selected record field to be created; the Record Field Name, Record Field Type, and Database Link Type are suggested based on the column information, but they can be changed

3. Select the columns from the database that will become record fields in the new record format by placing a check mark in the left column. For each selected column, specify the record field name, type, and connection type. For more information on connection types, see “Record Format Database Connection” on page 1792.

Each selected table column in the database table becomes a record field in the new record format. The record fields are automatically connected to the database according to the connection type specified.

Automatically Creating a Database Table from a Record Format

Database tables can be created from record formats (Vectorworks Design Series required). These steps quickly create a database table and link objects to it:

• Select the record format and create the database rows as described in the following steps.

• Connect the objects with that record format to the database table as described in the following steps. The new tables are automatically linked to the record formats.

The Record Format Database Connection dialog box opens, listing the record formats available in the current file.

Parameter	Description
Format Name	Displays the name of the selected record format
Database	Lists the databases connected to the current file; select the database to receive the new table
New Database Table Name	Specifies the name of the new data table to create; the suggested name is based on the record format name, but it can be changed
New Database Table Columns connection to Record Format Fields	Lists the columns and rows, based on the record format fields, that will become database table columns; the table column names are based on the record fields, but they can be changed in Selection. Remove any record format rows for data that does not need to be connected to a database.
Key	Selects which field from the record format is used to match the object records to the particular rows in the database. A key must be selected.
Add	Adds table rows; select each added row and specify its properties in Selection. This action does not affect the existing record format in the file.
Remove	Removes selected table row(s), when one or more record formats does not need to be used as the basis for a database table column. This action does not affect the existing record format in the file.
Selection	Specifies the parameters for the selected table column to be created; the Database Table Column Name, Database Table Column Type, Database Link Type, Format Field Name, and Format Field Type are suggested based on the record format information, but they can be changed. A column can be created in the database table, but not be connected to a record format field, by selecting <not connected> in the Format Field Name list.

3. Specify the parameters of the database table columns to be created. The columns will be created in the order listed; to change the order of a database table column, click and drag within the # column.

4. Click Close to return to the Export as Database Table dialog box and make any further necessary table column adjustments.

6. The database rows are currently empty; the objects in the drawing need to be connected to the table to fill in the data. In this example, the plant record database table was created, so plants in the drawing need to be connected. Select all plants in the drawing.

Use the Select Similar tool to easily select all objects of a particular type that should be connected to the database.

Since several objects were selected, the Multiple Objects Database Connection dialog box opens.

8. Select the applicable record from the Available Records list, and then select the Add New Database Table Rows option.

10. To verify that the database rows exist and have been populated with data, select Tools > Database > Manage Databases. The database rows have been created for the selected record format.

A dialog box opens, showing the database table for the associated record format.

12. The objects with the record are connected to the rows of the database table. The table is filled with data from the record formats of the objects in the drawing.

Specifying Update Settings

Setting Update Frequency

Database or Vectorworks file updates can be executed automatically or manually (Vectorworks Design Series required). The update settings control the frequency of automatic updates, or only permit manual updates.

Parameter	Description
Update database	Sets the frequency of updates from the Vectorworks file to the database
Manually	The database is only updated by selecting the Update External Database command
Every record change	Updates the database each time a record field value changes in Record Info
Every __ minutes	Updates the database after a set number of minutes
Update Vectorworks document	Sets the frequency of updates of the Vectorworks file from the database
Manually	The Vectorworks file is only updated by selecting the Update Vectorworks Document command
Every __ minutes	Updates the file after a set number of minutes

Manually Updating the Database

If the record fields have been changed for objects with linked fields to a database table, and the link type for the field is set to Read/Write or Write Only, the drawing data can update the database table data.

Manually Updating the Vectorworks File

If database information has changed, and the link type for connected record fields is Read/Write or Read Only, the information can be automatically sent to the Vectorworks file, updating the record fields of all object instances connected to the database.

ODBC Driver Information

When setting up a database program for ODBC functionality (Vectorworks Design Series required), related drivers may not be installed. This list describes drivers available for each platform, where to obtain them, and current problems that may exist.

Windows

* On Windows 7 64-bit systems, you need to run the 32-bit ODBC manager (odbcad32.exe) to see the built-in drivers, which are currently 32-bit only.

** FileMaker Pro includes the driver needed to share your database as a data source. To use your FileMaker database file locally, FileMaker Pro must be running, and the database file opened and shared. To share your FileMaker database file as a data source over a network, use FileMaker Server Advanced.

Mac

* FileMaker Pro includes the driver needed to share your database as a data source. To use your FileMaker database file locally, FileMaker Pro must be running, and the database file opened and shared. To share your FileMaker database file as a data source over a network, use FileMaker Server Advanced.

Driver Name	Driver	Driver Connection	Update Vectorworks records by reading data from database	Update database with Vectorworks record data	Add new database table rows through object connection
Microsoft Access	Integrated* in the OS	Supported, working	Supported, working	Supported, working	Supported, working
Microsoft Excel	Integrated* in the OS	Supported, working	Supported, working	Supported, working	Supported, working
Microsoft SQL Server	If not integrated in the OS, ask vendor	Supported, working	Supported, working	Supported, working	Supported, working
Microsoft Text Driver	Integrated* in the OS	Supported, working	Supported, working	Supported, working	Supported, working
MySQL	Available at: www.mysql.com	Supported, working	Supported, working	Supported, working	Supported, working
FileMaker Pro **	Available at: www.filemaker.com	Supported, working	Supported, working	Supported, working	Supported, working

Driver Name	Driver	Driver Connection	Update Vectorworks records by reading data from database	Update database with Vectorworks record data	Add new database table rows through object connection
Actual SQL	Available at: www.actualtech.com	Supported, working	Supported, working	Supported, working Problem reported: data length restriction when writing to database	Supported, working Problem reported: data length restriction when writing to database
Actual Access	Available at: www.actualtech.com	Supported, working	Not supported	Not supported	Not supported
MySQL	Available at: www.mysql.com	Supported, working	Supported, working	Supported, working	Supported, working
FileMaker Pro *	Available at: www.filemaker.com	Supported, working	Supported, working	Supported, working	Supported, working

Database Connectivity

ODBC Workflow