DBIx::Interp(3pm)

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

NAME

6       DBIx::Interp - Interpolate Perl variables into SQL with DBI
7

SYNOPSIS

9         use DBI;
10         use DBIx::Interp ':all';
11
12         my $dbx = DBIx::Interp->new($dbh);
13
14         my $rv = $dbx->do_i('INSERT INTO table', \%item);
15         my $rv = $dbx->do_i('UPDATE table SET',  \%item, 'WHERE item_id <> ', \2);
16         my $rv = $dbx->do_i('DELETE FROM table WHERE item_id = ', \2);
17
18         my $LoH = $dbx->selectall_arrayref_i('
19               SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v
20               ,attr(Slice=>{}));
21

DESCRIPTION

23       DBIx::Interp brings SQL::Interp and DBI together in a natural way.
24       Please read the documentation of those two modules if you are
25       unfamiliar with them.  The DBIx::Interp interface adds methods to the
26       DBI interface with an "_i" suffix, indicating that SQL::Interp
27       interpolatation is performed in these cases.
28
29        Note that this module is no longer used by the maintainer.
30       DBIx::Simple now features SQL::Interp integration and is the
31       recommended alternative.
32

Helper Functions you may need

34   "attr"
35         dbi_interp("SELECT * from my_table", attr( Slice => {} ) );
36
37       Creates and returns an DBIx::Interp::Attr object. When processed by
38       dbi_interp(), it will add the provided key/value pairs to the "%attrs"
39       hash that is accepted by many DBI methods.
40
41   "key_field"
42         $keyobj = key_field($key_field);
43
44       Creates and returns an DBIx::Interp::Key object. When processed by
45       dbi_interp(), an extra $key_field value will be returned in the result
46       so that the result is suitable for passing into $dbh->fetchrow_hashref
47       and related methods.
48
49         my ($sql, $key, $attr, @bind) =
50         my @params = dbi_interp "SELECT * FROM mytable", key_field('itemid');
51         $dbh->selectall_hashref(@params);
52

Database object (DBX) methods

54       An object of type DBIx::Interp represents (and wraps) a database
55       handle.  Most of its methods are wrappers around corresponding DBI
56       methods.
57
58       "new" (static method)
59            my $dbx = DBIx::Interp->new($db, %params);
60
61           Creates a new object, creating or attaching a DBI handle.
62
63           $db is either a DBI database handle or an ARRAYREF containing
64           parameters that will be passed to DBI::connect, e.g.
65           [$data_source, $username, $auth, \%attr].  This parameter may be
66           omitted.
67
68           Any additional %params are passed onto SQL::Interp::new.
69
70       "connect" (static method)
71            $dbx = DBIx::Interp->connect($data_source, $username, $auth, \%attr);
72
73           Connects to a database.
74
75           This is identical to DBI::connect except that it returns at
76           DBIx::Interp object.  An alternate way to connect or attach an
77           existing DBI handle is via the "new" method.
78
79       "dbh"
80            $dbh = $dbx->dbh();
81
82           Returns the underlying DBI handle $dbh.  The is useful if you need
83           to pass the DBI handle to code that does not use SQL::Interp.
84
85            $dbx->dbh()->selectall_arrayref(
86                "SELECT * FROM mytable WHERE x = ?", undef, $x);
87
88       "stx"
89            $stx = $dbx->stx();
90
91           Returns the underlying statement handle set $stx. (These are
92           discussed later.)  Each DBIx::Interp object contains one statement
93           handle set for use on non-prepared database calls (e.g.
94           selectall_.*() methods).
95
96            $dbx->stx()->max_sths(10);
97
98   do_i
99   selectall_arrayref_i
100   selectall_hashref_i
101   selectcol_arrayref_i
102   selectrow_array_i
103   selectrow_arrayref_i
104   selectrow_hashref_i
105       These methods are identical to those in DBI except interpolation is
106       performed via SQL::Interp.
107
108   prepare
109        $stx = $dbx->prepare();
110
111       Creates a new statement handle set ($stx of type SQL::Interp::STX)
112       associated with $dbx.  There are no parameters.
113
114       A statement handle set (stx) is an abstraction of a statement handle
115       and represents an entire set of statement handles for a given class of
116       SQL queries.  This abstraction is used because a single interpolation
117       list may interpolate into any number of SQL queries (depending on
118       variable input), so multiple statement handles may need to be managed
119       and cached.  Typically, you do not need to call "prepare" directly
120       because DBIx::Interp can transparently mangage a statement handle set
121       (see $dbx->stx()->max_sths(10)).
122
123       Up to one statement handle in a set is considered active.  Other
124       operations performed on the statement handle set are passed to the
125       active statement handle so that the statement handle set often looks
126       and feels like a regular statement handle.
127
128   Statement handle set (STX) methods
129       These methods are for statement handle set objects.
130
131       "new"
132             $stx = SQL::Interp::STX->new($dbx);
133
134           Creates a new statement handle set.  Typically this is not called
135           directly but rather is invoked through prepare().
136
137       "max_sths"
138             $max_sths = $stx->max_sths(); # get
139             $stx->max_sths($max_sths);    # set
140
141           Gets or sets the maximum number of statement handles to cache in
142           the statement handle set.  The default and minimum value is 1.
143
144       "sth"
145             $sth = $stx->sth();
146
147           Gets the current active statement handle (e.g. the only that was
148           just executed).  Returns undef on none.
149
150       "sths"
151             $sths = $stx->sths();
152
153           Return a hashref of contained statement handles (map: $sql ->
154           $sth).
155
156       "execute"
157             $rv = $stx->execute(@list);
158
159           Executes the query in the given interpolation list against a
160           statement handle.  If no statement matching statement handle
161           exists, a new one is prepared.  The used statement handle is made
162           the active statement handle.  Return on error behavior is similar
163           to DBI's execute.
164
165           @list is an interpolation list (suitable for passing to
166           dbi_interp()).
167
168       "fetch..."
169             $ary_ref = $stx->fetchrow_arrayref();
170
171           Various fetch.* methods analogous to those in DBIx::Interp are
172           available.  The fetch will be performed against the active
173           statement handle in the set.
174
175       These are more advanced examples.
176
177   Binding variable types (DBI bind_param)
178       Compare this much simpler code to the example in SQL::Interp.
179
180         $dbx->selectall_arrayref(
181             "SELECT * FROM mytable WHERE",
182             "x=", \$x, "AND y=", sql_type(\$y, SQL_VARCHAR), "AND z IN",
183             sql_type([1, 2], SQL_INTEGER)
184         );
185

