HSQLDB SQL in Firebird

[Zoarsk]

Hi

I have been getting to grips with Base, coming from a MS Access background. I have just installed 6.1 which has “Firebird” as a SQL engine option, and now I’m stuck on something simple.

I have a number of queries that need converting, the simplest example is below. In it’s HSQLDB format

SELECT Top 10 “ClassA”, “ClassB”, “ClassC”, “Date”, “ClassA” + “ClassB” + “ClassC” AS “Total”
FROM “tblData”
ORDER BY “Total” DESC, “Date” ASC

this works fine.

In what I believed is the correct Firebird form:

SELECT First 10 “ClassA”, “ClassB”, “ClassC”, “Date”, “ClassA” + “ClassB” + “ClassC” AS “Total”
FROM “tblData”
ORDER BY “Total” DESC, “Date” ASC

So changed “Top” to “First”. The issue is the Firebird SQL runs fine in design mode “F5”, however if I create a form, it runs and returns the top (First) 10 records correctly and in order but there is no value in the “Total” column.

Obviously done something wrong but for the life of me I can’t find an answer, any help appreciated.

[Ratslinger]

Hello,

Don’t believe it is you doing something wrong. My testing shows error but in a different sense. If your field type is of Numeric or Decimal then the problem may be in Base. See → Bug #118043.

My test was selecting field1 + field2 = field3 (350.00 + 1650.00 resulting in 200000) using Firebird embedded in LO v6.1.0.3 but the same select produced a correct result in Firebird 3 server (2000.00).

BTW - Firebird embedded has been available (experimental) for some time now, not just v6.1

Edit 2018-08-25:

Have performed some further testing on this. The table defs were field type numeric 12,2 and a select on the individual fields were correct. As mentioned above simple math reflected an incorrect result. However, if the math is cast as numeric it works:

SELECT Field1, Field2, cast(Field + Field2 as decimal(12,2)) as Result FROM MyTable

A cast to numeric or decimal produced the correct results.

[Zoarsk]

Thanks for the reply, at least I’m not going mad then. Guess I will have to stick with HSQLDB for now, I wonder if they will ever fix it, unlike the Calc Bugs that seem to have been there for ever. Cheers

[Ratslinger]

@Zoarsk As another alternative, as I have done for months now, you can use Firebird 3 server (my alternative - now moving from MySQL to PostgreSQL). You can modify everything in preparation for embedded and even use DB’s in embedded now. Fairly easy to move over.

Have even successfully ported a fair size MySQL DB to Firebird embedded through Firebird 3 server. Side by side running for months (daily use) and no problems.

[Zoarsk]

I may look at that, although the current work is corporate, is LO a viable alternative to well known commercial Office product, that will be a NO then. You don’t happen to be any good at LO Macros in Base? Have got so far but now stuck on how to write data back to table. Anyway thanks for your help

[Ratslinger]

@Zoarsk As for LO being a viable alternative, it always depends upon requirements.

Have worked with Base (and other LO areas) using macros for some time. There are many others in this forum with such experience. You should present this as a new question and please include specifics about environment (LO version, OS, DB, etc.). What code you have that is not working for you will also be of help.