| 2 |
|
============== |
| 3 |
|
|
| 4 |
|
fbsql is more than just a simple example and is an ISQL replacement console mode program for |
| 5 |
< |
non-interactive use. fbsql uses TIBXScript as its SQL Script Engine and TIBExtract to |
| 6 |
< |
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. |
| 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 |
| 14 |
– |
-f <filename> execute SQL script from file |
| 22 |
|
-h show this information |
| 23 |
+ |
-i <filename> execute SQL script from file |
| 24 |
+ |
-o <filename> output to this file instead of stdout |
| 25 |
|
-p <password> provide password on command line (insecure) |
| 26 |
|
-r <rolename> open database with this rolename |
| 27 |
|
-s <sql> Execute SQL text |
| 34 |
|
Saving the username and/or password as environment variables avoids having to enter |
| 35 |
|
them on the command line and is a more secure means of provding the password. |
| 36 |
|
|
| 37 |
+ |
If no password is provided on the command line or through the environment, then the |
| 38 |
+ |
user is prompted for a password to be entered securely. |
| 39 |
+ |
|
| 40 |
+ |
If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs |
| 41 |
+ |
interactively. |
| 42 |
+ |
|
| 43 |
|
fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus |
| 44 |
|
IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File" |
| 45 |
|
and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again |
| 46 |
|
immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to |
| 47 |
|
remember it. |
| 48 |
|
|
| 49 |
+ |
SQL Statements Supported |
| 50 |
+ |
======================== |
| 51 |
+ |
|
| 52 |
+ |
All DML and DDL Statements are supported. |
| 53 |
+ |
|
| 54 |
+ |
CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported. |
| 55 |
+ |
|
| 56 |
+ |
Additionally, RECONNECT is interpreted as dropping the connection and reconnecting. |
| 57 |
+ |
|
| 58 |
+ |
ISQL Command Support |
| 59 |
+ |
==================== |
| 60 |
+ |
|
| 61 |
+ |
SET SQL DIALECT |
| 62 |
+ |
SET TERM |
| 63 |
+ |
SET AUTODDL |
| 64 |
+ |
SET BAIL |
| 65 |
+ |
SET ECHO |
| 66 |
+ |
SET COUNT |
| 67 |
+ |
SET STATS |
| 68 |
+ |
SET NAMES <character set> |
| 69 |
+ |
SET HEADING |
| 70 |
+ |
SET ROWCOUNT |
| 71 |
+ |
SET PLAN |
| 72 |
+ |
SET PLAN ONLY |
| 73 |
+ |
QUIT |
| 74 |
+ |
EXIT |
| 75 |
+ |
|
| 76 |
+ |
Examples |
| 77 |
+ |
======== |
| 78 |
+ |
|
| 79 |
|
To use, compile the program in the Lazarus IDE and run it from the command line. The |
| 80 |
|
above gives the command line parameters. For example: |
| 81 |
|
|
| 84 |
|
will write out the metadata for the local employee database to stdout (assuming |
| 85 |
|
default password). |
| 86 |
|
|
| 87 |
+ |
fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee |
| 88 |
+ |
|
| 89 |
+ |
will dump the employee database to a text file (employeedump.sql). |
| 90 |
+ |
|
| 91 |
+ |
fbsql -u SYSDBA -p masterkey -i employeedump.sql |
| 92 |
+ |
|
| 93 |
+ |
will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE |
| 94 |
+ |
DATABASE" statement is at the start of this file and should be edited to identify |
| 95 |
+ |
the database file that is to be created. Alternatively, |
| 96 |
+ |
|
| 97 |
+ |
fbsql -u SYSDBA -p masterkey -i employeedump.sql new-employee.fdb |
| 98 |
+ |
|
| 99 |
+ |
will restore the database to the database file 'new-employee.fdb' provided that it |
| 100 |
+ |
has already been created as an empty database. Note that in this case, the "CREATE |
| 101 |
+ |
DATABASE" statement should remain commented out. |
| 102 |
+ |
|
| 103 |
|
fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee |
| 104 |
|
|
| 105 |
|
will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming |
