When working with an external database such as MySQL, there are times when it is logical to Run SQL command directly, also known as Native SQL, also known as Analyze SQL command = no, also known as Escape Processing = False. Yeah, lots of terms for the same thing in Base. I’m not going to go into lengthy details to describe when it is a good idea to enable this mode, as that’s off topic. Here are some images for those who don’t know what I’m talking about. All these images show the same thing. It’s a toggle that either tells Base’s internal query processor to analyze the SQL and potentially rewrite it according to its own internal syntax or to pass the statement directly as written to the back-end database (such as MySQL).
Run SQL command directly button/toggle in SQL command window:
Analyze SQL command toggle in Form properties dialog:
Escape Processing set to False, the Form’s property as revealed by MRI extension:
All those refer to the same thing, just different terminology in different interface and dialog contexts.
The problem with this “Direct/Native SQL” mode is that many features within Base become unusable with it turned “on” (when the SQL is passed on directly as written without being analyzed).
The following features are disabled with a Direct/Native SQL form source:
-
If a Direct SQL query is the data content type of a main form, any subforms can no longer be filtered via master/slave primary/foreign key links
-
The form can no longer be given a Filter (like
id= 1) through the Form control dialog, nor can the properties be effected via macros. The properties can be set, but they do nothing. -
The form can no longer be given a Sort (like ‘id’ ASC) through the Form control dialog, nor can the properties be effected via macros. The properties can be set, but they do nothing.
-
The form’s LIMIT (aka FetchSize & MaxRows pair of properties) can no longer be effected via macros.
Basically, nothing can be “injected” into the SQL when in Direct SQL mode. This kinda makes sense too. For example, if I use a direct SQL query like this:
SELECT *, COUNT(`id`) FROM `account` WHERE `name` LIKE '%John%' GROUP BY `name` ORDER BY `name` ASC
and run it in Direct Mode, how could Base add a filter, since a filter is akin to inserting a WHERE into the SELECT (and my sample already has a WHERE)? Or how could you change the ORDER BY if it’s already determined in the query? Answer is, you can’t if in Direct Mode. So using this mode makes it so MANY properties and features cannot be used. The master/slave primary/foreign key is particularly annoying.
I can’t exactly say this is a “bug” since it kind of makes sense why it works that way. It’s why they call it “Analyze SQL command.” Basically, it is giving Base permission to parse the query and insert a filter, an ORDER BY, a GROUP BY, a LIMIT, and other features that change the query. If in Direct/Native SQL mode, Base just passes the query to the back-end without changing it. You might be wondering…
IS THERE A QUESTION IN HERE?
Kind of, but it’s also informational or encouragement for those who, like me, have learned through much toil that Base functions this confusing/limiting way related to Direct/Native SQL mode. And maybe someone will find it interesting and start a dialog/conversation on the topic. If this post annoys you because of its length, just ignore it. I’m not fishing for an answer unless someone has solved all this already and has a great resource to point me to.
I am writing a bunch of macros to overcome these perceived weaknesses, but my efforts probably won’t relate directly to the challenges/situations of others. I just wanted to make sure other people kept this in mind or were aware of this.
The worst thing is, if you need to have a subform within a main form that is sourced by a SQL statement in Direct/Native mode… well, you CAN’T. The subform will ignore the master/slave and show all table results instead of the one matching record. So that means subforms become pointless in that situation, and you might as well write macro code to filter the “subform” manually based on a primary key read from the main form and WHERE primaryKey = foreignKey into the subform’s source query. This is essentially what I’m having to do.
Again, most people here will likely respond, “Why use Direct/Native SQL mode at all? Why not just have the form’s source be a table, and let Base interpret all the queries? Aren’t you doing more work than is necessary?” I can’t give a full answer because it’s a long one, but I have a reason. It relates to performance on a remote MySQL installation (NOT localhost, not XAMPP, not MySQL Workbench, no, no, no, etc… but YES where MySQL is installed on a remote web host in another timezone managed by a hosting company, and I can then point to the data from multiple computers and locations). Some people seem confused by the term “remote” but that’s what it means - that the data is NOT on my computer.
Base’s methods are insanely slow, inefficient, and they cause LO to crash constantly when accessing MySQL remotely (at least that has been my experience thus far)… so I’m writing more efficient queries in Direct/Native SQL mode that only return the exact one or few results needed without doing the junk * select everything in the table massive boggy grabs into memory (Base’s default, Elephant/Diplodocus/Apatosaur/Whale behavior). My method has thus far increased the speed of the form sources and form refreshes tenfold at least, and stability has also improved.
Handling all the query building via macros is more work because it relies less on Base’s user-friendly, built-in form dialogs, subforms, filters, etc. However, without macros and Direct/Native mode, I would probably have to skip Base as an option for the business software I’m writing. Constant crashes and 10 to 30 second waits for forms to draw is completely absurd and unacceptable in this day and age. When you have a table with many columns and 46000+ records, and Base tries to load the whole thing into memory… and it’s trying to do that over the internet no less… well it’s kinda obvious why it crashes and hangs all the time. No thanks, I’ve got a better way of doing it.
I’m disappointed that Base doesn’t do more “out of the box” and requires so much customization. I’ve read the development updates, and while Calc, Writer, Impress and the relatively new online collaborative format gain features, it seems like Base is all but abandoned by developers in the last several major version releases. It’s discouraging, but I’ll continue trying to make Base work unless I find too many more gotchas and caveats that destroy what I’m trying to accomplish. Hundreds of hours have been invested already, and I want it to be a viable solution. It’s way too simple of a database to require so much time. I’m not a MS fan, but I doubt Access would have given me so much grief! Sigh. I still have some hope remaining.
As usual, your experience might differ depending on LO version, operating system, etc. My platform is Windows 10 x64, LibreOffice 6.0.7.3 (x64), and I’ve tried all MySQL connectors - direct SDBC extension (no longer works because it’s 32-bit), ODBC connector and JDBC connector (both the latter are 64-bit from MySQL website). Thus far, I’ve found ODBC to be considerably more stable than JDBC (for remote, not necessarily localhost MySQL), but both connectors have given me plenty of grief.