CODEWITHSUNDEEP
perlsqlite
east.charm
east.charm

Reputation: 499

Why do I get a syntax error with DBD::SQLite even though my version of SQLite supports `CREATE TABLE IF NOT EXISTS`?

I am using SQLite v3 to write a Perl script. The sqlite version is 3.3.6.

When I run sqlite on the command line it works. But when I do the same thing in Perl it raises this error

DBD::SQLite::db do failed: near "NOT": syntax error(1) at dbdimp.c line 268 at file line 2675.

This is what I do on the console:

$ sqlite3 test.db
SQLite version 3.3.6
sqlite> create table if not exists  DATA_STATUS_VALUE (TYPE TEXT PRIMARY KEY, Seq INTEGER);
sqlite> .tables AllJobs            LOCKSTAT_VALUE     test_run12_data
DATA_STATUS_VALUE  STATUS_VALUE       test_run12_lock

The version of SQLite I'm using supports IF NOT EXISTS, so why am I getting an error?

This is my Perl code:

#!/usr/bin/perl

use DBI;
my $driver = "SQLite";

$database = "test.db";
$dsn      = "DBI:$driver:dbname=$database";
$dbh      = DBI->connect( $dsn, undef, undef, { RaiseError => 1 } );

$dbh->do("CREATE TABLE IF NOT EXISTS DATA_STATUS_VALUE (TYPE TEXT PRIMARY KEY, Seq INTEGER);");

Upvotes: 0

Views: 897

Answers (1)

ThisSuitIsBlackNot
ThisSuitIsBlackNot

Reputation: 24063

The version of SQLite I'm using supports IF NOT EXISTS, so why am I getting an error?

Because DBD::SQLite isn't using the version of SQLite you already had installed. DBD::SQLite comes bundled with its own version of SQLite; it will use the bundled version unless you tell it to use another version when you compile it.

You can find out the version of SQLite that DBD::SQLite is using by running:

perl -MDBD::SQLite -le'print $DBD::SQLite::sqlite_version'

Support for CREATE TABLE ... IF NOT EXISTS was added to SQLite in v3.3.0. You should upgrade DBD::SQLite, since the newest version (1.50) comes bundled with SQLite 3.10.2.

Upvotes: 9

Related Questions