Python+Firebird Dynamic SQL Error Token Unknown

When I connect to a Firebird database and try to execute a query, it throws me this error:

"Error while preparing SQL statement:")
fdb.fbcore.DatabaseError: ('Error while preparing SQL statement:n- SQLCODE: -104n- Dynamic SQL Errorn- SQL error code = -104n- Token unknown - line 27, column 113n- )', -104, 335544569)


My query looks like:

select distinct p.ID, p.DATE, p.PETITION, pac.KEY, ...,
pac.BIRTH_DATE, trunc(('today' - pac.BIRTH_DATE)/365.25) as AGE, p.NAME,...


And I've checked that the cause of the error is:

trunc(('today' - pac.BIRTH_DATE)/365.25) as AGE


if I remove it from the query, it works perfectly. Anyone knows why? Could you help me?

I've done what @ain told me:

trunc((cast(CURRENT_DATE - pac.BIRTH_DATE) as DOUBLE PRECISION)/365.25) as AGE

"Error while preparing SQL statement:")
fdb.fbcore.DatabaseError: ('Error while preparing SQL statement:n- SQLCODE: -104n- Dynamic SQL Errorn- SQL error code = -104n- Database SQL dialect 1 does not support reference to DATE datatype', -104, 335544569)


But this results in the same error. Also if I try:

DATEDIFF(YEAR from CURRENT_DATE to pac.BIRTH_DATE) as AGE

"Error while preparing SQL statement:")
fdb.fbcore.DatabaseError: (
'Error while preparing SQL statement:n-
SQLCODE: -104n-
Dynamic SQL Errorn-
SQL error code = -104n-
Database SQL dialect 1 does not support reference to DATE datatype',
-104, 335544569)


Finally I solved the issue by doing:

... trunc(DATEDIFF(DAY from cast(pac.BIRTH_DATE as DATE) to CURRENT_TIMESTAMP)/365.25) as AGE ...


I'll take a look to dialects too. Thank you so much for your help!

"Database SQL dialect 1 does not support reference to DATE datatype"

CURRENT_TIMESTAMP

CURRENT_DATE

DATE

CURRENT_DATE

2 Answers
2

You have to cast 'today' to date, otherwise it is just a string literal. Ie use

'today'

... trunc((cast('today' as date) - pac.BIRTH_DATE)/365.25) as AGE ...


You might also want to use CURRENT_DATE instead, see Context variables topic of the Firebird manual.

CURRENT_DATE

It seems you want to calculate age in "full years" so you could use DATEDIFF function for that:

DATEDIFF

DATEDIFF(YEAR from CURRENT_DATE to pac.BIRTH_DATE)


You are not supposed to use Dialect 1. It was obsoleted with releases of Interbase 6 and Firebird 0.9 in 2000. It is sole intention is backward compatibility with pre-2000 database server versions for pre-2000 legacy applications. In 2018 this purpose has little practical value and hence Dialect 1 not only blocks you from using most recent language and engine enhancements, but maybe outright buggy due to little real world exposure thus little out of the house testing.

You have to migrate your database to Dialect 3. Checking and ironing out all the incompatibilities that the database creator maybe introduced to it, coding it by pre-2000 language, along your migration.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.