Trap and display the database error messages?

[kkeyan]

Hi all,

I will be interested to trap the error messages as regards the database to be displayed through the web-page instead of looking into the error_log.

How can I go abt doing this? Can someone help me out?

Thanks in advance.
kkeyan

[kkeyan]

Will trace solve me the problem.
Do I have to say,
$handle->trace($trace_level). I went about documentation which says 2 will display the output to STDOUT.

I will try out this and come back.

[nicc777]

In Perl, you can do the following:

$sql = "SELECT * FROM user";
$sth = $dbh->prepare( $sql );
$rv = $sth->execute();

Notes :

1) I assume by this that you have used the $dbh variable to initiate the connection.

2) The return value ( nr. of rows )will be in $rv, and based on it you can execute various other bits of code.

3) When an error occurs, the script will most likely die, with the error printed on STDERR. You can redirect this to STDOUT ( on Unix ) with:

$ script.pl 2> /dev/stdout

4) There are other ways of achieving the same results - Google should help you there.

5) Finally, if you want to trap errors in your Perl script, try the following code:

#!/usr/bin/perl


use DBI;

$dbh = DBI->connect( "dbi:mysql:mysql", "root", "" ) or die("DB ERROR : $!");
$sql = "SELECT * FROM User";
$sth = $dbh->prepare( $sql );
$rv = $sth->execute() or debug( $dbh->errstr );

print "RV=$rv\n";

exit;

sub debug {

	my $msg = $_[0];
	print "An error has occured: $msg\n";

}

You should get the following output:

$ perl -w dbtest.pl 2> /dev/null
An error has occured: Table 'mysql.User' doesn't exist
RV=

If you correct the code, and replace table 'User' with table 'user', you will get the following output:

$ perl -w dbtest.pl
RV=5

Hope that helps.

Cheers.

[kkeyan]

Nico,

You have given an more general way of writing a subroutine to print the error.
But I am concerned with straightway trapping of the error messages posted when the dbh or sth or rv is executed.

Regards
Karthikeyan

[nicc777]

The error will only be created once we connect with $dbh, or when we execute the prepared statement with $rv so that is why I call the debug sub from that. You should not get a SQL error on the prepare, unless there's a Perl syntax error. The only modification I would do with the sample I posted, is to add the 'or debug' part to the actual connecting as well. Of course, if you want to omit the output of the error, or just log it and carry on, you could try something like the following:

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect( "dbi:mysql:mysql", "root", "", { PrintError => 1, } ) or debug("DB CONNECTION ERROR");
if ( $dbh ) {

	$sql = "SELECT * FROM user";
	$sth = $dbh->prepare( $sql );
	$rv = $sth->execute() or debug( $dbh->errstr );
	
	if ( $rv ) {
	
		print "RV=$rv\n";
		
	} else {
	
		debug( "The SQL statement failed to execute" );
	
	}

} else {

	debug( "A connection error prevents the statement from being executed." );

}

exit;

sub debug {

	my $msg = $_[0];
	print "An error has occured: $msg\n";

}

Now, the above should work with the output being:

$ perl -w dbtest.pl 2> /dev/null
RV=5

To emulate a connection error, change the 'mysql' database name to 'mysqll'. This will produce te following output:

$ perl -w dbtest.pl 2> /dev/null
An error has occured: DB CONNECTION ERROR
An error has occured: A connection error prevents the statement from being executed.

Now, correct the database name, and change the table name from 'user' to 'User':

$ perl -w dbtest.pl 2> /dev/null
An error has occured: Table 'mysql.User' doesn't exist
An error has occured: The SQL statement failed to execute

Now you have complete control over every aspect of 'where' the error occurs, and you can generate code depending on where the code fails.

Hope this was better

Cheers