Howdy bulrush! Could you be so kind and post complete, working code that demonstrates the problem? Yours not only doesn't compile (writeerr() is not included), it also doesn't do anything. Furthermore, it'd be nice if you could use proper <code> tags for your code, since that'll allow us to download code rather than having to copy and paste it.

Once we've got some code that demonstrates the issue you're experiencing, we'll be in a much better position to help you.

EDIT: to expand on the above, the following test script IS working for me:

#!/usr/bin/perl

use strict;
use warnings;
use feature qw/say/;
use DBD::SQLite;
use Data::Dumper;

our $dbfile = "test.db";

my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
    AutoCommit => 1,
    RaiseError => 1
});

# create table, if necessary
$dbh->do("CREATE TABLE IF NOT EXISTS data (skey TEXT PRIMARY KEY, sval
+ue TEXT);");

# insert data
$dbh->do("INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', 
+'Ge 1:1-more \&stuff\& here ');");

# fetch data
my $sth = $dbh->prepare("SELECT skey, svalue FROM data WHERE skey = 'S
+tuff';");
$sth->execute();

my $results = $sth->fetchall_hashref("skey");
say Dumper $results;
[download]

This correctly prints:

$VAR1 = {
          'Stuff' => {
                       'svalue' => 'Ge 1:1-more &stuff& here ',
                       'skey' => 'Stuff'
                     }
        };
[download]

Thanks. You are actually not using my code (because you escape the ampersands), but your answer does help me. Because my real questions were:
• Is there a problem with my connect() statement?
• And, what characters do I have to escape in the SQL statement and how do I escape them?

So, I will escape those ampersands. After searching Google and the SQLite docs, I could not find which characters to escape nor how to escape them. Do you know which I have to escape in the SQL statement?

Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)

You are actually not using my code (because you escape the ampersands),

It's a habit of mine; I tend to escape special characters in double-quoted strings. After all, you said you wanted to execute the following SQL statement:

INSERT INTO data (skey, svalue) VALUES ('Stuff', 'Ge 1:1-more &stuff& 
+here ');
[download]

without any interpolation possibly happening in Perl. However, in this case it's not actually necessary to do so: remove the backslashes from the INSERT OR REPLACE statement, and the script will still work. Don't be afraid to experiment; give it a try.

So, I will escape those ampersands. After searching Google and the SQLite docs, I could not find which characters to escape nor how to escape them. Do you know which I have to escape in the SQL statement?

It depends. On Perl's side, you will have to escape everything that Perl might otherwise interpret/interpolate. For the database side, take a look at SQLite's syntax diagrams, and also the reference page for expressions, which has this to say:

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.

Then again, the only thing that you'll have to escape on the DB is the single quote character itself. To do that, put in two single quotes. Replacing the INSERT OR REPLACE line in the above script with the following (note I also unescaped the ampersands here):

$dbh->do("INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', 
+'Ge 1:1-more &stuff& here ''');");
[download]

results in:

$VAR1 = {
          'Stuff' => {
                       'svalue' => 'Ge 1:1-more &stuff& here \'',
                       'skey' => 'Stuff'
                     }
        };
[download]

The backslash in the output is due to Data::Dumper, BTW, so don't let that confuse you.

All in all, I'd recommend two things:

1. Use single-quoted strings for your query strings so that Perl won't interpolate into them.
2. Use the the q// operator (see Quote and Quote like Operators) with a terminator other than ' so you won't have to escape single quotes.

For instance (linebreaks added for clarity), for statements that don't change:

$dbh->do(
    q/INSERT OR REPLACE INTO data (skey, svalue) VALUES ('Stuff', 'lit
+eral dollar sign: $, literal single quote: '', literal ampersand: &')
+;/
);
[download]

For statements that DO change, use ->prepare() and ->bind_param() instead:

my $sth = $dbh->prepare(
    'INSERT OR REPLACE INTO data (skey, svalue) VALUES (?, ?);'
);
$sth->bind_param(1, $skey);
$sth->bind_param(2, $svalue);
$sth->execute();
[download]

Don't assemble query strings in Perl using interpolation or string concatenation, this will lead to bugs and/or security risks. ("Little Bobby Tables" and all that.)

"For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended."

Yes, but also @rows=$sth->fetchrows_array also returns no records. Hence my puzzlement.
Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)

Method fetchrows_array does not exist. What where you expecting @rows to contain ?
Update: Maybe you meant fetchrow_array like this


#!perl
use strict;
use DBD::SQLite;
use Data::Dump 'pp';

my $dbfile = 'test.sqlite';
my $dbh = DBI->connect('dbi:SQLite:'.$dbfile , undef , undef , 
    {RaiseError =>1, AutoCommit =>1}) or die $DBI::errstr;

$dbh->do('CREATE TABLE IF NOT EXISTS data (
          skey TEXT PRIMARY KEY,
          svalue TEXT )');

$dbh->do("INSERT INTO data (skey, svalue) 
          VALUES ('Stuff','Ge 1:1-more &stuff&here ')");
       
my $sql = "SELECT svalue FROM data WHERE skey='Stuff'";       
my $sth = $dbh->prepare($sql);
$sth->execute();
my @row = $sth->fetchrow_array;                     
pp @row;
[download]

poj

Not an answer to your question, but if you are starting out with databases you may find Databases made easy helpful. See also the other offerings in the Database Programming section of the Tutorials.

Thank you. I understand databases just fine. But I assume different flavors of SQL escape different sets of characters, and sometimes do it differently. That was one of my questions.

Not all flavors of SQL adhere 100% to any given SQL spec.

Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)

What I meant was, over the years I find subtle differences in flavors of programming, string escapes, regex, and SQL, they are not all the same. So my assumption was what needs to be escaped in SQLite may not be the same as other flavors of SQL, and a Google search did not help me either.

I've used a few languages that claim to adhere to XYZ standard, but they don't do it 100%, so reading standards is usually a waste of my time. I look for specific documentation on the flavor of X I'm using at the moment.

Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)

sub prepex {
    my( $sql, @bindvalues ) = @_;
    my $row;
    eval {
       $row = $dbh->selectrow_arrayref( $sql, {}, @bindvalues );;
       1;
    } or do {
        writeerr( $@ );
        ...;
    };
    return $row->[0] unless wantarray;
    return @$row;
}
[download]

RaiseError tells you (via $@ )whether prepare or execute or fetch failed

If you want the number of rows selected, COUNT(), like

SELECT
    COUNT(*)
FROM
    data
WHERE
    skey='Stuff'
[download]

or using placeholders/bindvalues

prepex(
    q{
        SELECT
            COUNT(*)
        FROM
            data
        WHERE
            skey=? 
    },
    'Stuff',
);
[download]

see also this and DBI recipes

Also, if you find yourself often needing "both", the count and the data, DBIx::PreQL allows you to write your SQL in a templated fashion suitable for switching between the two forms.