Firebird Database related Memos

Firebird database There are some very special things, very useful, but because the usual use of not much, remember here, for later use when the query.

1, with ADO OLE ODBC drive access to Firebird, you can use the following connection string:

Fbconn:= ' Provider=msdasql.1;driver=firebird/interbase (r) driver;dbname=f:\test. FDB; Charset=none; UID=SYSDBA; Pwd=masterkey ';

2, Sequence generator, value is bigint type

When doing self-increment fields, the standard tutorial is written in the trigger, I think it is easier to write in user sql, that is, call the sequence generator from the foreground, generate the ID value, and then update from the foreground to the background. The ID field of the background can be as long as the normal int value.

Create sequence CUSTOMER;

Set GENERATOR CUSTOMER to 200;

Select gen_id (CUSTOMER, 1) from Rdb$database;

GENERATOR is a legacy reserved word, sql-99 support sequence keyword, FB is also supported.

CREATE {SEQUENCE | GENERATOR} < name >

DROP {SEQUENCE | GENERATOR} < name >

SET GENERATOR < name > to < start value > This seems to be incompatible with the spirit of SQL syntax, the following alter is the standard sense of language

ALTER SEQUENCE < name > RESTART with < start value >

GEN_ID (< name;, < growth value >)

NEXT value for < name >

The NEXT value for syntax is also standard SQL syntax, which can be used on the sequence builder:

INSERT into EMPLOYEE (ID, NAME)

ValueS (NEXT value for S_employee, ' John Smith ');

3, the IIf function is built in, IIF (<SEARCH_CONDITION>, <value1>, <value2>), as a shortcut to case.

4, ROWS syntax, especially for paging use

SELECT ...

[ORDER by <expr_list>]

ROWS <expr1> [to <expr2>]

There are similar syntax, similar to the above rows of the statement is:

First (<expr2>-<expr1>+1) SKIP (<expr1>-1)

5. EXECUTE BLOCK Statement

This statement is equivalent to dynamically establishing and executing a stored procedure. Or, dynamically execute a block of statements similar to a stored procedure.

The extended statement EXECUTE BLOCK of the SQL language makes it possible to use "dynamic PSQL" in the SELECT statement specification. It has such an effect that if it is stored in a procedure, it will allow a psql self-contained code to execute in a dynamic SQL statement.

Syntax format:

EXECUTE BLOCK [(param datatype =?, param datatype =?, ...)]

[RETURNS (param datatype, param datatype, ...)}

As

[DECLARE VARIABLE var datatype; ...]

BEGIN

...

END

For example:

EXECUTE BLOCK (x INTEGER =: x)

RETURNS (Y VARCHAR)

As

DECLARE V INTEGER;

BEGIN

INSERT into T (....) ValueS (...);

SELECT ... From T Into:y;

SUSPEND;

END

6.COMMENT statement

The COMMENT statement, used to set the metadata description, is very interesting.

Syntax format:

COMMENT on DATABASE is {' txt ' | NULL};
COMMENT on <basic_type> name is {' txt ' | NULL};
COMMENT on COLUMN tblviewname.fieldname is {' Text content ' | NULL};
COMMENT on PARAMETER procname.parname is {' txt ' | NULL};

7. Derivative tables

MS MSQL called online view, syntax similar to:

SELECT * FROM (SELECT * to TBL) as a inner join TBL2 B on a.id=b.id

The query results are used as tables.

8, FB2.0 publish the document bar, here is a link! Http://www.mesig.com/forum/th-abb-1

Firebird Database related Memos