1DBIx::Simple::Examples(U3s)er Contributed Perl DocumentatDiBoInx::Simple::Examples(3)
2
3
4
6 DBIx::Simple::Examples - Examples of how to use DBIx::Simple
7
9 DBIx::Simple provides a simplified interface to DBI, Perl's powerful
10 database module.
11
13 General
14 #!/usr/bin/perl -w
15 use strict;
16 use DBIx::Simple;
17
18 # Instant database with DBD::SQLite
19 my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat')
20 or die DBIx::Simple->error;
21
22 # Connecting to a MySQL database
23 my $db = DBIx::Simple->connect(
24 'DBI:mysql:database=test', # DBI source specification
25 'test', 'test', # Username and password
26 { RaiseError => 1 } # Additional options
27 );
28
29 # Using an existing database handle
30 my $db = DBIx::Simple->connect($dbh);
31
32 # Abstracted example: $db->query($query, @variables)->what_you_want;
33
34 $db->commit or die $db->error;
35
36 Simple Queries
37 $db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error;
38
39 for (1..100) {
40 $db->query(
41 'INSERT INTO randomvalues VALUES (?, ?)',
42 int rand(10),
43 int rand(10)
44 ) or die $db->error;
45 }
46
47 $db->query(
48 'INSERT INTO sometable VALUES (??)',
49 $first, $second, $third, $fourth, $fifth, $sixth
50 );
51 # (??) is expanded to (?, ?, ?, ?, ?, ?) automatically
52
53 Single row queries
54 my ($two) = $db->query('SELECT 1 + 1')->list;
55 my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list;
56
57 my ($name, $email) = $db->query(
58 'SELECT name, email FROM people WHERE email = ? LIMIT 1',
59 $mail
60 )->list;
61
62 Or, more efficiently:
63
64 $db->query('SELECT 1 + 1')->into(my $two);
65 $db->query('SELECT 3, 2 + 2')->into(my ($three, $four));
66
67 $db->query(
68 'SELECT name, email FROM people WHERE email = ? LIMIT 1',
69 $mail
70 )->into(my ($name, $email));
71
72 Fetching all rows in one go
73 One big flattened list (primarily for single column queries)
74
75 my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
76
77 Rows as array references
78
79 for my $row ($db->query('SELECT name, email FROM people')->arrays) {
80 print "Name: $row->[0], Email: $row->[1]\n";
81 }
82
83 Rows as hash references
84
85 for my $row ($db->query('SELECT name, email FROM people')->hashes) {
86 print "Name: $row->{name}, Email: $row->{email}\n";
87 }
88
89 Fetching one row at a time
90 Rows into separate variables
91
92 {
93 my $result = $db->query('SELECT name, email FROM people');
94 $result->bind(my ($name, $email));
95 while ($result->fetch) {
96 print "Name: $name, Email: $email\n";
97 }
98 }
99
100 or:
101
102 {
103 my $result = $db->query('SELECT name, email FROM people');
104 while ($result->into(my ($name, $email))) {
105 print "Name: $name, Email: $email\n";
106 }
107 }
108
109 Rows as lists
110
111 {
112 my $result = $db->query('SELECT name, email FROM people');
113 while (my @row = $result->list) {
114 print "Name: $row[0], Email: $row[1]\n";
115 }
116 }
117
118 Rows as array references
119
120 {
121 my $result = $db->query('SELECT name, email FROM people');
122 while (my $row = $result->array) {
123 print "Name: $row->[0], Email: $row->[1]\n";
124 }
125 }
126
127 Rows as hash references
128
129 {
130 my $result = $db->query('SELECT name, email FROM people');
131 while (my $row = $result->hash) {
132 print "Name: $row->{name}, Email: $row->{email}\n";
133 }
134 }
135
136 Building maps (also fetching all rows in one go)
137 map
138
139 A hash of hashes
140
141 my $customers =
142 $db
143 -> query('SELECT id, name, location FROM people')
144 -> map_hashes('id');
145
146 # $customers = { $id => { name => $name, location => $location }, ... }
147
148 A hash of arrays
149
150 my $customers =
151 $db
152 -> query('SELECT id, name, location FROM people')
153 -> map_arrays(0);
154
155 # $customers = { $id => [ $name, $location ], ... }
156
157 A hash of values (two-column queries)
158
159 my $names =
160 $db
161 -> query('SELECT id, name FROM people')
162 -> map;
163
164 # $names = { $id => $name, ... }
165
166 group
167
168 A hash of arrays of hashes
169
170 my $customers =
171 $db
172 -> query('SELECT id, name, location FROM people')
173 -> group_hashes('location');
174
175 # $customers = { $location => [ { id => $id, name => $name }, ... ], ... }
176
177 A hash of arrays of arrays
178
179 my $customers =
180 $db
181 -> query('SELECT id, name, location FROM people')
182 -> group_arrays(2);
183
184 # $customers = { $location => [ [ $id, $name ], ... ], ... }
185
186 A hash of arrays of values (two-column queries)
187
188 my $names =
189 $db
190 -> query('SELECT location, name FROM people')
191 -> group;
192
193 # $names = { $location => [ $name, $name, ... ], ... }
194
196 If you have SQL::Interp installed, you can use the semi-abstracting
197 method "iquery". This works just like "query", but with parts of the
198 query interleaved with the bind arguments, passed as references.
199
200 You should read SQL::Interp. These examples are not enough to fully
201 understand all the possibilities.
202
203 The following examples are based on the documentation of SQL::Interp.
204
205 my $result = $db->iquery('INSERT INTO table', \%item);
206 my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
207 my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
208
209 # These two select syntax produce the same result
210 my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
211 my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
212
213 for ($result->hashes) { ... }
214
215 Use a syntax highlighting editor for good visual distinction.
216
217 If you need the helper functions "sql" and "sql_type", you can import
218 them with "use SQL::Interp;"
219
221 If you have SQL::Abstract installed, you can use the abstracting
222 methods "select", "insert", "update", "delete". These work like
223 "query", but instead of a query and bind arguments, use abstracted
224 arguments.
225
226 You should read SQL::Abstract. These examples are not enough to fully
227 understand all the possibilities.
228
229 The SQL::Abstract object is available (writable) through the "abstract"
230 property.
231
232 The following examples are based on the documentation of SQL::Abstract.
233
234 Overview
235 If you don't like the defaults, just assign a new object:
236
237 $db->abstract = SQL::Abstract->new(
238 case => 'lower',
239 cmp => 'like',
240 logic => 'and',
241 convert => 'upper'
242 );
243
244 If you don't assign any object, one will be created automatically using
245 the default options. The SQL::Abstract module is loaded on demand.
246
247 my $result = $db->select($table, \@fields, \%where, \@order);
248 my $result = $db->insert($table, \%fieldvals || \@values);
249 my $result = $db->update($table, \%fieldvals, \%where);
250 my $result = $db->delete($table, \%where);
251
252 for ($result->hashes) { ... }
253
254 Complete examples
255 select
256
257 my @tickets = $db->select(
258 'tickets', '*', {
259 requestor => 'inna',
260 worker => ['nwiger', 'rcwe', 'sfz'],
261 status => { '!=', 'completed' }
262 }
263 )->hashes;
264
265 insert
266
267 If you already have your data as a hash, inserting becomes much easier:
268
269 $db->insert('people', \%data);
270
271 Instead of:
272
273 $db->query(
274 q[
275 INSERT
276 INTO people (name, phone, address, ...)
277 VALUES (??)
278 ],
279 @data{'name', 'phone', 'address', ... }
280 );
281
282 update, delete
283
284 $db->update(
285 'tickets', {
286 worker => 'juerd',
287 status => 'completed'
288 },
289 { id => $id }
290 )
291
292 $db->delete('tickets', { id => $id });
293
294 where
295
296 The "where" method is not wrapped directly, because it doesn't generate
297 a query and thus doesn't really have anything to do with the database
298 module.
299
300 But using the "abstract" property, you can still easily access it:
301
302 my $where = $db->abstract->where({ foo => $foo });
303
305 If you have DBIx::XHTML_Table installed, you can use the result methods
306 "xto" and "html".
307
308 You should read DBIx::XHTML_Table. These examples are not enough to
309 fully understand what is going on. When reading that documentation,
310 note that you don't have to pass hash references to DBIx::Simple's
311 methods. It is supported, though.
312
313 DBIx::XHTML_Table is loaded on demand.
314
315 Overview
316 To print a simple table, all you have to do is:
317
318 print $db->query('SELECT * FROM foo')->html;
319
320 Of course, anything that produces a result object can be used. The same
321 thing using the abstraction method "select" would be:
322
323 print $db->select('foo', '*')->html;
324
325 A DBIx::XHTML_Table object can be generated with the "xto" (XHTML_Table
326 Object) method:
327
328 my $table = $db->query($query)->xto;
329
330 Passing attributes
331 DBIx::Simple sends the attributes you pass to "html" both to the
332 constructor and the output method. This allows you to specify both HTML
333 attributes (like "bgcolor") and options for XHTML_Table (like
334 "no_ucfirst" and "no_indent") all at once:
335
336 print $result->html(
337 tr => { bgcolor => [ qw/silver white/ ] },
338 no_ucfirst => 1
339 );
340
341 Using an XHTML_Table object
342 Not everything can be controlled by passing attributes. For full
343 flexibility, the XHTML_Table object can be used directly:
344
345 my $table = $db->query($query)->xto(
346 tr => { bgcolor => [ qw/silver white/ ] }
347 );
348
349 $table->set_group('client', 1);
350 $table->calc_totals('credit', '%.2f');
351
352 print $table->output({ no_ucfirst => 1 }); # note the {}!
353
355 "$result->text("neat")"
356 Neither neat nor pretty, but useful for debugging. Uses DBI's
357 "neat_list" method. Doesn't display column names.
358
359 '1', 'Camel', 'mammal'
360 '2', 'Llama', 'mammal'
361 '3', 'Owl', 'bird'
362 '4', 'Juerd', undef
363
364 "$result->text("table")"
365 Displays a simple table using ASCII lines.
366
367 id | animal | type
368 ---+--------+-------
369 1 | Camel | mammal
370 2 | Llama | mammal
371 3 | Owl | bird
372 4 | Juerd |
373
374 "$result->text("box")"
375 Displays a simple table using ASCII lines, with an outside
376 border.
377
378 +----+--------+--------+
379 | id | animal | type |
380 +----+--------+--------+
381 | 1 | Camel | mammal |
382 | 2 | Llama | mammal |
383 | 3 | Owl | bird |
384 | 4 | Juerd | |
385 +----+--------+--------+
386
387 For "table" and "box", you need Anno Siegel's Text::Table module
388 installed.
389
391 Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
392
394 DBIx::Simple, SQL::Abstract
395
396
397
398perl v5.32.1 2021-01-27 DBIx::Simple::Examples(3)