1DBI(3)                User Contributed Perl Documentation               DBI(3)
2
3
4

NAME

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

SYNOPSIS

9         use XML::Generator::DBI;
10         use XML::SAX::Writer;
11         use DBI;
12         my $dbh = DBI->connect("dbi:Pg:dbname=foo", "user", "pass");
13         my $sth = $dbh->prepare("select * from mytable where mycol = ?");
14         my $generator = XML::Generator::DBI->new(
15                               Handler => XML::SAX::Writer->new(),
16                               );
17         $generator->execute($sth, $mycol_value);
18

DESCRIPTION

20       This module generates SAX events from SQL queries against a DBI
21       connection.
22
23       The default XML structure created is as follows:
24
25         <database>
26          <select>
27           <row>
28            <column1>1</column1>
29            <column2>fubar</column2>
30           </row>
31           <row>
32            <column1>2</column1>
33            <column2>intravert</column2>
34           </row>
35          </select>
36         </database>
37
38       Alternatively, pass the option AsAttributes => 1 to either the
39       execute() method, or to the new() method, and your XML will look like:
40
41         <database>
42           <select>
43             <row column1="1" column2="fubar"/>
44             <row column1="2" column2="intravert"/>
45           </select>
46         </database>
47
48       Note that with attributes, ordering of columns is likely to be lost,
49       but on the flip side, it may save you some bytes.
50
51       Nulls are handled by excluding either the attribute or the tag.
52

API

