c# - How do INSERT INTO Firebird, with autoincrement for the primary key?

Question

How do INSERT INTO Firebird, with autoincrement for the primary key?

For the table fields I have:

fstPriority VARCHAR(30), fstInfo VARCHAR(100), fstDateCreated VARCHAR(30), fstDateModified VARCHAR(30), fiKeyID INTEGER PRIMARY KEY

For the INSERT INTO I have:

FbConnection fbConn = new FbConnection(stOpenConn))
fbConn.Open();
...
FbTransaction fbTransaction = fbConn.BeginTransaction();
FbCommand fbCmd = new FbCommand("INSERT INTO " + stTableName + "(" + stFieldNames + ") VALUES ( @p0, @p1, @p2, @p3, @p4 ) RETURNING fiKeyID ", fbConn, fbTransaction);

but am uncertain what should be used for the fbCmd.Parameters.AddWithValue

fbCmd.Parameters.AddWithValue("@p0", "1st value");
fbCmd.Parameters.AddWithValue("@p1", "2nd value");
fbCmd.Parameters.AddWithValue("@p2", "3rd value");
fbCmd.Parameters.AddWithValue("@p3", "4th value");

Then what? For fiKeyID, do I add

fbCmd.Parameters.AddWithValue("@p4", "");

Also, I see at http://www.firebirdfaq.org/faq29/ creating an autoincrement column, but am uncertain how to do this in C# ... Firebird ADO.NET ... FirebirdClient.5.8.0 ... Visual Studio 2013.

CREATE GENERATOR ...;
SET GENERATOR ...;

set term !! ;
CREATE TRIGGER ...

are not recognized by the Visual Studio compiler.

Answer 1

An important thing is that SET TERM is not part of the Firebird statement syntax, instead it is a client-side feature to set the statement terminator in query tools like ISQL. This terminator is necessary to know when a statement is complete and can be sent to the server. By default these tools do that on a semi-colon (;), but that doesn't work with PSQL (stored procedures, triggers), because PSQL code uses the semi-colon as well. To address this, these tools have SET TERM to switch this terminator.

Using the Firebird ADO.net provider however, you need to execute statements one at a time, so a statement terminator is irrelevant.

To be able to generate a primary key you can use the following solutions:

1. Firebird 3 has an identity type column, so you don't need to create a sequence and trigger yourself:

   create table withgeneratedid(
       id integer generated by default as identity primary key, 
       column2 varchar(100)
   )
   

2. For Firebird 2.5 and earlier you will need to create a sequence and trigger:

   create table withgeneratedid(
       id integer primary key,
       column2 varchar(100)
   );
   
   create sequence seq_withgeneratedid;
   
   set term #;
   create trigger withgeneratedid_bi before insert on withgeneratedid
   as
   begin
       if (new.id is null) then new.id = next value for seq_withgeneratedid;
   end#
   set term ;#
   

When you insert values into a table and want to have a generated key, you should not include the id column in the column-list. Including the id column allows you to override the key value, but that might lead to future inserts generating a duplicate key!. If you do include the id column, then no key will be generated in the Firebird 3 example, in the Firebird 2.5 example a key will be generated if the value of the column is null, otherwise it will take the provided value.

In ADO.net you'd normally need to execute the statements individually (and not use set term). Alternatively, you could use FbScript to parse a DDL script and execute the parse statements. Note that FbScript does support (and even requires) set term.

To execute this with the Firebird ADO.net provider, you can do something like the example below. I have included three alternatives for creating the table Firebird3, Firebird2_5, and FbScriptFB2_5 (which is the same as Firebird2_5 but uses FbScript). It also show how to retrieve the generated key:

namespace FbGeneratedKeys
{
    class Program
    {
        private static SolutionType solutionType = SolutionType.FbScriptFB2_5;

        static void Main(string[] args)
        {
            var connectionString = new FbConnectionStringBuilder
            {
                Database = @"D:empgeneratedkey.fdb",
                ServerType = FbServerType.Default,
                UserID = "SYSDBA",
                Password = "masterkey",
            }.ToString();
            FbConnection.CreateDatabase(connectionString, pageSize: 8192, overwrite : true);

            using (FbConnection connection = new FbConnection(connectionString))
            using (FbCommand cmd = new FbCommand())
            {
                connection.Open();

                cmd.Connection = connection;
                switch (solutionType) {
                    case SolutionType.Firebird3:
                        Firebird3Example(cmd);
                        break;
                    case SolutionType.Firebird2_5:
                        Firebird2_5Example(cmd);
                        break;
                    case SolutionType.FbScriptFB2_5:
                        FbScriptFB2_5Example(cmd);
                        break;
                }

                cmd.CommandText = @"insert into withgeneratedid(column2) values (@column2) returning id";
                cmd.Parameters.AddWithValue("@column2", "some value");
                cmd.Parameters.Add(new FbParameter() { Direction = System.Data.ParameterDirection.Output });
                cmd.ExecuteNonQuery();

                Console.WriteLine("Id:" + cmd.Parameters[1].Value);
                Console.ReadLine();
            }
        }

        private static void Firebird3Example(FbCommand cmd)
        {
            // Firebird 3 identity column
            cmd.CommandText = @"create table withgeneratedid(
    id integer generated by default as identity primary key, 
    column2 varchar(100)
)";
            cmd.ExecuteNonQuery();
        }

        private static void Firebird2_5Example(FbCommand cmd)
        {
            // Firebird 2.5 and earlier normal primary key with trigger to generate key
            // Table
            cmd.CommandText = @"create table withgeneratedid(
    id integer primary key,
    column2 varchar(100)
)";
            cmd.ExecuteNonQuery();

            // Sequence
            cmd.CommandText = "create sequence seq_withgeneratedid";
            cmd.ExecuteNonQuery();

            // Trigger
            cmd.CommandText = @"create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
    if (new.id is null) then new.id = next value for seq_withgeneratedid;
end";
            cmd.ExecuteNonQuery();
        }

        private static void FbScriptFB2_5Example(FbCommand cmd)
        {
            string script = @"
create table withgeneratedid(
    id integer primary key,
    column2 varchar(100)
);

create sequence seq_withgeneratedid;

set term #;
create trigger withgeneratedid_bi before insert on withgeneratedid
as
begin
    if (new.id is null) then new.id = next value for seq_withgeneratedid;
end#
set term ;#
";
            FbScript fbScript = new FbScript(script);
            fbScript.Parse();
            FbBatchExecution exec = new FbBatchExecution(cmd.Connection);
            exec.AppendSqlStatements(fbScript);
            exec.Execute();
        }
    }

    enum SolutionType
    {
        Firebird3,
        Firebird2_5,
        FbScriptFB2_5
    }
}