[Solved] Native SQL

[gkick]

Showing my ignorance.
When putting together a list box, the data source property may be a table, sql or native sql. When would one use Native sql - when the backend is MySQL or Postgresql ? At the same time would that necesitate always running queries in direct sql mode?

Thks

And the learning curve continues...

[Villeroy]

This has nothing to do with ignorance. In fact this matter is difficult to explain. First of all "native SQL" is the same as "direct SQL".

Simply use SQL in parsed mode which is the opposite of direct/native SQL mode. This is the default mode in which you can use all the benefits Base has to offer. In this mode you may be confronted with 2 "escalation stages":
1) The query may be too complex to be edited in the graphical query designer. When you see such warning message when opening a query for editing, you know that you are limited to the textual SQL editor which is not a bad thing. You can copose very complex nested SELECT statements with parameters, aggregations, lots of functions, aliases and what not. Sometimes even your slightly more advanced statement is too much for the graphical query designer even if it is perfectly valid SQL. One example is a query where you join two tables with operators other than the equal operator as in: SELECT ... FROM "A" JOIN "B" ON "A"."X" >= "B"."Y". When things become very difficult, I use a code editor with syntax highlighting and find/replace. WIth external HSQL I sometimes start the "SQuirreL" which is a development tool for JDBC databases.

2) You may get some error because Base is not able to make any sense of your query even though it is syntactically correct.. I don't mean any syntax error nor no-such-column error. If you face a too-complex-to-be-parsed error message this is the point where you should try the direct SQL mode using a toggle button on the SQL editor's toolbar or using menu:Edit>"Run SQL directly". If your query does run in direct mode, you have one of these queries which is well understood by the underlying database enginge but not by Base. Base does not try to interprete a direct SQL statement. Instead it passes the literal SQL string down to the database engine and waits for some record set or error message in return. If you are connected to some external database engine, the direct mode allows you to run a lot of stuff that is specific to this database engine, possibly beyond the naked SQL standard.
The most common use case for HSQL in direct mode is a UNION where you merge 2 similar row sets into one by appending one set after the other: SELECT "A","B" FROM "Tbl1" UNION SELECT "X","Y" FROM "Tbl2" returns a row set with columns "A" and "B" with data from "X" and "Y" appended (if "X" and "Y" are the same column types as "A" and "B"). The Base parser can not handle UNION queries, not in graphical mode nor in text mode.

Even if the query is wrong for some obscure reason, it helps to turn on direct SQL mode because the error messages returned by HSQL are more helpful.

The row sets returned by direct SQL mode are not editable. They can't have named :Parameters and they don't work with form/subform relations. When used for a parent form or subform, you always get the unfiltered set in the subform.

Views are stored in the realm of the underlying database engine. Views are always "direct SQL"

P.S.
I forgot the worst stage of escalation when editing queries in Base, This is when the graphical design view turns your valid query into something else that is syntactically correct but not working as intended. Something like this: ... WHERE (A=B OR B IS NULL) AND (C=D OR D IS NULL) which you can find quite often in "power filtering" examples with optional filter criteria. You open the query for editing, the graphical shows something inomprehensible, the result set (if any) is wrong and when you switch to text mode, this is not the query you have written. The query works perfectly well in parsed mode and the graphical design tool "believes" it can handle this although it actually destroys your work. If this happens to you, close the query without saving and re-open with the SQL editor.

[gkick]

Thank you Villeroy, a lot clearer now.