Get GDL Parameters from VBScript by ODBC driver…

[Anonymous]

Good day,
I want to get table of parameters of GDL objects and use Archicad plan ODBC driver for it (at this moment I use v9.00.00.6201).

I have follow SQL query which has to do it for me:

select ID, XX.PARAMETERS.VARIABLE_NAME, XX.PARAMETERS.NAME, XX.PARAMETERS.VALUE from FLAT(OBJECTS, PARAMETERS) as XX where LIBRARY_PART_NAME=' My_object_name'

This query work correct through AC9 interface - I am inserting query to Calculate\SQL\Query... dialog and press to Execute button. After this I get table of parameters in IE browser. It's ok. But I want to get table by writing VBScript and query is not working. I wrote:

Dim conn, rsObjectList
set conn = WScript.CreateObject("ADODB.Connection")
If not IsObject(conn) Then 
	WScript.Echo "connection: error"
	WScript.Quit(-1)
End If
WScript.Echo "connection: ok"
conn.Open "AC9_Project"

Set rsObjectList = conn.Execute("select ID, XX.PARAMETERS.VARIABLE_NAME, XX.PARAMETERS.NAME, XX.PARAMETERS.VALUE from FLAT(OBJECTS, PARAMETERS) as XX where LIBRARY_PART_NAME='My_object_name'")

...

If I execute script I have follow error message in result window:

connection: ok
<name_of_project>(error in line 'Set rsObjectList..') Microsoft OLE DB Provider for ODBC Drivers: [Graphisoft][QEODBC]Failed to execute the specified statement.

If I excecute sample query like 'select * from OBJECTS' it's work fine. I think command 'FLAT(<table>, <parameter>)' is not working in VBScript.

Have I same error in code? How to create table of GDL object parameters in external application by ODBC driver?

-------
AC9 RUS 1965, WinXP SP2, IE 6.0 SP2, Archicad Plan ODBC driver v9.00.00.6201

[Karl Ottenstein]

Hi Denis,

The problem is an issue I posted about yesterday in response to someone else looking at the ODBC driver. (It is very interesting ... after nobody has talked about ODBC since it was released, we have two new people looking at it!)

The SQL syntax for the Calculate SQL dialog is different from that for the ODBC driver because the database schema (tables) is not the same. Don't ask me why, I'm just the messenger. 😉

The parameters are not a recordset for ODBC, but are instead a separate table, linked by the object ID. The ODBC table organization actually makes much more sense to me ... it is a standard relational (normal form) format.

This means, though, that you cannot cut/paste queries (in general) from one to the other.

It is best that you use Microsoft Access (or similar) to explore the format of the tables available via ODBC and proceed to code based on that. They are not otherwise documented.

It is confusing, but as soon as we jump to ODBC and scripting in this "Data Exchange" forum, we've actually jumped to issues that belong in the "Developer Forum". Please feel free to ask more questions there.
[Edit: the moderator has kindly moved this thread to the Developer Forum ... so feel free to reply to this thread now.]

Good luck with your project! 😉

Karl

[Anonymous]

Hello Karl,

Noticed that you have both Mac & PC. I've understood that ODBC driver
is PC only…have you connected to a PC served ArchiCAD-database with your Mac?



Juha

[Karl Ottenstein]

Juha wrote:
Noticed that you have both Mac & PC. I've understood that ODBC driver
is PC only…have you connected to a PC served ArchiCAD-database with your Mac?

Hi Juha,

Have not tried a network ODBC connection which I think is your question. However, if the PC is running IIS, then an ASP (etc) web page on the PC can "serve up" the data to a client making a web request from the Mac (or anywhere else for that matter).

Regards,
Karl

[Anonymous]

Karl,

Thanks for your advise. Connecting the database directly to the model seems to me very promising as at the moment we are exporting data from ArchiCAD to SQL-backend by exporting/importing .xls which is neither producing nor clever

-> so hope that MAC & PC can shake hands once again


--
Juha

[Anonymous]

Perhaps this sudden burst of interest will fire up the process of getting the Mac ODBC working and realizing some better support documentation. I was really jacked, when I found that my language of choice (PHP +GTK) which is free, open source, and cross platform was using ODBC syntax that the ArchiCAD driver didn't support.

More exactly, SQLExtendedFetch requests aren't supported by the ArchiCAD odbc driver. I'm stuck without my favorite toy. After a full day of development in c#, I find I could have recompiled PHP with ExtendedFetch turned off, but it's too late to turn back now.

My language choice came down to C# which puts me at some what of a disadvantage. It's not truly cross platform (yes I know about mono)(not that it matters yet since there are no Archicad ODBC Mac drivers), and I really don't know the language as well as I need to. Anyway, I'm moving forward and have connection and we're able to pull info from the plan file as we need.

I was using the sql database schema until right now when I read this post. I did a sql query in ArchiCAD and now I see how the parameters are layed out.

Try making a simple project, one wall with a door, and then
select * from doors
It's then pretty clear based on Karl's description.

Thanks for the quick responses and suggestions.

[Anonymous]

A lot of thanks, Karl!

It's brilliant idea to use Microsoft Access - I have all necessary table by MA. On next week I will show potential of ArchiCAD 9 to big client and I hope will have contract!

For those how want know what I did:
1. First of all you need setup ODBC - help of AC and of ODBC has this info. You need to install ODBC driver and need to add System DSN at Control Panel\Administration\Source of data (ODBC) dialog.
2. Open Access and create new project
3. On project field click right button of mouse and select Link table... command.
4. In Link dialog select type of file: 'ODBC database()' and Select Data Source dialog will show up.
5. Go to Machine Data Source page and select your System DSN point.
6. Click Ok button and you will have list of all tables of Database - of ArchiCAD 9 project!!!

Add table what you need and create that SQL query to AC9 project as you want. Great opportunity...

[Karl Ottenstein]

Denis wrote:
A lot of thanks, Karl!

You're welcome! I hope you get the contract. 😉

Karl

[Ben Cohen]

Karl wrote:
(It is very interesting ... after nobody has talked about ODBC since it was released, we have two new people looking at it!)

we have been using ODBC to connect to the ArchiCAD data file for a while now (using filemaker 7) with great success. A few points of interest
1. Installing the drivers sometimes gets tricky
2. ODBC connections through MS Apps (excel etc) can be dubious (filemaker is the preferred option)
3. Dont try and do more than two SQL joins per connection: rather bring the tables in separately then "connect" them through your database - or what ever app you are using
4. Be aware of the way in which ArchiCAD calculates the area of a wall opening...... (this is the big one)

hope this helps

Ben