DESIGN NOTES

187   Philosophy and requirements
188       DBIx::Interp is designed to look an feel like DBI even when the DBI
189       interface is not entirely user friendly (e.g. the
190       (fetch|select)(all|row)?_(array|hash)(ref)? and do methods).  Still,
191       the approach lowers the learning code and could simplify the process of
192       converting existing DBI code over to SQL::Interp.
193
194       The use of statement handle sets (STX) is not strictly necessary but is
195       rather designed to mimic DBI's statement handles more than anything
196       else.  The DBX object itself contains a statement handle set, which can
197       be used for non-prepared calls such as to selectall_.*() methods (i.e.
198       cache statement handles like in DBIx::Simple's keep_statements).
199
200         $dbx->stx()->max_sths(2);
201         $dbx->do(...) for 1..5;
202         $dbx->do(...) for 1..5;
203
204       An ideal solution would probably be to integrate SQL::Interp into
205       DBIx::Simple rather than directly into DBI.
206
207   Proposed enhancements
208       The following enhancements to SQL::Interp have been proposed.  The most
209       important suggestions are listed at top, and some suggestions could be
210       rejected.
211
212       DBI database handle and statement handle attributes are not currently
213       exposed from the wrapper except via $dbx->dbh()->{...}.  Maybe a Tie
214       can be used. e.g. $dbx->{mysql_insert_id}
215
216       Support might be added for something analogous to DBI's
217       bind_param_inout.
218
219       DBI's bind_param_array is not currently supported.  A syntax as follows
220       might be used:
221
222         "INSERT INTO mytable", [[...], [...], ...]
223
224       Passing identified variables:
225
226         my $x = {one => 'two'};
227         my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", sql_type(\$x);
228         $stx->execute_vars();
229         ...
230         $x->{two} = 'three';
231         $stx->execute_vars();
232         ...
233
234         my $x = {one => 'two'};
235         my $y = {one => 'three', two => 'four'};
236         my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", sql_type($x, 'x'));
237         $stx->execute_vars();
238         ...
239         $stx->execute_vars(sql_type($x, 'x'); # or?
240         $stx->execute_vars(x => $x); # or?
241         ...
242

CONTRIBUTORS

244       David Manura (<http://math2.org/david>) (author).  The existence and
245       original design of this module as a wrapper around DBI was suggested by
246       Jim Cromie.
247

FEEDBACK

249       Bug reports and comments on the design are most welcome.  See the main
250       SQL::Interp module for details.
251

LEGAL

253       Copyright (c) 2004-2005, David Manura.  This module is free software.
254       It may be used, redistributed and/or modified under the same terms as
255       Perl itself.  See <http://www.perl.com/perl/misc/Artistic.html>.
256