ViewVC Help
View File | Revision Log | Show Annotations | Download File
/cvs/PApp-SQL/SQL.pm
(Generate patch)

Comparing PApp-SQL/SQL.pm (file contents):
Revision 1.14 by root, Sun Mar 11 14:54:21 2001 UTC vs.
Revision 1.15 by root, Sun Apr 22 14:38:27 2001 UTC

… …
27 27
28This module provides you with easy-to-use functions to execute sql 28This module provides you with easy-to-use functions to execute sql
29commands (using DBI). Despite being easy to use, they are also quite 29commands (using DBI). Despite being easy to use, they are also quite
30efficient and allow you to write faster programs in less lines of code. It 30efficient and allow you to write faster programs in less lines of code. It
31should work with anything from perl-5.004_01 onwards, but I only support 31should work with anything from perl-5.004_01 onwards, but I only support
325.005+. 325.005+. UTF8 handling (the C<sql_u*> family of functions) will only be
33effective with perl version 5.006 and beyond.
33 34
34If the descriptions here seem terse or if you always wanted to know 35If the descriptions here seem terse or if you always wanted to know
35what PApp is then have a look at the PApp module which uses this module 36what PApp is then have a look at the PApp module which uses this module
36extensively but also provides you with a lot more gimmicks to play around 37extensively but also provides you with a lot more gimmicks to play around
37with to help you create cool applications ;) 38with to help you create cool applications ;)
… …
43use DBI (); 44use DBI ();
44 45
45BEGIN { 46BEGIN {
46 use base qw(Exporter DynaLoader); 47 use base qw(Exporter DynaLoader);
47 48
48 $VERSION = 0.122; 49 $VERSION = 0.123;
49 @EXPORT = qw( 50 @EXPORT = qw(
50 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec 51 sql_exec sql_fetch sql_fetchall sql_exists sql_insertid $sql_exec
52 sql_ufetch sql_ufetchall sql_uexists
51 ); 53 );
52 @EXPORT_OK = qw( 54 @EXPORT_OK = qw(
53 connect_cached 55 connect_cached
54 ); 56 );
55 57
… …
143 $dbcache{$id}; 145 $dbcache{$id};
144} 146}
145 147
146=item $sth = sql_exec [dbh,] [bind-vals...,] "sql-statement", [arguments...] 148=item $sth = sql_exec [dbh,] [bind-vals...,] "sql-statement", [arguments...]
147 149
150=item $sth = sql_uexec <see sql_exec>
151
148C<sql_exec> is the most important and most-used function in this module. 152C<sql_exec> is the most important and most-used function in this module.
149 153
150Runs the given sql command with the given parameters and returns the 154Runs the given sql command with the given parameters and returns the
151statement handle. The command and the statement handle will be cached 155statement handle. The command and the statement handle will be cached
152(with the database handle and the sql string as key), so prepare will be 156(with the database handle and the sql string as key), so prepare will be
… …
163The actual return value from the C<$sth->execute> call is stored in the 167The actual return value from the C<$sth->execute> call is stored in the
164package-global (and exported) variable C<$sql_exec>. 168package-global (and exported) variable C<$sql_exec>.
165 169
166If any error occurs C<sql_exec> will throw an exception. 170If any error occurs C<sql_exec> will throw an exception.
167 171
172C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
173utf8 before calling the C<execute> method.
174
168Examples: 175Examples:
169 176
170 # easy one 177 # easy one
171 my $st = sql_exec "select name, id from table where id = ?", $id; 178 my $st = sql_exec "select name, id from table where id = ?", $id;
172 while (my ($name, $id) = $st->fetchrow_array) { ... }; 179 while (my ($name, $id) = $st->fetchrow_array) { ... };
… …
181 sql_exec $dbh, "update file set name = ?", "oops.txt"; 188 sql_exec $dbh, "update file set name = ?", "oops.txt";
182 189
183 190
184=item sql_fetch <see sql_exec> 191=item sql_fetch <see sql_exec>
185 192
193=item sql_ufetch <see sql_uexec>
194
186Execute a sql-statement and fetch the first row of results. Depending on 195Execute an sql-statement and fetch the first row of results. Depending on
187the caller context the row will be returned as a list (array context), or 196the caller context the row will be returned as a list (array context), or
188just the first columns. In table form: 197just the first columns. In table form:
189 198
190 CONTEXT RESULT 199 CONTEXT RESULT
191 void () 200 void ()
… …
202 211
203 my($name, $amount) = sql_fetch "select ...", args... 212 my($name, $amount) = sql_fetch "select ...", args...
204 213
205... and it's still quite fast unless you fetch large amounts of data. 214... and it's still quite fast unless you fetch large amounts of data.
206 215
216C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
217utf8 and forces all result values to utf8.
218
207=item sql_fetchall <see sql_exec> 219=item sql_fetchall <see sql_exec>
220
221=item sql_ufetchall <see sql_uexec>
208 222
209Similarly to C<sql_fetch>, but all result rows will be fetched (this is 223Similarly to C<sql_fetch>, but all result rows will be fetched (this is
210of course inefficient for large results!). The context is ignored (only 224of course inefficient for large results!). The context is ignored (only
211list context makes sense), but the result still depends on the number of 225list context makes sense), but the result still depends on the number of
212columns in the result: 226columns in the result:
… …
224 238
225 for (sql_fetchall "select name, age, place from user") { 239 for (sql_fetchall "select name, age, place from user") {
226 my ($name, $age, $place) = @$_; 240 my ($name, $age, $place) = @$_;
227 } 241 }
228 242
243C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
244values to utf8 and forces all result values to utf8.
245
229=item sql_exists "<table> where ...", args... 246=item sql_exists "<table> where ...", args...
247
248=item sql_uexists <see sql_exists>
230 249
231Check wether the result of the sql-statement "select xxx from 250Check wether the result of the sql-statement "select xxx from
232$first_argument" would be empty or not (that is, imagine the string 251$first_argument" would be empty or not (that is, imagine the string
233"select * from" were prepended to your statement (it isn't)). Should work 252"select * from" were prepended to your statement (it isn't)). Should work
234with every database but can be quite slow, except on mysql, where this 253with every database but can be quite slow, except on mysql, where this
235should be quite fast. 254should be quite fast.
255
256C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to
257utf8.
236 258
237Examples: 259Examples:
238 260
239 print "user 7 exists!\n" 261 print "user 7 exists!\n"
240 if sql_exists "user where id = ?", 7; 262 if sql_exists "user where id = ?", 7;
… …
314 336
315package PApp::SQL::Database; 337package PApp::SQL::Database;
316 338
317=head2 THE DATABASE CLASS 339=head2 THE DATABASE CLASS
318 340
319Again (sigh) the problem of persistency. What do you do when you have to serialize on object 341Again (sigh) the problem of persistency. What do you do when you have
320that contains (or should contain) a database handle? Short answer: you don't. Long answer: 342to serialize on object that contains (or should contain) a database
343handle? Short answer: you don't. Long answer: you can embed the necessary
321you can embed the necessary information to recreate the dbh when needed. 344information to recreate the dbh when needed.
322 345
323The C<PApp::SQL::Database> class does that, in a relatively efficient 346The C<PApp::SQL::Database> class does that, in a relatively efficient
324fashion: the overhead is currently a single method call per access (you 347fashion: the overhead is currently a single method call per access (you
325can cache the real dbh if you want). 348can cache the real dbh if you want).
326 349

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines