Archicad C++ API
About Archicad add-on development using the C++ API.

SQL query, parameter select

Anonymous
Not applicable
Hi!

I'm using Archicad SQL queries and i would like to select few parameters from all of them.
Single parameter was no problem to get, but got problems getting more.

Tried using this structure :
SELECT *   
FROM   
flat(objects,parameters) as T_1,   
flat(objects,parameters) as T_2   
WHERE   
(T_1.library_part_name = 'Built-in Elevation Marker')   
and   
(T_1.parameters.variable_name = 'AC_TextPen_1')   
and   
(T_2.parameters.variable_name = 'Tst_1')   
and   
(T_1.parameters.value = T_2.parameters.value) 
from : http://archicad-talk.graphisoft.com/viewtopic.php?t=26969

Used it like this :
  
SELECT   xx.parameters.value, yy.parameters.value  
from   
flat (objects,parameters) as xx,  
flat (objects,parameters) as yy   
where (library_part_name like 'Wal%')  
and (xx.parameters.name like'Wid%' or yy.parameters.name like'Mod%')

Archicad just freezes, no error nothing just not responding.
Any suggestions ?
2 REPLIES 2
Anonymous
Not applicable
Rinovo wrote:
Hi!

I'm using Archicad SQL queries and i would like to select few parameters from all of them.
Single parameter was no problem to get, but got problems getting more.

Tried using this structure :
SELECT *   
FROM   
flat(objects,parameters) as T_1,   
flat(objects,parameters) as T_2   
WHERE   
(T_1.library_part_name = 'Built-in Elevation Marker')   
and   
(T_1.parameters.variable_name = 'AC_TextPen_1')   
and   
(T_2.parameters.variable_name = 'Tst_1')   
and   
(T_1.parameters.value = T_2.parameters.value) 
from : http://archicad-talk.graphisoft.com/viewtopic.php?t=26969

Archicad just freezes, no error nothing just not responding.
Any suggestions ?
Yes, I have this suggestion: The error message that sometimes appears gives a clue. It says something like the column isn't in the table (T_1 in your cases). Don't use the * in the SELECT statement. Rather, make sure that the SELECT statement names or specifies the columns containing values you are querying in the WHERE clause(s). For example, it may be
"SELECT T_1.parameters.variable_name, T_2.parameters.variable_name" and so on. Then the WHERE clause will actually query and name a column that appears in the table you're creating with FLAT (temporarily in the background, I guess). Also, I like to add "OBJECTS.ID, OBJECTS.USERID, OBJECTS.LIBRARY_PART_NAME" in the SELECT statement immediately following the word SELECT. It helps me get and keep my "data bearings."
Generally speaking because the SQL documentation for AC is so sketchy at the user level, and the SQL92 version is only partly supported, -- how about a COUNT statement? -- experiment with SQL queries systematically, starting with extremely simple statements and gradually building them up to more complex ones. A thorough understanding of FLAT and what it does, is important.
All that having been said, using JOINs is typically preferred over many ANDed WHERE clauses. They're often faster as the query complexity increases.
Also, I found it indispensable to use something like Wordpad to catalog the SQL statements I've created that WORK, and identify the ones that FAIL, what they do (or don't), and then paste them into the Query window. It may help to keep Task Manager open to check on memory usage for those queries that run wild. I've had some run for a couple of hours and (inefficiently) succeed, and many others just MIiA. Best of luck. Check out OBDC.
Anonymous
Not applicable
some time you wrote the post. can you tell me what wxactly you wrote so you get one parameter. It just doesnt work with me (AC 20)