SQL Dialect Summary
Assumption
Existing InterBase V5 customers can migrate to InterBase V6 client and InterBase V6 server without any changes of existing SQL scripts
Rules of Thumb
- An explicit SQL client dialect setting is taken
- Otherwise, InterBase chooses the database SQL dialect or the SQL dialect that the server API supports
- Create database:
- InterBase V6 client with Pre-V6 server: create the database with the SQL dialect that the server API supports
- InterBase V6 client with V6 server: database SQL dialect is same as the client SQL dialect setting
Definition
Warning - I did what you asked but be careful.
For example:
One connected to a DB dialect 3 database and executed SET SQL DIALECT 1. The client dialect value becomes 1 and a warning message is issued stating that it is a DB dialect 3 database.
Error - I did not do what you asked.
For example:
One connected to an ODS 9 database via the V6 server and executed SET SQL DIALECT 3. The client SQL dialect value is not changed and an error message is issued stating that client SQL dialect 3 is not supported.
| Client default SQL dialect | V3, V4, V5 Server, ODS < 10 | V6 Server, ODS 8-9 | V6 Server, ODS 10, DB Dialect 1 | V6 Server, ODS 10, DB Dialect 2 | V6 Server, ODS 10, DB Dialect 3 | V7 Server, ODS 11, DB Dialect 2, 3, or 4 | |
|---|---|---|---|---|---|---|---|
| ISQL default client dialect before connecting to any databases | /client default SQL dialect | ||||||
| Connecting to a database before client SQL dialect was set | Match client dialect with the dialect that the server API supports | Sets client dialect to 1 | Sets client dialect to 1 | Sets client dialect to 1 | Sets client dialect to 2 | Sets client dialect to 3 | Sets client dialect to 2, 3, or 4 depending on the DB SQL dialect |
| Connecting to a database after client SQL dialect was set | Match client dialect with the dialect that the server API supports and gives warning | Client SQL dialect 1: Ok |
Client SQL dialect 1: Ok |
Client SQL dialect 1: Ok |
Client SQL dialect 1 or 3: gives warning |
Client SQL dialect 1 or 2: gives warning |
Client SQL dialect 1: gives error and sets client SQL dialect value back to 2, 3, or 4 depending on the DB SQL dialect |
Client SQL dialect 2 or 3: gives error and sets client SQL dialect value back to 1 |
Client SQL dialect 2 or 3: gives warning and sets client SQL dialect value back to 1 |
Client SQL dialect 2 or 3: gives warning |
Client SQL dialect 2: Ok |
Client SQL dialect 3: Ok |
Client SQL dialect 2 or 3: Ok |
||
Client SQL dialect 4: gives error and sets client SQL dialect value back to 1 |
Client SQL dialect 4: gives error and sets client SQL dialect value back to 1 |
Client SQL dialect 4: gives error and sets client SQL dialect value back to 1 |
Client SQL dialect 4: gives error and sets client SQL dialect value back to 2 |
Client SQL dialect 4: gives error and sets client SQL dialect value back to 3 |
Client SQL dialect 4: Ok |
| Client default SQL dialect | V3, V4, V5 Server, ODS < 10 | V6 Server, ODS 8-9 | V6 Server, ODS 10, DB Dialect 1 | V6 Server, ODS 10, DB Dialect 2 | V6 Server, ODS 10, DB Dialect 3 | V7 Server, ODS 11, DB Dialect 2, 3, or 4 | |
|---|---|---|---|---|---|---|---|
| Set client SQL dialect **afte**r connected to an existing database | Match new client SQL dialect setting with the dialect that the server API supports and gives error when they are not compatible | Client SQL dialect 1: Ok |
Client SQL dialect 1: Ok |
Client SQL dialect 1: Ok |
Client SQL dialect 1: gives warning and client SQL dialect value is 1 |
Client SQL dialect 1: gives warning and client SQL dialect value is 1 |
Client SQL dialect 1: gives error and no change to client SQL dialect value |
Client SQL dialect 2: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 2: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 2: Ok |
Client SQL dialect 2: Ok |
Client SQL dialect 2 : gives warning and client SQL dialect value is 2 |
Client SQL dialect 2 : Ok |
||
Client SQL dialect 3: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 3: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 3: gives warning and client SQL dialect value is 3 |
Client SQL dialect 3: gives warning and client SQL dialect value is 3 |
Client SQL dialect 3: Ok |
Client SQL dialect 3 : Ok |
||
Client SQL dialect 4: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 4: gives error and set client SQL dialect value back to 1 |
Client SQL dialect 4: gives error and no change to client SQL dialect value |
Client SQL dialect 4: gives error and no change to client SQL dialect value |
Client SQL dialect 4: gives error and no change to client SQL dialect value |
Client SQL dialect 4: Ok |
| Client default SQL dialect | V3, V4, V5 Server, ODS < 10 | V6 Server, ODS 8-9 | V6 Server, ODS 10, DB Dialect 1 | V6 Server, ODS 10, DB Dialect 2 | V6 Server, ODS 10, DB Dialect 3 | V7 Server, ODS 11, DB Dialect 2, 3, or 4 | |
|---|---|---|---|---|---|---|---|
| Create database before a client SQL dialect was set | Take the dialect that the server API supports as client SQL dialect | Ok and sets client SQL dialect to 1. | N/A | N/A | Ok and sets client SQL dialect to 2. | N/A | To be determined by V7 |
| Create database after client SQL dialect was set | server API dialect does not compatible with client SQL dialect, then gives warning, sets client SQL dialect with server API dialect, and create database | Client SQL dialect 1: ok, else gives warning, sets client SQL dialect to 1, and create database |
N/A | Client SQL dialect 2 or 3: N/A Client SQL dialect 1: Ok Client SQL dialect 4: gives warning, sets client SQL dialect to 2, and create DB dialect 2 database |
Client SQL dialect 1 or 3: N/A Client SQL dialect 2: Ok Client SQL dialect 4: gives warning, sets client SQL dialect to 2, and create DB dialect 2 database |
Client SQL dialect 1 or 2: N/A Client SQL dialect 3: Ok Client SQL dialect 4: gives warning, sets client SQL dialect to 2, and create DB dialect 2 database |
To be determined by V7 |