CODEWITHSUNDEEP
perlsqlite
user466663
user466663

Reputation: 845

How to insert Perl variables into Sqlite3

I want to insert values into Sqlite3 table using Perl DBI. I was able to insert hard coded values without any problem. When I tried to use perl variables, then I get an error "DBD::SQLite::db do failed: no such column:"

This works:

$dbh->do("insert into Gene values (12, 'AAAAAA', 66, 86, 76)");

But this code

$dbh->do("INSERT INTO Gene values (NULL, $sequence, $siteNumber, $begin, $length)");

throws the error

DBD::SQLite::db do failed: no such column

Upvotes: 0

Views: 4618

Answers (2)

Borodin
Borodin

Reputation: 126722

You should always prepare and execute your SQL statements and use placeholders for variable values as a matter of course.

Try this code

my $sth = $dbh->prepare('INSERT INTO Gene VALUES (?, ?, ?, ?, ?)');
$sth->execute(undef, $sequence, $siteNumber, $begin, $length);

Upvotes: 8

Neil Slater
Neil Slater

Reputation: 27207

Your problem will have been at $sequence, which required quoting to be a valid SQL string literal in the insert statement. As a result it thought you were trying to refer to a column called AAAAAA, and not the string 'AAAAAA' that you intended. Perl's string interpolation doesn't add the quotes for you.

Placeholders and $dbh->prepapre are by far the most robust solution here though (for example, what to do if your string contains the quote character ' ? $dbh->prepare already has that coded correctly).

Upvotes: 1

Related Questions