www.developer.com/lang/article.php/2184681
April 10, 2003 Utility Methods and FunctionsTo round off our basic introduction to DBI, we'll tell you about some useful utility methods and functions that will make your life that little bit easier. These include the very useful quote escaping method, DBI execution tracing, and various functions to tidy up your data. Database-Specific Quote HandlingBy far the most important utility method is To confuse matters, database engines tend to have a different format for specifying these surrounding quotation marks. DBI circumvents this problem by declaring the This method, when executed against a database handle, converts the string given as an argument according to defined rules, and returns the correctly escaped string for use against the database. For example:
#!/usr/bin/perl -w
#
# ch04/util/quote1: Demonstrates the use of the
# $dbh->quote() method
use DBI;
### The string to quote
my $string = "Don't view in monochrome (it looks 'fuzzy')!";
### Connect to the database
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" , {
RaiseError => 1
} );
### Escape the string quotes ...
my $quotedString = $dbh->quote( $string );
### Use quoted string as a string literal in a SQL statement
my $sth = $dbh->prepare( "
SELECT *
FROM media
WHERE description = $quotedString
" );
$sth->execute();
exit;
For example, if you quoted the Perl string of Some databases require a more complex As a special case, if the argument is Tracing DBI ExecutionDBI sports an extremely useful ability to generate runtime tracing information of what it's doing, which can be a huge time-saver when trying to track down strange problems in your DBI programs. At the highest level, you can call the
The
#!/usr/bin/perl -w
#
# ch04/util/trace1: Demonstrates the use of DBI tracing.
use DBI;
### Remove any old trace files
unlink 'dbitrace.log' if -e 'dbitrace.log';
### Connect to a database
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" );
### Set the tracing level to 1 and prepare()
DBI->trace( 1 );
doPrepare();
### Set trace output to a file at level 2 and prepare()
DBI->trace( 2, 'dbitrace.log' );
doPrepare();
### Set the trace output back to STDERR at level 2 and prepare()
DBI->trace( 2, undef );
doPrepare();
exit;
### prepare a statement (invalid to demonstrate tracing)
sub doPrepare {
print "Preparing and executing statement\n";
my $sth = $dbh->prepare( "
SELECT * FROM megalith
" );
$sth->execute();
return;
}
exit;
This program generates quite a bit of trace information, of which we'll show just a small fragment:
-> prepare for DBD::Oracle::db (DBI::db=HASH(0xcd45c)~0xcd4a4 '
SELECT * FROM megalith
') thr0
<- prepare= DBI::st=HASH(0xcd648) at trace1 line 30.
-> execute for DBD::Oracle::st
(DBI::st=HASH(0xcd648)~0x16afec) thr0
dbd_st_execute SELECT (out0, lob0)...
!! ERROR: 942 'ORA-00942: table or view does not exist
(DBD ERROR: OCIStmtExecute)'
<- execute= undef at trace1 line 33.
DBD::Oracle::st execute failed: ORA-00942: table or view does
not exist (DBD ERROR: OCIStmtExecute) at trace1 line 33.
This trace information was generated with a setting of level 2, and shows the operations that DBI undertook when trying to prepare and execute a statement. Lines prepended with You can see the The trace information generated at level 1 is similar. The main difference is that the method entry lines ( The one drawback to this form of tracing is that if your program uses a lot of handles, then the volume of tracing information could be quite vast. Similarly, you might have tracked your problem down to a specific database operation that you'd like to trace individually. The
### Connect to a database...
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" );
### Trace the database handle to level 1 to the screen
$dbh->trace( 1 );
### Create a new statement
my $sth = ...;
### Trace the statement to level 2 to the file 'trace.lis'
$sth->trace( 2, 'trace.lis' );
Note that if a filename is specified when calling If your programs are exhibiting odd behavior or are generating errors on a regular basis, you should consider using the built-in tracing features of DBI to help you resolve your problems. This tool is extremely useful, as you will be able to see exactly what data is being passed to the database, allowing you to ensure that it's in the correct format. Finally, tracing can also be controlled via the use of an environment variable called
If the trace level isn't specified in the |
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs 