1DBIx::Simple(3)       User Contributed Perl Documentation      DBIx::Simple(3)
2
3
4

NAME

6       DBIx::Simple - Very complete easy-to-use OO interface to DBI
7

SYNOPSIS

9   DBIx::Simple
10           $db = DBIx::Simple->connect(...)  # or ->new
11
12           $db->keep_statements = 16
13           $db->lc_columns = 1
14           $db->result_class = 'DBIx::Simple::Result';
15
16           $db->begin_work         $db->commit
17           $db->rollback           $db->disconnect
18           $db->func(...)          $db->last_insert_id
19
20           $result = $db->query(...)
21
22   DBIx::SImple + SQL::Interp
23           $result = $db->iquery(...)
24
25   DBIx::Simple + SQL::Abstract
26           $db->abstract = SQL::Abstract->new(...)
27
28           $result = $db->select(...)
29           $result = $db->insert(...)
30           $result = $db->update(...)
31           $result = $db->delete(...)
32
33   DBIx::Simple::Result
34           @columns = $result->columns
35
36           $result->into($foo, $bar, $baz)
37           $row = $result->fetch
38
39           @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
46           %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
50           $rows = $result->rows
51
52           $dump = $result->text
53
54           $result->finish
55
56   DBIx::Simple::Result + DBIx::XHTML_Table
57           $html = $result->html(...)
58
59           $table_object = $result->xto(...)
60
61   Examples
62       Please read DBIx::Simple::Examples for code examples.
63

DESCRIPTION

