DBD::SQLite will connect and CREATE, but not INSERT...sort of

EvanK has asked for the wisdom of the Perl Monks concerning the following question:

DISCLAIMER: in the interests of full disclosure, I am currently coding and testing on a Mac OS X 10.4.9 system (due to variables beyond my control).

I'm writing a small web app that is initially given a csv file (specifically a csv export from an excel file), and I want to convert it to a sqlite database using DBD::SQLite, but I'm running into a strange issue. I can create the connection (and the actual database file) without incident, and can even issue a table CREATE statement without a problem:

# "connect" to sqlite database file, creating it if it doesnt exist
my $dbh = DBI->connect("dbi:SQLite:dbname=testing.sqlite","","");

# build/execute a CREATE statement from array of column names with all
+ rows as type 'TEXT'
my $sql = sprintf 'CREATE TABLE rowdata ( %s )', join(' TEXT, ', @head
+ers, ' TEXT');
$status = $dbh->do($sql);

# return error status if CREATE resulted in error
if (!defined $status) {
  die "CREATE failed";
}
[download]

However, any INSERT statements I try to execute from the same script, on the same db handle, simply fail.

# build/execute an INSERT statement from array of field values
# note: all of these values are already DBI quoted
my $sql = sprintf 'INSERT INTO rowdata VALUES( %s )', join(',', @row);
$status = $dbh->do($sql);

# return error status if INSERT resulted in error
if (!defined $status) {
  die "INSERT failed: ", $dbh->errstr;
}
[download]

This gives me the following erorr:

INSERT failed: 
near ")": syntax error(1) at dbdimp.c line 271
[download]

At first, I assumed it was a permission issue of some kind, but it actually creates the database file (permissions of which are 755), and succeeds with the CREATE statement which also modifies the file. Additionally, I printed the built queries directly to the screen, then input them one at a time via an interactive debugger session, and this worked without any problems.

I'm thoroughly puzzled, could anyone know what's causing this?

__________
Systems development is like banging your head against a wall...
It's usually very painful, but if you're persistent, you'll get through it.

Comment on DBD::SQLite will connect and CREATE, but not INSERT...sort of Select or Download Code

Replies are listed 'Best First'.
Re: DBD::SQLite will connect and CREATE, but not INSERT...sort of by bart (Canon) on Apr 23, 2007 at 22:42 UTC
`my $sql = sprintf 'INSERT INTO rowdata VALUES( %s )', join(',', @row);` [download] ... This gives me the following error: `INSERT failed: near ")": syntax error(1) at dbdimp.c line 271` [download] That smells to me like your `@row` is empty, and thus, your SQL statement actually is: `INSERT INTO rowdata VALUES()` [download]	[reply] [d/l] [select]
Re: DBD::SQLite will connect and CREATE, but not INSERT...sort of by Fletch (Bishop) on Apr 23, 2007 at 20:15 UTC
Considering you're getting a syntax error and not using `$dbh->quote` to quote what you're inserting I'd wager the problem is that you're, well, not giving SQLite valid SQL to execute. Print out `$sql` before you try and execute it (which should always be the first step you take to diagnose misbehaving DBI problems (well, that and/or turning on tracing on the handle)).	[reply] [d/l] [select]
Re^2: DBD::SQLite will connect and CREATE, but not INSERT...sort of by EvanK (Chaplain) on Apr 23, 2007 at 21:14 UTC
I am using `$dbh->quote`, I just didnt include the code for it (I instead noted that it was being quoted)...Ironically, I grabbed the newest source tarball for said module and compiled it from scratch and curiously...it worked. I'll consider that my "shooting oneself in the foot" moment of the day, since that should have been the first damn thing i tried. Sorry, feel free to throw rocks now :o __________ Systems development is like banging your head against a wall... It's usually very painful, but if you're persistent, you'll get through it.	[reply] [d/l]
Re^3: DBD::SQLite will connect and CREATE, but not INSERT...sort of by wjw (Priest) on Apr 24, 2007 at 03:18 UTC
Don't you love it when that happens? I bet I shoot myself in the foot 3 times a day on a bad day... I keep shooting and I will be walking on stumps soon... . Glad it worked out. :-) ...the majority is always wrong, and always the last to know about it...	[reply]
Re: DBD::SQLite will connect and CREATE, but not INSERT...sort of by dragonchild (Archbishop) on Apr 23, 2007 at 20:11 UTC
This doesn't solve your problem, but if you're just using DBD::SQLite for a basic ORM and you don't plan on using DBIC or CDBI or something similar to mange the ORM bits, just use DBM::Deep. It even has transactions. :-) As for the specific issue, I remember having to do silly things when running DBM::Deep tests on OSX. Specifically, when calling tempfile(), I had to flock( $fh, LOCK_UN ) prior to returning it because tempfile() on OSX returns a flocked filehandle. This may be related, depending on how DBD::SQLite handles temp files. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]