Search code examples
c++firebirdsqlapi++

View creation works in Firebird 3.0 but not in version 4.0

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.

Solution

  • 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.