Database

The Vectorworks Design Series products include the ability to communicate with external databases according to Open Database Connectivity (ODBC) and SQLite standards. A set of data in a database can be used to automatically update the records (record formats, as well as IFC and COBie 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.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary files. A complete SQL database with multiple tables, indices, triggers, and views is contained in a single file.

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 1812.

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 Define a link between the database and the record formats of the object types.

4 Identify the object instances of each type of object to be connected and link them to a row in the database table.

5 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.

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

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 1812. 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 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, click New. Browse to an appropriate location and enter a file name to create an empty SQLite database file. Adding the appropriate file format extension is recommended (such as .db, .sqlite, .sqlite3).
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. Required record formats that do not yet exist in the file can be created when connecting the records to the database. IFC/COBie entities can also be created and connected by linking their properties to the database table. 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 in the file. These records consist of Document (user defined), Parametric (related to parametric objects), or IFC (IFC and COBie records). 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	Opens the Specify Format Type dialog box, to indicate which type of record to create. • Select Document to create a new, user-defined record format as described in “Creating Record Formats” on page 286, and then connect it as described in the following steps. • Select IFC/COBie to select an IFC entity and connect its properties. See “Creating or Editing and Connecting IFC/COBie Properties” on page 1796.
Edit	The dialog box that opens depends on the type of format selected. • Selecting a document or parametric record format opens the Edit Record Format dialog box, to link the record format to the specified database and table as described in the following steps. • Selecting an IFC/COBie record format opens the Edit IFC/COBie Record Format dialog box. See “Creating or Editing and Connecting IFC/COBie Properties” on page 1796.
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 1807
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 1811; 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.

Creating or Editing and Connecting IFC/COBie Properties

As mentioned in the previous section, IFC entities that do not yet exist in the file can be created when connecting properties to a database. In addition, the properties of existing entities can be edited. The process is different when creating or editing IFC/COBie record formats. For IFC/COBie, instead of linking record fields to a database, properties are connected.

1 From the Record Format Database Connection dialog box, click New to create a new record format, or select an IFC/COBie record format and click Edit to edit the format.

The New IFC/COBie Record Format dialog box or the Edit IFC/COBie Record Format dialog box opens.When creating a new IFC/COBie format, select the entity from the list to display its properties and connect them. When editing an IFC/COBie format, the entity is already selected so only its properties display for editing connections.

Parameter	Description
IFC Version (new IFC/COBie format only)	When creating a new IFC/COBie record format, select the IFC Version. The associated list of IFC entities displays
Filter List (new IFC/COBie format only)	When creating a new IFC/COBie record format, enter keywords to filter the entity list
Full Entity List (new IFC/COBie format only)	When creating a new IFC/COBie record format, displays the list of IFC entities. Select an entity to view and connect its properties
Entity (edit IFC/COBie format only)	When editing an IFC/COBie record format, displays the name of the selected IFC entity
Properties for the selected Data Set	Lists the properties of the selected or displayed IFC entity. Each property can be selected, and its parameters display for connection to the database. The property parameters are displayed in columns for convenience.

3 Select the connected database to use, and assign a database table from the selected database. Select the associated table row from the database.

• 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 the property 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 property

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

5 Click OK to return to the Record Format Database Connection dialog box. The dialog box updates to display the associated database.

Object Database Connection

The final step in setting up a database connection is to connect object instances to the database. The procedure differs depending on whether one or multiple objects are selected for connection.

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 (including IFC and COBie records)
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.

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.

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.

The formatting of the queries depends on the database in use. Some databases expect brackets, while others use quotes. The ODBCDrivers.xml file, located in the Plug-ins\Common\Data folder, indicates the proper formatting to use for various drivers.

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 Example: [id],[room name],[area] • FROM: indicates the name of the database and table Example: [SpaceInfo].[dbo].[Spaces] • WHERE: indicates the name and value of the record format, with the record format name 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. When the query is executed, the Vectorworks program replaces the field name in angle brackets with the value from the record.
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 Example: [SpaceInfo].[dbo].[Spaces] • SET: indicates the name and value of the table column, with the column name 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 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. When the query is executed, the Vectorworks program replaces the field name in angle brackets with the value from the record.
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

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. 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. 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 do 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. 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, 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

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

* As of its version 12, 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. As of version 13 of FileMaker Pro, the ODBC driver is distributed with the server version of the product. For more details, see www.filemaker.com

Mac

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 Workflow