Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

Problems after upgrading to Sierra or High Sierra? Try these steps
Problems after upgrading to NeoOffice 2017? Try these steps
Download or installation problems? Try these steps


Support
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


RSS Feeds
· Announcements Only
· All Posts


  
NeoOffice :: View topic - Connecting to MySQL with ODBC in NeoOffice 2013
Connecting to MySQL with ODBC in NeoOffice 2013
 
Post new topic   Reply to topic    NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Tue May 20, 2014 10:37 am    Post subject: Reply with quote

amayze wrote:
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.


Thank you for the samples. In both cases, the hang is occurring in OS X's ODBC management library. From what I can see, the OS X SQLColAttribute() function is in an infinite loop and is leaking memory in each pass through its loop.

amayze wrote:
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.


Using NeoOffice 3.4.1 in the short term and then moving to OO or LibO makes sense. However, I would recommend thinking about a fallback plan in case Apple stops providing Java in a future version of OS X. IMHO, Apple has made it pretty clear that do not like Java running in OS X applications so we are doing everything we can to adapt to a "Java-less" OS X now.

OO and LibO work with Apple's Java, but you should check if they work with Oracle's Java periodically as that will likely be the only Java available for OS X at some point in the near future.

I understand the dilemma you are in. It really is too bad that Sun Microsystems made OO so dependent on Java. Unfortunately, rewriting all of Base's features in native code is just not feasible for Ed and I. Maybe OO or LibO will do that at some point, but I don't know if it feasible for either of them given that both run on much smaller staffing levels than back in the days when Sun Microsystems' engineers originally wrote the Base code.

Patrick
Back to top
View user's profile Send private message Visit poster's website
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Tue May 20, 2014 1:37 pm    Post subject: Reply with quote

pluby wrote:
amayze wrote:
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.

Thank you for the samples. In both cases, the hang is occurring in OS X's ODBC management library. From what I can see, the OS X SQLColAttribute() function is in an infinite loop and is leaking memory in each pass through its loop.

That's odd. The same actions work fine in OpenOffice. I wonder if it's to do with putting all the connector stuff inside the App bundle. I'll have a go with NeoOffice 3.4.1 and see if it behaves differently.
pluby wrote:
Using NeoOffice 3.4.1 in the short term and then moving to OO or LibO makes sense. However, I would recommend thinking about a fallback plan in case Apple stops providing Java in a future version of OS X. IMHO, Apple has made it pretty clear that do not like Java running in OS X applications so we are doing everything we can to adapt to a "Java-less" OS X now.

Yes, that's why I've been looking at MySQL for the backend. I guess if OO stops working in the future I could bolt on a FileMaker frontend. Of course the other option is just to maintain old hardware that will run Java and NeoOffice 3.4.1.
pluby wrote:

OO and LibO work with Apple's Java, but you should check if they work with Oracle's Java periodically as that will likely be the only Java available for OS X at some point in the near future.

Good point! Though I would hope they would as it's Oracle's Java that they run on in Windows and Unix.

Thanks

Andy
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Tue May 20, 2014 4:02 pm    Post subject: Reply with quote

amayze wrote:
That's odd. The same actions work fine in OpenOffice. I wonder if it's to do with putting all the connector stuff inside the App bundle. I'll have a go with NeoOffice 3.4.1 and see if it behaves differently.


I found a fix for this bug. I will post a test patch tonight.

It appears to be caused by a compiler bug when compiling with 64 bit on Mac OS X 10.8. The compiler's code optimization feature appeared to be scrambling the code so I fixed the bug by turning off the compiler optimization feature when compiling the OpenOffice ODBC management code.

amayze wrote:
Yes, that's why I've been looking at MySQL for the backend. I guess if OO stops working in the future I could bolt on a FileMaker frontend. Of course the other option is just to maintain old hardware that will run Java and NeoOffice 3.4.1.


Another fallback plan that you might consider is using a third-party reporting tool and keep using MySQL, dBASE, or whatever database engine for use within NeoOffice, OpenOffice, or LibreOffice. After all, the Oracle Report Builder extension that NeoOffice uses is really just an old version of Pentaho's Java-based SQL reporting tool.

Note that I am not recommending that you should switch to a third-party SQL reporting application now. But it might be worth "test driving" one or two reporting tools to see if that is a viable fallback option should Java no longer be available for NeoOffice, OpenOffice, or LibreOffice.

If it is a viable fallback and I can fix OpenOffice's ODBC bugs, we can put up a NeoWiki article and document this stuff should it ever be needed.

Patrick
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Tue May 20, 2014 9:07 pm    Post subject: Reply with quote

I have uploaded the following test patch with my fix for the hanging bug that you found when trying to view or edit a table using MySQL ODBC. Note that this bug only occurs in NeoOffice 2014 Developer Preview 2 as that version of NeoOffice is the only one compiled on Mac OS X 10.8 as 64 bit binaries. All other versions of NeoOffice are compiled on Mac OS X 10.6 as 32 bit binaries.

Can you install the following test patch and tell us if you can open and edit MySQL tables?:

