Jump to content
Henry Olive

Firebird 4.0 Unknown sql type err.

Recommended Posts

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
 

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post

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 by Serge_G

Share this post


Link to post

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.

 

Share this post


Link to post

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

Share this post


Link to post

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.

  • Like 2

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×