line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package SQL::Template; |
2
|
|
|
|
|
|
|
|
3
|
2
|
|
|
2
|
|
64440
|
use warnings; |
|
2
|
|
|
|
|
4
|
|
|
2
|
|
|
|
|
121
|
|
4
|
2
|
|
|
2
|
|
16
|
use strict; |
|
2
|
|
|
|
|
4
|
|
|
2
|
|
|
|
|
63
|
|
5
|
|
|
|
|
|
|
|
6
|
2
|
|
|
2
|
|
16
|
use Carp; |
|
2
|
|
|
|
|
10
|
|
|
2
|
|
|
|
|
198
|
|
7
|
2
|
|
|
2
|
|
2706
|
use DBI qw(:sql_types); |
|
2
|
|
|
|
|
32180
|
|
|
2
|
|
|
|
|
1036
|
|
8
|
2
|
|
|
2
|
|
902
|
use SQL::Template::XMLBuilder; |
|
0
|
|
|
|
|
|
|
|
0
|
|
|
|
|
|
|
9
|
|
|
|
|
|
|
|
10
|
|
|
|
|
|
|
=head1 NAME |
11
|
|
|
|
|
|
|
|
12
|
|
|
|
|
|
|
SQL::Template - A new way to organize your database code |
13
|
|
|
|
|
|
|
|
14
|
|
|
|
|
|
|
=head1 VERSION |
15
|
|
|
|
|
|
|
|
16
|
|
|
|
|
|
|
Version 0.2.4 |
17
|
|
|
|
|
|
|
|
18
|
|
|
|
|
|
|
=cut |
19
|
|
|
|
|
|
|
|
20
|
|
|
|
|
|
|
our $VERSION = '0.2.4'; |
21
|
|
|
|
|
|
|
|
22
|
|
|
|
|
|
|
=head1 SYNOPSIS |
23
|
|
|
|
|
|
|
|
24
|
|
|
|
|
|
|
use SQL::Template; |
25
|
|
|
|
|
|
|
|
26
|
|
|
|
|
|
|
my $sql = SQL::Template->new(-filename=>"my-custom-sqls.xml"); |
27
|
|
|
|
|
|
|
my $dbh =DBI->connect("dbi:SQLite:dbname=example.sqlite","",""); |
28
|
|
|
|
|
|
|
|
29
|
|
|
|
|
|
|
#Simple record insert |
30
|
|
|
|
|
|
|
$sql->do("insert_country", $dbh, {COUNTRY_ID=>'ES', NAME=>'SPAIN'} ); |
31
|
|
|
|
|
|
|
|
32
|
|
|
|
|
|
|
# fetch records |
33
|
|
|
|
|
|
|
my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} ); |
34
|
|
|
|
|
|
|
while( my $hr = $stmt->fetchrow_hashref ) { |
35
|
|
|
|
|
|
|
print $hr->{NAME}, "\n"; |
36
|
|
|
|
|
|
|
} |
37
|
|
|
|
|
|
|
$stmt->finish; |
38
|
|
|
|
|
|
|
|
39
|
|
|
|
|
|
|
|
40
|
|
|
|
|
|
|
### file: my-custom-sqls.xml |
41
|
|
|
|
|
|
|
|
42
|
|
|
|
|
|
|
|
43
|
|
|
|
|
|
|
|
44
|
|
|
|
|
|
|
|
45
|
|
|
|
|
|
|
INSERT INTO COUNTRY(COUNTRY_ID, NAME) |
46
|
|
|
|
|
|
|
VALUES( ${COUNTRY_ID}, ${NAME} ) |
47
|
|
|
|
|
|
|
|
48
|
|
|
|
|
|
|
|
49
|
|
|
|
|
|
|
|
50
|
|
|
|
|
|
|
SELECT * FROM PERSON |
51
|
|
|
|
|
|
|
|
52
|
|
|
|
|
|
|
NAME=${NAME} |
53
|
|
|
|
|
|
|
|
54
|
|
|
|
|
|
|
|
55
|
|
|
|
|
|
|
|
56
|
|
|
|
|
|
|
|
57
|
|
|
|
|
|
|
|
58
|
|
|
|
|
|
|
=cut |
59
|
|
|
|
|
|
|
|
60
|
|
|
|
|
|
|
=head1 DESCRIPTION |
61
|
|
|
|
|
|
|
|
62
|
|
|
|
|
|
|
Write SQL sentences in external files and use them from Perl. |
63
|
|
|
|
|
|
|
|
64
|
|
|
|
|
|
|
Imagine this situation: you know DBI and you like it, because you can make use of |
65
|
|
|
|
|
|
|
your SQL knowledge. But you are not happy having the SQL code into the Perl code. |
66
|
|
|
|
|
|
|
You can use other CPAN modules, which let us to abstract SQL code. But we want to |
67
|
|
|
|
|
|
|
write SQL code, we feel confortable with it. |
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
This module decouples SQL sentences from Perl code, writting sentences in a XML file, |
70
|
|
|
|
|
|
|
that you can use in different parts of your code. SQL::Template allows dynamic test of |
71
|
|
|
|
|
|
|
expressions, and reuse of fragments. |
72
|
|
|
|
|
|
|
|
73
|
|
|
|
|
|
|
The SQL handled sentences are SQL-inyection free; SQL::Template make use of parameter |
74
|
|
|
|
|
|
|
binding. |
75
|
|
|
|
|
|
|
|
76
|
|
|
|
|
|
|
=head1 XML file |
77
|
|
|
|
|
|
|
|
78
|
|
|
|
|
|
|
The XML file contains the SQL sentences that you will use with SQL::Template. This is more |
79
|
|
|
|
|
|
|
than a dictionary container, it allows us to build dinamyc SQL and reuse fragments. |
80
|
|
|
|
|
|
|
|
81
|
|
|
|
|
|
|
=head2 General |
82
|
|
|
|
|
|
|
|
83
|
|
|
|
|
|
|
The different parts are enclosed between C<< >> and C<< >> |
84
|
|
|
|
|
|
|
|
85
|
|
|
|
|
|
|
|
86
|
|
|
|
|
|
|
|
87
|
|
|
|
|
|
|
|
88
|
|
|
|
|
|
|
|
89
|
|
|
|
|
|
|
|
90
|
|
|
|
|
|
|
|
91
|
|
|
|
|
|
|
|
92
|
|
|
|
|
|
|
=head2 st:do |
93
|
|
|
|
|
|
|
|
94
|
|
|
|
|
|
|
This command is used to make DDL sentences or INSERT, UPDATE and DELETE. For example: |
95
|
|
|
|
|
|
|
|
96
|
|
|
|
|
|
|
|
97
|
|
|
|
|
|
|
UPDATE AUTHOR SET NAME=${NAME}, FIRST_NAME=${FIRSTNAME, SQL_VARCHAR} |
98
|
|
|
|
|
|
|
WHERE AUTHOR_ID=${ID} |
99
|
|
|
|
|
|
|
|
100
|
|
|
|
|
|
|
|
101
|
|
|
|
|
|
|
This simple command shows us important things: |
102
|
|
|
|
|
|
|
|
103
|
|
|
|
|
|
|
=over |
104
|
|
|
|
|
|
|
|
105
|
|
|
|
|
|
|
=item name |
106
|
|
|
|
|
|
|
|
107
|
|
|
|
|
|
|
The name attribute is mandatory, and it will be used to link the Perl code with the SQL |
108
|
|
|
|
|
|
|
|
109
|
|
|
|
|
|
|
=item parameters |
110
|
|
|
|
|
|
|
|
111
|
|
|
|
|
|
|
Parameters tou pass with a HASH reference to SQL::Template are binding to the SQL. In the |
112
|
|
|
|
|
|
|
previous example, C<${NAME}> and C<${FIRSTNAME, SQL_VARCHAR}>. The fisrt is the simple use, |
113
|
|
|
|
|
|
|
where the parameter will be replaced (using DBI bind). The second one will be used if you |
114
|
|
|
|
|
|
|
need to indicate the data type. |
115
|
|
|
|
|
|
|
|
116
|
|
|
|
|
|
|
=back |
117
|
|
|
|
|
|
|
|
118
|
|
|
|
|
|
|
|
119
|
|
|
|
|
|
|
=head2 st:select |
120
|
|
|
|
|
|
|
|
121
|
|
|
|
|
|
|
If we need to make SELECT sentences, the command C will be used. This is a simple |
122
|
|
|
|
|
|
|
example: |
123
|
|
|
|
|
|
|
|
124
|
|
|
|
|
|
|
|
125
|
|
|
|
|
|
|
SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID} |
126
|
|
|
|
|
|
|
|
127
|
|
|
|
|
|
|
|
128
|
|
|
|
|
|
|
Like the previous one, you can bind parameters with the C<${variable}> syntaxt |
129
|
|
|
|
|
|
|
|
130
|
|
|
|
|
|
|
|
131
|
|
|
|
|
|
|
=head2 st:fragment |
132
|
|
|
|
|
|
|
|
133
|
|
|
|
|
|
|
When we are writting SQL sentences, there are many of them similar, changing specific parts. |
134
|
|
|
|
|
|
|
I think that you can reuse SQL fragments in order to reduce the code you write, and to make |
135
|
|
|
|
|
|
|
the maintenance easier. |
136
|
|
|
|
|
|
|
|
137
|
|
|
|
|
|
|
=over 2 |
138
|
|
|
|
|
|
|
|
139
|
|
|
|
|
|
|
=item define a fragment |
140
|
|
|
|
|
|
|
|
141
|
|
|
|
|
|
|
|
142
|
|
|
|
|
|
|
AND NAME LIKE 'A%' |
143
|
|
|
|
|
|
|
|
144
|
|
|
|
|
|
|
|
145
|
|
|
|
|
|
|
=item use it |
146
|
|
|
|
|
|
|
|
147
|
|
|
|
|
|
|
|
148
|
|
|
|
|
|
|
SELECT * FROM AUTHOR WHERE AUTHOR_ID=${ID} |
149
|
|
|
|
|
|
|
|
150
|
|
|
|
|
|
|
|
151
|
|
|
|
|
|
|
|
152
|
|
|
|
|
|
|
=back |
153
|
|
|
|
|
|
|
|
154
|
|
|
|
|
|
|
=head2 Dynamic sentences |
155
|
|
|
|
|
|
|
|
156
|
|
|
|
|
|
|
SQL::Template dynamic feature is simple and strong. It allow us to write comple SQL |
157
|
|
|
|
|
|
|
sentences that can be different depending on parameters values. For example: |
158
|
|
|
|
|
|
|
|
159
|
|
|
|
|
|
|
|
160
|
|
|
|
|
|
|
SELECT * FROM AUTHOR |
161
|
|
|
|
|
|
|
WHERE YEAR=${YEAR} |
162
|
|
|
|
|
|
|
|
163
|
|
|
|
|
|
|
CITY != ${CITY} |
164
|
|
|
|
|
|
|
|
165
|
|
|
|
|
|
|
|
166
|
|
|
|
|
|
|
AGE > 18 |
167
|
|
|
|
|
|
|
|
168
|
|
|
|
|
|
|
|
169
|
|
|
|
|
|
|
|
170
|
|
|
|
|
|
|
As you can see, C<< >> command is used to build dynamic SQL. The "if" command |
171
|
|
|
|
|
|
|
can be used in C<< >> and C<< >>. It's composed by: |
172
|
|
|
|
|
|
|
|
173
|
|
|
|
|
|
|
=over 2 |
174
|
|
|
|
|
|
|
|
175
|
|
|
|
|
|
|
=item test |
176
|
|
|
|
|
|
|
|
177
|
|
|
|
|
|
|
Any valid Perl expression, where you can bind the parameters. SQL::Templante will eval |
178
|
|
|
|
|
|
|
this expression in order to calculate the result. Boolean "true" or "false" rules are the |
179
|
|
|
|
|
|
|
same that Perl uses in boolean expressions |
180
|
|
|
|
|
|
|
|
181
|
|
|
|
|
|
|
=item prepend |
182
|
|
|
|
|
|
|
|
183
|
|
|
|
|
|
|
If the test expression returns "true", prepend this text to the SQL block enclosed by "st:if". |
184
|
|
|
|
|
|
|
It isn't mandatory. |
185
|
|
|
|
|
|
|
|
186
|
|
|
|
|
|
|
=item |
187
|
|
|
|
|
|
|
|
188
|
|
|
|
|
|
|
The common "else" section in any "if" block. It isn't mandatory, and it will be used if |
189
|
|
|
|
|
|
|
the test expression returns false. |
190
|
|
|
|
|
|
|
|
191
|
|
|
|
|
|
|
=back |
192
|
|
|
|
|
|
|
|
193
|
|
|
|
|
|
|
=head1 METHODS |
194
|
|
|
|
|
|
|
|
195
|
|
|
|
|
|
|
SQL::Template methods are written in a way that it's similar to DBI interface, so I hope |
196
|
|
|
|
|
|
|
you will be confortable with them. |
197
|
|
|
|
|
|
|
|
198
|
|
|
|
|
|
|
|
199
|
|
|
|
|
|
|
=head2 new ( option=>value ) |
200
|
|
|
|
|
|
|
|
201
|
|
|
|
|
|
|
The C function takes a list of options and values, and returns |
202
|
|
|
|
|
|
|
a new B object which can then be used to use SQL sentences. |
203
|
|
|
|
|
|
|
The accepted options are (one of them is mandatory): |
204
|
|
|
|
|
|
|
|
205
|
|
|
|
|
|
|
=over |
206
|
|
|
|
|
|
|
|
207
|
|
|
|
|
|
|
=item -filename |
208
|
|
|
|
|
|
|
|
209
|
|
|
|
|
|
|
This determines the XML file which contains the SQL sentences. The object |
210
|
|
|
|
|
|
|
creation phase involves parsing the file, so any error (like syntax) cause |
211
|
|
|
|
|
|
|
an exception throw. If everything is fine, all commands searched are cached |
212
|
|
|
|
|
|
|
in order to improve the performance |
213
|
|
|
|
|
|
|
|
214
|
|
|
|
|
|
|
=item -string |
215
|
|
|
|
|
|
|
|
216
|
|
|
|
|
|
|
If you prefer to build a string with XML-syntax, you can build a SQL::Template |
217
|
|
|
|
|
|
|
object in that way. |
218
|
|
|
|
|
|
|
|
219
|
|
|
|
|
|
|
=back |
220
|
|
|
|
|
|
|
|
221
|
|
|
|
|
|
|
=cut |
222
|
|
|
|
|
|
|
|
223
|
|
|
|
|
|
|
#****************************************************************************** |
224
|
|
|
|
|
|
|
|
225
|
|
|
|
|
|
|
sub new { |
226
|
|
|
|
|
|
|
my ($class, %param) = @_; |
227
|
|
|
|
|
|
|
my $builder = SQL::Template::XMLBuilder->new; |
228
|
|
|
|
|
|
|
|
229
|
|
|
|
|
|
|
if( $param{-filename} ) { |
230
|
|
|
|
|
|
|
croak "XML config file not found [$param{-filename}]" unless(-e $param{-filename}); |
231
|
|
|
|
|
|
|
$builder->parse_file( $param{-filename} ); |
232
|
|
|
|
|
|
|
} |
233
|
|
|
|
|
|
|
elsif( $param{-string} ) { |
234
|
|
|
|
|
|
|
$builder->parse_string( $param{-string} ); |
235
|
|
|
|
|
|
|
} |
236
|
|
|
|
|
|
|
else { |
237
|
|
|
|
|
|
|
croak "XML config file not specified"; |
238
|
|
|
|
|
|
|
} |
239
|
|
|
|
|
|
|
|
240
|
|
|
|
|
|
|
my $self = { |
241
|
|
|
|
|
|
|
COMMANDS => $builder->get_commands |
242
|
|
|
|
|
|
|
}; |
243
|
|
|
|
|
|
|
return bless $self, $class; |
244
|
|
|
|
|
|
|
} |
245
|
|
|
|
|
|
|
|
246
|
|
|
|
|
|
|
#****************************************************************************** |
247
|
|
|
|
|
|
|
|
248
|
|
|
|
|
|
|
sub _prepare_and_bind { |
249
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
250
|
|
|
|
|
|
|
my $command = $self->{COMMANDS}->{lc($name)}; |
251
|
|
|
|
|
|
|
croak "Command not found: $name" if(!$command); |
252
|
|
|
|
|
|
|
my $sql = $command->sql($params); |
253
|
|
|
|
|
|
|
my $bindings = $command->bindings($params); |
254
|
|
|
|
|
|
|
|
255
|
|
|
|
|
|
|
my @matches = $sql =~ m!(\$\{\s*\w+\s*\})!gx; |
256
|
|
|
|
|
|
|
$sql =~ s!\$\{\s*\w+\s*\}!?!gx; |
257
|
|
|
|
|
|
|
|
258
|
|
|
|
|
|
|
my $stmt; |
259
|
|
|
|
|
|
|
eval { |
260
|
|
|
|
|
|
|
$stmt = $dbh->prepare($sql); |
261
|
|
|
|
|
|
|
}; |
262
|
|
|
|
|
|
|
croak "${@}with SQL: $sql" if($@); |
263
|
|
|
|
|
|
|
|
264
|
|
|
|
|
|
|
if( $bindings ) { |
265
|
|
|
|
|
|
|
my $pcount = 1; |
266
|
|
|
|
|
|
|
foreach my $key( @matches ) { |
267
|
|
|
|
|
|
|
if( ! exists($bindings->{$key}) ) { |
268
|
|
|
|
|
|
|
croak "parameter not found: $key"; |
269
|
|
|
|
|
|
|
} |
270
|
|
|
|
|
|
|
elsif( $bindings->{$key} and ('ARRAY' eq ref($bindings->{$key}) ) ) { |
271
|
|
|
|
|
|
|
$stmt->bind_param($pcount++, $bindings->{$key}->[0], eval($bindings->{$key}->[1]) ); |
272
|
|
|
|
|
|
|
} |
273
|
|
|
|
|
|
|
else { |
274
|
|
|
|
|
|
|
#print "BIND: $key => ", $bindings->{$key}, "\n"; |
275
|
|
|
|
|
|
|
$stmt->bind_param($pcount++, $bindings->{$key}); |
276
|
|
|
|
|
|
|
} |
277
|
|
|
|
|
|
|
} |
278
|
|
|
|
|
|
|
} |
279
|
|
|
|
|
|
|
return $stmt; |
280
|
|
|
|
|
|
|
} |
281
|
|
|
|
|
|
|
|
282
|
|
|
|
|
|
|
#****************************************************************************** |
283
|
|
|
|
|
|
|
|
284
|
|
|
|
|
|
|
=head2 select_stmt ( $name, $dbh, $params, $attrs ) |
285
|
|
|
|
|
|
|
|
286
|
|
|
|
|
|
|
This method search in the command cache, and if it's found, SQL::Template |
287
|
|
|
|
|
|
|
try to apply the params and execute in provided database handle. These are |
288
|
|
|
|
|
|
|
the arguments: |
289
|
|
|
|
|
|
|
|
290
|
|
|
|
|
|
|
=over |
291
|
|
|
|
|
|
|
|
292
|
|
|
|
|
|
|
=item $name |
293
|
|
|
|
|
|
|
|
294
|
|
|
|
|
|
|
The name of SQL sentence to use. This must match with a sentence in the |
295
|
|
|
|
|
|
|
XML file. |
296
|
|
|
|
|
|
|
|
297
|
|
|
|
|
|
|
=item $dbh |
298
|
|
|
|
|
|
|
|
299
|
|
|
|
|
|
|
The database handle to be used. Note tat SQL::Template doesn't establish a |
300
|
|
|
|
|
|
|
connection with your DB, it only use the one you want. |
301
|
|
|
|
|
|
|
|
302
|
|
|
|
|
|
|
=item $params |
303
|
|
|
|
|
|
|
|
304
|
|
|
|
|
|
|
When the SQL sentence needs parameters, you must provide them with a hash |
305
|
|
|
|
|
|
|
reference variable. |
306
|
|
|
|
|
|
|
|
307
|
|
|
|
|
|
|
=item $attrs |
308
|
|
|
|
|
|
|
|
309
|
|
|
|
|
|
|
Any aditional attribute you need to pass to the database driver, it will be used |
310
|
|
|
|
|
|
|
in the DBI commands. Typically, you don't use this param. |
311
|
|
|
|
|
|
|
|
312
|
|
|
|
|
|
|
=back |
313
|
|
|
|
|
|
|
|
314
|
|
|
|
|
|
|
This methods use the following DBI functions: prepare, bind_param, execute. It |
315
|
|
|
|
|
|
|
returns a DBI::st handle, you can fetch in the habitual way. For example: |
316
|
|
|
|
|
|
|
|
317
|
|
|
|
|
|
|
my $stmt = $sql->select_stmt("query_for_persons", $dbh, {NAME=>'JOHN'} ); |
318
|
|
|
|
|
|
|
while( my @row = $stmt->fetchrow_array ) { |
319
|
|
|
|
|
|
|
print "@row\n"; |
320
|
|
|
|
|
|
|
} |
321
|
|
|
|
|
|
|
$stmt->finish; |
322
|
|
|
|
|
|
|
|
323
|
|
|
|
|
|
|
=cut |
324
|
|
|
|
|
|
|
|
325
|
|
|
|
|
|
|
sub select_stmt { |
326
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
327
|
|
|
|
|
|
|
my $stmt = $self->_prepare_and_bind($name, $dbh, $params, $attrs); |
328
|
|
|
|
|
|
|
$stmt->execute; |
329
|
|
|
|
|
|
|
return $stmt; |
330
|
|
|
|
|
|
|
} |
331
|
|
|
|
|
|
|
|
332
|
|
|
|
|
|
|
=head2 selectrow_array ( $name, $dbh, $params, $attrs ) |
333
|
|
|
|
|
|
|
|
334
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
335
|
|
|
|
|
|
|
in section L"select_stmt">. |
336
|
|
|
|
|
|
|
In this case, SQL::Template makes a call to DBI C |
337
|
|
|
|
|
|
|
function and C the statement handle, returning an array |
338
|
|
|
|
|
|
|
with the results |
339
|
|
|
|
|
|
|
|
340
|
|
|
|
|
|
|
=cut |
341
|
|
|
|
|
|
|
|
342
|
|
|
|
|
|
|
sub selectrow_array { |
343
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
344
|
|
|
|
|
|
|
my $stmt = $self->select_stmt($name, $dbh, $params, $attrs); |
345
|
|
|
|
|
|
|
my @row = $stmt->fetchrow_array; |
346
|
|
|
|
|
|
|
$stmt->finish; |
347
|
|
|
|
|
|
|
return @row; |
348
|
|
|
|
|
|
|
} |
349
|
|
|
|
|
|
|
|
350
|
|
|
|
|
|
|
=head2 selectrow_arrayref ( $name, $dbh, $params, $attrs ) |
351
|
|
|
|
|
|
|
|
352
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
353
|
|
|
|
|
|
|
in section L"selectrow_array">. |
354
|
|
|
|
|
|
|
In this case, SQL::Template makes a call to DBI C |
355
|
|
|
|
|
|
|
function and C the statement handle, returning an array reference |
356
|
|
|
|
|
|
|
with the results |
357
|
|
|
|
|
|
|
|
358
|
|
|
|
|
|
|
=cut |
359
|
|
|
|
|
|
|
|
360
|
|
|
|
|
|
|
sub selectrow_arrayref { |
361
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
362
|
|
|
|
|
|
|
my $stmt = $self->select_stmt($name, $dbh, $params, $attrs); |
363
|
|
|
|
|
|
|
my $row = $stmt->fetchrow_arrayref; |
364
|
|
|
|
|
|
|
$stmt->finish; |
365
|
|
|
|
|
|
|
return $row; |
366
|
|
|
|
|
|
|
} |
367
|
|
|
|
|
|
|
|
368
|
|
|
|
|
|
|
=head2 selectrow_hashref ( $name, $dbh, $params, $attrs ) |
369
|
|
|
|
|
|
|
|
370
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
371
|
|
|
|
|
|
|
in section L"selectrow_array">. |
372
|
|
|
|
|
|
|
In this case, SQL::Template makes a call to DBI C |
373
|
|
|
|
|
|
|
function and C the statement handle, returning a hash reference |
374
|
|
|
|
|
|
|
with the results |
375
|
|
|
|
|
|
|
|
376
|
|
|
|
|
|
|
=cut |
377
|
|
|
|
|
|
|
|
378
|
|
|
|
|
|
|
sub selectrow_hashref { |
379
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
380
|
|
|
|
|
|
|
my $stmt = $self->select_stmt($name, $dbh, $params, $attrs); |
381
|
|
|
|
|
|
|
my $href = $stmt->fetchrow_hashref; |
382
|
|
|
|
|
|
|
$stmt->finish; |
383
|
|
|
|
|
|
|
return $href; |
384
|
|
|
|
|
|
|
} |
385
|
|
|
|
|
|
|
|
386
|
|
|
|
|
|
|
|
387
|
|
|
|
|
|
|
=head2 selectall_arrayref |
388
|
|
|
|
|
|
|
|
389
|
|
|
|
|
|
|
This method combines "prepare", "execute" and "fetchall_arrayref" into a single call. |
390
|
|
|
|
|
|
|
It returns a reference to an array containing a reference to an array (or hash, see below) |
391
|
|
|
|
|
|
|
for each row of data fetched. |
392
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
393
|
|
|
|
|
|
|
in section L"selectrow_array">. |
394
|
|
|
|
|
|
|
|
395
|
|
|
|
|
|
|
See DBI C method for more details. |
396
|
|
|
|
|
|
|
|
397
|
|
|
|
|
|
|
=cut |
398
|
|
|
|
|
|
|
|
399
|
|
|
|
|
|
|
sub selectall_arrayref { |
400
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
401
|
|
|
|
|
|
|
my $stmt = $self->select_stmt($name, $dbh, $params, $attrs); |
402
|
|
|
|
|
|
|
my $aref = $stmt->fetchall_arrayref; |
403
|
|
|
|
|
|
|
$stmt->finish; |
404
|
|
|
|
|
|
|
return $aref; |
405
|
|
|
|
|
|
|
} |
406
|
|
|
|
|
|
|
|
407
|
|
|
|
|
|
|
=head2 selectall_hashref |
408
|
|
|
|
|
|
|
|
409
|
|
|
|
|
|
|
This method combines "prepare", "execute" and "fetchall_arrayref" into a single call. |
410
|
|
|
|
|
|
|
It returns a reference to an array containing a reference to an hash |
411
|
|
|
|
|
|
|
for each row of data fetched. |
412
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
413
|
|
|
|
|
|
|
in section L"selectrow_array">. |
414
|
|
|
|
|
|
|
|
415
|
|
|
|
|
|
|
See DBI C method for more details. |
416
|
|
|
|
|
|
|
|
417
|
|
|
|
|
|
|
=cut |
418
|
|
|
|
|
|
|
|
419
|
|
|
|
|
|
|
sub selectall_hashref { |
420
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
421
|
|
|
|
|
|
|
my $stmt = $self->select_stmt($name, $dbh, $params, $attrs); |
422
|
|
|
|
|
|
|
my $href = $stmt->fetchall_hashref; |
423
|
|
|
|
|
|
|
$stmt->finish; |
424
|
|
|
|
|
|
|
return $href; |
425
|
|
|
|
|
|
|
} |
426
|
|
|
|
|
|
|
|
427
|
|
|
|
|
|
|
|
428
|
|
|
|
|
|
|
=head2 do ( $name, $dbh, $params, $attrs ) |
429
|
|
|
|
|
|
|
|
430
|
|
|
|
|
|
|
This method interface is similar to the previous you have seen |
431
|
|
|
|
|
|
|
in section L"select_stmt">. The main use of this function is |
432
|
|
|
|
|
|
|
to execute DDL commands and INSERT, UPDATE or DELETE commands. |
433
|
|
|
|
|
|
|
In this case, SQL::Template makes a call to DBI C |
434
|
|
|
|
|
|
|
function and returns its results to the caller. |
435
|
|
|
|
|
|
|
|
436
|
|
|
|
|
|
|
=cut |
437
|
|
|
|
|
|
|
|
438
|
|
|
|
|
|
|
|
439
|
|
|
|
|
|
|
sub do { |
440
|
|
|
|
|
|
|
my ($self, $name, $dbh, $params, $attrs) = @_; |
441
|
|
|
|
|
|
|
my $stmt = $self->_prepare_and_bind($name, $dbh, $params, $attrs); |
442
|
|
|
|
|
|
|
return $stmt->execute; |
443
|
|
|
|
|
|
|
} |
444
|
|
|
|
|
|
|
|
445
|
|
|
|
|
|
|
#************************************************************************* |
446
|
|
|
|
|
|
|
|
447
|
|
|
|
|
|
|
=head1 AUTHOR |
448
|
|
|
|
|
|
|
|
449
|
|
|
|
|
|
|
prz, C<< >> |
450
|
|
|
|
|
|
|
|
451
|
|
|
|
|
|
|
=head1 BUGS |
452
|
|
|
|
|
|
|
|
453
|
|
|
|
|
|
|
Please report any bugs or feature requests to C, or through |
454
|
|
|
|
|
|
|
the web interface at L. |
455
|
|
|
|
|
|
|
I will be notified, and then you'll automatically be notified of progress on your bug as I make changes. |
456
|
|
|
|
|
|
|
|
457
|
|
|
|
|
|
|
=head1 SUPPORT |
458
|
|
|
|
|
|
|
|
459
|
|
|
|
|
|
|
You can find documentation for this module with the perldoc command. |
460
|
|
|
|
|
|
|
|
461
|
|
|
|
|
|
|
perldoc SQL::Template |
462
|
|
|
|
|
|
|
|
463
|
|
|
|
|
|
|
|
464
|
|
|
|
|
|
|
You can also look for information at: |
465
|
|
|
|
|
|
|
|
466
|
|
|
|
|
|
|
=over 4 |
467
|
|
|
|
|
|
|
|
468
|
|
|
|
|
|
|
=item * RT: CPAN's request tracker |
469
|
|
|
|
|
|
|
|
470
|
|
|
|
|
|
|
L |
471
|
|
|
|
|
|
|
|
472
|
|
|
|
|
|
|
=item * AnnoCPAN: Annotated CPAN documentation |
473
|
|
|
|
|
|
|
|
474
|
|
|
|
|
|
|
L |
475
|
|
|
|
|
|
|
|
476
|
|
|
|
|
|
|
=item * CPAN Ratings |
477
|
|
|
|
|
|
|
|
478
|
|
|
|
|
|
|
L |
479
|
|
|
|
|
|
|
|
480
|
|
|
|
|
|
|
=item * Search CPAN |
481
|
|
|
|
|
|
|
|
482
|
|
|
|
|
|
|
L |
483
|
|
|
|
|
|
|
|
484
|
|
|
|
|
|
|
=back |
485
|
|
|
|
|
|
|
|
486
|
|
|
|
|
|
|
|
487
|
|
|
|
|
|
|
=head1 ACKNOWLEDGEMENTS |
488
|
|
|
|
|
|
|
|
489
|
|
|
|
|
|
|
|
490
|
|
|
|
|
|
|
=head1 COPYRIGHT & LICENSE |
491
|
|
|
|
|
|
|
|
492
|
|
|
|
|
|
|
Copyright 2009 prz. |
493
|
|
|
|
|
|
|
|
494
|
|
|
|
|
|
|
This program is free software; you can redistribute it and/or modify it |
495
|
|
|
|
|
|
|
under the terms of either: the GNU General Public License as published |
496
|
|
|
|
|
|
|
by the Free Software Foundation; or the Artistic License. |
497
|
|
|
|
|
|
|
|
498
|
|
|
|
|
|
|
See http://dev.perl.org/licenses/ for more information. |
499
|
|
|
|
|
|
|
|
500
|
|
|
|
|
|
|
|
501
|
|
|
|
|
|
|
=cut |
502
|
|
|
|
|
|
|
|
503
|
|
|
|
|
|
|
|
504
|
|
|
|
|
|
|
1; |