FSQL

FSQL is simple command line tool for working with Firebird databases. It is very similar to Firebird's standard ISQL, it implements most of it's functionality, plus some extra features, like possibility to insert file contents into blob, export data in csv format, gfix functionality accessible in sql script (e.g. shutdown database), show connected users, better indication of errors in script, etc. Currently SHOW commands use simpler formatting than ISQL (because there is no special code so far, they are just predefined selects), and few SHOW commands are not fully implemented yet.

Why to use FSQL instead of standard ISQL ? What extra features are in FSQL ?

• It "fixes" many bugs that are in ISQL.
• Less consumption of transactions.
• Uses colors to highlight errors, warnings, etc.
• If the error occurs in sql script, the offending command is displayed, even if echo is off.
• New commands:

  @                 <command>           (suppress echo for current command)
  CD                [<directory>]       (display or change working directory)
  DISCONNECT
  ECHO              [<message>]   - display message, can use variables %TIMER% %CURRENT_TIMESTAMP% %CURRENT_TIME% %CURRENT_DATE%
  IMPORT   {CSV|DBF} [FILE] <input-file>  '<insert-command>' [NOATOMIC]
  EXPORT   CSV {'<select-command>' | TABLE <table>} [[FILE] <output-file>]
  SLEEP             [<sec> [MS | MIN]]
  
  SET BAIL          [ON|OFF]            (leave script on error)
  SET HEADing       [ON|OFF|<number>]   (print columnnames)
  SET TALK          [ON|OFF]            (ON=show result, OFF=do dummy fetches)
  SET ROWCOUNT      <number>            (max. number of fetched rows, 0=OFF)
  SET SQLDA_DISPLAY [ON|OFF]            (show datatypes of output parameters)
  SET TIMER         [<name>]            (set timer variable %TIMER% or %TIMER_name%)
  SET VALUE[S]      [<value>,...]       (set values for parametrized queries)
  SET EXPORT  [DATE 'format'] [TIME {'format' | DEFAULT}] [DMY | MDY | YMD]
  SET STAT[S] [ON|OFF] [STATS|COUNTERS|ALL] [FULL|SHORT] [[NO|SEPARATE|ONLY] PREPARE] [[NO] SYSTEM]
  SET STAT[S]       START|END
  
  SHOW CONNECTION[S]                    (show list of connected users)
  SHOW TRANSACTION[S]
  SHOW SUMMARY                          (show number of db objects)
  SHOW COLLATION[S] [<collation> | <charset>]
  SHOW VALUE[S]                         (show values set by SET VALUES command
  

  EXPORT and IMPORT can handle blob fields, but IMPORT currently does not like binary values (limitation of Delphi's Readln() function).

• Extended commands:

  CONNECT '<dbspec>' [USER 'username'] [PASSWORD 'password'] [ROLE 'rolename']
    [CACHE int] [SWEEP] [PASSWORD_ENCRYPTED 'password']
    [SWEEP_INTERVAL <n>] [PAGE_BUFFERS <n>] [SET_SQL_DIALECT <n>]
    [[NO] FORCE_WRITE] [[NO] RESERVE] [READ {WRITE|ONLY}]
    [ONLINE [NORMAL|MULTI|SINGLE] ]
    [SHUTDOWN [MULTI|SINGLE|FULL] {ATTACHMENT|CONNECTION|TRANSACTION|FORCE} <sec>]
    [[NO] GARBAGE_COLLECT] [ACTIVATE_SHADOW] [DELETE_SHADOW]
    [VALIDATE [FULL] [NO_UPDATE] [IGNORE] [MEND]]
    [[NO] DB_TRIGGERS] [PROCESS_NAME 'name']
  SHOW DATABASE [FULL]
  SHOW INDEX    [<index> | <table> | SYSTEM | ALL]
  HELP          [SET | SHOW | SERVICE]
  BLOBDUMP <blobid> <file> [FILTER <num1> <num2>]    (export using blob-filter)
  EXIT [<exitcode>]  (without <exitcode>, fsql's exit-code will contain number of errors)
  QUIT [<exitcode>]
  

  INPUT command automatically changes current directory when called on upper level and full path is used. This is useful for commands INPUT, CREATE DATABASE, BLOBDUMP, @'blob', IMPORT, EXPORT

  Note that not all parameters of CONNECT can be used together, however FSQL does not currently make any checking.

• Inserting data from file to blob/varchar field using special macro in INSERT or UPDATE command:
  

    @'filename'

  Example:

    INSERT INTO tab(id, blobfield) VALUES (1, @'c:\f.txt'); 

• Although SHOW commands are not fully implemented yet, in some cases they will show more informations than ISQL, e.g.:

    SHOW INDEX ... will show index type (PRIMARY KEY,FOREIGN KEY,UNIQUE) and selectivity
    SHOW PROC  ... will display procedure owner
    SHOW ROLE  ... will display role owner, and possibly WITH ADMIN OPTION flag
    SHOW TABLE ... will display table owner and highest format number
    SHOW DATABASE  will display creation date, number of buffer pages, ReadOnly and Reserve flags
  

• FSQL is able to connect to service manager, and thus e.g. show all opened databases, create new users or make backups directly from sql script (for list of commands type HELP SERVICE).

    CONNECT SERVICE 'service_name' [USER 'username'] [PASSWORD 'password']
    DISCONNECT SERVICE
    SWEEP 'dbname'
  
    BACKUP 'dbname' 'backupfile' [<length> 'backup2' ...]
      [VERBOSE] [CONVERT] [METADATA[_ONLY]] [NO GARBAGE[_COLLECT]]
      [IGNORE_CHECKSUMS] [IGNORE_LIMBO] [NO DB_TRIGGERS]
    RESTORE 'backupfile' ['backup2' ...] 'dbfile' [<length> 'dbfile2' ...]
      [VERBOSE] [PAGE_BUFFERS <num>] [PAGE_SIZE <num>] [READ_ONLY|READ_WRITE]
      [INACTIVE] [KILL] [NO_VALIDITY] [ONE_AT_A_TIME] [USE_ALL_SPACE] [REPLACE]
      [FIX_FSS_DATA '<charset>'] [FIX_FSS_METADATA '<charset>']
  
    NBACKUP 'dbfile' 'backupfile' [LEVEL <n>] [[NO] DB_TRIGGERS]
    NRESTORE 'dbfile' 'backupfile' ['backupfile' ...]
  
    SVC_MGR CREATE USER 'username' [PASSWORD 'password'] [FIRSTNAME '...'] [MIDDLENAME '...'] [LASTNAME '...']
    SVC_MGR ALTER USER 'username' [PASSWORD 'password'] [FIRSTNAME '...'] [MIDDLENAME '...'] [LASTNAME '...']
    SVC_MGR DROP USER 'username'
    SVC_MGR SHOW USER[S] ['username']
  
    SHOW SERVICE
    SHOW ATTACHMENTS
    SHOW STATS 'dbname' [HEADER | [DATA] [INDEX] [ALL] [SYSTEM]] [RECORD[_VERSIONS]] [TABLE <tab>,...]
    SHOW LOG
  

Which features of ISQL are not implemeted in FSQL.

• Extracting database structure, i.e. SHOW commands and -X,-A command line switches.
  (In fact you can execute SHOW commands, but they will just execute simple predefined SELECT commands, so the result is usually not as complex and not as nicely formatted as that of ISQL)

Which features have these tools in common.

• They use early binding to link with fbclient library. (i.e. registry entry is not used)
• They use the same command line parameters, and (nearly) the same formatting of output.
• SQL scripts written for ISQL should work unchanged with FSQL.

What are the differences between these tools.

• FSQL does not copy ISQL's bugs, instead it implements its own bugs :)
• FSQL parses command line from left to right, ISQL from right to left. It means:
  In ISQL (unlike FSQL) you can create stored procedure without using SET TERM command if it fits into one line, like
    CREATE PROCEDURE P AS BEGIN POST_EVENT 'STOP'; END;
  In FSQL (unlike ISQL) you can put several commands on one line, like
    SET ECHO OFF; EXECUTE PROCEDURE P; COMMIT;

Other notes ...

• Why was this tool written ? Well, it is in fact just by-product, main goal was to create component for executing sql scripts for modifying and upgrading databases (which is main reason why full implementing of SHOW commands had low priority). FSQL is just UI wrapper for this component.
• Why the name FSQL ? Because the name of the firm it was written for is Farmis, because it works with Firebird, because it is Fast, and because it has some additional Features.
• Platform ... Windows.
• License ... freeware.
• Here are some examples of sql commands...

Linux version ?

Screenshot

Download

Download GUI version WFSQL v1.8.2

Some changes in the latest version (FSQL v1.8.2, 2009-12-10):

• bugfixes: RESTORE command, SHOW INDEX command, SHOW PROC command, executing DDL with AUTODDL OFF
• some minor improvements and bugfixes that I do not remember :)
• support for FB2.5
• new commands NBACKUP, NRESTORE
• new parametrs for RESTORE (FIX_FSS_DATA, FIX_FSS_METADATA)
• commands for managing users via services API now prepended with SVC_MGR, to distinguish them from built-in FB commands.