Embedded firebird not accepting create table statement

Question

The following SQL code works very well on MYSQL, and it contains valid SQL query language. However this doesn't work on embedded Firebird server.

The SQL code:

CREATE TABLE publications (
  id int(11) NOT NULL,
  filename varchar(500) NOT NULL,
  title varchar(500) DEFAULT NULL,
  authors varchar(1000) DEFAULT NULL,
  uploader int(7) DEFAULT NULL,
  keywords varchar(500) DEFAULT NULL,
  rawtext text,
  rawbinarydata blob NOT NULL,
  lastmodified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE publications
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY filename (filename);

ALTER TABLE publications
  MODIFY id int(11) NOT NULL AUTO_INCREMENT;

The C# code that is using the query is:

try
{
    using( cmd.Connection = connect_to_fbserver() )
    {
        cmd.CommandText = fresh_db_creation_statement;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }

    return true;
}
catch( Exception exx )
{
    lasterror = exx.Message;
    return false;
}

fresh_db_creation_statement is the sql code in the first code listing.

The error was caught at lasterror = exx.Message; with the value: "Dynamic SQL Error\nSQL error code = -104\nToken unknown - line 2, char 13", meaning the ( was flagged by the embedded firebird (that is line 2, char 13).

When I removed all sizes of the defined data value types (e.g. changed id int(11) NOT NULL to id int NOT NULL) it will flag the NOT.

How can I make Firebird accept this query and execute as normal?

Hilarius

The CREATE TABLE is not ANSI SQL compliant. (Use the SQL Validator to verify ANSI/ISO SQL syntax compliance, developer.mimer.com/validator/parser200x/index.tml#parser.) – Europium

Can you help with how to get a valid SQL code that can achieve the same? (note that the Mimer SQL Validator inidicated that AUTO_INCREMENT does not have a replacement). Does it mean that ANSI SQL does not support AUTO_INCREMENT ? – Hilarius

instead autoincrement we usually used generator lite this : CREATE GENERATOR GEN_JIZDA_ID; SET GENERATOR GEN_JIZDA_ID TO 1; newID = gen_id(gen_jizda_id,1); – Procumbent

This question has not accepted answer yet. – Procumbent

Using Fb with adoprovider cant execute multiple queries at once look here:#33636325 – Anthropometry

Answer 1

From what I can see, there are various problems

1. Why you alter the table with statemens which you could already do on create:

   id int not null primary key,

   filename varchar(500) not null unique,

   lastmodified timestamp default CURRENT_TIMESTAMP

2. Autoincrement does not exist, you need to build a trigger, see here: http://www.firebirdfaq.org/faq29/

3. Update timestamp automatically on change does not exist, you need to build a trigger too, see here: http://www.firebirdfaq.org/faq77/

Welcher

With your suggestions, I will need to do more than one query. The only thing that came to my mind is transactions. I have tried FbTransaction transaction = Connection.BeginTransaction( IsolationLevel.Serializable ); FbCommand command = new FbCommand( queryString, Connection, transaction ); command.ExecuteNonQuery(); transaction.Commit(); transaction.Dispose(); transaction = null; but it always flag the second statement – Hilarius

Also integers don't have a precision parameter in its definition in Firebird (nor in most SQL dialects, I think MySQL is the odd one with that), so int(11) is wrong. – Ehrenberg

@emmanuel You can't execute multiple statements at once. You need each statement individually; the Firebird.net provider has a helper class to execute scripts. – Ehrenberg

@MarkRotteveel The line ID INT NOT NULL PRIMARY KEY now gives another error: "unsuccessful metadata update [newline here] key size too big for index @1". What is wrong with this? – Hilarius

Answer 2

Simply : Create/Alter does not work in batch. You have to use sql in a separate Command.

If you want to use the commands in a batch, you must use the EXECUTE BLOCK AS BEGIN ... but then create table doesnt work ... see. below

          string sqlText = "create table pub(id int not null);";//----  OK ----
        //string sqlText = "EXECUTE BLOCK AS BEGIN \ncreate table pub(id int not null);\nalter table pub add primary key (id);\nEND";//----  FAILED  ----
        //string sqlText = "EXECUTE BLOCK AS BEGIN \nupdate jizda set cislovozidla = 99999 where cislovozidla = 999899;\nalter table pub add primary key (id);\nEND";//----  FAILED ----
        //string sqlText = "EXECUTE BLOCK AS BEGIN \nupdate jizda set cislovozidla = 99999 where cislovozidla = 999899;\nupdate jizda set cislovozidla = 99999 where cislovozidla = 99989;\nEND";//----  OK ----
        using (FbConnection dbConnection = new FbConnection(Program.ConnectDBData()))
        {
          dbConnection.Open();
          FbCommand cmd = new FbCommand(sqlText);
          cmd.CommandType = CommandType.Text;
          cmd.Connection = dbConnection;
          cmd.ExecuteNonQuery();
        }

Procumbent