NeoOffice 2014 Developer Preview 2:
http://nick.neooffice.org/test/NeoOffice-2014_Developer_Preview_2-Test-1-Intel.dmg

Patrick


Last edited by pluby on Mon May 26, 2014 9:14 pm; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Thu May 22, 2014 7:52 am    Post subject: Reply with quote

Even though you may have stopped your move to MySQL, would you be able to spare some time to the install the test patch in my last post and see if it fixes the hanging when using MySQL ODBC on your machine?

Patrick
Back to top
View user's profile Send private message Visit poster's website
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Thu May 22, 2014 12:39 pm    Post subject: Reply with quote

Yes, certainly! I managed to miss your post about the test patch somehow. It may not be till Saturday though, sorry.

I may still move to MySQL, as a longer term solution might be a web front end, though that would entail coding in three languages simultaneously, rather than the current two!

Andy
Back to top
View user's profile Send private message Visit poster's website
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Sat May 24, 2014 5:23 am    Post subject: Reply with quote

As promised! I've tried DP2 Test 1, and there is some progress. However instead of a hang I get a crash when I try to open a table with data in it.

I have a test SQL data base with two tables. Table1 has no data. Table2 has a couple of rows.

I can open Table1 under the Tables tab of the database. It successfully shows me the empty table, but does not allow data entry. I can also add Table1 to a query and execute it, with no output of course.

I cannot open Table2 without a crash. I can add Table2 to a query, however NeoOffice crashes when I try to execute the query.

Crash logs attached.

Andy
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Sat May 24, 2014 10:53 am    Post subject: Reply with quote

Thank you for the crash logs. I was originally testing with empty tables and once I added some rows via the "mysql" Terminal command, I can now reproduce both crashes.

The crash is in the OpenOffice code so I should be able to fix these crashing bugs. I will post a test patch when I have a fix for you to try.

Patrick
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Mon May 26, 2014 9:12 pm    Post subject: Reply with quote

I think that I have fixed the crashing when opening or querying a table using MySQL's ODBC driver in the following test patch.

I had to backport all of Apache OpenOffice 4.1's ODBC management code as I found that OpenOffice.org 3.1.1's ODBC code had too many serious bugs when using 64 bit ODBC drivers. I also fixed some bugs in OpenOffice 4.1's code that occur when using a "Unicode" instead of an "ANSI" MySQL ODBC data source.

Can you install the following test patch and tell us if you can open, query and edit MySQL tables without any crashing?:

NeoOffice 2014 Developer Preview 2:
http://nick.neooffice.org/test/NeoOffice-2014_Developer_Preview_2-Test-2-Intel.dmg

Patrick
Back to top
View user's profile Send private message Visit poster's website
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Tue May 27, 2014 6:33 am    Post subject: Reply with quote

I've installed DP2 Test 2 and it seems to have fixed all the crashing problems I was having.

I can now open tables which contain data and add tables to queries without crashes occurring. This is all using the "Unicode" version of the ODBC driver.

pluby wrote:
I also fixed some bugs in OpenOffice 4.1's code that occur when using a "Unicode" instead of an "ANSI" MySQL ODBC data source.

That would seem to suggest that if I do switch to Libre/OpenOffice in the future, with a MySQL backend, that I should use the "ANSI" driver!

So the only remaining step to converting a database from HSQLDB to MySQL is to find all the references to Tables within the Forms and Scripts and prepend the name of the database to them, as NeoOffice hides that when using HSQLDB!

Thanks for all your work on this Patrick. It seems a shame that ultimately I can't make use of it. Sad

Andy
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Tue May 27, 2014 8:44 am    Post subject: Reply with quote

amayze wrote:
That would seem to suggest that if I do switch to Libre/OpenOffice in the future, with a MySQL backend, that I should use the "ANSI" driver!


Correct. I found the OpenOffice 4.1.0 can only open tables using MySQL's ANSI driver.

amayze wrote:
So the only remaining step to converting a database from HSQLDB to MySQL is to find all the references to Tables within the Forms and Scripts and prepend the name of the database to them, as NeoOffice hides that when using HSQLDB!


You could also try putting the database name in your by adding a "Database = <database>" entry in your ~/Library/Containers/org.neooffice.NeoOfficeSecureEdition/Data/Library/ODBC/odbc.ini file. For example, below is my Unicode driver configuration that automatically connects to the "blah" database that I created my tables in:
Code:

[myodbc]
Driver = MySQL ODBC 5.3 Unicode Driver
SERVER = 127.0.0.1
PORT   = 3306
Database = blah


Patrick
Back to top
View user's profile Send private message Visit poster's website
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11806
Location: California, USA

PostPosted: Fri Jun 13, 2014 11:01 am    Post subject: Reply with quote

FYI. I have included the fix for the 64 bit MySQL ODBC driver connectivity bug in NeoOffice 2014 which is available in Apple's Mac App Store.

Patrick
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Goto page Previous  1, 2
Page 2 of 2

 
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

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.
Page Generation: 0.02 Seconds