65       DBIx::Simple provides a simplified interface to DBI, Perl's powerful
66       database module.
67
68       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
73       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
79   DBIx::Simple methods
80       Class methods
81
82       "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
89                     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
93                     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
97                     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
102                     This method is the constructor and returns a DBIx::Simple
103                     object on success. On failure, it returns undef.
104
105       Object methods
106
107       "query($query, @values)"
108                     Prepares and executes the query and returns a result
109                     object.
110
111                     If the string "(??)" is present in the query, it is
112                     replaced with a list of as many question marks as
113                     @values.
114
115                     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
121                     On success, returns a DBIx::Simple::Result object. On
122                     failure, returns a DBIx::Simple::Dummy object.
123
124       "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
129                     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
133       "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
140                     Requires Nathan Wiger's SQL::Abstract, which is available
141                     from CPAN.
142
143       "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
147                     "begin" is an alias for "begin_work".
148
149       "func(...)"   Calls the "func" method of DBI. See DBI for details.
150
151       "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
156       "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
163       Object properties
164
165       "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
170       "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
175       "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
181                     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
186                         # Wrong:
187                         $db->query("INSERT INTO foo VALUES ('$foo', '$bar', '$baz')");
188                         $db->query("SELECT FROM foo WHERE foo = '$foo' OR bar = '$bar'");
189
190                         # Right:
191                         $db->query('INSERT INTO foo VALUES (??)', $foo, $bar, $baz);
192                         $db->query('SELECT FROM foo WHERE foo = ? OR bar = ?', $foo, $baz);
193
194                     Of course, automatic value escaping is a much better
195                     reason for using placeholders.
196
197       "result_class = $string"
198                     Class to use for result objects. Defaults to
199                     DBIx::Simple::Result. A constructor is not used.
200
201       "error"       Returns the error string of the last DBI method. See the
202                     discussion of ""err"" and ""errstr"" in DBI.
203
204       "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
209                     Requires Nathan Wiger's SQL::Abstract, which is available
210                     from CPAN.
211
212                     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
216   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
221   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
226       "columns"     Returns a list of column names. Affected by "lc_columns".
227
228       "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
232                     Bound variables are very efficient. Binding a tied
233                     variable doesn't work.
234
235       "attr(...)"   Returns a copy of an sth attribute (property). See
236                     "Statement Handle Attributes" in DBI for details.
237
238       "func(...)"   This calls the "func" method on the sth of DBI. See DBI
239                     for details.
240
241       "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
245                     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
249       "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
256       Fetching a single row at a time
257
258       "fetch"       Returns a reference to the array that holds the values.
259                     This is the same array every time.
260
261                     Subsequent fetches (using any method) may change the
262                     values in the variables passed and the returned
263                     reference's array.
264
265       "into(LIST)"  Combines "bind" with "fetch". Returns what "fetch"
266                     returns.
267
268       "list"        Returns a list of values, or (in scalar context), only
269                     the last value.
270
271       "array"       Returns a reference to an array.
272
273       "hash"        Returns a reference to a hash, keyed by column name.
274                     Affected by "lc_columns".
275
276       "kv_list"     Returns an ordered list of interleaved keys and values.
277                     Affected by "lc_columns".
278
279       "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
283       "object($class, ...)"
284                     Returns an instance of $class. See "Object construction".
285                     Possibly affected by "lc_columns".
286
287       Fetching all remaining rows
288
289       "flat"        Returns a flattened list.
290
291       "arrays"      Returns a list of references to arrays
292
293       "hashes"      Returns a list of references to hashes, keyed by column
294                     name. Affected by "lc_columns".
295
296       "kv_flat"     Returns an flattened list of interleaved column names and
297                     values. Affected by "lc_columns".
298
299       "kv_arrays"   Returns a list of references to arrays of interleaved
300                     column names and values.  Affected by "lc_columns".
301
302       "objects($class, ...)"
303                     Returns a list of instances of $class. See "Object
304                     construction". Possibly affected by "lc_columns".
305
306       "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
313                     With unique keys, use "map". With non-unique keys, use
314                     "group", which gives an array of values per key.
315
316       "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
323                     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
327       "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
333                     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
337       "xto(%attr)"  Returns a DBIx::XHTML_Table object, passing the
338                     constructor a reference to %attr.
339
340                     Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
341                     which is available from CPAN.
342
343                     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
347       "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
351                     Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
352                     which is available from CPAN.
353
354                     This method is a shortcut method. That means that
355
356                         $result->html
357
358                         $result->html(
359                             tr => { bgcolor => [ 'silver', 'white' ] },
360                             no_ucfirst => 1
361                         )
362
363                     do the same as:
364
365                         $result->xto->output
366
367                         $result->xto(
368                             tr => { bgcolor => [ 'silver', 'white' ] }
369                         )->output(
370                             no_ucfirst => 1
371                         );
372
373       "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
378                     "table" and "box" require Anno Siegel's Text::Table,
379                     which is available from CPAN.
380
381   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
387       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
394       The default class when no class is given, is ":RowObject". It requires
395       Jos Boumans' Object::Accessor, which is available from CPAN.
396
397       Simple object construction
398
399       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
405       Example:
406
407           {
408               package DBIx::Simple::Result::ObjectExample;
409               sub new {
410                   my ($class, %args) = @_;
411                   return bless $class, \%args;
412               }
413
414               sub foo { ... }
415               sub bar { ... }
416           }
417
418
419           $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
420
421       Advanced object construction
422
423       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
429       "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
433       Example:
434
435           {
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
443               sub foo { ... }
444               sub bar { ... }
445           }
446
447           $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
448

MISCELLANEOUS

450       The mapping methods do not check whether the keys are unique. Rows that
451       are fetched later overwrite earlier ones.
452

LICENSE

454       Pick your favourite OSI approved license :)
455
456       http://www.opensource.org/licenses/alphabetical
457

AUTHOR

459       Juerd Waalboer <#####@juerd.nl> <http://juerd.nl/>
460

SEE ALSO

462       perl, perlref
463
464       DBI, DBIx::Simple::Examples, SQL::Abstract, DBIx::XHTML_Table
465
466
467
468perl v5.32.0                      2020-07-28                   DBIx::Simple(3)
Impressum