Interactive isql can be run locally or from a remote client.
Default Text Editor
Some isql commands access your system’s default text editor.
To start isql, open a command shell and cd to the Firebird /bin directory of your Firebird server or client installation. Key in the following command at the shell prompt and press the Enter key:isql [database_name] [-u[ser] <user-name> -pas[sword] <password>]
database_name is optional. If you include it, isql will open a connection to the database and start its shell already connected. It must be either the full path to the database from where you are or, in version 1.5 and later, a valid alias.
The switches –user <user-name> and –password <password> are optional when you are starting isql without a connection to the database and required when you are starting isql remotely. If the ISC_USER and ISC_PASSWORD environment variables are not set, they will also be required when you start isql locally.
starts the program../isql -user TEMPDBA -password osoweary [on POSIX] isql -user TEMPDBA -password osoweary [on Windows]
starts the program and stores the supplied user name and password without authenticating them.isql hotchicken:/data/mydatabase.fdb -user TEMPDBA -password osoweary
starts the program on a Windows client and connects to the database on a POSIX server, provided the user name and password are valid on the server../isql /data/mydatabase.fdb
starts the program locally on a Linux server and connects to the database, provided the environment variables ISC _USER and ISC _PASSWORD are set and are available to your Linux user profile.
If you are logged into the database when isql starts up, you will see a console display similar to Figure. The appearance of the surrounding shell depends on the operating system. The isql shell is the same on all platforms.
Console display when isql starts logged-in
If you did not enter a database path or you used a user name and password that are not defined on the server, you will see something similar to Figure.
Console display when isql starts not logged-in
If there were errors in the command string, or authentication problems, you might also see some error messages. If you can see the SQL> prompt, you are in the isql shell and you can connect to your database from there.
Connecting to a Database
To connect to a database from the SQL> prompt in the isql shell, use the following examples as syntax patterns. Notice that the syntax inside the isql shell is different from that used when passing the connection parameters from the system shell.CONNECT 'HOTCHICKEN:L:DATAEXAMPLESEMPLOYEE.FDB' USER 'SYSDBA' PASSWORD 'masterkey';
connects to a remote or local server named HOTCHICKEN.CONNECT 'L:DATAEXAMPLESEMPLOYEE.FDB';
connects to a local server where isql already knows your Firebird user name and password—either because you entered them correctly when you started isql or because isql is executing in a shell that can see the environment variables ISC_USER and ISC_PASSWORD.CONNECT 'HOTCHICKEN:EMP3' USER 'SYSDBA' PASSWORD 'masterkey';
is equivalent to the first example, in Firebird 1.5 and later, using an alias stored in aliases.conf on the server, that points to the path.CONNECT 'L:/DATA/EXAMPLES/EMPLOYEE.FDB';
is equivalent to the second example—slashes may be forward or backward in isql.
Server and Path Names
On Windows, do not confuse server names and shared disk resource names. The client/server network layer does not recognize mapped drives or shares. A drive identifier must always point to the actual drive letter of a hard drive or partition on the server host machine.
Regardless of whether you log in from the command line or do so when connecting inside the isql shell, authentication will fail if the server does not recognize the user name or the password. For example, Figure displays what you will see if your CONNECT statement fails.
If this happens to you, double-check that you have spelled the username and password correctly, and that the password is correct for case. Passwords are case sensitive; user names are not.
The Terminator Character
The default statement terminator is the semicolon (;), which is used for all of the examples in this chapter. You can change the terminator to any character or group of characters with the SET TERM[INATOR] command. For example, to change it to “!!”, use this statement:SQL> SET TERM !!; SQL>
The Continuation Prompt
If you press Enter without remembering to finish a statement with a terminator, you will see the continuation prompt CON> instead of the isql prompt SQL>:SQL> SHOW DATABASE CON>
If it was a mistake, simply type the terminator character and press Enter again. However, you can use this feature to make your typing easier to read, for example:SQL> CREATE TABLE ATABLE (
Transactions in isql
Transaction management in isql differs according to whether you issue a DDL statement, a SHOW command, or other kinds of statements.
When isql starts, it starts a transaction in SNAPSHOT (concurrency) isolation with a lock resolution setting of WAIT. Unless you run DDL statements or SHOW commands, the transaction stays current until you issue a COMMIT or ROLLBACK statement.
You can start an explicit transaction by committing the current transaction and using a SET TRANSACTION statement to start a new one. For example, to start a READ COMMITTED NO WAIT transaction:SQL> COMMIT;
When you have finished your task, just issue a COMMIT statement as usual. The next statement will revert to the default configuration.
Each time you issue a DDL statement, isql starts a special transaction for it and commits it immediately after you press Enter. A new transaction is started immediately afterward. You can change this automatic behavior by issuing the SET AUTODDL OFF command from the SQL prompt before you begin issuing your DDL statements:SQL> SET AUTODDL OFF;
To switch back to autocommit mode for DDL statements:SQL> SET AUTODDL ON;
For switching back and forth between autoddl on and off, a short version is available that simply sets autoddl off if it is on, and vice versa:SQL> SET AUTO;
The isql SHOW commands query the system tables. Whenever you invoke a SHOW command, isql commits the existing transaction and starts a new one in READ COMMITTED isolation. This ensures that you always have an up-to-date view of metadata changes as soon as they occur.
Retrieving the Line Buffer
From v.1.5 onward, isql allows you to retrieve the line buffer, in a similar fashion to the way the readline feature works on POSIX platforms. Use the up and down arrow keys to “scroll through” the isql buffer, a line at a time, to retrieve copies of lines you typed previously.
By default, isql issues warnings for certain conditions, for example:
For toggling the display off and on during an interactive isql session, use SET WARNINGS or its shorthand counterpart, SET WNG.
Errors in isql are handled and delivered in the same way as they are in a DSQL application. Isql displays an error message consisting of the SQLCODE variable and the text message from the Firebird status array, as shown in Figure.
Example of an error message in isql
SQL errors with sub-zero SQLCODEs mean the statement has failed to execute. They are all listed in Appendix X. You may also see one of the SQL warning or information messages, namely0: SUCCESS (successful execution) +1–99: SQLWARNING (system warning or information message)
+100: NOT FOUND (indicates that no qualifying rows were found, or “end of file”; that is, the end of the current active set of rows was detected)
Setting Dialect in isql
If you start isql and attach to a database without specifying a dialect, isql takes on the dialect of the database.
You can set the isql dialect in the following ways:
where n is 1, 2, or 3. If you specify the dialect this way, isql retains that dialect after connection unless you explicitly change it.
SET SQL DIALECT n;
Isql continues to operate in that dialect unless it is explicitly changed.
The dialect cannot be set as a parameter of a CREATE DATABASE statement.
The effects of commands may show some variations according to dialect:
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.