- NAME
- SYNOPSIS
- DESCRIPTION

- EXAMPLES
- TEMPLATES
- LISTING AVAILABLE TEMPLATES FOR A DB
- LISTING VARIABLES FOR A TEMPLATE

- ENVIRONMENT VARIABLES
- COMMAND LINE ARGUMENTS

# NAME

selectall_xml.pl

# SYNOPSIS

      selectall_xml.pl [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL

# DESCRIPTION

This script will query a database using either SQL provided by the script user, or using an SQL templates; the query results will be turned into XML using the the DBIx::DBStag manpage module. The nesting of the XML can be controlled by the DBStag SQL extension ``USE NESTING...''

## EXAMPLES

      selectall_xml.pl -d "dbi:Pg:dbname=mydb;host=localhost"\
            "SELECT * FROM a NATURAL JOIN b"

## TEMPLATES

A parameterized SQL template (canned query) can be used instead of specifying the full SQL

For example:

      selectall_xml.pl -d genedb /genedb-gene gene_symbol=Adh

Or:

      selectall_xml.pl -d genedb /genedb-gene Adh

A template is indicated by the syntactic shorthand of using a slash to precede the template name; in this case the template is called genedb-gene. the -t option can also be used.

All the remaining arguments are passed in as SQL template parameters. They can be passed in as either name=value pairs, or as a simple list of arguments which get passed into the template in order

To use templates, you should have the environment variable DBSTAG_TEMPLATE_DIRS set. See DBIx::DBStag for details.

## LISTING AVAILABLE TEMPLATES FOR A DB

       selectall_xml.pl -d mydb -h

## LISTING VARIABLES FOR A TEMPLATE

       selectall_xml.pl /genedb-gene -h

# ENVIRONMENT VARIABLES

DBSTAG_DBIMAP_FILE
  A file containing configuration details for local databases

DBSTAG_TEMPLATE_DIRS
  list of directories (seperated by :s) to be searched when templates are requested

# COMMAND LINE ARGUMENTS

-h|help
  shows this page if no other arguments are given

  if a template is specified, gives template details

  if a db is specified, lists templates for that db

  use in conjunction with -v for full descriptions

-d|dbname DBNAME
  this is either a full DBI locator string (eg dbi:Pg:dbname=mydb;host=localhost) or it can also be a shortened ``nickname'', which is then looked up in the file pointed at by the environment variable DBSTAG_DBIMAP_FILE

-u|user USER
  database user identity

-p|password PASS
  database password

-f|file SQLFILE
  this is a path to a file containing SQL that will be executed, as an alternative to writing the SQL on the command line

-n|nesting NESTING-EXPRESSIONS
  a bracketed expression indicating how to the resulting objects/XML should be nested. See the DBIx::DBStag manpage for details.

-t|template TEMPLATE-NAME
  the name of a template; see above

-wh|where WHERE-CLAUSE
  used to override the WHERE clause of the query; useful for combining with templates

  You can append to an existing where clause by using the prefix +

-s|select SELECT-COLS
  used to override the SELECT clause of the query; useful for combining with templates

-rows
  sometimes it is preferable to return the results as a table rather than xml or a similar nested structure. specifying -rows will fetch a table, one line per row, and columns seperated by tabs

-o|out FILE
  a file to output the results to

-w|writer WRITER
  writer class; can be any perl class, or one of these
  xml [default]
  sxpr
    lisp S-Expressions

  itext
    indented text

-color
  shows results in color (sxpr and itext only)

-show
  will show the parse of the SQL statement