objSQL Firebird Notes

The Firebird database server does not have native functionality to create an ID column with an autoincrementing integer datatype for insert queries.

• Firebird uses a Generator to autoincrement an ID field and requires that you retrieve the Generator client-side before an insert.
• objSQL does not support Generators but you can create a Sequence and a Trigger that will perform as an autoincrementing ID field when you create a new table.

<?php

try
{
    $table = 'CREATE TABLE mytable (ID INTEGER NOT NULL PRIMARY KEY, 
                                    FIELD_1 VARCHAR(20) NOT NULL, 
                                    FIELD_2 VARCHAR(20) NOT NULL)';
                                    
    $seq = 'CREATE SEQUENCE GEN_MYTABLE_ID';
    
    $trigger = 'CREATE TRIGGER TRIG_MYTABLE FOR mytable
                ACTIVE BEFORE INSERT POSITION 0
                AS
                BEGIN
                IF (NEW.ID IS NULL) THEN
                NEW.ID = GEN_ID(GEN_MYTABLE_ID,1);
                END';
    
    $dbh->obj_query( $table );
    $dbh->obj_query( $seq );
    $dbh->obj_query( $trigger );

    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );

}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>