Skip to content
CA Harvest SCM - 13.0
Documentation powered by DocOps

hsql Command-RDB Query

Last update December 23, 2016

The hsql relational database query utility enables you to generate reports from a client computer. This utility is limited to executing SQL SELECT queries, and cannot alter the contents of the CA Harvest SCM database. Output is generated in a form that can be imported into spreadsheet or word processing programs. If no input and output options are specified, hsql reads input from the standard input and writes output to the standard output.

When hsql is executed, from the command line or a user-defined process (UDP), a server process must be running to transact with the relational database.

When using the CA Harvest SCM hsql relational database query command line in the Program field of the UDP Properties dialog, the client and server UDP behaviors differ.

Note: Your SQL input cannot begin with a comment; it must begin with a SELECT statement.

This command has the following format:

hsql {-b name} [-f name] {-usr username -pw password} [-prompt] [-i inputfile.txt | -di inputfile.txt] [-eh filename] [-o filename | -oa filename] [-nh] [-s] [-t] [-gl] [-arg] [-wts] [-h]

  • -b name
    (Required) Specifies the CA Harvest SCM broker name.
  • -f name
    (Optional) Specifies an input file name. If a full path is not specified for the file, it must exist in the directory where the command is invoked. The -f option can be omitted on platforms that support the standard input, as most UNIX and Linux platforms do. In this case, the standard input is read for the SQL statement.
    The input file contains the SQL commands to be executed by hsql. Only query statements that start with the keyword SELECT are executed by hsql.
  • -usr username
    (Required, unless -prompt is specified) Specifies the user name for login to the broker. If -usr is defined without -pw, the user is prompted for the password at runtime.
  • -pw password
    (Required, unless -prompt is specified) Specifies the password for login to the broker. To hide the password from being exposed in plain text on the command line, use the -prompt option.
  • -eh filename
    (Optional) Specifies the encrypted CA Harvest SCM username-password file name. The file name used with this option contains encrypted username-password credentials and must be generated with the svrenc utility.
    -nh
  • (Optional) Specifies that a header should not be included in the command output. By default, the header is included in the output. The header is generated based on the table column names being queried, or as specified in the SQL being executed.
  • -s
    (Optional) Specifies to suppress the prompt that is displayed when no input file name is provided. When -s is not specified and no input file is specified, the following prompt is displayed:

    Enter SQL command:

    UNIX and Linux: End input by typing at the start of a new-line by typing Ctrl-D and then Enter.
    Windows: End input by typing at the start of a new-line by typing Ctrl-Z and then Enter.
    The display of this prompt can be undesirable in some situations. For example, if you plan to redirect the output of hsql to be used by another process, do not include this prompt.

  • -t
    (Optional) Specifies the formatting of output when using the hsigget or hsql commands. When -t is specified, the output is specially formatted to make it easy to import into a spreadsheet or word processor. Each field in the output is separated with a single tab.
    Fields that contain multiple lines of data present a special challenge for report generation. Because new-line characters separate records, if a field also contains new-line characters, the output becomes ambiguous and difficult to format.
    To make it easy to distinguish between a field separator and a record separator, special processing is done on multiple line output. Multiple line output for one field is merged into one long line with the string <NL> substituted for any occurrence of a new-line. Any occurrence of tabs in the data is replaced with the string <TAB>.
    This algorithm removes any ambiguity between field and record separators. After the data is imported into the spreadsheet or table, it is easy to reformat by searching and replacing the <NL> and <TAB> strings.
    When -t is not specified, each attribute of a record is specified on a new line and two new-lines separate records. If -nh is not specified, the output value follows the field name, unless the output is multi-line; in which case, output starts on a new line.
  • -eh filename
    (Optional) Specifies the encrypted CA Harvest SCM username-password file name. The file name used with this option contains encrypted username-password credentials and must be generated with the svrenc utility.
  • -gl
    (Optional) Specifies that the time format will be output according to the local settings.
  • -l 
    Converts the time in the local time zone.
Was this helpful?

Please log in to post comments.