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_arrays(...)
47           %map = $result->map_hashes(...)
48           %map = $result->map
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 Storberg'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_arrays($column_number)"
307                     Constructs a hash of array references keyed by the values
308                     in the chosen column, and returns a list of interleaved
309                     keys and values, or (in scalar context), a reference to a
310                     hash.
311
312       "map_hashes($column_name)"
313                     Constructs a hash of hash references keyed by the values
314                     in the chosen column, and returns a list of interleaved
315                     keys and values, or (in scalar context), a reference to a
316                     hash. Affected by "lc_columns".
317
318       "map"         Constructs a simple hash, using the two columns as
319                     key/value pairs. Should only be used with queries that
320                     return two columns. Returns a list of interleaved keys
321                     and values, or (in scalar context), a reference to a
322                     hash.
323
324       "xto(%attr)"  Returns a DBIx::XHTML_Table object, passing the
325                     constructor a reference to %attr.
326
327                     Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
328                     which is available from CPAN.
329
330                     In general, using the "html" method (described below) is
331                     much easier. "xto" is available in case you need more
332                     flexibility. Not affected by "lc_columns".
333
334       "html(%attr)" Returns an (X)HTML formatted table, using the
335                     DBIx::XHTML_Table module. Passes a reference to %attr to
336                     both the constructor and the "output" method.
337
338                     Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
339                     which is available from CPAN.
340
341                     This method is a shortcut method. That means that
342
343                         $result->html
344
345                         $result->html(
346                             tr => { bgcolor => [ 'silver', 'white' ] },
347                             no_ucfirst => 1
348                         )
349
350                     do the same as:
351
352                         $result->xto->output
353
354                         $result->xto(
355                             tr => { bgcolor => [ 'silver', 'white' ] }
356                         )->output(
357                             no_ucfirst => 1
358                         );
359
360       "text($type)" Returns a string with a simple text representation of the
361                     data. $type can be any of: "neat", "table", "box". It
362                     defaults to "table" if Text::Table is installed, to
363                     "neat" if it isn't.
364
365                     "table" and "box" require Anno Siegel's Text::Table,
366                     which is available from CPAN.
367
368   Object construction
369       DBIx::Simple has basic support for returning results as objects. The
370       actual construction method has to be provided by the chosen class,
371       making this functionality rather advanced and perhaps unsuited for
372       beginning programmers.
373
374       When the "object" or "objects" method is called on the result object
375       returned by one of the query methods, two approaches are tried. In
376       either case, pass the name of a class as the first argument. A prefix
377       of a single colon can be used as an alias for "DBIx::Simple::Result::",
378       e.g. ":Example" is short for "DBIx::Simple::Result::Example". When this
379       shortcut is used, the corresponding module is loaded automatically.
380
381       The default class when no class is given, is ":RowObject". It requires
382       Jos Boumans' Object::Accessor, which is available from CPAN.
383
384       Simple object construction
385
386       When "object" is given a class that provides a "new" method, but not a
387       "new_from_dbix_simple" method, "new" is called with a list of
388       interleaved column names and values, like a flattened hash, but
389       ordered. "objects" causes "new" to be called multiple times, once for
390       each remaining row.
391
392       Example:
393
394           {
395               package DBIx::Simple::Result::ObjectExample;
396               sub new {
397                   my ($class, %args) = @_;
398                   return bless $class, \%args;
399               }
400
401               sub foo { ... }
402               sub bar { ... }
403           }
404
405
406           $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
407
408       Advanced object construction
409
410       When "object" or "objects" is given a class that provides a
411       "new_from_dbix_simple" method, any "new" is ignored, and
412       "new_from_dbix_simple" is called with a list of the
413       DBIx::Simple::Result object and any arguments passed to "object" or
414       "objects".
415
416       "new_from_dbix_simple" is called in scalar context for "object", and in
417       list context for "objects". In scalar context, it should fetch exactly
418       one row, and in list context, it should fetch all remaining rows.
419
420       Example:
421
422           {
423               package DBIx::Simple::Result::ObjectExample;
424               sub new_from_dbix_simple {
425                   my ($class, $result, @args) = @_;
426                   return map { bless $class, $_ } $result->hashes if wantarray;
427                   return       bless $class, $result->hash;
428               }
429
430               sub foo { ... }
431               sub bar { ... }
432           }
433
434           $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
435

MISCELLANEOUS

437       The mapping methods do not check whether the keys are unique. Rows that
438       are fetched later overwrite earlier ones.
439

LICENSE

441       Pick your favourite OSI approved license :)
442
443       http://www.opensource.org/licenses/alphabetical
444

AUTHOR

446       Juerd Waalboer <#####@juerd.nl> <http://juerd.nl/>
447

SEE ALSO

449       perl, perlref
450
451       DBI, DBIx::Simple::Examples, SQL::Abstract, DBIx::XHTML_Table
452
453
454
455perl v5.16.3                      2011-01-04                   DBIx::Simple(3)
Impressum