You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This came from Alexandre B. Smith... isql -x from FB 3 RC2 is generated invalid sql script when computed by fields exists. The test works fine in FB 2.5:
create database "c:\sp\test.fdb";
CREATE TABLE T (A TIMESTAMP,
B COMPUTED BY (current_timestamp - a));
The result is:
---------------------
SET SQL DIALECT 3;
CREATE DATABASE 'test.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE;
COMMIT WORK;
/* Table: T, Owner: SYSDBA */
CREATE TABLE T (A TIMESTAMP,
B NUMERIC(0, 9) COMPUTED BY (NULL));
/* Computed fields */
ALTER TABLE T
ALTER B TYPE NUMERIC(0, 9) COMPUTED BY (current_timestamp - a);
---------------------
Note the type of Column B defined as *NUMERIC(0,9)*
if you run it on isql you will get:
---------------------
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'test2.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE;
SQL>
SQL>
SQL> COMMIT WORK;
SQL>
SQL> /* Table: T, Owner: SYSDBA */
SQL> CREATE TABLE T (A TIMESTAMP,
CON> B NUMERIC(0, 9) COMPUTED BY (NULL));
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Precision must be from 1 to 18
SQL>
SQL> /* Computed fields */
SQL>
SQL> ALTER TABLE T
CON> ALTER B TYPE NUMERIC(0, 9) COMPUTED BY (current_timestamp - a);
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Precision must be from 1 to 18
SQL>
Would it be possible to fix it in FB 3.0 too? I wonder how many people uses isql to generate DB metadata script and will have problems with all calculated fields declarations being invalid.
Submitted by: @WarmBooter
This came from Alexandre B. Smith... isql -x from FB 3 RC2 is generated invalid sql script when computed by fields exists. The test works fine in FB 2.5:
create database "c:\sp\test.fdb";
CREATE TABLE T (A TIMESTAMP,
B COMPUTED BY (current_timestamp - a));
commit;
exit;
isql -user sysdba -password masterkey test.fdb -x > test.sql
The result is:
---------------------
SET SQL DIALECT 3;
CREATE DATABASE 'test.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE;
COMMIT WORK;
/* Table: T, Owner: SYSDBA */
CREATE TABLE T (A TIMESTAMP,
B NUMERIC(0, 9) COMPUTED BY (NULL));
/* Computed fields */
ALTER TABLE T
ALTER B TYPE NUMERIC(0, 9) COMPUTED BY (current_timestamp - a);
---------------------
Note the type of Column B defined as *NUMERIC(0,9)*
if you run it on isql you will get:
---------------------
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'test2.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE;
SQL>
SQL>
SQL> COMMIT WORK;
SQL>
SQL> /* Table: T, Owner: SYSDBA */
SQL> CREATE TABLE T (A TIMESTAMP,
CON> B NUMERIC(0, 9) COMPUTED BY (NULL));
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Precision must be from 1 to 18
SQL>
SQL> /* Computed fields */
SQL>
SQL> ALTER TABLE T
CON> ALTER B TYPE NUMERIC(0, 9) COMPUTED BY (current_timestamp - a);
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Precision must be from 1 to 18
SQL>
Commits: c20d83a 1767a51 53de05b 72ff004 ee2456d
The text was updated successfully, but these errors were encountered: