Databases - Firebird SQL

Firebird SQL

Firebird 1.5 supports a fair amount of useful SQL not supported by Interbase 5, and, therefore, not in the Delphi 5 (Interbase 5) online manuals - This just documents some of it.

Debugging Scripts

I use isql.exe to run scripts - but it normally only shows errors. The following command permits isql to show each command as it executes and, therefore, which command failed.

set echo on;

Alter Table

Alter table is standard sql, but Alter column is new to Firebird 1.5

Alter table "Some_Name"
  Add "column_Name" Integer,            /* Add a new column              */
  Drop "xyz",                           /* Remove a column               */
  Alter column "Order" to "Sort_Order", /* changes the column name       */
  Alter column "Order" position 3,      /* changes the column's position */
  Alter column "Order" type VARCHAR(10),/* changes the column type       */
  Alter column "abc"   type d1          /* changes the column type       */
  ;

In the example, d1 is a domain name. As shown, you can also convert some columns from numeric to character types.

There are some limitations to modifying columns if they are used as part of a constraint, view, etc.

SET SQL DIALECT n

By default, Firebird 1.5 creates new databases using dialect 3 - this is a good thing. The Interbase DataPump automatically places the following non-standard sql in the build scripts if you specify that you will use that dialect.

SET SQL DIALECT 3;

In practice, this does nothing (since it is the default) ... setting the dialect to 1 will change how things work.

Firebird 1.0 used only dialect 1.

When a database is opened, the default dialect is dependent on the Firebird version used to create the table.

Date literals

Date literals are string values that can be used instaed of date values in SELECT, INSERT, and UPDATE statements.

'TODAY'
'NOW'
'YESTERDAY'
'TOMORROW'

select * from Table1
 where "Transaction_Date" = 'TODAY'

First & Skip

This will limit the number of rows returned.

select FIRST num_rows SKIP start_row columns FROM table

select FIRST num_rows SKIP start_row distinct columns FROM table

select first 5 * from "Table_1"

Notice that distinct must be after first and skip.

(I don't know if Interbase supports this)

Various Functions

These are some of the functions that can be used in various SQL statements - including SELECT, INSERT, and UPDATE - As far as I know, the function names are NOT case sensitive.

Numeric Functions
`COUNT(*)`	The number of rows
`MAX(Field1)`	Maximum
`MIN(Field1)`	Minimum
`AVG(Field1)`	Average
`SUM(Field1)`	Sum

String Functions
`Last \|\| ', ' \|\| First`	Concatenation ( \|\| ) - used to make a long string from smaller strings. Use cast to convert other types to strings.
`UPPER(Field1)`	Converts the string to uppercase
`LOWER(Field1)`	Converts the string to lower case This standard SQL function does not work with Firebird 1.5.3
`SUBSTRING(Field1 from 3 FOR 5) SUBSTRING(Field1 from 3)`	Returns a part of a string `select "Last_Name", SubString("Last_Name" from 3 for 8) as test from "Users_tbl"`
`trim(Field1) trim(Leading '*' from Field1)`	Removes leading and trailing spaces from the string Removes the leading characters as specified This standard SQL function does not work with Firebird 1.5.3

Testing Functions
`NULLIF(COL3, 12)`	Compares two expressions, returns NULL if they are equal, otherwise returns the first expression. With strings, this is case sensitive.
`COALESCE(COL1, COL2, 'Two NULL Columns')`	Returns the first NON-NULL value
`case .. when .. else .. end`	Allows the result to be dependent on a case statement SELECT COL1, COL2, COL3, CASE WHEN COL3 = 10 THEN 'Ten' WHEN COL3 = 34 THEN 'Thirty Four' WHEN COL3 IS NULL THEN 'Null value' ELSE COL3 END as test FROM FOO;
`iif(experssion, True_Value, False_Value) IIF(COL2 IS NULL, 'it is NULL', COL2)`	Immediate IF - Evaluates the expression and returns one of the 2 values This function does not work with Firebird 1.5.3

Other Functions
`GEN_ID(GeneratorName, 1)`	Calls the built-in generator function, returns the current value plus the second parameter and remembers the new value
`CAST(Field1 AS VARCHAR(10))`	Converts the value to a different datatype - can not be used with memo or blob data types
`Extract(Month from Field1)`	Returns the Year, Month, or Day from a Date or Timestamp field Returns the Hour, Minute, or Second from a Time or Timestamp field select F1, F2, F3 from Table1 where extract(month from F1) = 5

Reference - several functions listed here do NOT work with Firebird 1.5.3.

References

On-line references to this information are pretty poor (that's why I created this page).

This information is based in part on "The Firebird Book" by Helen Borrie.

Author: Robert Clemenzi - clemenzi@cpcug.org