Firebird 4.0 Unknown sql type err.

[Henry Olive 5]

I wish everyone a healthy day

Regarding below SQL I'm getting Unknown sql type err.msg. 

What is wrong ?

 

SELECT ID, (UPRICE * QTY) AS PRICE  /* Cause of the problem is multiplication */
FROM TABLE1

 

Thank You
 

[Vandrovnik 214]

If your app is not able to handle new datatypes introduced in Firebird 4, you can use

DataTypeCompatibility = 3.0

in firebird.conf

 

You can also typecast the result of the multiplication to something you can handle.

[Serge_G 87]

Please, when you post a question like this, don't forget to give us table description and in which context (Delphi+Component, GUI etc.!

 

An advice : Get the Firebird 4.0 migration guide https://www.firebirdnews.org/migration-guide-to-firebird-4/  or read short version here

Quote

Extracted from Firebird 4.0 migration guide :

Some programming languages, components, and access drivers may not support Firebird 4’s new data types.

For example,.... when we try to open a query that accesses a table with decfloat fields using a version of the IBObjects component that (so far) does not support decfloat you get an unsupported type column error

I think that one problem is how you have migrated your Interbase BDD to Firebird and I persist, in my mind, version 3 would have been a better target 

 

As Vandrovnick said typecasting should a solution but try also a ROUND function (especially if QTY is not an integer)

SELECT ID, ROUND(UPRICE * QTY,2) PRICE  -- To test
FROM TABLE1 

Note : did you test without parenthesis?

SELECT ID,UPRICE*QTY PRICE
FROM TABLE1

Edited November 18, 2021 by Serge_G

[Henry Olive 5]

Thank you so much Serge, Vandrovnick

Serge, i'm so sorry, you are right,  i should have written more info

UPRICE = NUMERIC(18,5)

QTY =NUMERIC(18,2)

I'm trying to execute the sql in FlameRobin ( not yet in Delphi )

with ROUND  i get same err.msg.

w/o  Parenthesis  i get same err.msg.

I'll try to change DataTypeCompatibility = 3.0  in Firebrid.Conf  tonight
Again Thank you so much for the time you spent for me.
 

[Henry Olive 5]

I just learnt that ( In this forum )  there is no support FB 4.0 in Delphi
so i'm thinking to move to FB 3.0

 

[ertank 27]

I would still stick with FirebirdSQL v4.0 and use advised DataTypecompatibility = 3.0 parameter.

You should be just fine and moreover, you will only need to disable that parameter once Delphi adds support for it in the future (most likely next year).

[Virgo 18]

You could also check server version on connect and execute

SET BIND OF TIMESTAMP WITH TIME ZONE TO LEGACY;

SET BIND OF TIME WITH TIME ZONE TO LEGACY;

SET BIND OF DECFLOAT TO LEGACY;

SET BIND OF NUMERIC(38) TO LEGACY;

That way program works with FirebirdSQL 4.0 without changing server config. Of cause you must not use new unsupported data types in that database. But that solves problems with automatic type conversions and use of new types in system tables.

[Henry Olive 5]

Thank you so much Virgo