Samuel Derenboim Posted August 24, 2022 Share Posted August 24, 2022 (edited) Can someone please explain this to me? We can host SQL servers to carry database information???? 😁 Does that mean I can host an MYSQL Server on docker and use it for referencing and crossreferencing databases? Edited August 24, 2022 by Samuel Derenboim Quote Link to comment
Samuel Derenboim Posted August 24, 2022 Author Share Posted August 24, 2022 Also, Would adding a search function for large databases be possible? Quote Link to comment
unearthed Posted August 24, 2022 Share Posted August 24, 2022 If you think this might work re ODBC, try and make the simplest workable case first, several of us here have wasted a lot of time trying to get ODBC anything working on VW despite their advertising and assurances. 2 Quote Link to comment
Jeff Prince Posted August 25, 2022 Share Posted August 25, 2022 I have nothing positive to add to this thread having been one of the people @unearthed referenced. 1 2 Quote Link to comment
Samuel Derenboim Posted August 25, 2022 Author Share Posted August 25, 2022 (edited) @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. I'll try using ODBC with mysql hosted on a local docker vm and update my progress soon. Edited August 25, 2022 by Samuel Derenboim Quote Link to comment
Thomas W Posted August 25, 2022 Share Posted August 25, 2022 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! Quote Link to comment
Samuel Derenboim Posted August 25, 2022 Author Share Posted August 25, 2022 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. after setting up the connection - i get this error 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. Quote Link to comment
Popular Post Samuel Derenboim Posted August 25, 2022 Author Popular Post Share Posted August 25, 2022 (edited) UPDATE 2 @ThomasW @unearthed @jeff prince ODBC Works with MYSQL!!!! There is a pretty convoluted way of getting it to work, however it all deals with the drivers. PROCEDURE: 1. INSTALL UPDATED DRIVERS FOR MYSQL ODBC CONNECTION. This is important for successful connection. See link below. https://dev.mysql.com/downloads/connector/odbc/ Both x86 and x64 drivers should work in the connection as I was able to set up an database connection using an ANSI ODBC Driver as well as the UNI ODBC Driver for MYSQL. (albeit with A LOT of trail and error) 2. Set up MYSQL server via localhost or docker. In my case I am running MYSQL over docker with PHPMYADMIN as the database connector. Additionally, I am using NOCODB as an airtable alternative in order to modify records remotely (i.e. via website). Additionally I am using Portainer to manage all of my containers. As of this note: I am currently not using a remote WAN capability for mysql server, however it is possible to do with a reverse proxy. With enough votes, i can set up a tutorial on how to do that if there are enough people interested. See screenshots below Since I have used custom portforwarding - my container port is 3306, but the facing WAN port in this case is 4040. (This is going into the real of IT, but i recommend having a bit of knowledge on that front in order to do this. Otherwise use a locally hosted version of MYSQL server. 3. After you have recorded the information for connection, next thing to do is to connect to the database using a GUI or a hosted system. In my case I am using noco db https://hub.docker.com/r/nocodb/nocodb https://www.nocodb.com/ Setting up nocodb is pretty easy. But in this case, using PHPmyAdmin would actually be better because you have control over database names, and table names. I recommend using a compose file with mysql, phpmyadmin as a resource in order to access everthing. Ill provide the docker file/compose code soon. 4. 5. 7. 8. 9. 10. 11 12. 13. Press the Test button for additional confirmation. If connection is successful, it should work. 14. Press OK to create the connection. Here is the tricky part - after creating EXIT OUT database connections. It needs to repopulate the DATABASE SOURCES above the connection string. 14. Go back into connections 15. The database should appear in this list. 16. REenter your credentials for the SQL server UPON CONNECTION - YOU should see the database rows Go back into record format connection to link the database to the internal records DB should populate Connect the record fields the the database record fields It should auto populate your selection after you update vectorworks connections For faster synchronisation 1. Create a shortcut key for vectorworks connection update 2. see below and Hope this helps!! Edited August 25, 2022 by Samuel Derenboim 5 Quote Link to comment
Samuel Derenboim Posted August 25, 2022 Author Share Posted August 25, 2022 (edited) UPDATE 3 When you start stacking up with the ODBC datasources, you still need to delete the ones that do not work. 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. Edited August 25, 2022 by Samuel Derenboim 1 Quote Link to comment
elepp Posted August 26, 2022 Share Posted August 26, 2022 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. Quote Link to comment
Samuel Derenboim Posted August 26, 2022 Author Share Posted August 26, 2022 (edited) @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 August 26, 2022 by Samuel Derenboim Quote Link to comment
elepp Posted August 27, 2022 Share Posted August 27, 2022 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. Quote Link to comment
gerardopellicer Posted September 30, 2022 Share Posted September 30, 2022 Hello. I am also trying to connect to my Mysql database. When I run the iODBC Administrator I get a success message when testing the connection But when I try to use it inside Vectorworks I get an <error accessing the external database> MacBook Pro (16-inch, 2021) Vectorworks 2022 SP5 Quote Link to comment
Samuel Derenboim Posted October 4, 2022 Author Share Posted October 4, 2022 (edited) 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 October 4, 2022 by Samuel Derenboim Quote Link to comment
GatRed Posted October 11, 2022 Share Posted October 11, 2022 Did anybody managed to create an ODBC with MySQL on a mac ? At the step Database connection, The button "Built" is not available aside of "Use connection String" on the mac, as it is said within the vectorworks help. Quote Link to comment
Samuel Derenboim Posted October 11, 2022 Author Share Posted October 11, 2022 (edited) @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 October 11, 2022 by Samuel Derenboim 1 Quote Link to comment
GatRed Posted October 12, 2022 Share Posted October 12, 2022 (edited) Hi @Samuel Derenboim, Thanks for your feedback. Here are more informations : 1. I installed MariaDB and phpmyadmin on a Synology NAS 2. I create within phpmyadmin a user 'gaetan' an a database 'Vectorworks_DB' with all access. 3. Using iODBC Connector, I created a user DSN and manage to do the test 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' 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. If you know what is happening, I would be really grateful. Edited October 12, 2022 by GatRed Quote Link to comment
Samuel Derenboim Posted October 12, 2022 Author Share Posted October 12, 2022 (edited) 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 October 12, 2022 by Samuel Derenboim Quote Link to comment
GatRed Posted October 12, 2022 Share Posted October 12, 2022 I’m not sure to understand what you mean… when I try to connect to the database, GRaMySQL is found. The error comes when I click on the OK button on the window in the screenshot below : Quote Link to comment
Samuel Derenboim Posted October 12, 2022 Author Share Posted October 12, 2022 can you show a screenshot of the error? It could possibly be the connection to MariaDB is not compatible with ODBC driver. If you want to use mariadb, try installing this driver. Mariadb was originally forked from Mysql, but i do not think they are the same. That could be another cause. https://mariadb.com/kb/en/mariadb-connector-odbc/ Quote Link to comment
GatRed Posted October 12, 2022 Share Posted October 12, 2022 (edited) Here are all the screenshots of the error using the mysql driver and mariaDB, the last one is shown when clicking on "Details…". 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 : PS : I use MariaDB because it is available as a package on Synology DSM system, whereas it is not for MySQL. Edited October 12, 2022 by GatRed Quote Link to comment
Samuel Derenboim Posted October 12, 2022 Author Share Posted October 12, 2022 (edited) 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 October 12, 2022 by Samuel Derenboim Quote Link to comment
GatRed Posted October 12, 2022 Share Posted October 12, 2022 OK, So I uninstalled MariaDB and phpmyadmin packages on my Synology. And I installed Dockers images of mysql and phpmyadmin. Connection with iODBC is OK. Acces with Sequel Ace is also OK. But within Vectorworks, I’ve still got this error Unkown database 'null'. Any Idea ? If I manage to make it, I promise, I will do a tutorial. Quote Link to comment
Samuel Derenboim Posted October 13, 2022 Author Share Posted October 13, 2022 (edited) 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 October 13, 2022 by Samuel Derenboim Quote Link to comment
GatRed Posted October 13, 2022 Share Posted October 13, 2022 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 I think I’m going to give up… if anyone manage to find how to set up this on a mac…… Quote Link to comment
Recommended Posts
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.