Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Thu May 15, 2014 1:27 pm Post subject: Connecting to MySQL with ODBC in NeoOffice 2013
In light of future developments, I'm currently investigating the possibilities of migrating from NeoOffice 3.4.1 to NeoOffice 2013, which also means moving my HSQLDB database to MySQL and connecting to it with ODBC drivers.
I've installed MySQL and the ODBC connectors, and in OpenOffice 4 I can connect fine, however in NeoOffice 2013 I have two issues.
1. In the New Database wizard, step 2, when I click on Browse to find a data source, only System DSNs are shown and no User DSNs are listed.
2. In step 3 when I test the connection I receive an error about opening the libmyodbc5a.so file (see screen shot).
I don't know if you've been able to test this, Patrick, but since JDBC connectors are ruled out by the App store, it seems quite important that it works.
I'll post more detailed steps for how I got to where I am tomorrow.
Posted: Thu May 15, 2014 1:37 pm Post subject: Re: Connecting to MySQL with ODBC in NeoOffice 2013
Let me setup a test MySQL database on my Mac OS X 10.9 machine and see if I can reproduce what you are seeing.
I will post back in the next day of what I have found.
One question: I remember one of your recent posts had an .odb file that stored its data in an .ods file. Is that what you are trying to move to MySQL? If so, that should continue to work in NeoOffice 2013. Only support for Java-based databases such as HSQLDB and JDBC are not available in our Mac App Store version.
Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Thu May 15, 2014 1:47 pm Post subject: Re: Connecting to MySQL with ODBC in NeoOffice 2013
pluby wrote:
One question: I remember one of your recent posts had an .odb file that stored its data in an .ods file. Is that what you are trying to move to MySQL? If so, that should continue to work in NeoOffice 2013. Only support for Java-based databases such as HSQLDB and JDBC are not available in our Mac App Store version.
No, this is a large HSQLDB 2.3.2 database that I use for my work. It is split from the .odb file, so in theory if I can get the data in to MySQL and point the .odb file at the new data source it should just keep working. That's the theory at least!
I can reproduce what you are seeing and it appears that the problem is caused by Apple's App Sandbox disallowing access to files in the ~/Library/ODBC and /usr/local folders.
What I will do is add code to force the native Open dialog to be displayed so that the user can grant access to those folders the first time that the OpenOffice code tries to read files in them.
When I have a test patch, I will post a test patch for you to try.
2. Install the 64 bit version of MySQL's ODBC driver (the 64 bit NeoOffice 2014 application won't be able to load 32 bit binaries).
OK, done both of those, and in anticipation of your patch I thought I'd just check to see that NeoOffice 2014 DP behaved the same as 2013, but it doesn't.
1. Open NeoOffice 2014 DP
2. Select File :: New :: Database
3. Select Connect to an existing Database and choose ODBC from the drop down menu
4. Click Next >>
5. Click Browse
At this point NeoOffice crashes, producing the attached crash log. The crash is repeatable.
I can reproduce this as well. I will find and fix that bug first.
BTW, I am not surprised to find a crash like this in a 64 bit build. Although the we are using the code, I have already found and fixed a few cases where the code behaved different when compiled as a 64 bit application instead of 32 bit.
The good news is that I have fixed the crashing bug that you found when trying to display the ODBC DSN list. Unfortunately, I had to rebuild all of NeoOffice's underlying OpenOffice code so I could not create a patch. Instead, you will need to download by latest build - NeoOffice 2014 Developer Preview 2 - from the link in this NeoWiki article.
The bad news is that Base does not work reliably when using MySQL's ODBC drivers. I was able to connect to a MySQL database using ODBC, but I found serious problems with Base once I was able to connect. These problems also occur in NeoOffice 3.4.1, OpenOffice 4.1, and LibreOffice 4.2 so this isn't just a NeoOffice 2014 issue. Instead, it is clear to me that Base's MySQL ODBC support is not reliable.
So if Base's MySQL ODBC support is not reliable, what to do? My recommendation is to use a dBASE database. dBASE databases are simple to create and backup. They are merely a folder with a file for each table so they don't require any software to be installed or have any connectivity issues.
To create a dBASE database in Base, you only need to do either of the following:
1. Select the "Create new database" radio button in the Database Wizard dialog as shown in the attached screen snapshot. Instead of embedding an HSQLDB database inside a new .odb file, your new .odb file will point to a new folder in your ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/Documents folder (note: "~" means your Home folder).
2. If you don't like your dBASE folder being buried in a hidden folder, you can create a new dBASE database by simply creating an empty folder in the Finder. Then, in NeoOffice's Database Wizard dialog, select the "Connect to an existing database" radio button and select "dBase" from the list box. Press the Next button and select the empty folder that you created.
The key things to remember when using a dBASE database is the following:
- Like MySQL, the actual database is not in the .odb file. The .odb file only stores the location of the actual database and any Base forms that you create.
- dBase is a single user database so be careful if you put the dBase database folder on a shared volume. If edits are made on two different machines at the same time, you can corrupt the database files.
If you are really set on using MySQL, then let me know and I will post steps for getting NeoOffice 2014 to connect to your database without any Apple App Sandbox security errors.
Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Sun May 18, 2014 5:04 pm Post subject:
pluby wrote:
I have good news and bad news.
The good news is that I have fixed the crashing bug that you found when trying to display the ODBC DSN list.
Great! Thanks.
Quote:
The bad news is that Base does not work reliably when using MySQL's ODBC drivers. I was able to connect to a MySQL database using ODBC, but I found serious problems with Base once I was able to connect. These problems also occur in NeoOffice 3.4.1, OpenOffice 4.1, and LibreOffice 4.2 so this isn't just a NeoOffice 2014 issue. Instead, it is clear to me that Base's MySQL ODBC support is not reliable.
I wonder where the problem lies, ie with Base or the MySQL Drivers. I know this article is old, but it doesn't inspire confidence. However I'd need to think before spending money to test a theory (and that's in no way a request for you to do it for me).
Quote:
So if Base's MySQL ODBC support is not reliable, what to do? My recommendation is to use a dBASE database. dBASE databases are simple to create and backup.
I'll have a look a dBase. As long as it's query language is similar to SQL so I don't have too rewrite to much of my code. I may also take another look at H2. It uses Java (which may be an issue going forward) but has an ODBC driver, so should also work with NeoOffice. It would at least show if the problems with MySQL are to do with the driver of NeoOffice.
I'll have a look a dBase. As long as it's query language is similar to SQL so I don't have too rewrite to much of my code. I may also take another look at H2. It uses Java (which may be an issue going forward) but has an ODBC driver, so should also work with NeoOffice. It would at least show if the problems with MySQL are to do with the driver of NeoOffice.
NeoOffice's underlying OpenOffice code has built-in SQL support for dBASE. Realistically, HSQLDB was a file-based database similar to dBASE. Like HSQLDB, dBASE databases support keys and indexes in tables. The big difference is that OpenOffice zipped up all the HSQLDB files inside the .odt file and dBASE files reside in a separate folder.
It's been that way for a while (years; shortly after Sun set up forums on the old CollabNet OOo site, IIRC) now; I'm a bit surprised Google still hasn't purged those results from its db yet.
Smokey _________________ "[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki
If you are looking for an OO forum with folks who can advise on database connections, you might check out the OpenOffice Community Forums: https://forum.openoffice.org/en/forum/. It's well maintained and several of the old voluntees from ooforum.org are active there.
When the OpenOffice community forums began several years ago, there was talk of copying/backing up the ooforum posts to salvage code snippets, etc. But I don't know if that ever happened.
If you are really set on using MySQL, then let me know and I will post steps for getting NeoOffice 2014 to connect to your database without any Apple App Sandbox security errors.
I though found issues when using MySQL's ODBC driver in Base, here are the steps that I used to enable their ODBC driver in NeoOffice 2013 and any other applications that run in Apple's App Sandbox:
1. Download the .tar.gz for MySQL's ODBC driver (32 bit for NeoOffice 2013 and 64 bit for NeoOffice 2014). The .dmg driver will not work with applications that run in Apple's App Sandbox.
2. Double-click on the downloaded .tar.gz file to unpack it. That should create a folder with name starting with "mysql-connector-odbc". Move that folder to your ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data folder (note: "~" means your Home folder).
3. If your DSN definitions are in the ~/Library/ODBC folder, copy that folder into your ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/Library folder.
4. Open the ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/Library/ODBC/odbc.ini that you created in a text editor and replace any occurrences of "localhost" with "127.0.0.1". Using "localhost" will cause the MySQL ODBC driver to use the /tmp/mysql.sock file to connect to the database and that file is not accessible to applications running in Apple's App Sandbox.
5. Open the ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/Library/ODBC/odbcinst.ini that you created in a text editor and replace any occurrences of "/usr/local/lib" with " /Users/<your>/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/<mysql>". Note: replace "<your>" with your Mac user account name and "<mysql>" with the name of the folder that you moved in step 2 above.
If you did the above correctly and your MySQL server is running, you should be able to connect to the MySQL server from BASE in NeoOffice 2013 or 2014. Note, however, that when you create a new .odb file that connects to your MySQL server, Base will not display any existing tables but you can query such tables using SQL. This is one of the problems that caused me to recommend using a dBASE database if you were using an HSQLDB database in NeoOffice 3.x or OpenOffice.
Joined: Oct 24, 2005 Posts: 561 Location: Edinburgh, Scotland
Posted: Tue May 20, 2014 9:51 am Post subject:
pluby wrote:
pluby wrote:
If you are really set on using MySQL, then let me know and I will post steps for getting NeoOffice 2014 to connect to your database without any Apple App Sandbox security errors.
I though found issues when using MySQL's ODBC driver in Base, here are the steps that I used to enable their ODBC driver in NeoOffice 2013 and any other applications that run in Apple's App Sandbox:
(snip)
If you did the above correctly and your MySQL server is running, you should be able to connect to the MySQL server from BASE in NeoOffice 2013 or 2014. Note, however, that when you create a new .odb file that connects to your MySQL server, Base will not display any existing tables but you can query such tables using SQL. This is one of the problems that caused me to recommend using a dBASE database if you were using an HSQLDB database in NeoOffice 3.x or OpenOffice.
I followed the steps above and have successfully connected to a MySQL database using the ODBC connector. Interestingly I can see a list of the tables in the database under the Tables tab of the main base window, however, attempting to open any of these tables (by double clicking) causes NeoOffice 2014 DP2 to hang. (Sample attached).
A hang also occurs when attempting to add tables to a query in design view and when running an SQL query in the query editor.
In the midst of checking what OpenOffice does in this situation, I noticed that there is no Reports tab in the main base window in NeoOffice 2014. I knew this already, but had obviously forgotten, anyway, as my database uses reports to produce printed output, this is a deal breaker for me.
Going forward, I can continue to use NeoOffice 3.4.1, but in the longer term it seems I am going to have to move away from NeoOffice and use OpenOffice or LibreOffice (as you recommend). This is a great shame given the extra polish and reliability of NeoOffice compared to OO and LO, however it also removes the need to migrate my database, which I am beginning to realise is not a small task.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
