Beefy Boxes and Bandwidth Generously Provided by pair Networks BBQ
Welcome to the Monastery
 
PerlMonks  

SQLite handling of errors through common function

by techman2006 (Sexton)
on Nov 06, 2013 at 12:06 UTC ( #1061416=perlquestion: print w/ replies, xml ) Need Help??
techman2006 has asked for the wisdom of the Perl Monks concerning the following question:

I was trying to put a custom handler for the error being raised by SQLite. Below is the snip of the sub routines which I put down.

sub handle_error { my $message = shift; my $database = shift; if ( its a connection error ) { log an error and exit else log an error and continue } sub initDB { my $dbh = shift; my $database = shift; my $driver = "SQLite"; my $dsn = "DBI:$driver:dbname=$$database"; my $userID = ""; my $password = ""; $$dbh = DBI->connect($dsn, $userID, $password, {RaiseError => 1, PrintError => 0, HandleError => \&handle_error, AutoCommit => 0 } ) or handle_error(DBI->errstr, $$database); $$dbh->do("PRAGMA synchronous=OFF"); }

Now is there a way I can identify its an connection error or some other error e.g. say insert operation fails. As I want to use a generic function which take cares of logging the errors based on the error.

Comment on SQLite handling of errors through common function
Download Code
Re: SQLite handling of errors through common function
by sundialsvc4 (Monsignor) on Nov 06, 2013 at 12:26 UTC

    Just my opinion here, but I generally find that approach to be quite inconvenient ... as inconvenient as having to check the return-code from “every frippin’ function-call” when writing in classic “C.”   Instead, I use the raiseError attribute to cause an exception to be thrown.   And, I routinely use sugar like Try::Tiny and Exception::Class to make it that much more “familiar.”

    So, the logic now becomes much easier to manage:

    begin transaction ... try: do things do more things commit transaction except: rollback transaction scream and shout

    Now, you are no longer constantly checking-for errors.   You just go about your business, executing as many statements as you need to do the work, knowing that if something goes wrong, DBI will raise the alarm.   Much nicer.   (And generally more robust, since some “things that can go wrong” raise runtime errors in their low-level code anyway.)

    Within the error-handling block, yes, it now makes good sense to standardize how you choose to respond to these errors, by writing a common error-handling subroutine or subroutines ... which can be passed a statement-handle, or connection-handle.   (Often what I do is to gather up the information from DBI, then use Exception::Class to throw another, named, exception object of a particular class that my higher-level routines within the application-at-large know about.

      I downvoted this because the OP already uses RaiseError. The answer does not fit the question.

        So sue me for overlooking that.   O_o ...   (Not that I give a damn about “XP” anyway, but let’s just stay on-topic here.)   Your eagle-eyed observation does raise a different point:   my understanding is that the presence of this option will prevent the execution of the existing handler-routine in the OP’s example, because (IIRC) it will throw an exception in case of any error.   The DBI-call won’t return a non-zero value ... it won’t return anything at all.   Instead, the code will die, and I see no error-handling logic (not even a basic eval{}) in it.   Which would definitely be a bug of a different sort.

Re: SQLite handling of errors through common function
by daxim (Chaplain) on Nov 06, 2013 at 12:35 UTC
    You cannot easily identify the type of error because DBI uses strings, not objects, to represent an error. You have to parse the messages to differentiate, unfortunately.

    edit: You're also looking for a logger. Log::Any is nice.

      So we don't have clear way as we get in other language like C where we can check if the object is NULL then it seems to be a connection error. Similarly for other operations.

Re: SQLite handling of errors through common function
by mje (Deacon) on Nov 06, 2013 at 17:16 UTC

    The first thing I'd point out is that if your connect worked but an error occurred later the arguments to the HandleError sub are 1. error string 2. the db handle being used when the fail occurred and 3. the first value returned by the failing method. Your HandleError sub seems to assume it is going to get a message/error string and a database name which it is ONLY going to get IF you call it like on line 23 of your example and not IF DBI calls it.

    The second thing to point out is that if you have not connected then there is no connection handle passed to HandleError so the 2nd argument will be a DBI::dr, instead. e.g.,

    perl -MDBI -le 'use Data::Dumper; sub he {print Dumper(\@_);};my $h = +DBI->connect("dbi:ODBC:does_not_exist", undef, undef, {HandleError => + \&he});' $VAR1 = [ 'DBI connect(\'does_not_exist\',\'\',...) failed: [unixODBC] +[Driver Manager]Data source name not found, and no default driver spe +cified (SQL-IM002)', bless( {}, 'DBI::dr' ), undef ];

    Lastly, you are calling your own handle_error if connect fails so at that point you already know the connect failed. Your lines 18 to 23 will end in handle_error being called twice if connect fails.

      One more quick thing is there a way to know DBI handler is valid. Say I do a connect call and it went fine. Now at some place in my code I need to perform the operation but before that I want to validate that #dbh is valid. Can we do that?

      Also can we have a way to create an schema which comprises of tables like we do in say MySQL etc. I mean to say we have a schema called master which contains tables like tbl1, tbl2 etc.

        I presume you really mean is the DBI handle I obtained from connect still actively connected to my database. If that is the case see the DBI ping method. As for the second part of your post I've no idea what you are asking. Of course you can create tables in a database.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1061416]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (10)
As of 2014-04-18 07:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (463 votes), past polls