FirebirdSQL logo

ADD

ADD table_name

This command, when passed a table name, prompts you for each column’s data and adds a row to the table.You may add as many rows as you wish, as the command continues until either an error occurs, or the kbd:[Enter] key is pressed without data.If you wish to set a column to NULL, type it in exactly as shown.

SQL> add country;

Enter data or NULL for each column.  RETURN to end.
Enter COUNTRY>Scotland
Enter CURRENCY>GBP

Enter COUNTRY>


SQL> commit;

COPY

COPY from_table_name to_table_name [other_database]

The copy command allows you to copy most of the structure of a table to a new table, in the current database or to a different one.Unfortunately it has a couple of problems:

  • It shells out to the commandline to do the work, and connects to the receiving database using an application named isql.If, like me, your system has renamed isql to isql-fb, you will actually end up running the wrong isql application and confusing error messages will be the only result.

  • When using an embedded connection, and the ServerMode is set to Super/ThreadedDedicated (the default), a copy within the same database will fail because the database file is opened with exclusive access by the first isql session.

  • It assumes that isql will be on the $PATH or %PATH%.

  • You need to define ISC_USER and ISC_PASSWORD for the child isql process to log in to the receiving database to create the table.This is very insecure.

  • Because of the need for ISC_USER and ISC_PASSWORD, the receiving database must be running on the same server as the source database.

  • The data in the table is not copied to the receiving database.Only the following parts of the table’s structure is copied.

    • Domains required to recreate the table.This only applies if the copy is to another database.

    • The table itself will be created.

    • Primary key constraint, if there is one.

    • The index used to support the primary key constraint, if there is one.

  • Not all structure of the table is actually copied.Missing are:

    • Foreign Key constraints.

    • Check constraints.

    • Indices other than the primary key index.

    • Triggers.

    • All table data.

If you wish to copy to a different database, then the other database must be on the same server as the current one.You cannot, for example, connect to a database on a server named tux, and copy a table to a database running on the server tuxrep.The copy command has no way to allow you to pass a username and/or password and, equally, setting ISC_USER and ISC_PASSWORD only affects databases on the current server.

tux> $ export ISC_USER=SYSDBA
tux> $ export ISC_PASSWORD=secret
tux> isql employee
Database:  employee, User: sysdba

SQL> -- MAke a copy of the employee table into this database.
SQL> copy employee employee_2;

SQL> -- Compare table structures...
SQL> show table employee;
EMP_NO                          (EMPNO) SMALLINT Not Null
FIRST_NAME                      (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME                       (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT                       VARCHAR(4) Nullable
HIRE_DATE                       TIMESTAMP Not Null DEFAULT 'NOW'
DEPT_NO                         (DEPTNO) CHAR(3) Not Null
                                CHECK (VALUE = '000' OR
                                (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
JOB_CODE                        (JOBCODE) VARCHAR(5) Not Null
                                CHECK (VALUE > '99999')
JOB_GRADE                       (JOBGRADE) SMALLINT Not Null
                                CHECK (VALUE BETWEEN 0 AND 6)
JOB_COUNTRY                     (COUNTRYNAME) VARCHAR(15) Not Null
SALARY                          (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
                                CHECK (VALUE > 0)
FULL_NAME                       Computed by: (last_name || ', ' || first_name)

CONSTRAINT INTEG_28:
  Foreign key (DEPT_NO)    References DEPARTMENT (DEPT_NO)
CONSTRAINT INTEG_29:
  Foreign key (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
  References JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
CONSTRAINT INTEG_27:
  Primary key (EMP_NO)
CONSTRAINT INTEG_30:
  CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country))

Triggers on Table EMPLOYEE:
SET_EMP_NO, Sequence: 0, Type: BEFORE INSERT, Active
SAVE_SALARY_CHANGE, Sequence: 0, Type: AFTER UPDATE, Active

SQL> show table employee_2;
EMP_NO                          (EMPNO) SMALLINT Not Null
FIRST_NAME                      (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME                       (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT                       VARCHAR(4) Nullable
HIRE_DATE                       TIMESTAMP Not Null DEFAULT 'NOW'
DEPT_NO                         (DEPTNO) CHAR(3) Not Null
                                CHECK (VALUE = '000' OR
                                (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
JOB_CODE                        (JOBCODE) VARCHAR(5) Not Null
                                CHECK (VALUE > '99999')
JOB_GRADE                       (JOBGRADE) SMALLINT Not Null
                                CHECK (VALUE BETWEEN 0 AND 6)
JOB_COUNTRY                     (COUNTRYNAME) VARCHAR(15) Not Null
SALARY                          (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
                                CHECK (VALUE > 0)
FULL_NAME                       Computed by: (last_name || ', ' || first_name)

CONSTRAINT INTEG_93:
  Primary key (EMP_NO)

SQL> -- Check indices on both tables...
SQL> show indices employee;
NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)
RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO)
RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY)
RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO)

SQL> show indices employee_2;
RDB$PRIMARY27 UNIQUE INDEX ON EMPLOYEE_2(EMP_NO)

SQL> -- Check data counts on both tables...
SQL> select count(*) from employee;

       COUNT
============
          42

SQL> select count(*) from employee_2;

       COUNT
============
           0

The copy command only works provided your isql application is really named isql.In addition, if you have lots of data in the table, you still have to copy it manually as the copy command will only copy the table structure.Remember that the new table will have no triggers, no foreign keys, no indices — other than the primary key one — and no data.

Note

It is possible that the copy command will be removed from isql at some future release.

INput

INput filename

This command enables the user to execute a number of commands from a script file rather than manually typing them all into isql at the prompt.The script may contain any mix of DDL and/or DDL commands, along with isql commands to redirect output, change options, etc.

SQL> shell;

$ cat test.sql
drop table fred;
commit;

$ exit;

SQL> show table fred;
A                               INTEGER Nullable
B                               INTEGER Not Null

SQL> input test.sql;

SQL> show table fred;
There is no table FRED in this database

OUTput

OUTput [filename]

This command redirects all output that normally is displayed on the screen, to a specific file.If a filename is supplied, all subsequent output goes to that file and is not displayed on screen.If no filename is supplied, output is once more redirected to the screen.

SQL> output test.log;

SQL> show tables;

SQL> output;

SQL> shell;

$ cat test.log

       COUNTRY                                CUSTOMER
       DEPARTMENT                             EMPLOYEE
       EMPLOYEE_PROJECT                       FRED
       JOB                                    PROJECT
       PROJ_DEPT_BUDGET                       SALARY_HISTORY
       SALES