dbi(3)

NAME

XML::Generator::DBI - Generate SAX events from SQL queries

SYNOPSIS

use XML::Generator::DBI;
use XML::Handler::YAWriter;
use DBI;
my $ya = XML::Handler::YAWriter->new(AsFile => "-");
my   $dbh  =  DBI->connect("dbi:Pg:dbname=foo",  "user",
"pass");
my $generator = XML::Generator::DBI->new(
                      Handler => $ya,
                      dbh => $dbh
                      );
$generator->execute($sql, [@bind_params]);

DESCRIPTION

This module is a replacement for the outdated
DBIx::XML_RDB module.

It generates SAX events from SQL queries against a DBI
connection. Unlike DBIx::XML_RDB, it does not create a
string directly, instead you have to use some sort of SAX
handler module. If you wish to create a string or write to
a file, use YAWriter, as shown in the above SYNOPSIS sec
tion. Alternatively you might want to generate a DOM tree
or XML::XPath tree, which you can do with either of those
module's SAX handlers (known as Builders in those distri
butions).

The XML structure created is as follows:

<database>

<select query="SELECT * FROM foo">

<row>

<column1>1</column1>
<column2>fubar</column2>

</row>
<row>

<column1>2</column1>
<column2>intravert</column2>

</row>

</select>

</database>

Alternatively, pass the option AsAttributes => 1 to either
the execute() method, or to the new() method, and your XML will look like:

<database>

<select query="SELECT * FROM foo">

<row column1="1" column2="fubar"/>
<row column1="2" column2="intravert"/>

</select>

</database>

Note that with attributes, ordering of columns is likely
to be lost, but on the flip side, it may save you some
bytes.

Nulls are handled by excluding either the attribute or the
tag.

API

XML::Generator::DBI->new()

Create a new XML generator.

Parameters are passed as key/value pairs:

Handler (required)

A SAX handler to recieve the events.

dbh (required)

A DBI handle on which to execute the queries. Must
support the prepare, execute, fetch model of execu
tion, and also support type_info if you wish to use
the ShowColumns option (see below).

AsAttributes

The default is to output everything as elements. If
you wish to use attributes instead (perhaps to save
some bytes), you can specify the AsAttributes option
with a true value.

RootElement

You can specify the root element name by passing the
parameter RootElement => "myelement". The default root
element name is "database".

QueryElement

You can specify the query element name by passing the
parameter QueryElement => "thequery". The default is
"select".

RowElement

You can specify the row element name by passing the
parameter RowElement => "item". The default is "row".

Indent

By default this module does no indenting (which is
different from the previous version). If you want the
XML beautified, pass the Indent option with a true
value.

ShowColumns

If you wish to add information about the columns to
your output, specify the ShowColumns option with a
true value. This will then show things like the name
and data type of the column, whether the column is
NULLABLE, the precision and scale, and also the size
of the column. All of this information is from
$dbh->type_info() (see perldoc DBI), and may change as I'm not 100% happy with the output.

$generator->execute($query, $bind, %params)

You execute a query and generate results with the execute
method.

The first parameter is a string containing the query. The
second is a single or set of bind parameters. If you wish
to make it more than one bind parameter, it must be passed
as an array reference:

$generator->execute(

"SELECT * FROM Users WHERE name = ?

AND password = ?",
[ $name, $password ],
);

Following the bind parameters you may pass any options you
wish to use to override the above options to new(). Thus
allowing you to turn on and off certain options on a perquery basis.

Other Information

Binary data is encoded using Base64. If you are using AsE
lements, the element containing binary data will have an
attribute xml:encoding="base64". We detect binary data as
anything containing characters outside of the XML UTF-8
allowed character set.

NB: Binary encoding is actually on the TODO list :-)

I'm thinking about adding something that will do nesting,
so that if you get back:

id activity colour

=============================

1 food green
1 garden yellow
2 garden red

It will automatically try and nest it as:

<database>

<select query="SELECT id, activity, colour FROM

Favourites">

<id>

<value>1</value>

<activity>food</activity>
<colour>green</colour>
<activity>garden</activity>
<colour>yellow</colour>

</id>
<id>

<value>2</value>
<activity>garden</activity>
<colour>red</colour>

</id>

</select>

</database>

(the format above isn't considered set in stone, comments
welcome)

I would only be able to do this based on changes in the
value in a particular column, rather than how certain
technologies (e.g. MS SQL Server 2000) do it based on the
joins used.

AUTHOR

Matt Sergeant, matt@sergeant.org

LICENSE

This is free software, you may use it and distribute it
under the same terms as Perl itself. Specifically this is
the Artistic License, or the GNU GPL Version 2.

SEE ALSO

PerlSAX, XML::Handler::YAWriter.