ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/examples/fbsql/readme.txt
Revision: 209
Committed: Wed Mar 14 12:48:51 2018 UTC (6 years ago) by tony
Content type: text/plain
File size: 4857 byte(s)
Log Message:
Fixes Merged

File Contents

# Content
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