54   XML::Generator::DBI->new()
55       Create a new XML generator.
56
57       Parameters are passed as key/value pairs:
58
59       Handler (required)
60           A SAX handler to recieve the events.
61
62       dbh (required)
63           A DBI handle on which to execute the queries. Must support the
64           prepare, execute, fetch model of execution, and also support
65           type_info if you wish to use the ShowColumns option (see below).
66
67       AsAttributes
68           The default is to output everything as elements. If you wish to use
69           attributes instead (perhaps to save some bytes), you can specify
70           the AsAttributes option with a true value.
71
72       RootElement
73           You can specify the root element name by passing the parameter
74           RootElement => "myelement". The default root element name is
75           "database".
76
77       QueryElement
78           You can specify the query element name by passing the parameter
79           QueryElement => "thequery". The default is "select".
80
81       RowElement
82           You can specify the row element name by passing the parameter
83           RowElement => "item". The default is "row".
84
85       Indent
86           By default this module does no indenting (which is different from
87           the previous version). If you want the XML beautified, pass the
88           Indent option with a true value.
89
90       ShowColumns
91           If you wish to add information about the columns to your output,
92           specify the ShowColumns option with a true value. This will then
93           show things like the name and data type of the column, whether the
94           column is NULLABLE, the precision and scale, and also the size of
95           the column. All of this information is from $dbh->type_info() (see
96           perldoc DBI), and may change as I'm not 100% happy with the output.
97
98       ByColumnName
99           It allows usage of column names (aliases) for element generation.
100           Aliases can contain slashes in order to generate child elements.
101           It is limited by the length of aliases - depends on your DBMS
102
103           Example:
104
105            $select = qq(
106               SELECT  c.client as 'client_id',
107                       c.company_name as 'company_name',
108                       c.address_line as 'address/address_line',
109                       c.city as 'address/city',
110                       c.county as 'address/county',
111                       c.post_code as 'address/post_code',
112                       co.name as 'address/country',
113                       c.phone as 'phone',
114                       c.fax as 'fax',
115                       c.payment_term as 'payment_term',
116                       c.accounting_id as 'accounting_id'
117
118               FROM    client c,
119                       country co
120
121               WHERE   c.country = co.country
122               AND     c.client = $client_id
123                       );
124
125            $gen->execute(
126                                $select,
127                                undef,
128                                ByColumnName => 1,
129                                RootElement => 'client_detail',
130                                RowElement => 'client',
131                                QueryElement => undef
132                                   );
133
134            print $output;
135
136            <?xml version="1.0" encoding="UTF-8"?>
137            <client_detail>
138              <client>
139                <client_id>3</client_id>
140                <company_name>SomeCompanyName</company_name>
141                <address>
142                  <address_line>SomeAddress</address_line>
143                  <city>SomeCity</city>
144                  <county>SomeCounty</county>
145                  <post_code>SomePostCode</post_code>
146                  <country>SomeCountry</country>
147                </address>
148                <phone>22222</phone>
149                <fax>11111</fax>
150                <payment_term>14</payment_term>
151                <accounting_id>351</accounting_id>
152              </client>
153            </client_detail>
154
155       GroupBy
156           By this parameter you can group rows based on changes in the value
157           of a particular column. It relys on ordering done by your SQL
158           query.  This parameter requires two more parameters:
159
160           GroupElement - the name of element holding all 'row' elements.
161           GroupAttribute
162               or
163
164           GroupValueElement
165               GroupAttribute - when the 'value' goes as attribute of
166               GroupElement.  GroupAttribute is the name of this attribute.
167
168               GroupValueElement - when the 'value' goes in a separate
169               element.  GroupValueElement is the name of the element holding
170               'value'.
171
172           Note that in order to avoid unwanted nesting RowElement is undef.
173
174           Example:
175
176            contractor_job time_record
177            -------------- -----------
178                         9          10
179                         9          13
180                         9          14
181                        10           9
182                        10          11
183                        10          12
184
185            $select = qq(
186               SELECT  time_record,
187                       contractor_job
188
189               FROM    time_record
190
191               ORDER BY contractor_job
192                       );
193
194           Using GroupAttribute:
195
196            $gen->execute(
197                                $select,
198                                undef,
199                                ByColumnName => 1,
200                                RootElement => 'client_detail',
201                                RowElement => undef,
202                                GroupBy => 'contractor_job',
203                                GroupElement => 'group',
204                                GroupAttribute => 'ID',
205                                QueryElement => undef
206                                   );
207
208            print $output;
209
210            <?xml version="1.0" encoding="UTF-8"?>
211            <client_detail>
212              <group ID="9">
213                <time_record>10</time_record>
214                <time_record>13</time_record>
215                <time_record>14</time_record>
216              </group>
217              <group ID="10">
218                <time_record>9</time_record>
219                <time_record>11</time_record>
220                <time_record>12</time_record>
221              </group>
222            </client_detail>
223
224           Using GroupValueElement:
225
226            $gen->execute(
227                                $select,
228                                undef,
229                                ByColumnName => 1,
230                                RootElement => 'client_detail',
231                                RowElement => undef,
232                                GroupBy => 'contractor_job',
233                                GroupElement => 'group',
234                                GroupValueElement => 'ID',
235                                QueryElement => undef
236                                   );
237
238            print $output;
239
240            <?xml version="1.0" encoding="UTF-8"?>
241            <client_detail>
242              <group>
243                <ID>9</ID>
244                <time_record>10</time_record>
245                <time_record>13</time_record>
246                <time_record>14</time_record>
247              </group>
248              <group>
249                <ID>10</ID>
250                <time_record>9</time_record>
251                <time_record>11</time_record>
252                <time_record>12</time_record>
253              </group>
254            </client_detail>
255
256   $generator->execute($query, $bind, %params)
257       You execute a query and generate results with the execute method.
258
259       The first parameter is a string containing the query. The second is a
260       single or set of bind parameters. If you wish to make it more than one
261       bind parameter, it must be passed as an array reference:
262
263           $generator->execute(
264               "SELECT * FROM Users WHERE name = ?
265                AND password = ?",
266                [ $name, $password ],
267                );
268
269       Following the bind parameters you may pass any options you wish to use
270       to override the above options to new(). Thus allowing you to turn on
271       and off certain options on a per-query basis.
272
273   $generator->execute_one($query, $bind, %params)
274       If you wish to execute multiple statements within one XML structure,
275       you can use the "execute_one()" method, as follows:
276
277         $generator->pre_execute();
278         $generator->execute_one($query);
279         $generator->execute_one($query);
280         $generator->post_execute();
281
282       The pre and post calls are required.
283

Other Information

285       Binary data is encoded using Base64. If you are using AsElements, the
286       element containing binary data will have an attribute
287       dbi:encoding="base64", where the DBI namespace is bound to the URL
288       "http://axkit.org/NS/xml-generator-dbi". We detect binary data as
289       anything containing characters outside of the XML UTF-8 allowed
290       character set.
291

AUTHOR

293       Matt Sergeant, matt@sergeant.org
294

LICENSE

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

SEE ALSO

301       PerlSAX, XML::Handler::YAWriter.
302
303
304
305perl v5.34.0                      2021-07-23                            DBI(3)
Impressum