I use Ubuntu 20.04 and LibreOffice 7.3.2.2
I am trying to convert MySql files to HSQLDB with Razor. The above is what I get when trying to use SQL to create the table and to populate it. This about as simple as I can get it. I have checked syntax on other sites on internet. I find this HSQLDB totally frustrating to use!
Thank in advance for help in this.
Hello,
Your SQL to create the table is incorrect. Field B was defined twice and a comma before the ) signifying another field to come. It should be:
CREATE TABLE T1(
A VARCHAR(61) DEFAULT 'NULL',
B VARCHAR(24) NOT NULL,
C VARCHAR(19) DEFAULT 'NULL'
);
While this will work as well as adding the data with an insert statement, you cannot add records through other mechanisms because when using Base a Primary key is required.
Also unsure as to why you are migrating from MySQL. Base is a front end to a variety of databases including MySQL. HSQLDB embedded is old and Firebird embedded has been discussed for some time (available as an experimental function).
You can connect to MySQL using a native connector, JDBC or ODBC. See the documentation > Base Guide. See Chapter 2 for further information.
Hi,
Thanks for your reply. The second B in the code was a typo. I actually used C in the test run I did.
I guess I was trying to keep everything LibreOffice oriented. I will go back to using MySql on your recommendation.
The error messages indicate that you enter data definition and data manipulation statements into the query editor. Anything that does not start with SELECT has to be entered in Tools>SQL …
Hi,
Thanks for your reply.
Based on Ratslingers email I think I’ll just go with MySql. I have done this before but thought it would be better to keep everything part of LibreOffice.
The answer remains the same no matter which database you use with Base. The query designer is for SELECT statements whereas the SQL window takes data definition statements (CREATE, ALTER, DROP) and data manipulation statements (INSERT, UPDATE, DELETE).
This depends upon the database, connector and statement used. Statements such as INSERT and UPDATE will work in Query SQL view with Run SQL directly on. You get a message saying the query doesn’t return a valid result set but the statement does work.
.
Just re-tested with MySQL 8.x (some with 5.7.x) using ODBC (JDBC doesn’t work). Also minimal testing currently with Firebird embedded.
Thanks Villeroy,
I’ll remember that!
R