Query Error in Converted Firebird (solved)

[JohnD]

Seeing that in LO 6.3.0 the migration from HSQLDB to Firebird seemed to have problems corrected with numeric and decimal errors I charged ahead with conversions of my very simple database files.
I have now run into a problem with some queries that contain date criterion. I am getting this error ----

The data content could not be loaded.

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 591
*‘2019-01-01’
caused by
‘isc_dsql_prepare’

The criterion entered is ---- >= #01/01/2019#

Obviously something has changed with the date syntax and/or sql.

Any help would be appreciated.

[Ratslinger]

Hello,

Don’t have a problem running simple test. Best to post your SQL.

Have you verified field types converted correctly - especially dates?

As for SQL - manual found here → Firebird documentation.

[Ratslinger]

This may also be of help → Migrating HSQLDB Base files to Firebird Base files

[JohnD]

This one was a bit confusing (especially for an old thick-headed guy like me.)

After some research and experimenting I thought I found the solution to be changing the query criteria to using the BETWEEN syntax. However when I checked other queries that used the criterion of >= ‘01/01/2019’ they worked correctly. Also a form filter of ( “Medical”.“Date” >= {d ‘2019-01-01’ } ) worked correctly.

I then found only two or three similar queries (out of 16) that didn’t work but after going into edit, making no changes, and then exiting with a save, voila - they worked!

Apparently the conversion left something that was not correctly translated but by simply going into the edit mode and exiting it got corrected. ???

So I am happy with the results and maybe this little exercise might help someone else.

Ratslinger - Thanks for the link to the Firebird documentation!