topic Re: SQL request for single object parameters in Collaboration with other software

SQL request for single object parameters

Jochen Suehlo — Sun, 04 Jan 2009 12:29:54 GMT

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"

Re: SQL request for single object parameters

Laszlo Nagy — Sun, 04 Jan 2009 19:33:33 GMT

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.

Re: SQL request for single object parameters

Karl Ottenstein — Sun, 04 Jan 2009 20:27:04 GMT

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

Re: SQL request for single object parameters

Karl Ottenstein — Sun, 04 Jan 2009 20:36:49 GMT

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

Re: SQL request for single object parameters

Karl Ottenstein — Sun, 04 Jan 2009 21:05:03 GMT

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

Re: SQL request for single object parameters

Jochen Suehlo — Sun, 04 Jan 2009 21:14:45 GMT

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!

Re: SQL request for single object parameters

Laszlo Nagy — Sun, 04 Jan 2009 21:17:15 GMT

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.

Re: SQL request for single object parameters

Karl Ottenstein — Sun, 04 Jan 2009 22:04:51 GMT

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

Re: SQL request for single object parameters

Anonymous — Mon, 05 Jan 2009 11:35:14 GMT

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.

Re: SQL request for single object parameters

Karl Ottenstein — Mon, 05 Jan 2009 17:42:58 GMT

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

Re: SQL request for single object parameters

Anonymous — Mon, 05 Jan 2009 21:50:25 GMT

Hello,

Karl wrote:
(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.)

I have used this when reading manuals 'from the dark side of the moon'
http://chmox.sourceforge.net/.

Regards, Juha

Re: SQL request for single object parameters

Karl Ottenstein — Tue, 06 Jan 2009 00:03:02 GMT

Thanks for the tip/link, Juha!

Karl