Base UNO statements fail on Mac

English
, , ,
#1

Before I waste time chasing errors in macros that work perfectly well on W10 PCs but fail with unhelpful error codes on Mac, can anyone tell me if UNO commands in macros running on Big Sur have been extensively tested, or am I in uncharted territory?

I have a database running in 7.0.5.2 LO, HSQLDB embedded, including several macros in Basic which work fine on an Intel Core 7 powered PC running Windows10. When this same .odb is run on a Macbook Pro M1 running Big Sur, the macro code fails on some lines of code like “Set oFS = CreateObject(“Scripting.FileSystemObject”)” or “Stmt.executeUpdate(strSQL)”.
In the latter case, LO crashes and has to be forcibly killed to regain control. I upgraded LO to 7.1 on the Mac to see if it made any difference - it doesn’t.

Has anyone else seen such behaviour? If so, how did they get around the problem?

Thank you in anticipation of some helpful advice.

#2

Set oFS = CreateObject(“Scripting.FileSystemObject”)

That code is Microsoft technology and only works on Windows. It doesn’t even work in MS Excel on a Mac.

Stmt.executeUpdate(strSQL)

That is standard LibreOffice Basic code so it should work on a Mac just fine. However I do not have a Mac so I cannot confirm this. The reason for the crash is difficult to guess given the limited information in the question, such as the contents of strSQL. Have you tested running the SQL directly with Tools → SQL?

To write LO Basic macros, see www.pitonyak.org/oo.php. Nearly everything in Andrew’s book should work on a Mac.

#3

I didn’t try that on the Mac but it all works fine on the PC. If it works in the SQL window on the Mac and then does not execute in the code, what would that mean? Likewise if it does not work in the tool but the same statement works on the PC what would that show us?

I’ll report back later today when I have had time to try it.

#4

I have now run the query in tools->sql on both platforms, and also run it via the macro. My intial conclusion that LO was crashing was wrong. If I left it for long enough the query completed successfully and the macro exited correctly.

So what I was seeing on the PC was the process completing very quickly, but on the Mac taking an age and preventing any other use of LO in the meantime. Interestingly if I run the the MAC odb file in W10, after running it in the Mac, it seems to inherit the behaviour of very slow to complete.

So in conclusion, a completely different problem to the one I alleged. I’ll keep investigating, but if anyone has any ideas I would welcome them. At least I know my logic works even if it isn’t proving to be efficient!

On the other problem I mentioned, thank you for pointing out that Microsoft technology isn’t supported on Mac. I will have to work out the UNO way of achieving the same end. Thank you Jim K for your help on this.

#5

Doing more testing on the Mac, I found that after multiple operations with a very long wait, the system will suddenly start working as expected and execute in seconds. I also did an experiment with short SQL statements and they took many minutes to complete as well, so the problem is not related to the length of my SQL statements.

Once the time is down to seconds, it will operate that way consistently. If I save the odb but don’t quit Libre Office, it continues to work properly. If I quit Libre Office and then open it again, the behaviour goes back to square one.

Does this mean that there is some config or initialisation problem going on? Any thoughts? Where should I be looking next?

#6

HSQLDB embedded is a relatively weak setup, recommended only for testing, so it’s easy to see how that could cause initialization slowdown. I’d try either switching to a split database setup with an upgraded version of HSQLDB, or even better, switch to a more well known engine such as PostgreSQL.

If you must use HSQLDB embedded then one thing to check is what type of Java runtime your system is using, because HSQLDB depends on it. Also maybe it is having trouble unzipping the embedded engine files. That’s why split setups are so much better.

#7

Thank you for your wise advice Jim. I have a parallel development going using LO as a front end to MySQL, and you are right, it hasn’t suffered from the issues I am seeing. I was hoping to have a single application for my one user database, but I think that I’ll have to relent and have LO front end and a separate localhost incidence of a database (MySQL as I am familiar with it and Workbench).

Thank you again for your expert advice.

#8

The evidence seems to point to embedded HSQLDB not being robust enough for the stress I am putting it under. I have now established it does work on PC and Mac, but unacceptably slowly on a Mac most of the time, with no obvious cause.

The solution is to use a local instance of the database part, and use LO as the front end. Otherwise I shall spend too many hours chasing vague sympoms with very uncertain chance of identifying the cause (or indeed causes) of the problem.

“Horses for courses” as the old adage goes. Thank you for listening and advising - much appreciated.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.