This morning I was tinkering further with Hash lookups, Database lookups, and Scalability, trying to squeeze a little better performance out of the database query. In the CB, jZed mentioned that for my application, selectrow_array() might be an improvement over fetchrow_array(). I hadn't considered selectrow_array() because I hadn't noticed in the docs for DBI that you can pass it a statement handle to a pre-prepared SQL query. Perfect, that should be just the ticket. But when I made the change, I got a pretty little segfault.

I'm posting this question to see if anyone can shed any light on why the switch to selectrow_array() generated a segfault.

I am using DBI, DBD::SQLite version 1.07 (which is based on SQLite version 3.x), on a Win32 machine with ActiveState Perl 5.8.4. My DBI version is 1.45 (the latest). With respect to windows machines, the problem has been confirmed on both Win98 and WinXP. However, while collaborating in the CB with jZed and Corion, we discovered that the problem seems to exist cross-platform, including on Linux. jZed also discovered that the problem doesn't occur with DBD::SQLite2, which is based on the older SQLite 2.x engine.

Corion managed to determine that the problem occurs when placeholders are used, not when WHERE clause values are placed directly in the prepare() for the SQL statement.

The following is a snippet that Corion wrangled up, boiling the problem down to a simple test case. It is based loosely on my benchmarking code which originally produced the segfault. The failure appears to occur upon the second call to selectrow_array(), not the first call. Have a look at the code and see if any of you can cast additional light on the problem. I think it's a problem with the SQLite 3.x engine, but I'm not sure. 

use strict;
use warnings;
use Test::More tests => 4;
use DBI;

my $database = 'allwords.sqlite-v3';
unlink $database;

my( %forward, %backward );
my $dbh = DBI->connect( "DBI:SQLite:$database",
                        '', '',
                        { RaiseError => 1,
                          AutoCommit => 0 }
);

diag "Using DBD::SQLite $DBD::SQLite::VERSION";

diag "Creating database\n";
my @words = split /\s+/, <DATA>;
@forward{ @words } = reverse @words;

diag "Creating word-pair database...\n";
$dbh->do(
            "CREATE TABLE wordtable (
                  Left  varchar,
                  Right varchar
             ) "
);
my $sth = $dbh->prepare(
            "INSERT INTO wordtable
             ( Left, Right ) VALUES ( ?, ? )"
);
while ( my( $left, $right ) = each %forward ) {
            $sth->execute( $left, $right );
}
$sth->finish;
$dbh->commit;
diag "Database created.\n";

diag "Selecting (static value, 1st)\n";
my $value;
$sth = $dbh->prepare( "SELECT Right FROM wordtable WHERE Left = 'shine
+'" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
            );
};
is( $value, 'shine', 'Got "shine"');

diag "Selecting (static value, 2nd)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
         );
};
is( $value, 'shine', "Got 'shine' and didn't crash");

diag "Selecting (placeholder, 1st round)\n";

$sth = $dbh->prepare( "SELECT Right FROM wordtable WHERE Left = ?" );

undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                   {},
                                   'shine'
            );
};
diag $@ if $@;
is( $value, 'shine', 'Got "shine"');

# This one crashes
diag "Selecting (placeholder, 2nd round)\n";
undef $value;
eval {
  $value = $dbh->selectrow_array( $sth,
                                {},
                                   'shine'
         );
};
diag $@ if $@;
is( $value, 'shine', "Got 'shine' and didn't crash");

$sth->finish();
$dbh->disconnect();
__DATA__
shine    shine

[download]

Curiosity abounds..........

Update: selectrow_arrayref() and selectall_arrayref() are also both similarly contaminated.


Dave

In reply to New way to segfault: selectrow_array() with placeholders, DBD::SQLite by davido

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.