Jump to content

Updating Data via ODBC / Connect with a Symbol definition


Recommended Posts

Hello,

 

I am trying to read live data from an SQL Database via ODBC into Projects.

So far i was able to connect multiple symbol instances to the database and reading the needed data into the record field, meaning that the connection is working.

My intent is to connect symbol definitions from a library to this database, but i am not able to that.

Also, in the same time i can't connect single symbol instances to Database Table Rows. Which it doesn't make any sense because the instances are the ones that i can connect as multiple instances. Am i missing something?

My question are:

- can be symbol definitions connected to databases in order to be used as a library?

  The thing is that the option is given. This means that should work.

- in the case that this should work, why am i not able to that?

  Is it related to the issue that single symbol instances cant be connected?

- in the case that this should work, can be multiple symbol definitions connected to the database?

 

I would be grateful if you could help me with this matter.

 

Edited by Bogdan Ciocan
Link to comment
  • Vectorworks, Inc Employee

Hi @Bogdan Ciocan,

 

11 hours ago, Bogdan Ciocan said:

can be symbol definitions connected to databases in order to be used as a library?

  The thing is that the option is given. This means that should work.

 

Well, technically, the symbol definitions are pre-set graphics that are ready to be placed in the drawing. They do not store any information, well they hold default values, but not countable information.

 

Then they cannot be connected to the DB as it holds actual data.

 

So, maybe your question is how to connect the default values of the symbol data to a database? But that would be strange as when you insert the symbol in the drawing, you can have several copies of the same data. Is that required?

 

What is the workflow you want to implement?

 

At the moment, a DB table for vectorworks is a Format and Record at the same time. Format represents the table and it's columns (the definition), and a record is a single row. This way, when you have several records of a format, you are dealing with several rows of that table.

 

 

11 hours ago, Bogdan Ciocan said:

in the case that this should work, why am i not able to that?

  Is it related to the issue that single symbol instances cant be connected?

 

Following my thought from above, if you have a record attached in the drawing, you can associate it to a single row from it's DB table.


So, if you symbol instance has a record attached to it, you can associate it with a DB table row and get/put the data there.
This works via the 'key' columns (one or several) identified when connecting to the database. They are used in the 'WHERE' of the SQL to identify a single row. this is the so-called primary-key and secondary-key of a table, and unique so they can identify a row uniquely.

 

This means that your format needs to connect to these columns, so when you connect a record, it has a place to store the 'keys' to uniquely identify that particular row in the DB table always.

 

 

11 hours ago, Bogdan Ciocan said:

in the case that this should work, can be multiple symbol definitions connected to the database?

 

Yes, selecting multiple objects and trying to connect them with the menu command, will allow you to use logic to associate the multiple objects to multiple rows in the DB table using pre-existing values in the keys.

 

You can also use the Data Manager to exchange data between records and parametric objects.

 

Regards,

Vlado

 

  • Like 1
Link to comment

Hi @Vlado,

 

thank you for your clarification. This helps me to understand better the ways the DBs function.

The reason to connect the default values of the symbol definitions to an external DB is to create a library which would only read data from ODBC. This should improve our resource utilization and cut times to deliver Projects.

 

10 hours ago, Vlado said:

So, maybe your question is how to connect the default values of the symbol data to a database? But that would be strange as when you insert the symbol in the drawing, you can have several copies of the same data. Is that required?

 

Until now we used such a library because we are never changing the default values. It doesn't matter how many symbol-instances are in the drawing, because we use the data for resource management and this works great.

 

10 hours ago, Vlado said:

So, if you symbol instance has a record attached to it, you can associate it with a DB table row and get/put the data there.
This works via the 'key' columns (one or several) identified when connecting to the database. They are used in the 'WHERE' of the SQL to identify a single row. this is the so-called primary-key and secondary-key of a table, and unique so they can identify a row uniquely.

 

I've been able to connect multiple objects (through attached records) to SQL via the key, in our case only just one. But when I'm trying to connect a single instance it doesn't work.

 

But my aim is to connect the default values of the symbol definitions. Or multiple default values. Not in the drawing because the goal is to create a library.

 

I am not able to understand why this doesn't work because in the resource manager (not in the drawing) the option to connect the default values is given.

Is there any constraint in order to do this?

 

Best regards,

Bogdan

Edited by Bogdan Ciocan
Link to comment
  • Vectorworks, Inc Employee

Hi @Bogdan Ciocan

 

10 hours ago, Bogdan Ciocan said:

I've been able to connect multiple objects (through attached records) to SQL via the key, in our case only just one. But when I'm trying to connect a single instance it doesn't work.

 

How do you do this? You surely have objects in the drawing for this?

 

 

10 hours ago, Bogdan Ciocan said:

I am not able to understand why this doesn't work because in the resource manager (not in the drawing) the option to connect the default values is given.

Is there any constraint in order to do this?

 

The fundamental concept is that you connect a database to your VWX drawing. Then you define the format to be connected to a table, and establish which format field connects to which database table column. 

 

