1DBIx::Simple(3) User Contributed Perl Documentation DBIx::Simple(3)
2
6 DBIx::Simple - Very complete easy-to-use OO interface to DBI
7
9 DBIx::Simple
10 $db = DBIx::Simple->connect(...) # or ->new
11 $db->keep_statements = 16
13 $db->lc_columns = 1
14 $db->result_class = 'DBIx::Simple::Result';
15 $db->begin_work $db->commit
17 $db->rollback $db->disconnect
18 $db->func(...) $db->last_insert_id
19 $result = $db->query(...)
21 DBIx::SImple + SQL::Interp
23 $result = $db->iquery(...)
24 DBIx::Simple + SQL::Abstract
26 $db->abstract = SQL::Abstract->new(...)
27 $result = $db->select(...)
29 $result = $db->insert(...)
30 $result = $db->update(...)
31 $result = $db->delete(...)
32 DBIx::Simple::Result
34 @columns = $result->columns
35 $result->into($foo, $bar, $baz)
37 $row = $result->fetch
38 @row = $result->list @rows = $result->flat
40 $row = $result->array @rows = $result->arrays
41 $row = $result->hash @rows = $result->hashes
42 @row = $result->kv_list @rows = $result->kv_flat
43 $row = $result->kv_array @rows = $result->kv_arrays
44 $obj = $result->object @objs = $result->objects
45 %map = $result->map %grouped = $result->group
47 %map = $result->map_hashes(...) %grouped = $result->group_hashes(...)
48 %map = $result->map_arrays(...) %grouped = $result->group_arrays(...)
49 $rows = $result->rows
51 $dump = $result->text
53 $result->finish
55 DBIx::Simple::Result + DBIx::XHTML_Table
57 $html = $result->html(...)
58 $table_object = $result->xto(...)
60 Examples
62 Please read DBIx::Simple::Examples for code examples.
63
65 DBIx::Simple provides a simplified interface to DBI, Perl's powerful
66 database module.
67 This module is aimed at rapid development and easy maintenance. Query
69 preparation and execution are combined in a single method, the result
70 object (which is a wrapper around the statement handle) provides easy
71 row-by-row and slurping methods.
72 The "query" method returns either a result object, or a dummy object.
74 The dummy object returns undef (or an empty list) for all methods and
75 when used in boolean context, is false. The dummy object lets you
76 postpone (or skip) error checking, but it also makes immediate error
77 checking simply "$db->query(...) or die $db->error".
78 DBIx::Simple methods
80 Class methods
81 "connect($dbh)", "connect($dsn, $user, $pass, \%options)"
83 "new($dbh)", "new($dsn, $user, $pass, \%options)"
84 The "connect" or "new" class method takes either an
85 existing DBI object ($dbh), or a list of arguments to
86 pass to "DBI->connect". See DBI for a detailed
87 description.
88 You cannot use this method to clone a DBIx::Simple
90 object: the $dbh passed should be a DBI::db object, not a
91 DBIx::Simple object.
92 For new connections, PrintError is disabled by default.
94 If you enable it, beware that it will report line numbers
95 in DBIx/Simple.pm.
96 For new connections, RaiseError is enabled by default
98 unless the environment variable
99 "PERL_DBIX_SIMPLE_NO_RAISEERROR" is set to a non-empty
100 non-0 value.
101 This method is the constructor and returns a DBIx::Simple
103 object on success. On failure, it returns undef.
104 Object methods
106 "query($query, @values)"
108 Prepares and executes the query and returns a result
109 object.
110 If the string "(??)" is present in the query, it is
112 replaced with a list of as many question marks as
113 @values.
114 The database drivers substitute placeholders (question
116 marks that do not appear in quoted literals) in the query
117 with the given @values, after them escaping them. You
118 should always use placeholders, and never use raw user
119 input in database queries.
120 On success, returns a DBIx::Simple::Result object. On
122 failure, returns a DBIx::Simple::Dummy object.
123 "iquery(...)" Uses SQL::Interp to interpolate values into a query, and
125 uses the resulting generated query and bind arguments
126 with "query". See SQL::Interp's documentation for usage
127 information.
128 Requires Mark Stosberg's SQL::Interp, which is available
130 from CPAN. SQL::Interp is a fork from David Manura's
131 SQL::Interpolate.
132 "select", "insert", "update", "delete"
134 Calls the respective method on "abstract", and uses the
135 resulting generated query and bind arguments with
136 "query". See SQL::Abstract's documentation for usage
137 information. You can override the object by assigning to
138 the "abstract" property.
139 Requires Nathan Wiger's SQL::Abstract, which is available
141 from CPAN.
142 "begin_work", "begin", "commit", "rollback"
144 These transaction related methods call the DBI respective
145 methods and Do What You Mean. See DBI for details.
146 "begin" is an alias for "begin_work".
148 "func(...)" Calls the "func" method of DBI. See DBI for details.
150 "last_insert_id(...)"
152 Calls the "last_insert_id" method of DBI. See DBI for
153 details. Note that this feature requires DBI 1.38 or
154 newer.
155 "disconnect" Destroys (finishes) active statements and disconnects.
157 Whenever the database object is destroyed, this happens
158 automatically if DBIx::Simple handled the connection
159 (i.e. you didn't use an existing DBI handle). After
160 disconnecting, you can no longer use the database object
161 or any of its result objects.
162 Object properties
164 "dbh" Exposes the internal database handle. Use this only if
166 you know what you are doing. Keeping a reference or doing
167 queries can interfere with DBIx::Simple's garbage
168 collection and error reporting.
169 "lc_columns = $bool"
171 When true at time of query execution, makes several
172 result object methods use lower cased column names.
173 "lc_columns" is true by default.
174 "keep_statements = $integer"
176 Sets the number of statement objects that DBIx::Simple
177 can keep for reuse. This can dramatically speed up
178 repeated queries (like when used in a loop).
179 "keep_statements" is 16 by default.
180 A query is only reused if it equals a previously used one
182 literally. This means that to benefit from this caching
183 mechanism, you must use placeholders and never
184 interpolate variables yourself.
185 # Wrong:
187 $db->query("INSERT INTO foo VALUES ('$foo', '$bar', '$baz')");
188 $db->query("SELECT FROM foo WHERE foo = '$foo' OR bar = '$bar'");
189 # Right:
191 $db->query('INSERT INTO foo VALUES (??)', $foo, $bar, $baz);
192 $db->query('SELECT FROM foo WHERE foo = ? OR bar = ?', $foo, $baz);
193 Of course, automatic value escaping is a much better
195 reason for using placeholders.
196 "result_class = $string"
198 Class to use for result objects. Defaults to
199 DBIx::Simple::Result. A constructor is not used.
200 "error" Returns the error string of the last DBI method. See the
202 discussion of ""err"" and ""errstr"" in DBI.
203 "abstract = SQL::Abstract->new(...)"
205 Sets the object to use with the "select", "insert",
206 "update" and "delete" methods. On first access, will
207 create one with SQL::Abstract's default options.
208 Requires Nathan Wiger's SQL::Abstract, which is available
210 from CPAN.
211 In theory, you can assign any object to this property, as
213 long as that object has these four methods, and they
214 return a list suitable for use with the "query" method.
215 DBIx::Simple::Dummy
217 The "query" method of DBIx::Simple returns a dummy object on failure.
218 Its methods all return an empty list or undef, depending on context.
219 When used in boolean context, a dummy object evaluates to false.
220 DBIx::Simple::Result methods
222 Methods documented to return "a list" return a reference to an array of
223 the same in scalar context, unless something else is explicitly
224 mentioned.
225 "columns" Returns a list of column names. Affected by "lc_columns".
227 "bind(LIST)" Binds the given LIST of variables to the columns. Unlike
229 with DBI's "bind_columns", passing references is not
230 needed.
231 Bound variables are very efficient. Binding a tied
233 variable doesn't work.
234 "attr(...)" Returns a copy of an sth attribute (property). See
236 "Statement Handle Attributes" in DBI for details.
237 "func(...)" This calls the "func" method on the sth of DBI. See DBI
239 for details.
240 "rows" Returns the number of rows affected by the last row
242 affecting command, or -1 if the number of rows is not
243 known or not available.
244 For SELECT statements, it is generally not possible to
246 know how many rows are returned. MySQL does provide this
247 information. See DBI for a detailed explanation.
248 "finish" Finishes the statement. After finishing a statement, it
250 can no longer be used. When the result object is
251 destroyed, its statement handle is automatically finished
252 and destroyed. There should be no reason to call this
253 method explicitly; just let the result object go out of
254 scope.
255 Fetching a single row at a time
257 "fetch" Returns a reference to the array that holds the values.
259 This is the same array every time.
260 Subsequent fetches (using any method) may change the
262 values in the variables passed and the returned
263 reference's array.
264 "into(LIST)" Combines "bind" with "fetch". Returns what "fetch"
266 returns.
267 "list" Returns a list of values, or (in scalar context), only
269 the last value.
270 "array" Returns a reference to an array.
272 "hash" Returns a reference to a hash, keyed by column name.
274 Affected by "lc_columns".
275 "kv_list" Returns an ordered list of interleaved keys and values.
277 Affected by "lc_columns".
278 "kv_array" Returns a reference to an array of interleaved column
280 names and values. Like kv, but returns an array reference
281 even in list context. Affected by "lc_columns".
282 "object($class, ...)"
284 Returns an instance of $class. See "Object construction".
285 Possibly affected by "lc_columns".
286 Fetching all remaining rows
288 "flat" Returns a flattened list.
290 "arrays" Returns a list of references to arrays
292 "hashes" Returns a list of references to hashes, keyed by column
294 name. Affected by "lc_columns".
295 "kv_flat" Returns an flattened list of interleaved column names and
297 values. Affected by "lc_columns".
298 "kv_arrays" Returns a list of references to arrays of interleaved
300 column names and values. Affected by "lc_columns".
301 "objects($class, ...)"
303 Returns a list of instances of $class. See "Object
304 construction". Possibly affected by "lc_columns".
305 "map"
307 "group" Constructs a simple hash, using the two columns as
308 key/value pairs. Should only be used with queries that
309 return two columns. Returns a list of interleaved keys
310 and values, or (in scalar context), a reference to a
311 hash.
312 With unique keys, use "map". With non-unique keys, use
314 "group", which gives an array of values per key.
315 "map_hashes($column_name)"
317 "group_arrays($column_number)"
318 Constructs a hash keyed by the values in the chosen
319 column, and returns a list of interleaved keys and
320 values, or (in scalar context), a reference to a hash.
321 Affected by "lc_columns".
322 With unique keys, use "map_hashes", which gives a single
324 hash per key. With non-unique keys, use "group_hashes",
325 which gives an array of hashes per key.
326 "map_arrays($column_number)"
328 "group_arrays($column_number)"
329 Constructs a hash keyed by the values in the chosen
330 column, and returns a list of interleaved keys and
331 values, or (in scalar context), a reference to a hash.
332 With unique keys, use "map_arrays", which gives a single
334 array per key. With non-unique keys, use "group_arrays",
335 which gives an array of arrays per key.
336 "xto(%attr)" Returns a DBIx::XHTML_Table object, passing the
338 constructor a reference to %attr.
339 Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
341 which is available from CPAN.
342 In general, using the "html" method (described below) is
344 much easier. "xto" is available in case you need more
345 flexibility. Not affected by "lc_columns".
346 "html(%attr)" Returns an (X)HTML formatted table, using the
348 DBIx::XHTML_Table module. Passes a reference to %attr to
349 both the constructor and the "output" method.
350 Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
352 which is available from CPAN.
353 This method is a shortcut method. That means that
355 $result->html
357 $result->html(
359 tr => { bgcolor => [ 'silver', 'white' ] },
360 no_ucfirst => 1
361 )
362 do the same as:
364 $result->xto->output
366 $result->xto(
368 tr => { bgcolor => [ 'silver', 'white' ] }
369 )->output(
370 no_ucfirst => 1
371 );
372 "text($type)" Returns a string with a simple text representation of the
374 data. $type can be any of: "neat", "table", "box". It
375 defaults to "table" if Text::Table is installed, to
376 "neat" if it isn't.
377 "table" and "box" require Anno Siegel's Text::Table,
379 which is available from CPAN.
380 Object construction
382 DBIx::Simple has basic support for returning results as objects. The
383 actual construction method has to be provided by the chosen class,
384 making this functionality rather advanced and perhaps unsuited for
385 beginning programmers.
386 When the "object" or "objects" method is called on the result object
388 returned by one of the query methods, two approaches are tried. In
389 either case, pass the name of a class as the first argument. A prefix
390 of a single colon can be used as an alias for "DBIx::Simple::Result::",
391 e.g. ":Example" is short for "DBIx::Simple::Result::Example". Modules
392 are loaded on demand.
393 The default class when no class is given, is ":RowObject". It requires
395 Jos Boumans' Object::Accessor, which is available from CPAN.
396 Simple object construction
398 When "object" is given a class that provides a "new" method, but not a
400 "new_from_dbix_simple" method, "new" is called with a list of
401 interleaved column names and values, like a flattened hash, but
402 ordered. "objects" causes "new" to be called multiple times, once for
403 each remaining row.
404 Example:
406 {
408 package DBIx::Simple::Result::ObjectExample;
409 sub new {
410 my ($class, %args) = @_;
411 return bless $class, \%args;
412 }
413 sub foo { ... }
415 sub bar { ... }
416 }
417 $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
420 Advanced object construction
422 When "object" or "objects" is given a class that provides a
424 "new_from_dbix_simple" method, any "new" is ignored, and
425 "new_from_dbix_simple" is called with a list of the
426 DBIx::Simple::Result object and any arguments passed to "object" or
427 "objects".
428 "new_from_dbix_simple" is called in scalar context for "object", and in
430 list context for "objects". In scalar context, it should fetch exactly
431 one row, and in list context, it should fetch all remaining rows.
432 Example:
434 {
436 package DBIx::Simple::Result::ObjectExample;
437 sub new_from_dbix_simple {
438 my ($class, $result, @args) = @_;
439 return map { bless $class, $_ } $result->hashes if wantarray;
440 return bless $class, $result->hash;
441 }
442 sub foo { ... }
444 sub bar { ... }
445 }
446 $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
448
450 The mapping methods do not check whether the keys are unique. Rows that
451 are fetched later overwrite earlier ones.
452
454 Pick your favourite OSI approved license :)
455 http://www.opensource.org/licenses/alphabetical
457
459 Juerd Waalboer <#####@juerd.nl> <http://juerd.nl/>
460
462 perl, perlref
463 DBI, DBIx::Simple::Examples, SQL::Abstract, DBIx::XHTML_Table
465perl v5.28.0 2017-12-08 DBIx::Simple(3)