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 |
-t specify output format for SQL Statements
|
30 |
BLK (default) for block format
|
31 |
CSV for CSV format
|
32 |
INS for Insert Statement format
|
33 |
-u <username> open database with this username (defaults to SYSDBA)
|
34 |
|
35 |
Environment Variables:
|
36 |
ISC_USER Login user Name
|
37 |
ISC_PASSWORD Login password
|
38 |
|
39 |
Saving the username and/or password as environment variables avoids having to enter
|
40 |
them on the command line and is a more secure means of provding the password.
|
41 |
|
42 |
If no password is provided on the command line or through the environment, then the
|
43 |
user is prompted for a password to be entered securely.
|
44 |
|
45 |
If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs
|
46 |
interactively.
|
47 |
|
48 |
fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus
|
49 |
IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File"
|
50 |
and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again
|
51 |
immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to
|
52 |
remember it.
|
53 |
|
54 |
SQL Statements Supported
|
55 |
========================
|
56 |
|
57 |
All DML and DDL Statements are supported.
|
58 |
|
59 |
CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.
|
60 |
|
61 |
Additionally, RECONNECT is interpreted as dropping the connection and reconnecting.
|
62 |
|
63 |
ISQL Command Support
|
64 |
====================
|
65 |
|
66 |
SET SQL DIALECT
|
67 |
SET TERM
|
68 |
SET AUTODDL
|
69 |
SET BAIL
|
70 |
SET ECHO
|
71 |
SET COUNT
|
72 |
SET STATS
|
73 |
SET NAMES <character set>
|
74 |
SET HEADING
|
75 |
SET ROWCOUNT
|
76 |
SET PLAN
|
77 |
SET PLAN ONLY
|
78 |
QUIT
|
79 |
EXIT
|
80 |
|
81 |
Examples
|
82 |
========
|
83 |
|
84 |
To use, compile the program in the Lazarus IDE and run it from the command line. The
|
85 |
above gives the command line parameters. For example:
|
86 |
|
87 |
fbsql -a -u SYSDBA -p masterkey employee
|
88 |
|
89 |
will write out the metadata for the local employee database to stdout (assuming
|
90 |
default password).
|
91 |
|
92 |
fbsql -a -g -u SYSDBA -p masterkey employee
|
93 |
|
94 |
as above metadata will include grants to normal users (and not just to PUBLIC).
|
95 |
|
96 |
fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee
|
97 |
|
98 |
will dump the employee database to a text file (employeedump.sql).
|
99 |
|
100 |
fbsql -u SYSDBA -p masterkey -i employeedump.sql
|
101 |
|
102 |
will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE
|
103 |
DATABASE" statement is at the start of this file and should be edited to identify
|
104 |
the database file that is to be created. Alternatively,
|
105 |
|
106 |
fbsql -u SYSDBA -p masterkey -i employeedump.sql new-employee.fdb
|
107 |
|
108 |
will restore the database to the database file 'new-employee.fdb' provided that it
|
109 |
has already been created as an empty database. Note that in this case, the "CREATE
|
110 |
DATABASE" statement should remain commented out.
|
111 |
|
112 |
fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee
|
113 |
|
114 |
will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming
|
115 |
default password).
|
116 |
|
117 |
fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee
|
118 |
|
119 |
will run the script CreateCountriesTable.sql from the script engine test suite and apply
|
120 |
it to the local employee database. Each statement will be echoed to stdout and
|
121 |
processing will stop on the first error.
|
122 |
|
123 |
Note that on Linux, to run a program from the command line that is not on the PATH,
|
124 |
you need to:
|
125 |
|
126 |
cd to the example directory "ibx/examples/fbsql"
|
127 |
run the program as "./fbsql" e.g.
|
128 |
|
129 |
./fbsql -a -u SYSDBA -p masterkey employee
|
130 |
|
131 |
|