examples/fbsql/readme.txt

Example: fbsql
==============

fbsql is more than just a simple example and is an ISQL replacement console mode program for
both interactive and non-interactive use. fbsql uses TIBXScript as its SQL Script Engine and
TIBExtract to extract metadata from the database. Select queries are handled by by outputing the
query results to stdout in CSV format suitable for loading into a spreadsheet, as insert statements,
or in a block format.

It can be used to dump an entire database in text (SQL) format including metadata and user
data, with binary blobs and array data encoded in a simple XML format. It can also be used
to create a datbase from an SQL dump. This format is less compact that gbak but allows
for metadata editing, simplifying complex metadata changes where there are many nested
dependencies to resolve.

Usage: fbsql <options> <database name>
Options:
-a            write database metadata to stdout
-A            write database metadata and table data to stdout
-b            stop on first error
-e            echo sql statements to stdout
-h            show this information
-i <filename> execute SQL script from file
-o <filename> output to this file instead of stdout
-p <password> provide password on command line (insecure)
-r <rolename> open database with this rolename
-s <sql>      Execute SQL text
-u <username> open database with this username (defaults to SYSDBA)

Environment Variables:
ISC_USER      Login user Name
ISC_PASSWORD  Login password

Saving the username and/or password as environment variables avoids having to enter
them on the command line and is a more secure means of provding the password.

If no password is provided on the command line or through the environment, then the
user is prompted for a password to be entered securely.

If neither an "-s" or a "-i" option is provided on the command line, then fbsql runs
interactively.

fbsql uses IBX in console mode. Before opening this project you should tell the Lazarus
IDE about the ibexpressconsolemode package. All you need to do in the IDE is to select "Packages->Open Package File"
and open ibexpressconsolemode.lpk which you can find in the ibx root directory. You should then close it again 
immediately afterwards. There is no need to install or compile it. Opening the package is sufficient for Lazarus to
remember it.

SQL Statements Supported
========================

All DML and DDL Statements are supported.

CREATE DATABASE, DROP DATABASE, CONNECT and COMMIT are supported.

Additionally, RECONNECT is interpreted as dropping the connection and reconnecting.

ISQL Command Support
====================

SET SQL DIALECT
SET TERM
SET AUTODDL
SET BAIL
SET ECHO
SET COUNT
SET STATS
SET NAMES <character set>
SET HEADING
SET ROWCOUNT
SET PLAN
SET PLAN ONLY
QUIT
EXIT

Examples
========

To use, compile the program in the Lazarus IDE and run it from the command line. The
above gives the command line parameters. For example:

fbsql -a -u SYSDBA -p masterkey employee

will write out the metadata for the local employee database to stdout (assuming
default password).

fbsql -A -u SYSDBA -p masterkey -o employeedump.sql employee

will dump the employee database to a text file (employeedump.sql).

fbsql -u SYSDBA -p masterkey -i employeedump.sql

will recreate the database dumped in the file "employeedump.sql". Note that the "CREATE
DATABASE" statement is at the start of this file and should be edited to identify
the database file that is to be created. Alternatively,

fbsql -u SYSDBA -p masterkey -i employeedump.sql  new-employee.fdb

will restore the database to the database file 'new-employee.fdb' provided that it
has already been created as an empty database. Note that in this case, the "CREATE
DATABASE" statement should remain commented out.

fbsql -s "Select * From EMPLOYEE" -u SYSDBA -p masterkey employee

will write out the contents of the EMPLOYEE table in the local employee database to stdout (assuming
default password).

fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee

will run the script CreateCountriesTable.sql from the script engine test suite and apply
it to the local employee database. Each statement will be echoed to stdout and
processing will stop on the first error.

Note that on Linux, to run a program from the command line that is not on the PATH,
you need to:

cd to the example directory "ibx/examples/fbsql"
run the program as "./fbsql" e.g.

./fbsql -a -u SYSDBA -p masterkey employee


Revision:	47
Committed:	Mon Jan 9 15:31:51 2017 UTC (7 years, 10 months ago) by tony
Content type:	text/plain
File size:	4486 byte(s)
Log Message:	Committing updates for Release R2-0-1
#	User	Rev	Content
1	tony	37	Example: fbsql
2			==============
3
4			fbsql is more than just a simple example and is an ISQL replacement console mode program for
5	tony	47	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	tony	37
10	tony	47	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	tony	37	Usage: fbsql <options> <database name>
17			Options:
18			-a write database metadata to stdout
19	tony	47	-A write database metadata and table data to stdout
20	tony	37	-b stop on first error
21			-e echo sql statements to stdout
22			-h show this information
23	tony	47	-i <filename> execute SQL script from file
24			-o <filename> output to this file instead of stdout
25	tony	37	-p <password> provide password on command line (insecure)
26			-r <rolename> open database with this rolename
27			-s <sql> Execute SQL text
28			-u <username> open database with this username (defaults to SYSDBA)
29
30			Environment Variables:
31			ISC_USER Login user Name
32			ISC_PASSWORD Login password
33
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	tony	47	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	tony	37	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	tony	47	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	tony	37	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
82			fbsql -a -u SYSDBA -p masterkey employee
83
84			will write out the metadata for the local employee database to stdout (assuming
85			default password).
86
87	tony	47	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	tony	37	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
106			default password).
107
108			fbsql -b -e ../scriptengine/tests/CreateCountriesTable.sql -u SYSDBA -p masterkey employee
109
110			will run the script CreateCountriesTable.sql from the script engine test suite and apply
111			it to the local employee database. Each statement will be echoed to stdout and
112			processing will stop on the first error.
113
114			Note that on Linux, to run a program from the command line that is not on the PATH,
115			you need to:
116
117			cd to the example directory "ibx/examples/fbsql"
118			run the program as "./fbsql" e.g.
119
120			./fbsql -a -u SYSDBA -p masterkey employee
121
122