PIRL.Database
Class Query_DB

java.lang.Object
  PIRL.Database.Query_DB

public class Query_DB
extends Object

Query_DB sends a SQL query to a Database and lists the results.

The Database server that is queried may be any for which the Database can obtain a Data_Port. Access information for the Database is provided in a Configuration file, which may be supplemented by command line options.

Database queries use standard SQL syntax. Depending on the specific database server that is accessed the syntax may have limitations and/or extensions.

The command line usage description provides details on the application operation.

Version:

3.9

Author:

Bradford Castalia, Sean Whitsitt - UA/PIRL

See Also:

PIRL.Database, PIRL.Configuration

Field Summary

static String	DATA_RECORD_DELIMITER Delimiter between a data record number and its field values.
static String	DEFAULT_CONFIGURATION_FILENAME The default Configuration filename.
static int	EXIT_CONFIGURATION_PROBLEM Application exit status when there is a Configuration file problem.
static int	EXIT_DATABASE_ERROR Application exit status when there is a problem accessing the Database.
static int	EXIT_INVALID_COMMAND_LINE_SYNTAX Exit status when invalid command line syntax was encounterd.
static int	EXIT_QUERY_FAILURE Application exit status when one or more Database queries failed.
static int	EXIT_SUCCESS Success exit status.
static String	FIELD_NAMES_PREFIX Prefix for the data table field names line.
static String	ID The Class identification with revision number and date.
static String	SQL_QUERY_FAILED Prefix for the listing line that indicates a failed SQL query.
static String	SQL_QUERY_PREFIX Prefix for the listing line that contains the SQL expression sent to the database server.

Constructor Summary

Query_DB()

Method Summary

static void	main(String[] arguments) Run the Query_DB application.
static void	Print_Table(Vector<Vector<String>> table, boolean prefix, boolean first_record_field_names) Print a table of field records.
static void	Usage() Command line usage syntax.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

ID

public static final String ID

The Class identification with revision number and date.

See Also:

Constant Field Values

DEFAULT_CONFIGURATION_FILENAME

public static final String DEFAULT_CONFIGURATION_FILENAME

The default Configuration filename.

See Also:

Constant Field Values

SQL_QUERY_PREFIX

public static final String SQL_QUERY_PREFIX

Prefix for the listing line that contains the SQL expression sent to the database server.

See Also:

Constant Field Values

SQL_QUERY_FAILED

public static final String SQL_QUERY_FAILED

Prefix for the listing line that indicates a failed SQL query.

See Also:

Constant Field Values

FIELD_NAMES_PREFIX

public static final String FIELD_NAMES_PREFIX

Prefix for the data table field names line.

See Also:

Constant Field Values

DATA_RECORD_DELIMITER

public static final String DATA_RECORD_DELIMITER

Delimiter between a data record number and its field values.

See Also:

Constant Field Values

EXIT_SUCCESS

public static final int EXIT_SUCCESS

Success exit status.

See Also:

Constant Field Values

EXIT_INVALID_COMMAND_LINE_SYNTAX

public static final int EXIT_INVALID_COMMAND_LINE_SYNTAX

Exit status when invalid command line syntax was encounterd.

See Also:

Constant Field Values

EXIT_CONFIGURATION_PROBLEM

public static final int EXIT_CONFIGURATION_PROBLEM

Application exit status when there is a Configuration file problem.

See Also:

Constant Field Values

EXIT_DATABASE_ERROR

public static final int EXIT_DATABASE_ERROR

Application exit status when there is a problem accessing the Database.

See Also:

Constant Field Values

EXIT_QUERY_FAILURE

public static final int EXIT_QUERY_FAILURE

Application exit status when one or more Database queries failed.

See Also:

Constant Field Values

Constructor Detail

Query_DB

public Query_DB()

Method Detail

main

public static void main(String[] arguments)

Run the Query_DB application.

Exit status values:

EXIT_SUCCESS

All queries succeeded.

EXIT_INVALID_COMMAND_LINE_SYNTAX

The command line contains invalid syntax.

EXIT_CONFIGURATION_PROBLEM

There was a problem with the configuration source. Either it couldn't be found or its contents are not valid PVL.

EXIT_DATABASE_ERROR

A connection with the database server could not be established. This could be due to incorrect access information in the configuration.

EXIT_QUERY_FAILURE

One or more queries failed. If multiple queries were specified some may have succeeded.

