For Hauler version 1.0.0. Documentation revised 8-Feb-2003.
Copyright (c) 2001-2003 by Rune Berg.
Licensed under the GNU General Public License (GPL).

Hauler homepage on the Internet || Latest version of this document on the Internet

Contents:

• Introduction
• Prerequisites for running Hauler
• Using Hauler
• Usage / Command syntax
• XML output
• CSV output
• Delimited output
• Version history
• Known bugs & limitations
• TODO
• Contact information
• Appendix A : Technical information
• Libraries needed
• Building

Hauler is a command line driven software program that executes user-defined SQL queries towards a MySQL database server and prints the result sets on the standard output stream, as XML or various text ("flat file") formats.

Hauler is useful for web applications or data export purposes where the database is held in a MySQL server.

Note that the MySQL database server is a product by MySQL AB. I am not - nor is Hauler - affiliated with that company.

Hauler runs on x86 Linux systems the appropriate system libraries installed. (You may succeed in building and running Hauler on other platforms, but I haven't tried that.)

Obviously, you need a running MySQL server (or network access to such) and the necessary login credentials. Talk to your database/system administrator if you're unsure.

To do anything useful with Hauler (or any other MySQL client), you need the MySQL Reference Manual, as this explains the SQL query syntax used. This should be available from http://www.mysql.com/documentation/index.html

Usage / Command syntax

hauler [--outputformat=xml] [options] query rowsetelement rowelement [... ... ...]

hauler --outputformat=csv [options] query

hauler --outputformat=del [options] query

--host=string	Set the name or IP address of the machine running the MySQL server. If not given, 'localhost' is assumed.
--socket=string	Set the name of the UNIX socket to use when connecting to 'localhost'. If not given, the value of the MYSQL_UNIX_PORT environment variable is used. If you're having a problem getting this parameter right, try connecting with the 'mysql' client, then issue a status query to see which UNIX socket it uses.
--port=number	Set the TCP port to use when connecting to a host other than 'localhost'. If not given, the value of the MYSQL_TCP_PORT environment variable is used.
--database=string	Set the name of a database within that MySQL server. If not given, there'll be no current database, which means the query must have a 'database.' prefix before every table name.
--user=string	Set the user name with which to connect. If not given, you'll only succeed in connecting if the MySQL server allows anonymous connections.
--password=string	Set the password corresponding to that user name. If not given, you'll only succeed in connecting if the MySQL server allows non-password connections for the given user name.

--outputformat={xml|csv|del}

Set the output format: - xml means XML (the default) - see separate description; - csv means Comma Separated Values (CSV) - see separate description; - del means delimited by some user-defined string - see separate description.

--encoding=string	Set the encoding for the XML declaration; the default being IS0-8859-1. This option does not otherwise affect Hauler's XML output, which is always single-byte encoded, as delivered by the MySQL server.
--rootelement=string	Set the name of the root (document) element; the default being ROOT. Must be a valid XML name.
--rownumattr	Print a NUM="current-row-number" attribute (1-based) in each ROW element.
--indent=string	Set the string used to indent nested (i.e. row and field) elements; the default being a tab. \t denotes a tab, \\ a backslash.
--nulls	Make NULL values produce field elements.
--nullattr	Make NULL field elements contain a NULL="true" attribute.
--nonewlines	Don't print newlines after elements. The use of this option also implies no indentation of nested elements.

--nofieldnamesrow	Don't print an initial row of field names.
--fielddelimiter=string	Set the field delimiter (separator). For CSV output, the field delimiter must be exactly one character, and not a double quote ("). For delimited output it can be anything, even empty.

--uppercase	Print all element/attribute/field names in upper case.
--lowercase	Print all element/attribute/field names in lower case.
--null=string	For fields whose value is NULL, print this instead of the default NULL.

--help	Print the copyright/usage banner on the standard error stream, then exit.
--tracediagnostics	Print any internal diagnostics on the standard error stream. This is a 'verbose' kind of option.

XML output

This output format (which is the default) prints the SQL query result set(s) as an XML (Extensible Markup Language) document.

The XML document produced has a root element ROOT (but see the --rootelement option) containing one row set element (as given in the command) for each query / result set. Each row set element contains one row element (as given in the command) per result set row. Each row element, in turn, contains one element per result set column. Any '<' within a column (field) value gets encoded as < in the corresponding element, and any '&' as &.

To describe XML output, let's start with a MySQL database haulerdoc containing a table person, for which the SQL query:

select * from person

yields the following result set:

  id name   comment
---- ------ ----------------------
   1 john   imagine all the people
   2 paul   bass player
   3 george NULL                     (this is NULL in the SQL sense)
   4 ringo  submarine

Given this table, let's see what output is produced by various Hauler invokations.

First, a 'default', single query Hauler invokation:

hauler --user=**** --password=******* --database=haulerdoc 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
<ROOT>

Notice here that:

• The root tag is the default ROOT (but see --rootelement option).
• The row set tag is PERSONS as given in the command invokation.
• The row tag is PERSON as given in the command invokation.
• Each column produces an element within each PERSON element, except for the third row, in which the comment element is missing because it was NULL (but see the --nulls option).

Next, let's use two queries, to get an XML document with two row set elements:

hauler --user=**** --password=*******  --database=haulerdoc 
   'select * from person where id in (1,3)' GUITARPLAYERS PERSON 
   'select * from person where id in (2,4)' OTHERPERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <GUITARPLAYERS ROWCOUNT="2">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
      </PERSON>
   </GUITARPLAYERS>
   <OTHERPERSONS ROWCOUNT="2">
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </OTHERPERSONS>
</ROOT>

Notice here that:

• Each query produces a row set (GUITARPLAYERS and OTHERPERSONS, respectively) element on the output, according to the command line arguments.
• (No offense whatsoever is implied with regards to the guitar playing abilities of Paul and Ringo! :-)

Next, let's use the --rootelement option to change the root element:

hauler --user=**** --password=******* --database=haulerdoc --rootelement=MYROOT 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<MYROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
</MYROOT>

Next, let's use the --rownumattr option to add a NUM="..." attribute in each row element:

hauler --user=**** --password=******* --database=haulerdoc --rownumattr 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON NUM="1">
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON NUM="2">
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON NUM="3">
         <id>3</id>
         <name>george</name>
      </PERSON>
      <PERSON NUM="4">
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
</ROOT>

Next, let's use the --nulls option to make any NULL values (in the SQL sense) produce an output element:

hauler --user=**** --password=******* --database=haulerdoc --nulls 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
         <comment>NULL</comment>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
</ROOT>

Notice how the comment field from the third row now produces an output element NULL (which you can change using the --null option)

Next, let's use the --null option combined with the --nulls option to make any NULL values (in the SQL sense) produce an output element with an element value of our choice:

hauler --user=**** --password=******* --database=haulerdoc --nulls --null=MYNULLVALUE 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
         <comment>MYNULLVALUE</comment>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
</ROOT>

Next, let's use the --nullattr option to make any NULL values (in the SQL sense) produce an output element with a NULL="true" attribute

hauler --user=**** --password=******* --database=haulerdoc --nullattr 'select * from person' PERSONS PERSON

the output is

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <PERSON>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </PERSON>
      <PERSON>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </PERSON>
      <PERSON>
         <id>3</id>
         <name>george</name>
         <comment NULL="true">NULL</comment>
      </PERSON>
      <PERSON>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </PERSON>
   </PERSONS>
</ROOT>

Notice how the presence of the --nullattr option implies the effect of the --nulls option.

Next, let's use the --indent option to change per-element-level indentation from a tab to a space:

hauler --user=**** --password=******* --database=haulerdoc '--indent= ' 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
 <PERSONS ROWCOUNT="4">
  <PERSON>
   <id>1</id>
   <name>john</name>
   <comment>imagine all the people</comment>
  </PERSON>
  <PERSON>
   <id>2</id>
   <name>paul</name>
   <comment>bass player</comment>
  </PERSON>
  <PERSON>
   <id>3</id>
   <name>george</name>
  </PERSON>
  <PERSON>
   <id>4</id>
   <name>ringo</name>
   <comment>submarine</comment>
  </PERSON>
 </PERSONS>
</ROOT>

Next, let's use the --nonewlines option to strip the output of newlines:

hauler --user=**** --password=******* --database=haulerdoc --nonewlines 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?><ROOT><PERSONS ROWCOUNT="4"><PERSON><id>1</id><name>john</name><comment>imagine all the people</comment></PERSON><PERSON><id>2</id><name>paul</name><comment>bass player</comment></PERSON><PERSON><id>3</id><name>george</name></PERSON><PERSON><id>4</id><name>ringo</name><comment>submarine</comment></PERSON></PERSONS></ROOT>

Notice how the presence of the --nonewlines option implies the effect of the --indent option, i.e. making the output devoid of any insignificant whitespace.

Next, let's use the --uppercase option to force all element and attribute names to upper case:

hauler --user=**** --password=******* --database=haulerdoc --uppercase 'select * from person' persons person

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ROOT>
   <PERSONS ROWCOUNT="4">
      <ROW>
         <ID>1</ID>
         <NAME>john</NAME>
         <COMMENT>imagine all the people</COMMENT>
      </ROW>
      <ROW>
         <ID>2</ID>
         <NAME>paul</NAME>
         <COMMENT>bass player</COMMENT>
      </ROW>
      <ROW>
         <ID>3</ID>
         <NAME>george</NAME>
      </ROW>
      <ROW>
         <ID>4</ID>
         <NAME>ringo</NAME>
         <COMMENT>submarine</COMMENT>
      </ROW>
   </PERSONS>
</ROOT>

Finally, let's use the --lowercase option to force all element and attribute names to lower case:

hauler --user=**** --password=******* --database=haulerdoc --lowercase 'select * from person' PERSONS PERSON

The output is:

<?xml version="1.0" encoding="ISO-8859-1"?>
<root>
   <persons rowcount="4">
      <row>
         <id>1</id>
         <name>john</name>
         <comment>imagine all the people</comment>
      </row>
      <row>
         <id>2</id>
         <name>paul</name>
         <comment>bass player</comment>
      </row>
      <row>
         <id>3</id>
         <name>george</name>
      </row>
      <row>
         <id>4</id>
         <name>ringo</name>
         <comment>submarine</comment>
      </row>
   </persons>
</root>

CSV output

The CSV (Comma Separated Values) output format (which is enabled by the --outputformat=csv option) is a 'flat file' type of format. The SQL query result set is printed as an initial line of column names (but see the --nofieldnamesrow option) followed by one line per result set row.

The CSV format works according to the following rules:

• Fields are separated (delimited) by a comma (but see the --fielddelimiter option).
• If a field contains the field delimiter, it is enclosed in double quotes ("").
• If a field contains a double quote, that double quote is preceded by another double quote and the field as a whole must be surrounded by double quotes.

To describe CSV output, let's start with a MySQL database haulerdoc containing a table quote, for which the query:

select * from quote

yields the following result set:

  id name        quote
---- ----------- -------------------------------------
   1 Shakespeare to be or not to be
   2 Clint       go ahead, make my day
   3 Nobody      NULL
   4 Me          this field has a double quote " in it

Given this table, let's see what output is produced by various Hauler invokations.

First, a 'default' Hauler CSV invokation (i.e. one with no command line options to affect the CSV output):

hauler --outputformat=csv --user=***** --password=******* --database=haulerdoc 'select * from quote'

The output is:

id,name,quote
1,Shakespeare,to be or not to be
2,Clint,"go ahead, make my day"
3,Nobody,NULL
4,Me,"this field has a double quote "" in it"

Notice here that:

• The first output line contains the column names of the quote table (but see --nofieldnamesrow option).
• Each following output line comes from the corresponding SQL query result set row.
• Values are separated (delimited) by a comma (but see the --fielddelimiter option).
• Values that contain the field delimiter (in this case, a comma) are surrounded by a double quotes.
• Values that contain a double quote have those double quotes 'escaped' by preceding doube quotes.
• The third SQL result set row has NULL value, which is output as NULL (but see the --null option).

Next, let's use the --nofieldnamesrow option to skip the column names (i.e. the first output line)

hauler --outputformat=csv --user=***** --password=******* --database=haulerdoc --nofieldnamesrow 
         'select * from quote'

The output is:

1,Shakespeare,to be or not to be
2,Clint,"go ahead, make my day"
3,Nobody,NULL
4,Me,"this field has a double quote "" in it"

Next, let's use the --fielddelimiter option to separate output fields by a semicolon:

hauler --outputformat=csv --user=***** --password=******* --database=haulerdoc '--fielddelimiter=;' 
         'select * from quote'

The output is:

id;name;quote
1;Shakespeare;to be or not to be
2;Clint;go ahead, make my day
3;Nobody;NULL
4;Me;"this field has a double quote "" in it"

Next, let's use the --null option to change the output for NULL values:

hauler --outputformat=csv --user=***** --password=******* --database=haulerdoc --null=MYNULLVALUE 
         'select * from quote'

The output is:

id,name,quote
1,Shakespeare,to be or not to be
2,Clint,"go ahead, make my day"
3,Nobody,MYNULLVALUE
4,Me,"this field has a double quote "" in it"

Next, let's use the --uppercase option to print the column names in upper case:

hauler --outputformat=csv --user=***** --password=******* --database=haulerdoc --uppercase 
         'select * from quote'

The output is:

ID,NAME,QUOTE
1,Shakespeare,to be or not to be
2,Clint,"go ahead, make my day"
3,Nobody,NULL
4,Me,"this field has a double quote "" in it"

The --lowercase option works the opposite: the column names row get printed in lower case.

Delimited output

This format is rather similar to the CSV format, only 'dumber': no double quotes are printed around values that contain the field delimiter or a double quote, which can make the output unsuitable for futher processing. On the plus side, the field delimiter can any length.

To describe delimited output, let's start with a MySQL database haulerdoc containing the same table quote used in the CSV examples above. That is, one for which the query:

select * from quote

yields the following result set:

  id name        quote
---- ----------- -------------------------------------
   1 Shakespeare to be or not to be
   2 Clint       go ahead, make my day
   3 Nobody      NULL
   4 Me          this field has a double quote " in it

Given this table, let's see what output is produced by various Hauler invokations.

First, a 'default' Hauler delimited invokation (i.e. one with no command line options to affect the delimited output):

hauler --outputformat=del --user=***** --password=******* --database=haulerdoc 'select * from quote'

The output is:

id,name,quote
1,Shakespeare,to be or not to be
2,Clint,go ahead, make my day
3,Nobody,NULL
4,Me,this field has a double quote " in it

Notice here that:

• The first output line contains the column names of the quote table (but see --nofieldnamesrow option).
• Each following output line comes from the corresponding SQL query result set row.
• Columns are separated by a comma (but see the --fielddelimiter option, which for delimited output is not restricted to a single character).
• The third SQL result set row has NULL value, which is output as 'NULL' (but see the --null option).
• The second SQL result set row has a field that contains the field delimiter, but Hauler does not surround the field by double quotes. It would probably be a good idea to use a different field delimiter in this case.
• The fourth SQL result set row has a field that contains a double quote, but Hauler does not insert an 'escaping' double quote nor surrounding double quotes.

Next, lets use the --fielddelimiter option to change the field delimiter to two semicolons:

hauler --outputformat=del --user=***** --password=******* --database=haulerdoc '--fielddelimiter=;;' 'select * from quote'

The output is:

id;;name;;quote
1;;Shakespeare;;to be or not to be
2;;Clint;;go ahead, make my day
3;;Nobody;;NULL
4;;Me;;this field has a double quote " in it

The --nofieldnamesrow, --null, --uppercase and --lowercase options work the same way as for CSV output.

The public releases of Hauler are:

Version	Date	Changes
1.0.0	8-Feb-2003	- Bug-fix: Invoking hauler with no arguments would cause a crash; now a proper error message is printed.
1.0.0-beta-1	8-Feb-2002	- For XML output, now supports multiple queries and result sets. This requires that each query have a row set name and row element name given on the command line. Consequently, the --rowsettag and --rowtag options are no longer supported, and the --rootelement option has been added. Also, the ROWCOUNT attribute now appears in every row set element.
1.0.0-alpha-1	15-Nov-2001	- (initial alpha release)

These are the known bugs in Hauler 1.0.0:

• None.

These are the known limitations in Hauler 1.0.0:

• Hauler has only been tested with MySQL version 3.23. I don't know if it will work well (or at all) with other MySQL versions.
• No testing has been done using the BLOB/TEXT data types. Beware.
• Hauler can only write XML in single-byte encodings, such as ISO 8859-1 (Western/Latin-1).

For up to date Hauler information, visit the Hauler homepage on the Internet.

(This chapter will contain a list of features for future Hauler versions.)

Hauler homepage on the Internet: http://home.online.no/~runeberg/hauler

Hauler author's email address: runeberg@online.no

Libraries needed

The provided Hauler executable is statically linked to the MySQL client library (libmysqlclient). (However, the makefile has out-commented defs for dynamic linking.)

The provided Hauler executable requires the following shared libraries to run:

• libz.so.1
• libc.so.6

If you choose to (or must) build Hauler yourself, you may get away with different library versions.

Building

Use the supplied makefile (but read that file's initial comments - you may need to tweak it slightly). I've tried and succeeded building Hauler on SuSE Linux 8.1. Your mileage may vary.

You'll need to have installed the appropriate packages for GCC (I use version 3.2) and MySQL client development. These should be included with your Linux distribution.