| 1 |
Example: fbsql
|
| 2 |
==============
|
| 3 |
|
| 4 |
fbsql is more than just a simple example and is an ISQL replacement console mode program for
|
| 5 |
both interactive and non-interactive use. fbsql uses TIBXScript as its SQL Script Engine and
|
| 6 |
TIBExtract to extract metadata from the database. Select queries are handled by by outputing the
|
| 7 |
query results to stdout in CSV format suitable for loading into a spreadsheet, as insert statements,
|
| 8 |
or in a block format.
|
| 9 |
|
| 10 |
It can be used to dump an entire database in text (SQL) format including metadata and user
|
| 11 |
data, with binary blobs and array data encoded in a simple XML format. It can also be used
|
| 12 |
to create a datbase from an SQL dump. This format is less compact that gbak but allows
|
| 13 |
for metadata editing, simplifying complex metadata changes where there are many nested
|
| 14 |
dependencies to resolve.
|
| 15 |
|
| 16 |
Usage: fbsql <options> <database name>
|
| 17 |
Options:
|
| 18 |
-a write database metadata to stdout
|
| 19 |
-A write database metadata and table data to stdout
|
| 20 |
-b stop on first error
|
| 21 |
-e echo sql statements to stdout
|
| 22 |
-g include grants to normal users in database metadata
|
| 23 |
-h show this information
|
| 24 |
-i <filename> execute SQL script from file
|
| 25 |
-o <filename> output to this file instead of stdout
|
| 26 |
-p <password> provide password on command line (insecure)
|
| 27 |
-r <rolename> open database with this rolename
|
| 28 |
-s <sql> Execute SQL text
|
| 29 |
-u <username> open database with this username (defaults to SYSDBA)
|
| 30 |
|
| 31 |
Environment Variables:
|
| 32 |
ISC_USER Login user Name
|
| 33 |
ISC_PASSWORD Login password
|
| 34 |
|
| 35 |
Saving the username and/or password as environment variables avoids having to enter
|
| 36 |
them on the command line and is a more secure means of provding the password.
|
| 37 |
|
| 38 |
If no password is provided on the command line or through the environment, then the
|
| 39 |
user is prompted for a password to be entered securely.
|
| 40 |
|
| 41 |
If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs
|
| 42 |
interactively.
|
| 43 |
|
| 44 |
fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus
|
| 45 |
IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File"
|
| 46 |
and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again
|
| 47 |
immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to
|
| 48 |
remember it.
|
| 49 |
|
| 50 |
SQL Statements Supported
|
| 51 |
========================
|
| 52 |
|
| 53 |
All DML and DDL Statements are supported.
|
| 54 |
|
| 55 |
CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.
|
| 56 |
|
| 57 |
Additionally, RECONNECT is interpreted as dropping the connection and reconnecting.
|
| 58 |
|
| 59 |
ISQL Command Support
|
| 60 |
====================
|
| 61 |
|
| 62 |
SET SQL DIALECT
|
| 63 |
SET TERM
|
| 64 |
SET AUTODDL
|
| 65 |
SET BAIL
|
| 66 |
SET ECHO
|
| 67 |
SET COUNT
|
| 68 |
SET STATS
|
| 69 |
SET NAMES <character set>
|
| 70 |
SET HEADING
|
| 71 |
SET ROWCOUNT
|
| 72 |
SET PLAN
|
| 73 |
SET PLAN ONLY
|
| 74 |
QUIT
|
| 75 |
EXIT
|
| 76 |
|
| 77 |
Examples
|
| 78 |
========
|
| 79 |
|
| 80 |
To use, compile the program in the Lazarus IDE and run it from the command line. The
|
| 81 |
above gives the command line parameters. For example:
|
| 82 |
|
| 83 |
fbsql -a -u SYSDBA -p masterkey employee
|
| 84 |
|
| 85 |
will write out the metadata for the local employee database to stdout (assuming
|
| 86 |
default password).
|
| 87 |
|
| 88 |
fbsql -a -g -u SYSDBA -p masterkey employee
|
| 89 |
|
| 90 |
as above metadata will include grants to normal users (and not just to PUBLIC).
|
| 91 |
|
| 92 |
fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee
|
| 93 |
|
| 94 |
will dump the employee database to a text file (employeedump.sql).
|
| 95 |
|
| 96 |
fbsql -u SYSDBA -p masterkey -i employeedump.sql
|
| 97 |
|
| 98 |
will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE
|
| 99 |
DATABASE" statement is at the start of this file and should be edited to identify
|
| 100 |
the database file that is to be created. Alternatively,
|
| 101 |
|
| 102 |
fbsql -u SYSDBA -p masterkey -i employeedump.sql new-employee.fdb
|
| 103 |
|
| 104 |
will restore the database to the database file 'new-employee.fdb' provided that it
|
| 105 |
has already been created as an empty database. Note that in this case, the "CREATE
|
| 106 |
DATABASE" statement should remain commented out.
|
| 107 |
|
| 108 |
fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee
|
| 109 |
|
| 110 |
will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming
|
| 111 |
default password).
|
| 112 |
|
| 113 |
fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee
|
| 114 |
|
| 115 |
will run the script CreateCountriesTable.sql from the script engine test suite and apply
|
| 116 |
it to the local employee database. Each statement will be echoed to stdout and
|
| 117 |
processing will stop on the first error.
|
| 118 |
|
| 119 |
Note that on Linux, to run a program from the command line that is not on the PATH,
|
| 120 |
you need to:
|
| 121 |
|
| 122 |
cd to the example directory "ibx/examples/fbsql"
|
| 123 |
run the program as "./fbsql" e.g.
|
| 124 |
|
| 125 |
./fbsql -a -u SYSDBA -p masterkey employee
|
| 126 |
|
| 127 |
|
