IQU - Commands

Connect / Disconnect

 
CONNECT '<databasename>' [USER '<username>'] [PASSWORD '<password>'] [ROLE <role>][/sql]
 

Establishes a connection with the database <databasename>, logs in using the name <username> and the password <password>. When the username and/or the password are missing IQU will ask for them. If a <role> is specified, the connection will be established using the rights for the specified role.

 
DISCONNECT
 

Disconnects from the active database.

 
RECONNECT
 

Executes a COMMIT, disconnects from the database (DISCONNECT)

and reconnects to the database (CONNECT).

Can be usefull when the database locks an object because of a previous statement and you want to execute a DDL-statement.

 
SET STORE_CONNECT <ON|OFF>
 

Set STORE_CONNECT to ON if you want IQU to remember the last connect command.

The command will be displayed when IQU is restarted.

 

Create / Drop Database

 
CREATE DATABASE '<databasename>' USER '<username>' PASSWORD '<password>' [PAGE_SIZE <pagesize>] [DEFAULT CHARACTER SET <character set>]
 

Creates a database with the filename <databasename>. Logs in as user <username> with password <password>. De <pagesize> and <character set> are optional. Consult your manual for additional information on pagesizes and character sets.

 
DROP DATABASE
 

Removes the active database.

 

Transaction management

 
START TRANSACTION
 

Starts a transaction.

 
COMMIT [TRANSACTION]
 

Commit the changes of the current transaction.

The keyword TRANSACTION is optional.

 
ROLLBACK [TRANSACTION]
 

Rollback the pending changes of the current transaction.

The keyword TRANSACTION is optional.

 
SET START_TRANSACTION <IMPLICIT|EXPLICIT>
 

Switches between the implicit and explicit transaction model. The default model is the implicit transaction model. In the implicit transaction model a transaction is started immediatly after you have been connected to the database. After you commit or rollback a new transaction is automaticly started. In the explicit model you will have to start a transaction using the START TRANSACTION command.

 
SET TRANSACTION_MODE <READONLY|READCOMMITTED|SNAPSHOT>

or

SET TRANSACTION MODE <READONLY|READCOMMITTED|SNAPSHOT>
 

Sets the transaction mode.

 

User management

 
CREATE USER '<username>' PASSWORD '<password>'
 

Creates a user with the name <username> and the password <password>.

 
DROP USER '<username>'
 

Removes the user with name <username>.

 
ALTER USER '<username>' PASSWORD '<new password>'
 

Changes the password of the user <username> in <new password>.

 

Script options

 
SET PARAMETER <name> <value>
 

Sets parameter <name> to <value>.

 
SET PARAMETER_REPLACE <ON|OFF>
 

Enables or disabled the replacement of parameters.

 
ECHO <Text>
 

Displayes the text <text> to the output screen.

 
SET SUPPRESS_ERROR <ON|OFF>
 

If SUPPRESS_ERROR is switched on errors while be ignored when executing a script. After an error IQU will continue to execute the next statement. The error message will be displayed in the result screen.

 
SET SUPPRESS_OUTPUT <ON|OFF>
 

If the SUPPRESS_OUTPUT is on, the result of every query in the current statementblock is suppressed. The SUPPRESS_OUTPUT is automatically disabled for the next statementblock.

 
EXECUTE SCRIPT '<scriptname>'
 

Executes the script <scriptname>.

 
EXECUTE PROGRAM '<program name>'
 

Runs the program <program name> and waits until the program finishes.

 
SLEEP <Number>
 

Waits <number> of seconds before continuing.

 
SET ROOT_DIRECTORY '<directory>'
 

Sets the base directory for the databases to <directory>. After setting the base directory you can omit the directory when using other commands such as CONNECT.

 

Connection settings

 
SET FORCED_WRITE <ON|OFF>
 

