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?