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.30 by stefan, Wed Jan 28 19:50:38 2004 UTC vs.
Revision 1.34 by root, Mon Jan 9 06:10:40 2006 UTC

… …
44use DBI (); 44use DBI ();
45 45
46BEGIN { 46BEGIN {
47 use base qw(Exporter DynaLoader); 47 use base qw(Exporter DynaLoader);
48 48
49 $VERSION = 0.143; 49 $VERSION = '1.01';
50 @EXPORT = qw( 50 @EXPORT = qw(
51 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_uexec sql_ufetch sql_ufetchall sql_uexists 52 sql_uexec sql_ufetch sql_ufetchall sql_uexists
53 ); 53 );
54 @EXPORT_OK = qw( 54 @EXPORT_OK = qw(
… …
62our $DBH; # the default database handle 62our $DBH; # the default database handle
63our $Database; # the current SQL::Database object, if applicable 63our $Database; # the current SQL::Database object, if applicable
64 64
65our %dbcache; 65our %dbcache;
66 66
67=head2 GLOBAL VARIABLES 67=head2 Global Variables
68 68
69=over 4 69=over 4
70 70
71=item $sql_exec 71=item $sql_exec
72 72
… …
88be nice as a placeholder for the database object that corresponds to 88be nice as a placeholder for the database object that corresponds to
89$PApp::SQL::DBH. 89$PApp::SQL::DBH.
90 90
91=back 91=back
92 92
93=head2 FUNCTIONS 93=head2 Functions
94 94
95=over 4 95=over 4
96 96
97=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect 97=item $dbh = connect_cached $id, $dsn, $user, $pass, $flags, $connect
98 98
… …
186package-global (and exported) variable C<$sql_exec>. 186package-global (and exported) variable C<$sql_exec>.
187 187
188If any error occurs C<sql_exec> will throw an exception. 188If any error occurs C<sql_exec> will throw an exception.
189 189
190C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to 190C<sql_uexec> is similar to C<sql_exec> but upgrades all input arguments to
191utf8 before calling the C<execute> method. 191UTF-8 before calling the C<execute> method.
192 192
193Examples: 193Examples:
194 194
195 # easy one 195 # easy one
196 my $st = sql_exec "select name, id from table where id = ?", $id; 196 my $st = sql_exec "select name, id from table where id = ?", $id;
… …
230 my($name, $amount) = sql_fetch "select ...", args... 230 my($name, $amount) = sql_fetch "select ...", args...
231 231
232... and it's still quite fast unless you fetch large amounts of data. 232... and it's still quite fast unless you fetch large amounts of data.
233 233
234C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to 234C<sql_ufetch> is similar to C<sql_fetch> but upgrades all input values to
235utf8 and forces all result values to utf8 (this does I<not> include result 235UTF-8 and forces all result values to UTF-8 (this does I<not> include result
236parameters, only return values. Using bind variables in cinjunction with 236parameters, only return values. Using bind variables in conjunction with
237sql_u* functions results in undefined behaviour - we use utf8_on on 237sql_u* functions might result in undefined behaviour - we use UTF-8 on
238bind-variables and it seems to work on DBD::mysql which just means 238bind-variables at execution time and it seems to work on DBD::mysql as it
239that that DBD-driver is broken). 239ignores the UTF-8 bit completely. Which just means that that DBD-driver is
240broken).
240 241
241=item sql_fetchall <see sql_exec> 242=item sql_fetchall <see sql_exec>
242 243
243=item sql_ufetchall <see sql_uexec> 244=item sql_ufetchall <see sql_uexec>
244 245
… …
261 for (sql_fetchall "select name, age, place from user") { 262 for (sql_fetchall "select name, age, place from user") {
262 my ($name, $age, $place) = @$_; 263 my ($name, $age, $place) = @$_;
263 } 264 }
264 265
265C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input 266C<sql_ufetchall> is similar to C<sql_fetchall> but upgrades all input
266values to utf8 and forces all result values to utf8 (see the caveats in 267values to UTF-8 and forces all result values to UTF-8 (see the caveats in
267the description of C<sql_ufetch>, though). 268the description of C<sql_ufetch>, though).
268 269
269=item sql_exists "<table_references> where <where_condition>...", args... 270=item sql_exists "<table_references> where <where_condition>...", args...
270 271
271=item sql_uexists <see sql_exists> 272=item sql_uexists <see sql_exists>
… …
275"select * from" were prepended to your statement (it isn't)). Should work 276"select * from" were prepended to your statement (it isn't)). Should work
276with every database but can be quite slow, except on mysql, where this 277with every database but can be quite slow, except on mysql, where this
277should be quite fast. 278should be quite fast.
278 279
279C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to 280C<sql_uexists> is similar to C<sql_exists> but upgrades all parameters to
280utf8. 281UTF-8.
281 282
282Examples: 283Examples:
283 284
284 print "user 7 exists!\n" 285 print "user 7 exists!\n"
285 if sql_exists "user where id = ?", 7; 286 if sql_exists "user where id = ?", 7;
… …
298 299
299 mysql: first C<AUTO_INCREMENT> column set to NULL 300 mysql: first C<AUTO_INCREMENT> column set to NULL
300 postgres: C<oid> column (is there a way to get the last SERIAL?) 301 postgres: C<oid> column (is there a way to get the last SERIAL?)
301 sybase: C<IDENTITY> column of the last insert (slow) 302 sybase: C<IDENTITY> column of the last insert (slow)
302 informix: C<SERIAL> or C<SERIAL8> column of the last insert 303 informix: C<SERIAL> or C<SERIAL8> column of the last insert
304 sqlite: C<last_insert_rowid()>
303 305
304Except for sybase, this does not require a server access. 306Except for sybase, this does not require a server access.
305 307
306=cut 308=cut
307 309
… …
312 314
313 $driver eq "mysql" and return $sth->{mysql_insertid}; 315 $driver eq "mysql" and return $sth->{mysql_insertid};
314 $driver eq "Pg" and return $sth->{pg_oid_status}; 316 $driver eq "Pg" and return $sth->{pg_oid_status};
315 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY'); 317 $driver eq "Sybase" and return sql_fetch($dbh, 'SELECT @@IDENTITY');
316 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1]; 318 $driver eq "Informix" and return $sth->{ix_sqlerrd}[1];
319 $driver eq "SQLite" and return sql_fetch($dbh, 'SELECT last_insert_rowid ()');
317 320
318 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid"; 321 die "sql_insertid does not spport the dbd driver '$driver', please see PApp::SQL::sql_insertid";
319} 322}
320 323
321=item [old-size] = cachesize [new-size] 324=item [old-size] = cachesize [new-size]
… …
357 360
358reinitialize; 361reinitialize;
359 362
360package PApp::SQL::Database; 363package PApp::SQL::Database;
361 364
362=head2 THE DATABASE CLASS 365=head2 The Database Class
363 366
364Again (sigh) the problem of persistency. What do you do when you have 367Again (sigh) the problem of persistency. What do you do when you have
365to serialize on object that contains (or should contain) a database 368to serialize on object that contains (or should contain) a database
366handle? Short answer: you don't. Long answer: you can embed the necessary 369handle? Short answer: you don't. Long answer: you can embed the necessary
367information to recreate the dbh when needed. 370information to recreate the dbh when needed.
… …
449 452
450L<PApp>. 453L<PApp>.
451 454
452=head1 AUTHOR 455=head1 AUTHOR
453 456
454 Marc Lehmann <pcg@goof.com> 457 Marc Lehmann <schmorp@schmorp.de>
455 http://www.goof.com/pcg/marc/ 458 http://home.schmorp.de/
456 459
457=cut 460=cut
458 461

Diff Legend

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