selectall_xml.pl
selectall_xml.pl [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL
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 DBIx::DBStag module. The nesting of the XML can be controlled by the DBStag SQL extension "USE NESTING..."
selectall_xml.pl -d "dbi:Pg:dbname=mydb;host=localhost"\ "SELECT * FROM a NATURAL JOIN b"
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
selectall_xml.pl -d genedb /genedb-gene gene_symbol@=Adh,dpp,bam,indy
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.
selectall_xml.pl -d mydb -h
selectall_xml.pl /genedb-gene -h
A file containing configuration details for local databases
list of directories (seperated by :s) to be searched when templates are requested
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
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
database user identity
database password
this is a path to a file containing SQL that will be executed, as an alternative to writing the SQL on the command line
a bracketed expression indicating how to the resulting objects/XML should be nested. See DBIx::DBStag for details.
the name of a template; see above
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 +
used to override the SELECT clause of the query; useful for combining with templates
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
a piece of SQL is that is executed immediately before the main query; e.g.:
-pre "SET search_path=myschema,public"
a file to output the results to
writer class; can be any perl class, or one of these
lisp S-Expressions
indented text
shows results in color (sxpr and itext only)
will show the parse of the SQL statement
To install DBIx::DBStag, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::DBStag
CPAN shell
perl -MCPAN -e shell install DBIx::DBStag
For more information on module installation, please visit the detailed CPAN module installation guide.