SQL in ARCHICAD
Note: Find these commands using the Options > Work Environment > Menus tab page, and search the Calculation category after listing the menu items as “All commands by theme”.
Select Execute Query to open the SQL Query Window, where you can enter your SQL commands to create lists.
The ARCHICAD Project database in SQL can be queried in tables. These contain information about elements, parameters, attributes within the Project. For example, the WALLS table contains all the columns (parameters, values connected to Walls) that can be listed for each Wall element.
By selecting the Show Data Structure command, your browser window will display an XML file listing all Tables that can be used for queries with the SQL engine.
One of the most used commands of the SQL language is the SELECT command. With it you can specify items to be listed. The syntax to be followed in this:
In the <parameters> field you can place names of parameters available from the referenced table. If you use the * character, all parameters will be listed. If you want several parameters listed, you can separate them by commas.
The <table> field contains the name of the table from which the SQL commands will collect data.
The above SELECT * FROM WALLS command takes all parameters available in the WALLS table and lists them. The example below lists the User ID, Length, Height and Thickness as the desired parameters, separated with commas.
The next SQL command modifier is WHERE. With this you can set conditions, thereby effectively limiting which items will be listed. The following code,
There is a special feature in ARCHICAD database which is not part of regular SQL. This is the fact that certain parameters of certain tables are tables themselves. For example, the Parameters column of the OBJECTS table is a table itself. IF you enter the following code:
The result contains the User ID, Library part Name and Parameters of the Armchair 01.gsm object. As you can see, you do not need to use file extensions when referring to Library Part Names in SQL commands. The Parameters column contains a table containing the PARAMETERS table with its own columns.
There is a command in GRAPHISOFT SQL to obtain only this table or columns thereof. It is called the FLAT command and its syntax is the following:
In the <table> and <parameter> field write the name of the parameter and the table you want to be displayed on its own. In the <name> field give a new name to this table so you can refer to it.
Here is an example for listing all parameters of the Armchair 01.gsm object. The PARAMETERS table of the OBJECTS table is named XX. You can refer to these embedded parameters as XX.PARAMETERS.<parameter>, where XX.PARAMETERS is the Object parameter table. The screen capture below shows the result of this SQL Query:
The SQL commands to achieve a similar result are the following. With the WHERE part, we can list only Zones belonging to flat ‘A1-03’:
The SQL language has many more commands and opportunities than what is described here. You can find detailed information on it on the Internet. A good place to start may be the following web site: http://www.sql.org/
Documentation is available for GRAPHISOFT SQL. It is included in the GRAPHISOFT ODBC (Open Database Connectivity) documentation which can be downloaded from the Documentation section of the GRAPHISOFT Developer Center at the following web page:
http://www.graphisoft.com/support/developer/.
http://www.graphisoft.com/support/developer/.
There is also an ODBC driver available for download. It enables users of database applications to connect to ARCHICAD Project databases (*.PLN files for example) and extract data from them. This can be downloaded from the Download section of the GRAPHISOFT Developer Center on GRAPHISOFT’s home page.