SQL is short for Structured Query Language and is the most popular computer language used with databases. It can run queries on databases and get the results in a structured way.
Since an ARCHICAD project is basically a database of building elements (Walls, Slabs, Roofs, Shells, Objects etc.), it is possible using SQL to obtain data about placed elements in a projects and display them in table form. By default, the SQL menu items are not enabled in any of the Work Environment Profiles as these are quite advanced features. To enable them, add the following commands to your work environment:
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.
SQL commands work on either selected elements or the whole ARCHICAD Project database. That means if you just started a new Project and no elements are placed yet, any SQL command will give no results.
The way it works is you type SQL commands in the SQL Query Window and the SQL Engine will create an XML file that contains the results and will display it in a browser window.
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,
will list only those Walls of the Project that are 2.7 meters in height (numbers in GRAPHISOFT® commands are always metric).
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:
Let us see a simple example. With one of the basic Zones listing schemes, we can create the following list of the rooms of a flat in a residential building. All zones belonging to the same flat have the same User ID: ‘A1-03’.
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:
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.