Jump to content

ODBC Drivers / Purpose


Recommended Posts

@jeff prince@unearthed

I'll do some experiments, stay tuned!

 

But here is the summary of what I've discovered so far (on a local level)

When using an sqlite file, I modify the sqlite file in an open source sqlite file editor - something like

https://sqlitestudio.pl/

 

I add tables to it specifically to what I need (I.E. product database with insulation characteristics, or pricing tables, or zoning / building code reference database and I link the database to record information in VW in read only mode.

 

When i assign that record to an object - it does not update immediately, i have to use the update vectorworks database in order for it to choose. The table above was an experiment of sqlite database reference - and in large databases, a search query or search by record would be fundamental to using ODBC.

 

The part that is annoying is that I can change a record in the sqlite file upon entering the information, but I cannot update the object upon selecting the record, which is a bit frustrating.

 

image.png.3290a7862ec6ae7eddfe2c1c2114ed08.png

 

I'll try using ODBC with mysql hosted on a local docker vm and update my progress soon.

 

Edited by Samuel Derenboim
Link to comment

Bonjour Samuel,

 

J'ai fait quelques tests simples sur des fichiers SQLite il y a un mois.

 

Le test simple était de modifier le fichier SQLite et de récupérer les données modifiées dans Vector, ça fonctionnait pour les données de l'onglet "Info Objet" comme le prix ou la référence.

Je ne suis pas allé plus loin pour l'instant mais suis intéressé par ce sujet!

 

Bon test!

01- ID générés dans Vector avec étiquette de données.png

02- ID visible dans le fichier SQLite.png

03- Modifications des références et des prix dans le fichier SQLite.png

04- Affichage des valeurs modifiées dans Vectorworks.png

Link to comment

great news everyone, ODBC works for mssql . however I am missing something. I am still getting an error after setting up a connection after successful test via ODBC.

 

I used MS SQL server 2019. Am i to use an earlier version?

 

 I was able to set up a table inside the container and was able to connect via test connection when setting up ODBC parameters.

 

image.png.44e1f4627233090911f9ea01c0ec4870.png

 

 

image.png.41ca9fec2815bbe3b5dc3e97aa7c68e1.png

 

after setting up the connection - i get this error

 

image.png.c5af50c22503cd278f5c1ec7233d9d58.png

 

 

Note - I have confirmed the sql tables work via noco database, so the problem lies with either the driver, sql version number, or something else. (see screenshot below)

 

connection currently works, i just don't know why it rejects the connection after successful test.

 

image.thumb.png.a16c45d9355696c8af91db6f8ff7e1bc.png

Link to comment

UPDATE 3

 

When you start stacking up with the ODBC datasources, you still need to delete the ones that do not work.

 

image.thumb.png.e699022b0c0063e48d36460191c0a7c8.png

 

Go into administrative tools and ODBC Data sources and remove any data sources you do not need.

 

Also, if you want to set up new datasources flexibly without VECTORWORKS, this is the way to do it! In vectorworks you cannot modify existing ODBC databases. Here you can.

 

image.png.949a7869542e040a266ec5ee8bf19c62.png

 

 

Edited by Samuel Derenboim
  • Like 1
Link to comment

I think that connected data bases are one of the most underutilised features. We still do a lot of 2d projects and the floorplans and views are seperate files, so I used a sqlite table to syncronise the record for our window symbols. Only one colleague had to update the information in the floor plan and the data tags in the view file was updated accordingly. Huge time saver.

 

Link to comment

@elepp

 

I agree . Great potential.

I'll start another post later, but combing this with a web gui remotely will sky rocket it's potential.

 

May I ask, what do you use it for in particular ? Sorry about that, too early in the morning, didn't see you mention you used it for windows.

 

Do window sizes auto update upon selection of the record?

 

And can you lookup database tables to cross reference records in a worksheet? ( it's a topic I will start a new post on soon )

Edited by Samuel Derenboim
Link to comment
On 8/26/2022 at 3:01 PM, Samuel Derenboim said:

Do window sizes auto update upon selection of the record?

Hi, that project was done with 2d symbols, because the windows were to complicated to generate with the vectorworks window tool.

 

I didn't manage to play more with the functions, we used it mainly to shift the data from file a into file b.

 

I see a bit of potential for facility management with a data base you are setting up. The main tool would be the data base and Vectoworks would be for visualising the data in the floorplan. Like cleaning schedules or seating plans.

Link to comment
  • 1 month later...

Try installing the latest mysql 8 odbc driver. The native one doesn't work, had similar issues. Also, use the universal driver rather than ansi

 

Also note that you can actually access the database and have your password and login correctly

 

I dont have Mac unfortunately, can't test it.

Edited by Samuel Derenboim
Link to comment

@GatRed

 

Are you running a mysql server? does it work via phpmyadmin? or similar? Have you created a database table inside the server? did you install the latest odbc driver for mac? Can you show us a screenshot of your DB connector window?

 

FYI

https://dev.mysql.com/downloads/connector/odbc/

 

Use the driver to connect to your database first. If it connects, you will be able to use it in VW. See above posts for referencing db info into VW

 

Also, once you confirm connection via outside source (i.e. self hosted http access), try this connection (it should work the same, system independent)

 

DSN=MYSQL_Uni;UID=sa;PWD=password;

DSN : Driver name for mysql after you install it

UID : Username (often its root, but if you create a separate user to access the db table, you can use that)

PWD=password for username to access database table

Edited by Samuel Derenboim
  • Like 1
Link to comment

Hi @Samuel Derenboim,

Thanks for your feedback.

Here are more informations :

1. I installed MariaDB and phpmyadmin on a Synology NAS

1308151424_Capturedcran2022-10-1210_19_45.png.3faeef03da6853d4a1693b814c36af16.png

230776505_Capturedcran2022-10-1210_20_10.thumb.png.d10551d8a459a90ac5dab233e6ca30c2.png

2. I create within phpmyadmin a user 'gaetan' an a database 'Vectorworks_DB' with all access.

258975_Capturedcran2022-10-1210_22_42.thumb.png.a2cf5a7618c47551aaa5946f57744612.png

1520778108_Capturedcran2022-10-1210_23_10.thumb.png.435420140c830223b81176f12b5e98eb.png

3. Using iODBC Connector, I created a user DSN and manage to do the test

1498374039_Capturedcran2022-10-1210_24_18.png.ec77b216191933e5d3820eafc3d63689.png

2047599725_Capturedcran2022-10-1210_25_00.png.dd2a772af31bab63b2282cdf5554913b.png

4. But within Vectorworks, when a do the connection, I’ve got the following error message :

[1049] Driver error: S1000 [MySQL][ODBC 8.0(w) Driver]Unknown database 'null'

1190828402_Capturedcran2022-10-1214_26_33.png.2d49f19b3c9c61733f68421f6e1472ef.png

1635021434_Capturedcran2022-10-1210_26_46.png.e6cf4e6d7fc7fd8bdad5c90a4bd6a124.png617026296_Capturedcran2022-10-1210_27_09.png.2fe24a5c1fa16b092889eb00e1ff26b5.png

 

I think I did something wrong within myphpadmin, but I don’t know what…

 

In addition, I manage to connect and edit the database with a third party application like Sequel Ace.

71001004_Capturedcran2022-10-1212_11_27.thumb.png.7e5362692c13759a56ac940a66c9d946.png

 

If you know what is happening, I would be really grateful.

Edited by GatRed
Link to comment

Your dsn appears to be Gramysql not odbc 8.0 there error show that it cannot find the driver name. They have to match.

 

Additionally since you populated it in your iodbx driver, it might not be necessary to enter it into vw. Repopulate available databases in the first table where you load the database. Gramysql should be there.

Edited by Samuel Derenboim
Link to comment

Here are all the screenshots of the error using the mysql driver and mariaDB, the last one is shown when clicking on "Details…".

89670745_Capturedcran2022-10-1218_08_45.png.2d5e44d1cfd25b33e1cddf982ad1c0bb.png

 

I tried with the MariaDB ODBC Connector. I had to install Homebrew and openssl via Terminal commands also.

But then, I get this error while making the test with iODBC :

779014616_Capturedcran2022-10-1218_46_27.png.1aaf1d5b11db4983969b0e7a49e6084e.png

 

 

PS : I use MariaDB because it is available as a package on Synology DSM system, whereas it is not for MySQL.

 

 

Edited by GatRed
Link to comment

it seems mariadb is not supported

 

https://app-help.vectorworks.net/2023/eng/VW2023_Guide/Database/ODBC_driver_information.htm

 

https://mariushosting.com/how-to-install-mysql-on-your-synology-nas/

 

Just FYI, I started out with a Synology NAS as well, but after a certain point it became too limited for hosting options, which is why I learned to use Docker.

Edited by Samuel Derenboim
Link to comment

OK,

So I uninstalled MariaDB and phpmyadmin packages on my Synology.

And I installed Dockers images of mysql and phpmyadmin.

1197966045_Capturedcran2022-10-1221_58_44.thumb.png.d4f5d705836bcfeb80f4c0dcb521933a.png863172575_Capturedcran2022-10-1221_58_36.thumb.png.782a7df0bdaf836db43b7c1b0bd07a88.png

Connection with iODBC is OK.

650614755_Capturedcran2022-10-1221_59_04.png.b0145af77f50c3009cdbae6ef62f5f05.png

Acces with Sequel Ace is also OK.1476848470_Capturedcran2022-10-1222_00_43.thumb.png.1ebdaf9273c26ad10baf6ed9c8afd1e2.png

But within Vectorworks, I’ve still got this error Unkown database 'null'.

1459156487_Capturedcran2022-10-1222_01_05.png.d06b030d7a2f45d6d3fa01c10444811e.png994721963_Capturedcran2022-10-1222_01_08.png.a2a46b4db2ba38a0c71a9bf193197229.png537482240_Capturedcran2022-10-1222_01_14.png.c78210f6d09ef6d2417358e566f5b32d.png

Any Idea ?

If I manage to make it, I promise, I will do a tutorial.

Link to comment

what version of mysql is it? can you post the docker / docker compose script? Also, try playing with the settings of the mysql driver. They also have 2 drivers, 1 uni and 1 ansi. maybe the ansi one works better on mac?

 

Also, as an experiment, try recreating another connection via iodbc driver. There are also certain options you can use in the connection, maybe there is something in the settings that is in conflict with VW

Edited by Samuel Derenboim
Link to comment

Thanks @Samuel Derenboim for your help.
I think you’re on of the few people who are using the database link future in Vectorworks.
I’m starting to understand why, as it is so difficult to set up…
 

In tried to create another DSN, to use the Ansi driver, etc. but without any different result : always the same error.

Here is a screenshot of the mysql server version : 8.0.31-1.el8

 

862161887_Capturedcran2022-10-1310_37_45.thumb.png.1e4050b2629300eafde478f2fdf80e41.png

 

I think I’m going to give up…

if anyone manage to find how to set up this on a mac……

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