ferretusmaximus
Posts: 10
Joined: Mon Aug 14, 2017 11:09 am

LO Base SQL

Thu Sep 14, 2017 11:22 am

Please can anyone tell if me the SQL interface in Base is complete?
I understand that the Base Query Wizard can only be used for SELECT queries and that straight SQL is needed for UPDATE queries.
I have tried to create a number of queries using the "Create Query in SQL View" option and only very simple SELECT style queries appear to work.
For example, taken verbatim from the HyperSQL User Guide:

CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40))

Generates the error: The given command is not a SELECT statement. Only queries are allowed.

If it not possible to run UPDATE queries and standard SQL statements in this fashion how on earth do you manipulate a Base DB from SQL?

Coming from an Access background I am used to writing Basic code to manipulate data. Without a method to run straight SQL statements makes using Base quite a challenge.

TIA....

User avatar
kyrios
Posts: 1920
Joined: Thu Sep 22, 2016 4:20 pm

Re: LO Base SQL

Thu Sep 14, 2017 3:58 pm

ferretusmaximus wrote:
Thu Sep 14, 2017 11:22 am
Please can anyone tell if me the SQL interface in Base is complete?
I understand that the Base Query Wizard can only be used for SELECT queries and that straight SQL is needed for UPDATE queries.
I have tried to create a number of queries using the "Create Query in SQL View" option and only very simple SELECT style queries appear to work.
For example, taken verbatim from the HyperSQL User Guide:

CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40))

Generates the error: The given command is not a SELECT statement. Only queries are allowed.

If it not possible to run UPDATE queries and standard SQL statements in this fashion how on earth do you manipulate a Base DB from SQL?

Coming from an Access background I am used to writing Basic code to manipulate data. Without a method to run straight SQL statements makes using Base quite a challenge.

TIA....
viewtopic.php?f=7&t=9809

ferretusmaximus
Posts: 10
Joined: Mon Aug 14, 2017 11:09 am

Re: LO Base SQL

Fri Sep 15, 2017 11:36 am

Many thanks for the quick reply Kyrios.
Could you please point me to the best place to download the LibreOffice sources that are compatible with the current Solus implementation so that I don't disrupt my existing software environment with incompatible versions.
Thanks

User avatar
kyrios
Posts: 1920
Joined: Thu Sep 22, 2016 4:20 pm

Re: LO Base SQL

Fri Sep 15, 2017 5:31 pm

ferretusmaximus wrote:
Fri Sep 15, 2017 11:36 am
Many thanks for the quick reply Kyrios.
Could you please point me to the best place to download the LibreOffice sources that are compatible with the current Solus implementation so that I don't disrupt my existing software environment with incompatible versions.
Thanks
Sorry for just posting the link but I was on my mobile phone and I don't like the small virtual keyboard. I just wanted to let you know there was something going on so you can follow/join the discussion.

It looks like Justin has committed the change so it should be available shortly when the repository will he synchronized. :)

ferretusmaximus
Posts: 10
Joined: Mon Aug 14, 2017 11:09 am

Re: LO Base SQL

Mon Mar 05, 2018 9:49 am

Having tested against Base version 6.0.1.1 there appears to be some progress.
The Base SQL facility now permits query types other than SELECT (e.g., INSERT queries).
The same queries can be executed from LibreOffice BASIC code, but with some exceptions (e.g., DELETE FROM "ATable" works from Base SQL but not from BASIC).
Some SQL instructions are not recognised in the Base SQL facility (e.g., TRUNCATE TABLE).
Am I missing something?

sunnyflunk
Development Team
Posts: 2926
Joined: Sun Mar 13, 2016 11:35 pm

Re: LO Base SQL

Mon Mar 12, 2018 12:29 am

ferretusmaximus wrote:
Mon Mar 05, 2018 9:49 am
Having tested against Base version 6.0.1.1 there appears to be some progress.
The Base SQL facility now permits query types other than SELECT (e.g., INSERT queries).
The same queries can be executed from LibreOffice BASIC code, but with some exceptions (e.g., DELETE FROM "ATable" works from Base SQL but not from BASIC).
Some SQL instructions are not recognised in the Base SQL facility (e.g., TRUNCATE TABLE).
Am I missing something?
What functionality are you after? postresql support was added as someone requested that specifically. That is likely not a good option unless you are using an external database (at least I assume that's what it does). Installing openjdk-8 will likely provide hsqldb support. I believe there are alternatives and it would be easier to track and get resolved as a task on Phabricator https://dev.solus-project.com/ rather than comments spread across multiple mediums

ferretusmaximus
Posts: 10
Joined: Mon Aug 14, 2017 11:09 am

Re: LO Base SQL

Mon Mar 12, 2018 12:29 pm

Thanks for the input sunnyflunk.
I was trying to answer the question "can I migrate from Windows to Linux without losing functionality?"
I use Access to run database queries and would therefore need a means to replicate that functionality.
Base appears to be the equivalent on Solus so I therefore tried to convert some Access code to run under Base.
I was using the forum to see if anyone could guide me with some questions.
I have concluded that I can replace Word and Excel quite easily, but anything using Access Basic needs to be rewritten in some other application as Base doesn't (yet) appear to offer the full functionality available in Access Basic.

sunnyflunk
Development Team
Posts: 2926
Joined: Sun Mar 13, 2016 11:35 pm

Re: LO Base SQL

Thu Mar 15, 2018 2:13 am

ferretusmaximus wrote:
Mon Mar 12, 2018 12:29 pm
Thanks for the input sunnyflunk.
I was trying to answer the question "can I migrate from Windows to Linux without losing functionality?"
I use Access to run database queries and would therefore need a means to replicate that functionality.
Base appears to be the equivalent on Solus so I therefore tried to convert some Access code to run under Base.
I was using the forum to see if anyone could guide me with some questions.
I have concluded that I can replace Word and Excel quite easily, but anything using Access Basic needs to be rewritten in some other application as Base doesn't (yet) appear to offer the full functionality available in Access Basic.
I would suggest running the AppImage, to see if it's any difference (it is available from here https://www.libreoffice.org/download/appimage/) to see if the firebird backend is a better replacement (this isn't enabled in the Solus build atm). If it does, I can get it enabled in Solus

ferretusmaximus
Posts: 10
Joined: Mon Aug 14, 2017 11:09 am

Re: LO Base SQL

Fri Mar 16, 2018 12:52 pm

Thanks again sunnyflunk.
Installed AppImage as recommended and can report that the same results are produced as previously. It would appear that the current limitations are part of the underlying product rather than the Solus distro.
Thanks for your help with this, I will await the next release of LibreOffice....

sunnyflunk
Development Team
Posts: 2926
Joined: Sun Mar 13, 2016 11:35 pm

Re: LO Base SQL

Sun Mar 18, 2018 11:56 pm

ferretusmaximus wrote:
Fri Mar 16, 2018 12:52 pm
Thanks again sunnyflunk.
Installed AppImage as recommended and can report that the same results are produced as previously. It would appear that the current limitations are part of the underlying product rather than the Solus distro.
Thanks for your help with this, I will await the next release of LibreOffice....
Thanks for checking, often that's usually the best way of finding out :)

Return to “Other Questions”