FORM with DATEDIFF QUERY in FIREBIRD

[BrianStew]

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).
DB-test-FB.odb

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.

[Ratslinger]

Hello,

Will help where I can.

I’m really becoming disillusioned
about moving to Firebird

I can understand your concerns. First do not see changes anytime soon. However, it is well known the problems which exist using HSQLDB embedded. It is very old and under various conditions it is prone to losing data.

Can help here. You can change to an HSQLDB split DB. It still offers some portability and data loss is greatly reduced. As an extra benefit you can use newer versions of HSQLDB and get the benefits of additional functionality. See this post for how to:
[Wizard] Create a new ‘split’ HSQL 2.x database.

Now to your question. The are a few open bugs in regard to Firebird and using an alias for a field. See: Firebird alias bugs.

If instead of using the alias name in the form you use the system generated name, it will work. In other words no alias name:

You can use alias names if the query is changed to a view. Have attached your original sample containing both of these modifications: DB_test_FB.odb

[BrianStew]

Hi @Ratslinger,
You really are amazing, your knowledge is phenomenal.
I meanwhile feel like a complete numpty:-

I’ve tried removing the Alias names and yes that works. Many thanks for that.

1. Ive just tried looking at splitting the DB and when downloading the “Split_HSQLDB_2.3.2_Wizard_v3d.zip” I don’t see any “odb” file to open in base.
   All I see is 7 folders(Basic, Configurations2, Dialogs, driver, forms, META-INF, reports) and 3 files (content.xml, mimetype, settings.xml) What am I doing wrong?

2. I’ve also tried creating the Query as a View as you suggested but get an error message:-
   [No table format could be found/ /build/libreoffice/parts/libreoffice/build/dbaccess/source/ui/app/AppControllerGen.cxx:112]. Help?

I’m continuing to convert to Firebird, having to do a lot of structural changes and workarounds while having to do lots of double-checking to make sure the large amount of data in my tables is not lost. I’m sure I’ll have many more problems to come.

[Ratslinger]

@BrianStew,

The downloading changed the saved file name. Just rename from:

Split_HSQLDB_2.3.2_Wizard_v3d.zip

to:

Split_HSQLDB_2.3.2_Wizard_v3d.odb

As for the View, you can see this worked in the sample I posted. Here is what I did. Copied SQL from Query. Went to tables section and selected Create view.... Closed Add Tables dialog. Switched Design View off (gets me to SQL view). Pasted in SQL from working query. Tested. Saved.

[BrianStew]

@Ratslinger,
Many thanks again. Renamed the zip file to odb (Doh!) and worked, I guess I just have to copy my existing tables, queries, forms and reports across and re-establish relationships. Might have problems establishing my constraints (I can’t remember what half of them were).

View.
I right clicked on the query and selected “Create as View” from the menu which gave the error message previously mentioned, clicked OK and on refreshing the tables the View had been created anyway.

I tried your method, pasting the SQL into Tables / Create View… and got a syntax error message on trying to save, but then selected “Run SQL command directly” which worked too.

Many thanks for your help again.
Migration to Firebird continues…

[Ratslinger]

@BrianStew,

As with all questions, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.