Many Base Features Don't Work in SQL Direct / Native Mode

asked 2018-11-19 13:23:31 +0100

PhLo gravatar image

updated 2018-11-19 13:59:47 +0100

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:

image description

Analyze SQL command toggle in Form properties dialog:

image description

Escape Processing set to False, the Form's property as revealed by MRI extension:

image description

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:

1) 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

2) 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.

3) 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.

4) 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 ... (more)

edit retag flag offensive close merge delete