Parameters:

arguments - The command line arguments.

Print_Table

public static void Print_Table(Vector<Vector<String>> table,
                               boolean prefix,
                               boolean first_record_field_names)

Print a table of field records.

Each record in the table is listed as fields separated by a single horizontal tab character. Each record listed is terminated by a new-line sequence defined by the "line.separator" System property.

N.B.: While data tables are typically rectangular, no presumption is made that each record will have the same number of field values.

If the prefix argument is true each line of the table listing will contain a prefix that identifies the table record. If the first_record_field_names argument is also true the prefix for the first record will be the FIELD_NAMES_PREFIX. All other records will have as their prefix the record number followed by the DATA_RECORD_DELIMITER.

Parameters:

table - A Vector of Vector of Strings. If null nothing is done.

prefix - If true, each record listed will have an indentifying prefix; if false, no prefix will be listed.

first_record_field_names - If true, the first table record is taken to contain the column names of the field values that will be in the remaining records; if false, all table records are taken to contain field values.

Usage

public static void Usage()

Command line usage syntax.

Usage: Query_DB [<Options>] [-Query] <query> [...]
  Each query is an SQL query expression.
  Options -
    -Configuration [<filename>]
    -Server <server name>
    -CAtalog <catalog name>
    -Verbose
    -Help

Queries:

Each query is an SQL expression that will be run on the database server. Depending on the specific database server that is accessed the SQL query expression syntax may have limitations and/or extensions to the standard SQL syntax.

If the query contains spaces then it must be quoted; If it contains quotes then these must be escaped from shell interpretation (by a preceeding backslash) or the specification is already quoted then the specification quotes must be different from the enclosing quotes (use double quotes to enclose the specification and single quotes in the specification content). Other shell metacharacters - such as '*' or '!' - must also be escaped or enclosed in single quotes.

An example of a query expression to obtain all the records from the "Table" table in the "Catalog" catalog is:

'select * from Catalog.Table'

If the -catalog option had specified the "Catalog" catalog as the default then the "Catalog." portion of the expression would not be needed.

An example of a query expression that will list the "Field_1" and "Field_2" field values of "Table", using the default catalog, with a conditional clause that selects only those records having a "Text" field value of "text" and a "Number" field value of "123" is:

'select Field_1,Field_2 from Table where Text="text" and Number=123'

The result of each query is listed to the standard output. The first line of each query listing contains the selected field names; this line will be prefixed with "=== ". The following lines contain the values of the fields that were selected; each record line is prefixed with its record number (starting with 1) followed by the " - " delimiter. If no table records were obtained from the query only the field names will be listed. Each record line, including the line with the field names, separates each field with a single horizonatal tab character. If the -verbose option is specified the table listing is preceeded by a line beginning with ">>> " listing the SQL query that was sent to the database server. If the query fails the SQL query line is followed by a "!!! Query failed." line. If the -verbose option is not specified and the query fails nothing will be listed for that query. A query failure will not prevent other queries from being sent to the database server. When multiple queries are specified each query result will be separated from the next query result by a single empty line.

All error reports are listed to the standard error output. If a query fails the failure report is listed to the standard error output.

Configuration:

If the name of the Configuration file is not specified the DEFAULT_CONFIGURATION_FILENAME will be used. If the configuration file is not in the current working directory, it will be looked for in the user's home directory. The configuration file must contain the necessary information needed to identify and connect with the database server (as required by the Database constructor and its Connect method). These are typically the server "Host" name and database "Type", "User" and "Password" access values.

Only one configuration file may be used.

Database server name:

The Configuration file may contain connection information for more than one database. The information for each database is organized by Server name, which may be specified. If no server name is provided, the Database will attempt to use the default (first) Server specified in the Configuration.

Only one database server may be used.

Database catalog name:

The default database catalog to use. This will override any Catalog parameter value from the configuration. The default catalog will only be used when a query expression does not specify a catalog.

Verbose

With the verbose option each query result is preceeded by listing the SQL expression sent to the database server. And, if the query fails, a "!!! Query failed." message will be listed instead of nothing.

Help:

The command line syntax usage is listed and the program exits. N.B.: If the -Help option occurs anywhere on the command line no other command line arguments will be used. If the command line is empty the usage will be listed.

N.B.The usage is printed to stderr. This method always results in a System.exit with a status of EXIT_INVALID_COMMAND_LINE_SYNTAX.