Handling of special characters in SQL

[nexturejohn]

Hi, for a utility program I am writing in Base 6.3.3.2 on Windows, I need to identify the names of all the tables in an odb file. I am using Firebird, and according to their site I can use the SQL query:

select rdb$relation_name
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0);

I tried it in LibreOffice Base, Tools, SQL and effectively it works.
When though I put this same request in the SQL content of a ComboBox, it always returns a Syntax error.
I guess that the problem is the use of $ in the field rdb$relation_name.
Any ideas how to get round this, or any other SQL-based suggestions to simply get the names of all tables?

Thanks.

[Ratslinger]

Hello,

Use SQL[Native] for Type of list contents or save as Query (turn on Run SQL command directly) and use that. Both work.

Edit 2020-01-06:

Run SQL command directly can be turned on from the toolbar icon:

It is also available for selection from the menu item Edit.

Then for Type of list contents set as Query and List Content select the query name it was saved as.

[nexturejohn]

Hi,
Selecting “Sql Native” in the combobox solves the problem, thanks!

I would like to understand though what you mean by your other suggestion: “save as query (turn on SQL command directly)”. Can you explain? Thanks again.

[nexturejohn]

thankyou for your help Ratslinger! One final question on this topic: how can I write a Query to do this? Again the SQL gives me a syntax error.

[Ratslinger]

What is shown in the answer is a query using the same syntax in your question. Simply select Create Query in SQL View from main Base screen under Query. Make sure to select Run SQL command directly (it’s a toggle to turn on/off).

[nexturejohn]

thanks Ratslinger, it was this toggle that I was missing!! Thanks again for all your help, I am getting there!