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_arrays(...)
47 %map = $result->map_hashes(...)
48 %map = $result->map
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 Storberg'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_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 "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 "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 "xto(%attr)" Returns a DBIx::XHTML_Table object, passing the
325 constructor a reference to %attr.
326 Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
328 which is available from CPAN.
329 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 "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 Requires Jeffrey Hayes Anderson's DBIx::XHTML_Table,
339 which is available from CPAN.
340 This method is a shortcut method. That means that
342 $result->html
344 $result->html(
346 tr => { bgcolor => [ 'silver', 'white' ] },
347 no_ucfirst => 1
348 )
349 do the same as:
351 $result->xto->output
353 $result->xto(
355 tr => { bgcolor => [ 'silver', 'white' ] }
356 )->output(
357 no_ucfirst => 1
358 );
359 "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 "table" and "box" require Anno Siegel's Text::Table,
366 which is available from CPAN.
367 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 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 The default class when no class is given, is ":RowObject". It requires
382 Jos Boumans' Object::Accessor, which is available from CPAN.
383 Simple object construction
385 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 Example:
393 {
395 package DBIx::Simple::Result::ObjectExample;
396 sub new {
397 my ($class, %args) = @_;
398 return bless $class, \%args;
399 }
400 sub foo { ... }
402 sub bar { ... }
403 }
404 $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
407 Advanced object construction
409 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 "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 Example:
421 {
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 sub foo { ... }
431 sub bar { ... }
432 }
433 $db->query('SELECT foo, bar FROM baz')->object(':ObjectExample')->foo();
435
437 The mapping methods do not check whether the keys are unique. Rows that
438 are fetched later overwrite earlier ones.
439
441 Pick your favourite OSI approved license :)
442 http://www.opensource.org/licenses/alphabetical
444
446 Juerd Waalboer <#####@juerd.nl> <http://juerd.nl/>
447
449 perl, perlref
450 DBI, DBIx::Simple::Examples, SQL::Abstract, DBIx::XHTML_Table
452perl v5.16.3 2011-01-04 DBIx::Simple(3)