CODEWITHSUNDEEP
firebirdjaybirdfirebird-3.0
Phil
Phil

Reputation: 4069

Specify firebird connection dialect, not database dialect

I have a Firebird database which is using Dialect 1. Some old Delphi apps MUST have it using Dialect 1 or certain queries will fail. The issue is, I'm using ColdFusion to communicate with the database for our web applications and it needs Dialect 3.

Is there some way I can set the dialect at the client connection level? So that my ColdFusion Datasource can use Dialect 3, while the old Delphi apps can still use Dialect 1?

I've tried using the following but it did not work at all.

jdbc:firebirdsql:localhost/3050:C:\fbdb\master25.fdb?sql_dialect=3

and when I did this way, it changed the dialect completely to dialect 3 which broke the Delphi apps.

jdbc:firebirdsql:localhost/3050:C:\fbdb\master25.fdb?set_sql_dialect=3

Any ideas? I'm using the Jaybird JDBC Driver to connect from ColdFusion.

Update

The problem is it looks like things have changed on Firebird 3.0. On our 2.5 database under Dialect 1, we have stored procedures where arguments are declared as date. In Firebird 3.0 under Dialect 1, these stored procedures throw an error stating

Database SQL dialect 1 does not support reference to DATE datatype

When we switch over to Dialect 3, everything works - but then our Delphi applications break due to many of them having direct queries where it selects date type columns which Firebird 3.0 is now treating as TIMESTAMP. So instead of getting back 2018-06-25 for example, we now get 2018-06-25 23:59:59.

If there is some way I can set things up so our Delphi apps can communicate as Dialect 1 where also allowing the ColdFusion Data Source to communicate as Dialect 3, that would be ideal.

Is that even possible?

Upvotes: 0

Views: 1253

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

To answer the question implied by the title (without addressing the specifics of the body of the question): you can specify the connection dialect using connection property dialect (with aliases sqlDialect and sql_dialect), which takes values 1 (legacy dialect 1), 2 (bridge dialect) and 3 (current dialect). Technically 0 (auto-select based on database dialect) works too, but in some cases this causes dialect 3 to be used anyway even if the database is dialect 1. The default is dialect 3.

The above applies to Jaybird 3.0.x, dialect selection with Jaybird 2.2.x and earlier will apply a combination of the specified dialect and dialect 3, so should be considered broken.

Upvotes: 1

Related Questions