FrontBase 3.6 - Release Notes

This is a release with focus on performance improvements, new features and of course some bug fixes as well.

The performance improvements are, in particular, in the area of SELECT and INSERT performance.

Users on Windows and Mac OS X will see up to 25% improvement in execution time on most multi-row SELECTs and INSERTs. Solaris users should experience up to 10% improvement.

FrontBase 3.6 - New Features

Per table based encryption/obfuscation:

      ALTER TABLE <table name> SET ENCRYPTION DES | XOR DEFAULT KEY;

All data that is stored in a database as part of <table name> will get encrypted/obfuscation using either a DES based schema or XOR. The key to use for the encryption is automatically generated and stored internally in the database.

Sampling:

      SELECT * FROM <table name> [SAMPLE ROW | BLOCK <percent expr>];

SAMPLE ROW <percent expr> will select randomly <percent> of all the rows in the table.

SAMPLE BLOCK <percent expr> will select randomly blocks of rows so that <percent expr> of all rows in the table will be returned. The number of rows in a block is calculated as how many rows that ideally can fit into a physical memory disk block (currently 2048 bytes).

Execution of a string with SQL statements:

      EXECUTE IMMEDIATE <string expr>
      [ INTO <list of variables and/or parameters> ]
      [ USING [ IN | OUT | IN OUT] <expr>, [ IN | OUT | IN OUT] <expr>, ... ];

Executes the <string expr> as a list of SQL statements. The <string expr> can be general string expression, e.g. also a scalar sub-query.

The components of the <list of variables and/or parameters> are bound to the actual statement in the order that the place holders (?) are located from left to right.

The [IN | OUT | IN OUT] specifications in the USING clause are ignored and only allowed to reflect the syntax implemented by Oracle.

Example:

      DECLARE V0 INT;
      DECLARE V1 INT;
      CREATE TABLE T0(PK INT, C0 INT, C1 INT);
      INSERT INTO T0 VALUES (1, 2, 3);
      --
      EXECUTE IMMEDIATE 'SELECT C0, C1FROM T0 WHERE PK = ?;'
      INTO :V0, :V1
      USING 1;
      --
      VALUES (:V0, :V1);	-- Returns (2, 3)


      CREATE TABLE T1(SQL_CMD VARCHAR(100), PK INT);
      INSERT INTO T1 VALUES('UPDATE T0 SET C0 = ? WHERE PK = ?;', 99);
      --
      EXECUTE IMMEDIATE (SELECT SQL_CMD FROM T1 WHERE PK = 99)
      USING 88, 1;
      --

Bitwise OR:

      <bitwise OR operator> ::= |

The bitwise OR operator works on values of all the 4 integer types (TINYINT, SMALLINT, INT, LONGINT).

Example:

      VALUES 1 | 7 ;	-- Returns 7

Size of BLOB/CLOB values:

      OCTET_LENGTH(<BLOB or CLOB expression>)

Returns the size in bytes of the BLOB/CLOB expression.

Example:

      CREATE TABLE T2(D2 BLOB, ...);
      ...
      SELECT D2, OCTET_LENGTH(D2) AS BSIZE FROM T2 ORDER BY BSIZE;