Base: Trivial SQL queries fail

[meteor]

Hi,

I’ve just set up LibreOffice Base under Linux 64-bit (though I’ve encountered the same issue under WIndows) to learn the SQL language. I’ve found that commands that reference tables (SELECT fieldname FROM tablename;) work, but commands that don’t reference a table fail. This includes commands listed in tutorials (and that execute in online interpreters). The simplest example is

SELECT 1 + 1;

Another is

SELECT FIELD(‘A’, ‘A’, ‘B’)

For both, I get the error message

Syntax error in SQL statement ./dbaccess/source/core/api/SingleSelectQueryComposer.cxx:109

In trying to google this, I’ve read that it may be an interpreter problem and the fix is to enable the ‘Run SQL commands directly’ option. However, all commands (including the ones that work without it) fail with that option enabled. I get the error message

The data content could not be loaded. /connectivity/source/commontools/dbtools.cxx:751

And below that

Access is denied. FIELD in statement [SELECT FIELD(‘A’, ‘A’, ‘B’)] ./connectivity/source/drivers/jdbc/Object.cxx:175

I assume I haven’t set up something correctly. (I didn’t to do anything except installing Base with “sudo apt-get install libreoffice-base” and then creating an empty database.) But I don’t know what to do about it.

Any help would be appreciated!

[RobertG]

If you are working with Base and the internal databases all this kind of queries wouldn’t work. There is needed a table in the statement. Without FROM "Tablename" you won’t get a result.
So you could start

SELECT 1+1 FROM "table"

if you have created a table with this name. You would get as much results as there are rows in the table.
There are databases (like MySQL), who will allow SELECT 1+1, if you choose the direct SQL execution in the query editor or Tools → SQL. The second query you have tested will also work on MySQL/MariaDB, but won’t work with the internal databases.

Please have a look at the Base documentation of LO: English Base Guide. If you understand German: the Base Handbuch 7.2 will show all about HSQLDB and internal Firebird. The internal databases won’t work with this kind of queries you have tested. You have detected a tutorial for MySQL. So you need a MySQL or MariaDB installed on your system. Base could connect to this kind of databases.

[meteor]

I see. I specifically need understand how internal databases through libre-office (or the microsoft analog) work, so if that’s a fundamental restriction, then that’s fine. I was mostly worried because I assumed the behavior signaled some problem with my setup.

I will probably spend some time with the documentation you’ve linked; it seems pretty well written. In any case, thanks.