R – Why would the rollback method not be available for a DBI handle

dbimoduleperlrollback

For some reason I am having troubles with a DBI handle. Basically what happened was that I made a special connect function in a perl module and switched from doing:

do 'foo.pl'

use Foo;

and then I do

$dbh = Foo->connect;

And now for some reason I keep getting the error:

Can't locate object method "rollback" via package "Foo" at ../Foo.pm line 171.

So the weird thing is that $dbh is definitely not a Foo, it's just defined in foo. Anyway, I haven't had any troubles with it up until now. Any ideas what's up?

Edit: @Axeman: connect did not exist in the original. Before we just had a string that we used like this:

do 'foo.pl';
$dbh = DBI->connect($DBConnectString);

and so connect is something like this

sub connect {
    my $dbh = DBI->connect('blah');
    return $dbh;
}

Best Answer

From perlfunc:

        do 'stat.pl';

    is just like

        eval `cat stat.pl`;

So when you do 'foo.pl', you execute the code in the current context. Because I don't know what goes on in foo.pl or Foo.pm, I can't tell you what's changed. But, I can tell you that it's always executed in the current context, and now in executes in Foo:: namespace.

The way you're calling this, you are passing 'Foo' as the first parameter to Foo::connect or the returned sub from Foo->can('connect'). It seems that somehow that's being passed to some code that thinks it's a database handle, and that's telling that object to rollback.

Related Solutions

Sql – Protecting against SQL tablename injection – how far is too far

use quote_identifier:

my $sql = "SELECT * FROM ".
  $dbh->quote_identifier($cfg->{tablename}).
  " WHERE foo = ?";

Sql – get the table names from an SQL query with Perl’s DBI

Probably not. Your SQL generation code takes the wrong approach. You are hiding too much information from your program. At some point, your program knows which table to select from. Instead of throwing that information away and embedding it inside an opaque SQL command, you should keep it around. Then your logger function doesn't have to guess where the log data should go; it knows.

Maybe this is clearer with some code. Your code looks like:

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return "SELECT $columns FROM $table WHERE $conditions";
}

sub run_query {
    my ($query) = @_;
    $dbh->prepare($query);
    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

This doesn't let you do what you want to do. So you should structure your program to do something like:

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return +{
        query => "SELECT $columns FROM $table WHERE $conditions",
        table => $table,
    } # an object might not be a bad idea
}

sub run_query {
    my ($query) = @_;

    $dbh->prepare($query->{query});
    log_to_file( $query->{table}.'.log', ... );

    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

The API is the same, but now you have the information you need to log the way you want.

Also, consider SQL::Abstract for dynamic SQL generation. My code above is just an example.

Edit: OK, so you say you're using SQLite. It has an EXPLAIN command which you could parse the output of:

sqlite> explain select * from test;
0|Trace|0|0|0|explain select * from test;|00|
1|Goto|0|11|0||00|
2|SetNumColumns|0|2|0||00|
3|OpenRead|0|2|0||00|
4|Rewind|0|9|0||00|
5|Column|0|0|1||00|
6|Column|0|1|2||00|
7|ResultRow|1|2|0||00|
8|Next|0|5|0||00|
9|Close|0|0|0||00|
10|Halt|0|0|0||00|
11|Transaction|0|0|0||00|
12|VerifyCookie|0|1|0||00|
13|TableLock|0|2|0|test|00|
14|Goto|0|2|0||00|

Looks like TableLock is what you would want to look for. YMMV, this is a bad idea.

Best Answer

Related Solutions

Sql – Protecting against SQL tablename injection – how far is too far

Sql – get the table names from an SQL query with Perl’s DBI

Related Topic