I have a LO Base V7.2 database using a SQLite database throught ODBC on Win10 and I’m trying to query the query schema. LibreOffice Base Guide 6.4 Appendix A has a section Information tables for HSQLDB and another on Further information on database archive files.
Why can’t I bring up any of the table information using the names? eg: SELECT * FROM ″INFORMATION_SCHEMA″.″SYSTEM_COLUMNS" returns:
Good one. After the details on queries in the Base database!
Please note: All content of Base Guide is written for the internal databases HSQLDB and Firebird. If you are connecting to external databases (which is also described) you have to switch to the special SQL description for this databases.
As I understand it the tables are in SQLite and the queries, forms and reports are in the Base database (??) Which would be HSQLDB (??)
If you are connecting to an external database the tables (and also the SQL-code) will be a special code for this external database. There are many external databases LibreOffice Base could connect to. You have connected to SQLite, so you have to look for a description of the SQL-code for SQLite.
If you create a new embedded database you could use HSQLDB or, if experimental mode is set, Firebird. This kind of database is included in the *.odb-file, which will be created by Base. The description in the Guide is made for the embedded databases, not for external databases.
There is much code, which could also be used for many external database, but you can’t use all the SQL code, which is written down in the Guide, for SQLite. So there are queries, which would run with the internal HSQLDB, but won’t run the same way with the internal Firebird database and won’t run with SQLite. Also there will be code, which couldn’t be understand by HSQLDB. The Guide will only show the code for HSQLDB and Firebird.
All this has nothing to do with the query editor or forms or reports. They will work well if you are using the SQL code for your special database.
>sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open chinook.db
sqlite> select * from sqlite_master;
chinook.odb uses chinook.db with an odbc connection.chinook.odb contains query qAlbum defined as: SELECT * FROM "albums"
How is the system table for query qAlbum accessed?
Try to replace your select * from sqlite_master;
with
select * from sqlite_schema;
when I’m reading the link above right.
But if you use the sqlite-cli you may start with .tables to get a list of available tables
PS: There is also a portable Software SqliteStudio available to get a second view and useful if you extend a sqlite-db
You can type the same you use in Base at the cli-interface
SELECT * FROM albums
.
.
The queries are in the chinook.odb xlm too. content.xml:
...
-<office:body>
-<office:database>
-<db:data-source>
-<db:connection-data>
<db:connection-resource xlink:type="simple" xlink:href="sdbc:odbc:chinook"/>
<db:login db:is-password-required="false"/>
</db:connection-data>
<db:driver-settings db:base-dn="" db:system-driver-settings=""/>
-<db:application-connection-settings db:max-row-count="100" db:append-table-alias-name="false" db:is-table-name-length-limited="false">
-<db:table-filter>
-<db:table-include-filter>
<db:table-filter-pattern>%</db:table-filter-pattern>
</db:table-include-filter>
</db:table-filter>
</db:application-connection-settings>
</db:data-source>
-<db:queries>
<db:query db:name="qAlbums" db:command="SELECT * FROM "albums""/>
</db:queries>
-<db:table-representations>
<db:table-representation db:name="albums"/>
<db:table-representation db:name="vqAlbums"/>
</db:table-representations>
</office:database>
</office:body>
Aim is to get the query column attributes, save on close and restore on opening (Bug 86315 workaround). These attributes seem to be in settings.xml. Surely this doesn’t involve manipulating xml directly.
You are right. Doc is either false or misleading or I have to update. But sqlite_master seems to work for me.
Base *.odb database file is not necessarily a database and certainly not for a SQLite file - see Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 3: OpenDocument Schema
Yes, that works but it only has access to tables, views and associated indexes in the SQLite database. I want access to queries [and add forms and reports too].
.
Edit: Still can’t find table column layout attributes.
Wrong place to look. Query and Form are stored in the .odb, so SQLITE can’t tell.
You have to ask Base then…
.
I think the extent of query attributes is the sql shown in content.xml and the default query result table layout (in settings.xml). The query result is presented in a table using default formatting which can be changed but not saved. Tables use and save the same formatting so I’m now looking for table formatting (in the .odb file) to see if I can apply it to the query result table.
This seems a waste of time. There is tdf#86315 (as you already noted) for saving query format.
Until resolved just create a form with a table control.