IF FORCED_WRITE is on every change in the database will be written directly to the disk. Switching FORCED_WRITE off will give a significant performance boost to SQL-statements or script which are inserting, updating or deleting a lot of records. IF FORCED_WRITE is turned off and the computer fails the unwritten changes will be lost. After changing the FORCED_WRITE setting you have to logoff and on to activate it.

 
SET CHARACTER_SET <character set>

or

SET NAMES <character set>
 

Changes the active character set to <character set>. After changing this setting you have to logoff and on to activate it.

 
SET SQL_DIALECT <dialect number>

or

SET SQL DIALECT <dialect number>
 

Sets the SQL dialect to <dialect number>. Where the <dialect number> 1, 2 or 3. After changing this setting you have to logoff and on to activate it.

 

Meta data

 
DESCRIBE [<relationname>]
 

DESCRIBE shows a list of all tables.

DESCRIBE <relationname> shows information about the columns of the specified <relationname>

The systems tables will not be shown.

 
DESCRIBE PROCEDURE [<procedurename>]

or

SHOW PROCEDURE [<procedurename>]
 

SHOW PROCEDURE shows a list with all stored procedures.

SHOW PROCEDURE <procedurename> shows the parameters for the stored procedure <procedurename>.

 
DESCRIBE FUNCTION [<functionname>]

or

SHOW FUNCTION [<functionname>]
 

SHOW FUNCTION shows a list with all functions.

SHOW FUNCTION <functionname> shows the parameters of the function <functionname>

 
SHOW SYSTEM_TABLES
 

Shows a list with all system tables.

 
DESCRIBE COMPLETE PROCEDURE [<procedurename>]
 

DESCRIBE PROCEDURE shows the source of all stored procedures.

DESCRIBE PROCEDURE <procedurename> shows the source of the stored procedure <procedurename>.

 
DESCRIBE TRIGGER [<triggername>]
 

DESCRIBE TRIGGER shows the source of all triggers.

DESCRIBE TRIGGER <triggername> shows the source for the trigger <triggername>.

 
DESCRIBE TRIGGER FOR <relationname>
 

Shows the source for all the triggers of table <relationname>.

 
DESCRIBE GENERATOR [<generatorname>]
 

DESCRIBE GENERATOR shows all the generators.

DESCRIBE GENERATOR <generatorname> shows the current value for <generatorname>.

 
DESCRIBE EXCEPTION [<exceptionname>]
 

DESCRIBE EXCEPTION shows all the exceptions.

DESCRIBE EXCEPTION <exceptionname> shows the <exceptionname>.

 
DESCRIBE DOMAIN [<domainname>]
 

DESCRIBE DOMAIN shows all the domains.

DESCRIBE DOMAIN <domainname> shows the <domainname>.

 
DESCRIBE INDEX [<indexname>]
 

DESCRIBE INDEX shows all the indices.

DESCRIBE INDEX <indexname> shows the <indexname>.

 
DESCRIBE INDEX FOR <relationname>
 

Shows all the indices for <relationname>.

 
DESCRIBE DATABASE
 

Shows the complete database.

 

Display settings

 
SET COLOR_SCHEME <DEFAULT|CLASSIC>
 

Sets the color scheme.

Persistent.

 
SET DISPLAY <ON|OFF>
 

Set DISPLAY off to disable the output to the output screen.

 
SET ECHO <ON|OFF>
 

If the ECHO is on, the executed statement will be displayed before the'#13#10'result in the result screen.

 
SET PLAN <ON|OFF|ONLY>
 

If SET PLAN is on, IQU will show the indixes used to execute the query. If SET PLAN is set to ONLY, the query is not executed.

 
SET SHOW_RECORD_COUNT <ON|OFF>
 

If SHOW_RECORD_COUNT is on, at the end of each query the number of returned records will be displayed.

 
SET SHOW_RECORD_HEADER <ON|OFF>
 

If the SHOW_RECORD_HEADER is on, the columnnames and a separatorline will be displayed before the result in the result screen.

 
SET STATISTICS_DISPLAY <ON|OFF>
 