Note, there is no data flow at this point, only connections that tells it how the data will flow, and where the data will be stored in both places: <vectorworks' format field> to/from <db table column>

 

Now, when you have a record of this format, which is the actual instance of a record, and can only be attached on objects in the drawing, then you have data, which is stored in a row of the data connected to this format. fields, i.e. db table columns, define how the data from that row will be exchanged with the record.

 

So, now, if you edit a symbol definition, having nothing selected means that you are editing the defaults values that will be attached when this symbol is placed in the drawing. Noice that the 'Object Database Connection' is disabled in the context of symbol edit.

 

Now, if you insert this symbol into the drawing, the record is attached to that symbol, and you can use the Data pane button, or menu Tools -> Database -> Object Connection to associate a row of the db table to this record. Note you can do that on a single selection or multiple selection in Vectorworks.

The single selection will be like either add or select a row to provide the data for this symbol instance, where multiple will give you a chance to add or find rows depending on cirterias.

 

As far as I understand, you are trying to build up a library with prebuild values from symbol defintions connected directly to db. This can be possible with scripting (either python, vectorscript or marionette).

But there is one main question: if you add multiple instances of this symbol definition, you'll have repeating data...essentially the key will be duplicated, so several objects will contribute to a single row in the db table.

Maybe your data works that way?

 

 

 

 

Link to comment

Hi @Vlado,

 

I've been testing the connection ODBC and my database and can say that its working, somehow. But not with the DB from SQL which i want. My assumption is some SQL DB settings or restrictions.

The main reason to connect the prebuild values to the DB is to count how many instances i have in a drawing and to compare how many do we have in our warehouse in a specific period of time.

Now we can check that directly in our ERP system but the amount of data that we must compare is to large and takes a while.

 

On 5/5/2020 at 7:23 PM, Vlado said:

As far as I understand, you are trying to build up a library with prebuild values from symbol defintions connected directly to db. This can be possible with scripting (either python, vectorscript or marionette).

But there is one main question: if you add multiple instances of this symbol definition, you'll have repeating data...essentially the key will be duplicated, so several objects will contribute to a single row in the db table.

Maybe your data works that way?

 

Until now we've done this using the VW tables to count the amount of instances which worked great for us. And to be more clearly we are never changing the prebuild values from our library. We have no interest to do this for the large part of our library is a system and don't want to mess up with our data.

 

On 5/5/2020 at 7:23 PM, Vlado said:

So, now, if you edit a symbol definition, having nothing selected means that you are editing the defaults values that will be attached when this symbol is placed in the drawing. Noice that the 'Object Database Connection' is disabled in the context of symbol edit.

 

I've managed to this during my tests with another DB. I am using VW 2020 SP2.2R1 in german.

image.thumb.png.05826623cb32ec8f96bf7641e095ed16.png

 

On 5/5/2020 at 7:23 PM, Vlado said:

Now, when you have a record of this format, which is the actual instance of a record, and can only be attached on objects in the drawing, then you have data, which is stored in a row of the data connected to this format. fields, i.e. db table columns, define how the data from that row will be exchanged with the record.

 

So, now, if you edit a symbol definition, having nothing selected means that you are editing the defaults values that will be attached when this symbol is placed in the drawing. Noice that the 'Object Database Connection' is disabled in the context of symbol edit.

 

Yes i think will be doable but must run more tests.

I think the best solution in our case would be when the Symbols are inserted in the drawing, these would be already connected to DB.

 

Now the question is how can read data in a specific period of time. But this must be done in SQL, i think or with scripting.

 

On 5/5/2020 at 7:23 PM, Vlado said:

This can be possible with scripting (either python, vectorscript or marionette).

 

Which one would you recommend?

 

Best regards,

Bogdan

 

Link to comment
  • Vectorworks, Inc Employee
On 5/11/2020 at 4:29 AM, Bogdan Ciocan said:

I've been testing the connection ODBC and my database and can say that its working, somehow. But not with the DB from SQL which i want. My assumption is some SQL DB settings or restrictions.

 

If it works in general but not for a specific database, maybe there is a specific things about the SQL language of that database, like comma or quotations. We have a configuration file that can set these things up, as from Vectorworks perspective they are all the same, we just sent requests.

 

What's the database you are using?

 

On 5/11/2020 at 4:29 AM, Bogdan Ciocan said:
On 5/5/2020 at 1:23 PM, Vlado said:

So, now, if you edit a symbol definition, having nothing selected means that you are editing the defaults values that will be attached when this symbol is placed in the drawing. Noice that the 'Object Database Connection' is disabled in the context of symbol edit.

 

I've managed to this during my tests with another DB. I am using VW 2020 SP2.2R1 in german.

 

It looks you've found a place where the buttons should have been disabled but they are not. Symbols are not accounted for when exchanging data. 

 

On 5/11/2020 at 4:29 AM, Bogdan Ciocan said:
On 5/5/2020 at 1:23 PM, Vlado said:

This can be possible with scripting (either python, vectorscript or marionette).

 

Which one would you recommend?

 

It's either say, all these use the same API.

 

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...