HI.
I would like to know if with the integrated HSQLDB, it is possible in a validation constraint to force the entry of the number of characters defined in a CHAR column. I tried this:
COLUMN_NAME CHAR (10),
CONSTRAINT CK_COLUMN_NAME CHECK (LENGTH(COLUMN_NAME) = 10)
When creating the table via SQL, no error message, but the constraint has no effect on input.
Thanks.
The missing chars of a CHAR field are filled with spaces.
COLUMN_NAME CHAR (10),
CONSTRAINT CK_COLUMN_NAME CHECK (LENGTH(RTRIM(LTRIM(COLUMN_NAME))) = 10)
That’s not really the question I was asking. What’s the point of defining a fixed number of characters if it’s not mandatory to enter the defined number?
By example, if an employee has to type in a 10 digits reference on an input form and forgets one, there may be consequences if a blank is automatically entered instead. With the constraint I’ve indicated, for example, PostGreSql refuses to validate the entry if the number of characters is less, which helps avoid careless mistakes. I was wondering whether a similar method existed in the embedded version of LO Base’s HSQLDB.
The embedded version of HSQL is Chapter 9. SQL Syntax
<installation_directory>/program/classes/hsqldb.jar is the unmodified Java library (compressed 754975 bytes).
How about CHECK CAST("XID" AS LONG)>999999999 ?
By the way: Embedded databases should never be used for productive tasks. As your database grows, it becomes more likely that you lose all your data because the whole db is extracted to a temp. directory and wrapped back into the odb (which is a zip archive) when closing the session.
It is fairly easy to re-connect your Base document with an extracted stand-alone version of your database and upgrade the driver. Apache OpenOffice Community Forum - [Python] Macro to extract and reconnect embedded HSQLDB - (View topic)
It is fairly easy to connect Base with PostgreSQL, MySQL or any other relational database providing access via ODBC or JDBC. There are also native LibreOffice drivers (SDBC) for MySQL and Postgre.
I have installed (on the same PC) the latest version of HSQLDB to take advantage of the advanced options, but I can’t connect from LO.
As for the constraint you propose, it’s indeed very good, except if a reference or number is alpha-numeric.
Base is constructed around HSQL 1.8 which is delivered together with the office suite. You can install as many HSQL versions to your system as you want. LO will use its own for the embedded databases.
In the above link, I outlined the procedure to convert embedded to stand-alone HSQL, which can be upgraded up to 2.4.1 and from 2.4.1 to recent versions.