|
NAMEXML::Generator::DBI - Generate SAX events from SQL queriesSYNOPSISuse XML::Generator::DBI; use XML::SAX::Writer; use DBI; my $dbh = DBI->connect("dbi:Pg:dbname=foo", "user", "pass"); my $sth = $dbh->prepare("select * from mytable where mycol = ?"); my $generator = XML::Generator::DBI->new( Handler => XML::SAX::Writer->new(), ); $generator->execute($sth, $mycol_value); DESCRIPTIONThis module generates SAX events from SQL queries against a DBI connection.The default XML structure created is as follows: <database> <select> <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> <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. APIXML::Generator::DBI->new()Create a new XML generator.Parameters are passed as key/value pairs:
Note that in order to avoid unwanted nesting RowElement is undef. Example: contractor_job time_record -------------- ----------- 9 10 9 13 9 14 10 9 10 11 10 12 $select = qq( SELECT time_record, contractor_job FROM time_record ORDER BY contractor_job ); Using GroupAttribute: $gen->execute( $select, undef, ByColumnName => 1, RootElement => 'client_detail', RowElement => undef, GroupBy => 'contractor_job', GroupElement => 'group', GroupAttribute => 'ID', QueryElement => undef ); print $output; <?xml version="1.0" encoding="UTF-8"?> <client_detail> <group ID="9"> <time_record>10</time_record> <time_record>13</time_record> <time_record>14</time_record> </group> <group ID="10"> <time_record>9</time_record> <time_record>11</time_record> <time_record>12</time_record> </group> </client_detail> Using GroupValueElement: $gen->execute( $select, undef, ByColumnName => 1, RootElement => 'client_detail', RowElement => undef, GroupBy => 'contractor_job', GroupElement => 'group', GroupValueElement => 'ID', QueryElement => undef ); print $output; <?xml version="1.0" encoding="UTF-8"?> <client_detail> <group> <ID>9</ID> <time_record>10</time_record> <time_record>13</time_record> <time_record>14</time_record> </group> <group> <ID>10</ID> <time_record>9</time_record> <time_record>11</time_record> <time_record>12</time_record> </group> </client_detail> $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 per-query basis. $generator->execute_one($query, $bind, %params)If you wish to execute multiple statements within one XML structure, you can use the "execute_one()" method, as follows:$generator->pre_execute(); $generator->execute_one($query); $generator->execute_one($query); $generator->post_execute(); The pre and post calls are required. Other InformationBinary data is encoded using Base64. If you are using AsElements, the element containing binary data will have an attribute dbi:encoding="base64", where the DBI namespace is bound to the URL "http://axkit.org/NS/xml-generator-dbi". We detect binary data as anything containing characters outside of the XML UTF-8 allowed character set.AUTHORMatt Sergeant, matt@sergeant.orgLICENSEThis 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 ALSOPerlSAX, XML::Handler::YAWriter.
Visit the GSP FreeBSD Man Page Interface. |