I’ve been trying to migrate an existing HSQLDB database to Firebird (with great difficulty).
Using the migration tool was a disaster as date field values changed by a day (documented problem).
I copied the HSQLDB tables to a spreadsheet and then imported to new tables in Firebird.
Many queries failed because Firebird syntax is different, especially when using input parameters and testing for NULL or when using DATEDIFF function. Annoyingly having to set “Run SQL command directly” inside each query.
I’ve now hit a problem with using a form based upon a query that uses the DATEDIFF function, None of the calculated fields appear in the form despite them working fine in the query.
I’ve created a test database with 1 table, 2 Queries and 2 Forms (based on each of the 2 queries).
Query1 simply adds 2 numeric fields from the table to create a 3rd number (this works fine in the Query and the Query1 Form).
Query2 is the same as Query1 but uses DATEDIFF to calculate the number of days between 2 dates in the table (This works fine in the Query but shows nothing in the form for either the calculated 3rd Number or the number of days).
I’m really becoming disillusioned about moving to Firebird but am worried that at some time my HSQLDB database will fail at sometime in the future if HSQLDB support is dropped.
There is an excellent tutorial series by thefrugalcomputerguy on BASE but is based upon HSQLDB, I learnt a lot from this series but am struggling to find a similar thing based around Firebird particularly when dealing with user parameters, assigning and viewing constraints etc.