0

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?

Phil
  • 3,779
  • 3
  • 51
  • 94
  • 1
    Which version of Jaybird are you using? In any case, a dialect 1 database will never be fully dialect 3, as there are also a number of differences on storage level. Note that the second example is expected to change the dialect, that is why that property includes **set** in `set_sql_dialect`. – Mark Rotteveel Jun 26 '18 at 06:32
  • You may also want to include exactly what problem you run into with ColdFusion, as Jaybird 2.2.x will always use dialect 3, while Jaybird 3 will use dialect 3 unless explicitly told not to. – Mark Rotteveel Jun 26 '18 at 12:25
  • @MarkRotteveel - updated to include exact error we're getting – Phil Jun 26 '18 at 12:44
  • Specifically which Jaybird version you using? Could you post a reproducible test case (without involving ColdFusion please). In any case, have you tried explicitly specifying the connection dialect to 1, because as I said, Jaybird will default to dialect 3. And in dialect 1, a `DATE` includes time (it is effectively a dialect 3 `TIMESTAMP`). – Mark Rotteveel Jun 26 '18 at 12:51
  • The error you get, you would also get in 2.5.8. Was your previous Firebird a 2.5.4 or earlier, because as far as I can tell this error message was introduced in Firebird 2.5.5 to avoid dialect mismatches. – Mark Rotteveel Jun 26 '18 at 13:10
  • I'm using the JayBird 3.0.4 driver – Phil Jun 26 '18 at 13:19
  • Could you ask this question on the Firebird-java mailing list. I can't trigger your error (although I can trigger one that probably has a similar underlying cause), and although I have a number of theories, Stack Overflow is not a suitable format for troubleshooting and validation of those theories. – Mark Rotteveel Jun 26 '18 at 13:47
  • Also, your expectation is wrong. A dialect 1 `DATE` is a timestamp, so it should include time information. If before it didn't, then either your database was not really dialect 1, or something screwed up had happened (mixing of dialect 1 and dialect 3 meanings of `DATE` that are now explicitly prevented by Firebird). – Mark Rotteveel Jun 26 '18 at 13:53
  • @MarkRotteveel - is it possible for me to create a custom build of the JDBC driver that will default to Dialect 1 instead of 3? I figure you'd be the guy to ask since you're the developer of that driver. – Phil Jun 26 '18 at 13:53
  • There is no need for that, just specify `dialect=1` (or `sqlDialect=1`) in the connection string and it will explicitly use dialect 1. – Mark Rotteveel Jun 26 '18 at 13:58
  • @MarkRotteveel - to confirm, my connection string looks like this. jdbc:firebirdsql:localhost/3050:C:\fbdb\master25.fdb?dialect=1 - correct? – Phil Jun 26 '18 at 14:19
  • Yes, that would be the right format (or `jdbc:firebirdsql://localhost:3050/C:\fbdb\master25.fdb?dialect=1`, which is the preferred format) – Mark Rotteveel Jun 26 '18 at 14:26
  • The error _"Database SQL dialect 1 does not support reference to DATE datatype"_ may indicate that you are using an expression that results in a value of a dialect 3 type `date` (that is without time component), eg using `CURRENT_DATE`. The dialect 1 `date` contains a time component and is equivalent to a dialect 3 `timestamp`. – Mark Rotteveel Jul 24 '18 at 15:52

1 Answers1

1

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.

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158