topic Re: ArchiCAD and SQL in Collaboration with other software

ArchiCAD and SQL

Anonymous — Thu, 12 Feb 2004 14:30:32 GMT

hi,
i'm trying to use sql for making queries to the database, but i can't quite figure out what sql-statements it understands and which ones i doesn't. does anybody have a clue or is i documentet somewhere?
regards,
vannassen

Re: ArchiCAD and SQL

Djordje — Thu, 12 Feb 2004 18:01:19 GMT

vannassen wrote:
i'm trying to use sql for making queries to the database, but i can't quite figure out what sql-statements it understands and which ones i doesn't. does anybody have a clue or is i documentet somewhere?

The local database expert is Karl Ottenstein; I am sure he will respond soon ...

Re: ArchiCAD and SQL

Karl Ottenstein — Thu, 12 Feb 2004 18:30:24 GMT

vannassen wrote:
hi,
i'm trying to use sql for making queries to the database, but i can't quite figure out what sql-statements it understands and which ones i doesn't. does anybody have a clue or is i documentet somewhere?
regards,
vannassen

The available tables and fields are fully documented, but the version of SQL is not. I had asked GS tech support about this once and was simply told it was 'standard' SQL and given no further information. Of course, there are many dialects and so we are on our own it seems to determine the exact syntax allowed.

The following post with some sample code might help you:
http://www.graphisoft.com/community/archicad-talk/viewtopic.php?p=6914#6914

I used to know other versions of SQL, but after having used Microsoft Access for so many years, that's the only version I know these days ... and the FLAT keyword was new to me. The other syntax in the linked example might be enough for you to extrapolate your SQL knowledge to retrieve what you want.

Post some examples for others ... of things that don't work, and we'll see if we can figure them out jointly ... or of things that work that people can use Cookbook-style like my tip above. It seems that very few people are using this feature.

Cheers,
Karl

Re: ArchiCAD and SQL

Anonymous — Mon, 16 Feb 2004 09:57:57 GMT

thanx!
i haven't seen the flat-keyword before, so this was really helpfull!

regards,
vannassen

Re: ArchiCAD and SQL

Petros Ioannou — Mon, 16 Feb 2004 13:17:11 GMT

There's a new article in ArchiGuide named "Using SQL Queries within ArchiCAD" dated 16.02.2004 but until now the link was broken.I reported it and I hope they'll fix it.

Re: ArchiCAD and SQL

Petros Ioannou — Mon, 16 Feb 2004 15:38:34 GMT

The link was fixed and it was Karl's tip!

Re: ArchiCAD and SQL

eldhead — Thu, 26 Feb 2004 14:19:41 GMT

And now we want to connect OBJECT ID with LIBRARYPART PREVIEW, but how?

/Johan

Re: ArchiCAD and SQL

Karl Ottenstein — Thu, 26 Feb 2004 20:43:07 GMT

eldhead wrote:
And now we want to connect OBJECT ID with LIBRARYPART PREVIEW, but how?

Well, this should never be possible, as the OBJECT ID is a user defined piece of text that is part of the placed object (OBJECTS table) and not part of the LIBRARYPART (actual GDL definition).

However, doing a join to obtain the preview for placed objects should be possible ... getting ANY data about placed objects for that matter should be possible, but the current table design does not allow this (I will report the problem).

The LIBRARYPARTS table has the UNID field that gives the new unique identifier for a library part - that exactly identifies it, since parts with the same name can exist. HOWEVER, the OBJECTS table - which lists all placed objects in the file - contains only the ID (unique to that table) and the USERID (the text assigned by the user)! It MUST have the UNID for the placed object as well to permit a proper JOIN.

At present, the best you can do is to use the LIBRARY_PART_NAME field, perhaps, in the OBJECTS table and just run the risk of having parts with duplicate names. A textual JOIN such as this is very inefficient, too.

The following query will more or less get you what you want:

SELECT DISTINCT PREVIEW FROM LIBRARYPARTS JOIN OBJECTS
ON (LIBRARYPARTS.NAME = OBJECTS.LIBRARY_PART_NAME)

Karl