Issue Details (XML | Word | Printable)

Key: CORE-3972
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Adriano dos Santos Fernandes
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Allow the selection of SQL_INT64, SQL_DATE and SQL_TIME in dialect 1

Created: 04/Nov/12 12:15 AM   Updated: 28/May/15 04:31 PM
Component/s: API / Client Library, Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 1

QA Status: Done successfully


 Description  « Hide
As an immediate workaround for CORE-3964, I propose to let BIGINT (and DATE and TIME) to be passed for dialect 1 clients.

Thechanges just transform something that is currently an error into something that may be unexpected (SQL_INT64) but will work for good clients.

Arithmetics will not be changed:

-- Create database on dialect 3

isql

create table t1 (n1 numeric(12,3));
insert into t1 values (1.23);
insert into t1 values (10.23);
insert into t1 values (3.567);
commit;
exit;


-- Change database and client to dialect 1 (with new semantics)

gfix -sql_dialect 1 zz.fdb
isql -sql_dialect 1 zz.fdb

set sqlda_display on;
select n1, n1 / 2 from t1;

OUTPUT SQLDA version: 1 sqln: 20 sqld: 2
01: sqltype: 581 INT64 Nullable sqlscale: -3 sqlsubtype: 1 sqllen: 8
  : name: (2)N1 alias: (2)N1
  : table: (2)T1 owner: (6)SYSDBA
02: sqltype: 481 DOUBLE Nullable sqlscale: 0 sqlsubtype: 0 sqllen: 8
  : name: (6)DIVIDE alias: (6)DIVIDE
  : table: (0) owner: (0)

                   N1 DIVIDE
===================== =======================
                1.230 0.6150000000000000
               10.230 5.115000000000000
                3.567 1.783500000000000


-- Test with current dialect 1

Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-N1
-Client SQL dialect 1 does not support reference to BIGINT datatype

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 05/Nov/12 06:00 PM
Since Dialect 1 already has a DATE (which is actually date/time -- ie. TIMESTAMP), how will the DATE type be handled?


Adriano dos Santos Fernandes added a comment - 05/Nov/12 06:13 PM
If the database was in dialect 3 and a DATE column is created, then database (or client) is switched to dialect 1, that DATE column will be selected as a SQL_DATE type. Previous it raised an error.

Sean Leyne added a comment - 05/Nov/12 06:23 PM
I was asking, how in Dialect 1, will a user be able to define a new column using the existing DATE (Timestamp) functionality, as well as the Dialect 3 DATE (date only)?

Are you suggesting that the new datatypes will only be supported if a database is created as Dialect 3 and then "regressed" to Dialect 1?

Adriano dos Santos Fernandes added a comment - 05/Nov/12 06:33 PM
The title says "allow the selection", it's what it does. Something (invalid in dialect 1) must already be created.

Sean Leyne added a comment - 05/Nov/12 06:44 PM
Adriano,

"Allow the selection" is not as clear a statement as you think it is. The statement can mean:

- Allow SQL statements from Dialect 1 client to refer/use columns defined using SQL_INT64, SQL_DATE and SQL_TIME datatypes
and/or
- Allow SQL_INT64, SQL_DATE and SQL_TIME datatypes to be selected when defining columns in Dialect 1 database