SQL request for single object parameters

[Jochen Suehlo]

Is it possible to make a SQL request for a single parameter of a GDL object?
The following request gives back only the whole column of the parameters names:
"SELECT XX.PARAMETERS.NAME FROM FLAT(OBJECTS, PARAMETERS) AS XX"

[Laszlo Nagy]

Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.

[Karl Ottenstein]

Hi Joachim,

I was going crazy trying to get this to work ... and it turns out that the ArchiCAD SQL interpretter only allows single quote for text strings... I was using double quotes for half an hour!

The attached is an example that works to display the value of the 'A' parameter for all placed instances of a particular chair:

select flc.parameters.name, flc.parameters.value  from flat(objects,parameters) as flc
where (library_part_name='Armchair 01 12') and (flc.parameters.variable_name = 'A' )

I tried using the parameter value ('value')in the where clause, but it is of type 'gdlvariant' (permitting text, integer, floating point, etc) - and the only way to get a valid comparison is to use a type coercion function ... and I could not find any that were accepted by the interpreter. If you happen to figure that out, please post.

Cheers,

Karl

[Karl Ottenstein]

laszlonagy wrote:
Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.

From his example that calls the flattened table 'XX', it is likely that Joachim was working from those pages...

But, thanks, Laszlo for pointing out this reference! The 5 pages there are an excellent tutorial for the SQL feature which was introduced in ArchiCAD 8.0 with no documentation at that time. I did not realize that something had finally been written up since then...and I see these same pages in the AC 10 and AC 11 Calculation Guide. So, good news! 🙂

Cheers,
Karl

[Karl Ottenstein]

Attached is the GS SQL reference, extracted from the AC 12 ODBC help chm as a PDF. No time to review myself just yet, but when ODBC and SQL arrived with 8.0, the syntax for each was slightly different, so may still be so. But, the pages that Laszlo pointed us to further point to the ODBC documentation for the Graphisoft SQL guide, so hopefully this will help.

(It is a little odd that the ODBC help, including this syntax, is in a Microsoft Windows CHM format help file, that is not accessible to Mac users - even though the SQL Query window does work on mac. It is only ODBC that is Windows-only.)

Cheers,
Karl

[Jochen Suehlo]

Thanks Laszlo, Thanks Karl.
Yes, I used the Calculation Guide. And as you write it, Karl, I remember the SQL Guide, that I downloaded some years ago.
I still have acess to Windows, so it wil be no problem for me, to read it.
I hope it will help and I will post if I find out news.
Thank You again!

[Laszlo Nagy]

Karl wrote:

laszlonagy wrote:
Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.

From his example that calls the flattened table 'XX', it is likely that Joachim was working from those pages...

But, thanks, Laszlo for pointing out this reference! The 5 pages there are an excellent tutorial for the SQL feature which was introduced in ArchiCAD 8.0 with no documentation at that time. I did not realize that something had finally been written up since then...and I see these same pages in the AC 10 and AC 11 Calculation Guide. So, good news! 🙂

Cheers,
Karl

I originally wrote this short introduction for Graphisoft for AC9 and it has not changed since. As stated there, there is a lot more that can be done with SQL than is described in those few pages.
But thanks for chiming in Karl, I think you are probably the one who knows the most of SQL in practice.

[Karl Ottenstein]

laszlonagy wrote:
I originally wrote this short introduction for Graphisoft for AC9 and it has not changed since.

I'm glad that they had you do that! (And, sorry I had not noticed it! I was so used to the Calculate Guide getting no improvements, that I stopped looking closely at it!)

Thanks!

Karl

[Anonymous]

Karl wrote:
I tried using the parameter value ('value')in the where clause, but it is of type 'gdlvariant' (permitting text, integer, floating point, etc) - and the only way to get a valid comparison is to use a type coercion function ... and I could not find any that were accepted by the interpreter. If you happen to figure that out, please post.

ODBC manual (odbc.chm) describes GDLVARIANT as totally unconvertable (Graphisoft SQL Language Reference->Data Type Conversion chapter). May be you can use "GDL Helper Object" trick as a workaround.

Create 'SQL_Tst_1' object with 'Tst_1' variable as a parameter. Define this variable with absolutely the same type, as the type of the field you want to search for (pen number in my case). And set it to the value you want to search for. Place the object and execute this instruction:

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)

It is very important to write WHERE part carefully. We have to select rows with the same GDL type of comparable parameters. Otherwise SQL reports a bug.

[Karl Ottenstein]

Nice trick, Leo. 😉 After I posted the PDF, I saw that chart with the lack of coercion functions for variants (unlike in most languages). Unfortunate to have to create an object like yours to perform the search, but great lateral thinking! 😉

Cheers,
Karl