Revit- MySQL link

Hi dear friends

I am going to integrate Revit into structural health monitoring sensors data capturing, storage and visualization. For this purpose, I modeled a building in Revit with several hypothetical sensors such as Accelerometer, Thermocouples, and Strain gauge. Now, I want to make a link between physical sensors and virtual sensors by attaching sensors data (which is usually in the form of text file) to Revit sensors, and then exporting those data, as well as sensors Revit ID, etc into an external database such as MySQL for storage and retrieval purposes as well as some further analysis. Can anybody help and guide me to do this integration. So the two main initial steps are 1: attaching text file sensors data to Revit sensor objects, and 2: mining and sending these attached text file info as well as sensors specification parameters to MySQL database.
Any help is kindly appreciated in advance.

Thank u so much

It is possible- but someone is not going to create a complete solution for you. Posting the same thing twice does not help.

To get you started- the main thing is to have a unique key in the model and database that you use to join the records.
Please post your progress so far.

Andrew

1 Like

Thank you Andrew for your response.
For the first step, to attach text file to Revit components (sensors), do I have to use Python script in Dynamo, or there is also another easier way, since I am a beginner in Python. Could u please show me the map? Your help is kindly appreciated. Thanx

Hi Andrew

I did some works in Dynamo to extract some data from the rooms and specialty equipments in Revit to create two separate table for them. I got some helps from a video in youtube although it was so fast. When I run it, it didn’t work. I attached four files here. an screenshot from the dynamo and MySQl Workbench showing that no table was created in MySQl Workbench, dynamo file, and Revit file. Could u please check them? Thanx



2.dyn (46.7 KB)

1 Like

Sorry the Revit file that I uploaded with my other files were wrong. This the correct model. Thanx

It is not clear what you are trying to achieve.

The Dynamo graph is chopped off- but it looks like you are concatenating 3 SQL queries to create a database, create tables, & populate the tables with data from Revit/Dynamo

I would suggest breaking it down into separate steps that you can check (and understand) before trying to do everything in one go using Dynamo.

Using MySQL is also quite complicated and only necessary if you have gazillions of records. It would be much simpler to use SQLite- you just point it at a file rather than worrying about ports, authentication modes etc

The steps I would take are

  1. Create a database (Dynamo not needed, use Workbench)
  2. Create a test table in this database with just one column, and check you can connect to it with something simple like Excel
  3. Check you can connect to it from Dynamo using the same connection string and a simple SELECT FROM query
  4. Create one table, say Rooms (Dynamo not needed) and populate it with data from Dynamo INSERT INTO…
  5. Do the same for the other table (Specialty Equipment)
    and so on.

This way you will understand how it is working, and be able to determine where it is going wrong.

Andrew

Thank you Andrew for your helpful information and guidance. I created a database in MySQL Workbench. Then I created a test Schema with a table with 5 columns.
I tried to only export Room data from Dynamo to this database, but again it failed. I don’t know why. Maybe my server number is wrong. I am sure that the port, userID and password are ok, since I go to my database in workbench by this username and password.
Could u please try my dynamo file with your own MySQL database? I attached my modified Dynamo file. I had already attached my Revit file before here.
About SQLite, which software should we install? For example, for MySQL, I downloaded MySQL Workbench. Could u please show me a very simple example of sending Revit data into SQLite using Dynamo? Thank u so much again

3.dyn (23.5 KB)

Hi,

I had sucses when writing to a SQL (Azure) if i logged on with -servername- and -servername\password- in the DSN while using Revit. Just the password wasnt working for me. It had to be including the anticipatory severname. MySQL might be another puppy, but this helped me

Marcel

@MOJTABA256

Testing it in my environment would not prove much- I’d have to try and replicate exactly what you have, including the same database, authentication mode, ports etc

This is why I recommend SQLite- you don’t need to worry about any of this, just point it at a file,
I would recommend http://sqlitestudio.pl or ETLTools Database browser tools for working with the database outside of Dynamo

As I said- you should break it down into smaller steps and then you will be able to to work out where it is failing.
Did you do my step 3. i.e connecting to the database using something simple ? Such as Excel or the aforementioned tools

You graph is too complicated if you do not understand what it is doing.
Start off simple, and work up from there.The following demonstrate how to create a database, then verify it is working from Dynamo.



Thank u so much Andrew for your explanation. Finally I could create an Schema in MySQl Workbench using Dynamo called “Facility Management” as shown in attached pic. But I do not know why my code doesn’t work for creating table. I am going to create a table called “Rooms” with two columns, Room_ID and Name. But it gets fail, Do you have any idea?


Thanx Marcel. the password and username and server were solved for me, since I could create a Schema in MySQL workbench using Dynamo. My problem now is that my code for creating a table called “Rooms” with two columns (Room_ID and Name) as shown in my attached pic does not work. Do you have any idea? Does my code have problem? Thanx

@MOJTABA256

I would remove all unnecessary stuff from the graph
i.e isolate just the table creation part so you can determine where it fails

You don’t need the schema creation (it is already done) and the writing attributes part (will not work until the table exists)

And simplify the table creation so that it is just one code block, without the concatenation of database and table names, with just one varchar column

Assuming this works, you can build it back up and test as you go, and you will probably find out where it was failing

Although your database seems to have worked, it is good practice not to use spaces in database, table or column names

Good luck

Andrew

Dear Andrew. Thanks

As you suggested, I remove all unnecessary nodes for this step. But I don’t know why again it failed. I tried the same code block with different spaces and etc. but it hasn’t worked yet. Could u please show me how to create a table with one column under a defined Schema in MySQL by Dynamo? I think my code block has a very small problem. Thanx

Can you post your graph (dyn file)

On second thoughts, please just carefully follow what I have suggested in earlier posts
To create a table, you only need 4 nodes:

Thanx Andrew. For me it finally worked. The problem was using spacing in the name of database (Facility Management). I will go for the next step and let you know if I have any question. Thanx again for all your helps

Hi Andrew
In the next step, I could make a link between the database and the table, which I already created in MySQL, and Revit model. Now I want to update the Revit database if something is added or modified in MySQL. For this purpose as you showed me before, I read Values from SQL Table using Query.MySQL_Query node. I know that I may have to use Element SetParameterByName node to do that, but I am not sure, because When I did it, it didn’t work for me. Do you have any idea? I attached a picture from it. After I mined the values from the Revit and sent it to MySQL. I modified one of them and tried to update Revit model. But it failed. Do you know how can I solve this issue? Thanx

Hi, i have the same issue, i can créate the tables, rows, but i cant move any data from dynamo to Mysql in that rows, do you have any ideas? thanks a lot!!!

This is an old thread.
Please start a new one and post details of your work showing where the problem is

Andrew