Re: SQLite select wont' return records

[roboticus]

bulrush:

Based on your description, I'd guess that when you're running the script:

• you're not accessing the same SQLite database that you're using when you're using dbish, or
• your select criteria has an error causing nothing to be selected.

Your script is overly complicated, you repeat yourself a good bit, so you should perhaps try a simple experiment without all the extraneous stuff. Something like:

use warnings;
use strict;
use DBI;
use Data::Dumper;
my $histfn = >>>put a path here for your temporary/test database<<<
my $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn", { RaiseError=>1 })
+; 

$dbsql->do(q{create table foo (id int, msg text)});
$dbsql->do(q{insert into foo VALUES (1,'hello')});
my $ra = $dbsql->selectall_arrayref('select * from foo');
print Dumper($ra);

Run it, and see if it is able to run successfully and print records. If so, then make a copy of your program and comment out chunks of it until it's nearly as simple as this one, and get that working. Then uncomment bits here and there until it breaks again. That last bit you uncommented will have a flaw in it.

The way I comment chunks out is to insert pod comments like this:

use warnings;
use strict;

=h1 Does perl even work on this box?

use DBI;
use Data::Dumper;
my $histfn = >>>put a path here for your temporary/test database<<<
my $dbsql=DBI->connect("dbi:SQLite:dbname=$histfn", { RaiseError=>1 })
+; 

$dbsql->do(q{create table foo (id int, msg text)});
$dbsql->do(q{insert into foo VALUES (1,'hello')});
my $ra = $dbsql->selectall_arrayref('select * from foo');
print Dumper($ra);

=cut

print "hello, world!\n";

So as you uncomment, you simply move your =h1 and =cut lines accordingly.

Update: Made the correction reported by bulrush.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[bulrush]

Thanks. Well, your code works on my machine except you have to change your INSERT line to this:

$dbsql->do(q{insert into foo VALUES (1,'hello')});

And why don't I have to do a prepare? I know that $dbsql->selectall_arrayref() is a valid method but other examples did basically this:

$stsql= $dbsql->prepare($ssql);
$rvsql=$stsql->execute();

Which format is required in which situations?

[roboticus]

bulrush:

The $DBH->do(...) method automatically combines the prepare and execute into a single operation. It's quick and dirty and handy for quick tests and oddball operations that you do only once in your program (such as table creation). For actual data manipulations, I generally prefer to use prepare/execute, but since this was a quick test to verify your installation, I didn't worry about it.

So to answer your question: You can do it either way, there aren't requirements as such. BUT the prepare/execute format for data manipulation offers some advantages that easily outweigh the slightly larger coding effort: You can get a lot of safety by using placeholders, you can get better performance if your database compiles statements for reuse. Consider this:

print "Please enter the last name:\n";
my $user_data = <>;
chomp $user_data;

$DBH->do("select * from people where last_name='$user_data'");

So if someone enters a last name like:

Jones'; drop table people; --

Then your application won't work very well any more. So if you need to use data provided by a user (via input, a web application or whatever), you need to be careful that your data doesn't cause a serious problem. DBI covers you by offering placeholders: Rather than you having to worry about the quoting rules for the database code, you just use a ? and let DBI do the quoting for you:

print "Please enter the last name:\n";
my $user_data = <>;
chomp $user_data;

my $STH = $DBH->prepare("select * from people where last_name=?");
$STH->execute($user_data);

Now if the user enters that last name, you'll simply get no data (unless you happen to have such a strangely-named person in your database).

Now on to the performance aspects: when you run an SQL statement, the database can spend a significant amount of time determining out what it's going to do, which indexes it needs to reference and/or update. Some databases will precompute all that information for an SQL statement, and then use that same plan to execute multiple SQL statements. That can save a *lot* of time in some cases. For example, what if your application was reading last names from a file instead of prompting from the user:

print "Please enter the file name:\n";
my $user_data = <>;
open my $FH, '<', $user_data;
while (my $last_name = <$FH>) {
    chomp $last_name;
    my $STH = $DBH->prepare("select * from people where last_name=?");
    $STH->execute($last_name);
}

If you have a thousand names in your file, then it will figure out how to do that statement a thousand times, and then do it a thousand times. But by moving one statement, you can get a performance boost:

print "Please enter the file name:\n";
my $user_data = <>;
open my $FH, '<', $user_data;
my $STH = $DBH->prepare("select * from people where last_name=?");
while (my $last_name = <$FH>) {
    chomp $last_name;
    $STH->execute($last_name);
}

Now the database need only figure out how to do the statement once, and then just do it for a thousand times. So you get safety and a possible performance boost. Note: not all databases will give you a performance boost, but you still get the safety. But if you use placeholders, then if you switch to a database that does precompilation of statements, you'll get a free boost.

Since you don't create a thousand tables at once, and the database couldn't benefit from precompilation in that case anyway, using the do method is just fine.

Update: Fixed broken code tag.

...roboticus

When your only tool is a hammer, all problems look like your thumb.