cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 
Collaborate forum

ArchiCAD and SQL

Anonymous
Not applicable
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
7 REPLIES 7

Djordje
Mentor
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 ...
Djordje



ArchiCAD since 4.55 ... 1995
HP Omen

Karl Ottenstein
Moderator
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
One of the forum moderators   •   AC 26 USA and earlier   •   MacOS 11.6.8, iMac Pro 10-Core, Radeon Pro Vega 64

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

regards,
vannassen

Petros Ioannou
Newcomer
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.
ArchiCAD 22 4023 UKI FULL,
Archicad 21 6013 UKI FULL, ArchiCAD 20 8005 UKI FULL
iMac Retina 5K, 27-inch, 2017
4.2 GHz Intel Core i7
32 GB 2400 MHz DDR4
Radeon Pro 580 8192 MB

Petros Ioannou
Newcomer
The link was fixed and it was Karl's tip!
ArchiCAD 22 4023 UKI FULL,
Archicad 21 6013 UKI FULL, ArchiCAD 20 8005 UKI FULL
iMac Retina 5K, 27-inch, 2017
4.2 GHz Intel Core i7
32 GB 2400 MHz DDR4
Radeon Pro 580 8192 MB

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

/Johan
Archicad 7 - 24, HP ZBook 15 Mobile Workstation, Win 10

Karl Ottenstein
Moderator
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
One of the forum moderators   •   AC 26 USA and earlier   •   MacOS 11.6.8, iMac Pro 10-Core, Radeon Pro Vega 64

Start a new conversation!

Labels

Still looking?

Browse more topics

Back to forum

See latest solutions

Accepted solutions

Start a new discussion!