DBI - get info from statement handles

[shemp]

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.

[blokhead]

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.

[htoug]

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

[ezra]

$query_handle->{'Statement'} should do it