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.

See Also:
  `PIRL.Database`, `PIRL.Configuration`

### Field Summary

Fields
`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

Constructors
Constructor and Description
`Query_DB()`

### Method Summary

Methods
`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``.