Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

DBI - get info from statement handles

by shemp (Deacon)
on Sep 29, 2004 at 19:03 UTC ( #395104=perlquestion: print w/replies, xml ) Need Help??

shemp has asked for the wisdom of the Perl Monks concerning the following question:

I want to write a little wrapper function to the prepare() and execute() part of querying a database. For instance, here's a relatively simple version:
sub safe_query_execute { my ($db_handle, $query) = @_; my $query_handle = $db_handle->prepare($query) or confess "Error preparing query: $query\n" . "Error: $DBI::errstr\n"; eval { $query_handle->execute(); }; if ( $@ ) { confess "QUERY ERROR: $DBI::errstr\n" . "QUERY: $query\n"; } return $query_handle; }
Pretty basic, given a db handle and a query, prepare()'s and execute()'s it, returning the executed handle, doing error checking along the way.

Yes, i'm aware that i could accomplish this other ways, but I dont necessarily want to execute all queries this way in a script, some queries will want some of their own specialized error checking, like recovering from the error, etc. I wrote this because I write similar code often in my scripts, so i wrapped it.

Note, i used or confess on the prepare(), and eval{} on the execute() - opinions on that could be another thread.

Anyhow, i want to write a nice (similar) wrapper for queries with input bind params. The problem im running into is getting the query for error statements. I'd like the function to look something like:

sub bind_param_safe_query_execute { my ($query_handle, @params) = @_; eval { $query_handle->execute(@params); }; if ( $@ ) { confess "QUERY ERROR: $DBI::errstr\n" # somehow print the query } }
I dont want to have to pass in the query, sometimes they're created on the fly, and are out of scope when the handles are executed, but i cant find any way to get the query out of the handle itself. Is this possible?

Any other approaches to accomplish this same idea?

Note: someday i'll perhaps add stuff for giving SQL_TYPES to the bind params, perhaps with a Tie::IxHash instead of an array. Perhaps other options eventually too.

Replies are listed 'Best First'.
Re: DBI - get info from statemet handles
by blokhead (Monsignor) on Sep 29, 2004 at 19:34 UTC
    Did you check the pod? Under Statement Handle Attributes:
    "Statement" (string, read-only) Returns the statement string passed to the "prepare" method +.
    So $query_handle->{Statement} is what you want.


Re: DBI - get info from statement handles (ShowErrorStatement)
by htoug (Deacon) on Sep 30, 2004 at 05:28 UTC
    Unless you turn off PrintError on your $db_handle the DBI will print the errormessage whenever an error occurs.
    Adding $db_handle->{ShowErrorStatement} = 1 will add the SQL statement to the error message (with bound paramters added for readability in newer DBIs and DBDs).

    There is no need to eval the executes, they won't die unless you have RaiseError set on the db-handle. So it just won't work as you expect. Any errors on the execute phase will go undetected.

    My recommendation is to use RaiseError, and add a HandleError for cases when a simple die is not good enough.

    janitored by ybiC: Retitle from "ShowErrorStatement" because onewordnodetitles hinder site search

Re: DBI - get info from statemet handles
by ezra (Scribe) on Sep 29, 2004 at 19:39 UTC
    $query_handle->{'Statement'} should do it

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://395104]
Approved by blokhead
Front-paged by htoug
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (5)
As of 2021-01-21 05:43 GMT
Find Nodes?
    Voting Booth?