sqlfirebirdddlsqldatatypesibexpert

Sql error while creating tables - Firebird


I have simple sql code for create table and then add constraint to it. It looks like this:

CREATE TABLE bills (
    id              INTEGER NOT NULL,
    code        VARCHAR2(25) NOT NULL,
    dateOfGeneration   DATE NOT NULL,
    job_id     INTEGER NOT NULL
);

ALTER TABLE bills ADD CONSTRAINT bills_pk PRIMARY KEY ( id,job_id );

I am using IBExpert - client for Firebird. When I execute this code I get 2 errors:

First error: - in code VARCHAR2(25) NOT NULL

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 3, column 29.
(.

Second error: - in code ALTER TABLE ...

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 8, column 1.
ALTER.

The first one I think is because i am using varchar2 instead of varchar. What about second error? How to fix this?


Solution

    1. There is no VARCHAR2 type in Firebird - https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-chartypes.html
    2. If you want to run two commands - you have to run TWO commands. You try to run two commands in one, but that is not a way to do it. You have to split them and run one after another. Or you have to wrap them into one EXECUTE BLOCK command.
    3. Also IBExpert has a separate window of Script Executive for multiple commands running. It is not SQL Editor which is designed to execute ONE command, it is a separate window in another menu - https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.ScriptExecutive

    Table creation command is described here: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html

    Basically what you trying to do looks like this, if to do it in one command:

    CREATE TABLE bills (
        id              INTEGER NOT NULL,
        code        VARCHAR(25) NOT NULL, 
        dateOfGeneration   DATE NOT NULL,
        job_id     INTEGER NOT NULL,
      PRIMARY KEY ( id,job_id )
    )
    

    or if you insist on naming then perhaps

    CREATE TABLE bills (
        id              INTEGER NOT NULL,
        code        VARCHAR(25) NOT NULL, 
        dateOfGeneration   DATE NOT NULL,
        job_id     INTEGER NOT NULL,
      CONSTRAINT bills_pk PRIMARY KEY ( id,job_id )
    )