If STATISTICS_DISPLAY is on, after each SQL-statement a statistical summary'#13#10'will be displayed.

Records returned / Elapsed time / Reads from memory buffer / Writes to memory buffer / Page reads / Page writes / Delta Server memory

 
SET TIME_DISPLAY <ON|OFF>
 

If the TIME_DISPLAY is on, both the date and the time will be displayed in the result screen for datetime fields.

 
SET TRIM_CHAR_FIELDS <ON|OFF>
 

Set TRIM_CHAR_FIELDS to ON if the trailing spaces of Char fields must be removed before the field is shown.

 

Excel export

 
XLS CREATE '<filename>'
 

Creates a new Excel file with the filename <filename>.

 
XLS CLOSE
 

Closes the Excel file.

 
XLS SHEET '<sheet name>'
 

Activates the sheet with name <sheet name>.

 
XLS HEADER '<string>'
 

Writes a header to the Excel file.

 
XLS ECHO '<string>'
 

Writes the <string> to the Excel file.

 
XLS CAPTURE <ON|OFF>
 

By default capturing is turned on. When capturing is turned on the result of select statements is stored in the Excel file.

 
XLS OPEN '<filename>'
 

Opens a existing Excel file with the filename <filename>.

 
XLS COLUMN '<column number>'
 

Sets the cursor in Excel to the specified <column number>. Column A corresponds to number 1.

 
XLS ROW '<row number>'
 

Sets the cursor in Excel to the specified <row number>. Row 1 corresponds to number 1.

 
XLS MOVE ROW '<number>'
 

Moves the cursor in Excel with <number> rows down. When the <number> is negative the cursor moves up.

 
XLS FORMULAPREFIX '<prefix>'
 

Sets the prefix of how IQU determines if the data contains a formula. Default value is '=='.

If the cell data starts with the prefix, the data is split in a formula part and a formatting part. Syntax <prefix>"formula","format". The text %row% is replaced by the current row.

Example usage:

XLS FORMULAPREFIX ==;

XLS CREATE "C:\Output.xls";

SELECT Sum(UsedItems) Used_Items, Sum(NewItems) New_Items, '=="A%row%+B%row%","0"' Total_Items, '=="A%row%/C%row%","0%"' Percentage_Used

FROM Stock;

XLS CLOSE;

This will add two columns: A numeric Total_Items column which adds column A and B and a percentage column Percentage Used which will display the percentage of used items.

 

Misc

 
HELP [<command>]
 

HELP shows a list of all the commands.

HELP <command> shows a brief explaination of a <command>.

 
SET TERM <terminator>
 

Changes the SQL statement terminator to <terminator>.

 
SET WARN_ON_RECORDS_RETURNED <number|0>
 

Setting the WARN_ON_RECORDS_RETURNED will give you a warning after the <number> records have been fetched.

Use 0 to disable the warning. By default the <number> is 1000.

 
SET CLIENT_LIBRARY <Auto|FBClient|GDS32|FBEmbed>
 

Set CLIENT_LIBRARY to the desired Firebird/InterBase client DLL.

 

Statement generation

 
GET INSERT <relationname>
 

Generates an insert statement for the table <relationname>.

 
GET SELECT <relationname>
 

Generates a select statement for the table <relationname>.

 
GET UPDATE <relationname>
 

Generates an update statement for the table <relationname>.

 
GET CREATE <relationname> [TYPE CODE] [FK1 <relationname>] [FK2 <relationname]> ...
 

Generates a create table statement for the table <relationname>. By default a primary key column will be created with the name ID. A generator and trigger will be created for this column. If TYPE CODE is specified a table will be created with two columns: 'code' and 'name'. The code column is the primary key and is of type Char. The name column is the secondary key and is of type VarChar. FK1, Fk2 etc can be used to specify a reference to another table. Such a reference result in a column with the name of the referenced table. The column will have the domain of the primary key of the referenced table and a foreign key to the referenced table will be generated.