4

I created a music database application a few years ago in C++ (Code::Blocks + wxWidgets + SQLAPI++) and Firebird as the database server (running as a service in classic mode) on the Windows platform (v10). It creates a SQL database with tables, views, triggers, generators.

So far, it has been running perfectly up to Firebird 3 (Latest version). Now Firebird 4.0 is out, I thought I try it out.

In order to narrow down on the problem, I created a new app that only creates the database, tables, triggers, generators,and only 2 views which are focused around the problem area.

The code for vew_AlbumDetails I use in my test app is:

CREATE VIEW vew_AlbumDetails (Album_Name, Album_NrSeconds)
AS
SELECT b.Album_Name, SUM(a.NumberOfSamples/NULLIF(b.SampleRate,-1))
FROM tbl_Tracks a
INNER JOIN tbl_AlbumNames b ON a.AlbumName_ID = b.ID
GROUP BY b.Album_Name
ORDER BY b.Album_Name;

The code for vew_ReportDetails I use in my test app is:

CREATE VIEW vew_ReportDetails (Album_Name, Album_NrSeconds)
AS
SELECT b.Album_Name, a.NumberOfSamples/NULLIF(b.SampleRate,-1)
FROM tbl_Tracks a
INNER JOIN tbl_AlbumNames b ON a.AlbumName_ID = b.ID
ORDER BY b.Album_Name;

When I create the database with Firebird 3 running as a service, and open it in FlameRobin, everything is OK. In VIEW vew_AlbumDetails, Album_NrSeconds type is BIGINT. (see image below)

enter image description here

When I create the database with Firebird 4 running as a service, and open it in FlameRobin, everything is NOT OK. In VIEW vew_AlbumDetails, Album_NrSeconds type is (16). (see image below)

In VIEW vew_ReportDetails, Album_NrSeconds type is BIGINT. This is OK (see image below)

enter image description here

In FlameRobin, I also manually added a new view (vew_Manual_Added_View) with the same code as for vew_AlbumDetails (except for the name). The code is shown in above image.

Strange is that the type for Album_NrSeconds is now DOUBLE PRECISION instead of (16) under Firebird 4 service or BIGINT under Firebird 3 service.

My problem is the following when running Firebird 4 as a service:

My Music app creates the database without errors, but with vew_AlbumDetails, Album_NrSeconds type as (16). It crashes without any error message when the vew_AlbumDetails is being used to show an overview of the stored albums. Album_NrSecondse being of type (16) is causing this.

There are 2 things I do not understand when using Firebird 4 as a service.

  1. Why is Album_NrSecondse of type (16) when creating vew_AlbumDetails with my app?
  2. Why is Album_NrSecondse of type (Double Precision) when the exactly same code is used for adding a view manually?

Is there a bug in Firebird 4.0 that causes this strange behaviour?, or do I need to adapt my code somehow?

I hope somebody can help me understand what causes the different behavior between Firebird 3.0 and 4.0, and sends me on the way to a solution.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Ruud
  • 97
  • 6
  • `When running Firebird-4.0.0.2496-1 the view field ALBUM_NRSECOND shows as (16)` - shows WHERE ? what is the application that shows it ? That application probably was not upgraded to FB4 yet, so you have to check data type using Firebird's tools instead. `It fails on " SUM(a.NumberOfSamples/NULLIF(b.SampleRate,-1)) ". More specifically on the division` - HOW EXACTLY does it fail ? we do not see what you do see on your screen. – Arioch 'The Dec 20 '21 at 08:46
  • 1
    Please provide a [mre] of the problem, which includes DDL to reproduce this (and reduce the number of columns in that example to only focus on the issue at hand) This has likely to do with the fact that `SUM` in this case produces an `INT128` result in Firebird 4.0, where it produced `BIGINT` in Firebird 3.0, and your driver or framework does not support this new type. You could specify the `DataTypeCompatibility` parameter in `firebird.conf` or `databases.conf`, or use `SET BIND` after connection (or `isc_db_set_bind` during connect), to map the types. – Mark Rotteveel Dec 20 '21 at 10:57
  • Also, your wording doesn't make clear if you get an explicit error when creating the view, or if the problem is with using the view. If you get an explicit error, then include that error in your question. – Mark Rotteveel Dec 20 '21 at 11:00
  • @ Mark Rotteveel: I am using an older version of SQLAPI++. So I changed to DataTypeCompatibility = 3.0 in firebird.conf . Unfortunately the result is unchanged after reboot. Album_NrSecondse remains of type (16). – Ruud Dec 22 '21 at 08:45
  • @Ruud why would you not either stay within FB 3 then or update SQLAPI++ ? It would be more natural and safe way than trying to find workarounds. But if you firmly settled on workarounds, then maybe you can add into your SQL types coercion? example: `select cast( 1 + 1 * -2 as VarChar(20) ) from RDB$DATABASE` https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=a5c3233527f4d56f655ec82ae6c87916 – Arioch 'The Dec 22 '21 at 09:04
  • Please also provide the DDL of the table, or at minimum the types of `tbl_Tracks.NumberOfSamples` and `tbl_AlbumNames.SampleRate` – Mark Rotteveel Dec 22 '21 at 09:26
  • The setting only changes the datatypes in query result columns and statement parameters, it doesn't change the column information of the database metadata. The fact that the type remains `(16)` is probably because it is an `INT128`, or possibly a `DECFLOAT(34)`, and - at a guess - FlameRobin doesn't know this type and thus reports its size in bytes or something like that. The important thing is, did changing `DataTypeCompatibility` fix the problem for your application or not? – Mark Rotteveel Dec 22 '21 at 09:26

1 Answers1

2

I have added 'DataTypeCompatibility = 3.0' to both databases.conf and firebird.conf.

The datatype for Album_NrSeconds is now NUMERIC.

My application runs flawlessly under Firebird 4.0 as a service after these 2 edits.

Thank you Mark Rotteveel for your suggestion. Its much appreciated.

Ruud
  • 97
  • 6