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
-g            include grants to normal users in database metadata
-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
-t            specify output format for SQL Statements
              BLK (default) for block format
              CSV for CSV format
              INS for Insert Statement format
-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 -g -u SYSDBA -p masterkey employee

as above metadata will include grants to normal users (and not just to PUBLIC).

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:	209
Committed:	Wed Mar 14 12:48:51 2018 UTC (6 years, 1 month ago) by tony
Content type:	text/plain
File size:	4857 byte(s)
Log Message:	Fixes Merged
#	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	tony	143	-g include grants to normal users in database metadata
23	tony	37	-h show this information
24	tony	47	-i <filename> execute SQL script from file
25			-o <filename> output to this file instead of stdout
26	tony	37	-p <password> provide password on command line (insecure)
27			-r <rolename> open database with this rolename
28			-s <sql> Execute SQL text
29	tony	209	-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	tony	37	-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	tony	47	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	tony	37	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	tony	47	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	tony	37	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	tony	143	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	tony	47	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	tony	37	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