SQL INSERTs don't work

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

Hi there!
I've been trying to use DBI with DBD::SQLite to handle some databases in a program I'm writing. I have created a little 'setup script' that creates appropriate tables and inserts some initial entries.
The tables are created with no problems. When performing simple INSERTs, no errors are raised, but dumping the results of a simple "SELECT * FROM table_name" gives nothing, actually stopping the program (That's how it seems).

For example, let's say I'm doing this (Using an example from CPAN):

my $q = new CGI;
print $q->header, $q->start_html(-title=>'Test');
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","", { RaiseError
+ => 1 });
for my $sql( split /;\n+/,"
    CREATE TABLE user ( user_name TEXT, phone TEXT );
    INSERT INTO user VALUES ('Fred Bloggs','233-7777');
    INSERT INTO user VALUES ('Sanjay Patel','777-3333');
    INSERT INTO user VALUES ('Junk','xxx-xxxx');
    DELETE FROM user WHERE user_name = 'Junk';
    UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel
+';
    SELECT * FROM user
"){
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    $sth->dump_results() if $sth->{NUM_OF_FIELDS};
}
$dbh->disconnect;
print $q->end_html;
[download]

The output here will utterly be nothing. The only thing that will be printed is the output from $q->start_html. No output is given from $q->end_html.
Executing this script again will die complainning that the 'user' table already exists.

Any thoughts?
Ido.

-------------------------
Live fat, die young

Comment on SQL INSERTs don't work Select or Download Code

Replies are listed 'Best First'.
Re: SQL INSERTs don't work by davorg (Chancellor) on Nov 08, 2004 at 13:44 UTC
Having added the "use DBI" and "use CGI" lines and given a value to $dbfile it all seems to work here when I test it from the command line. Which makes me think that it might well be a file permissions problem. Does the user that you are running the program as have permissions to create the SQLite file? -- <http://www.dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re^2: SQL INSERTs don't work by tinita (Parson) on Nov 08, 2004 at 14:26 UTC
Does the user that you are running the program as have permissions to create the SQLite file? note: permissions to write to the directory where the database file is, are also needed with SQLite. maybe the sqlite file is already there with the right permissions but directory permissions aren't there so that inserts fail...	[reply]
Re: SQL INSERTs don't work by reneeb (Chaplain) on Nov 08, 2004 at 13:45 UTC
You do not test, whether the connection to the database fails or not. Test it: `my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","", { RaiseError + => 1 }) or die $DBI::errstr;` [download]	[reply] [d/l]
Re^2: SQL INSERTs don't work by thor (Priest) on Nov 08, 2004 at 13:48 UTC
Pretty sure that RaiseError also applies to the connect. That is to say that if you specify RaiseError and the connect fails, the script will die. Someone can (and will) correct me if I'm wrong... thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply]
Re^3: SQL INSERTs don't work by dragonchild (Archbishop) on Nov 08, 2004 at 14:14 UTC
RaiseError does not apply to the connect. You should always check the return value of the connect() call, period. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^4: SQL INSERTs don't work by thor (Priest) on Nov 08, 2004 at 21:59 UTC
Re^5: SQL INSERTs don't work by dragonchild (Archbishop) on Nov 09, 2004 at 13:50 UTC
Re: SQL INSERTs don't work by jZed (Prior) on Nov 08, 2004 at 16:00 UTC
This script works for me on linux with DBI 1.45 and DBD::SQLite 1.07. If you are testing only via the web, you should probably put in "use CGI::Carp qw(fatalsToBrowser)" to make sure you see error output. Although I wrote the test script you are using, I'm wondering if I was being too clever with the "if $sth->{NUM_OF_FIELDS}" since perhaps older versions of DBD::SQLite don't set NUM_OF_FIELDS (although they should, and do AFAIK). Just in case, try changing that to "if $sql =~ /^\s*SELECT/". If you continue to get errors, please show the results of DBI->installed_versions.	[reply]
Re^2: SQL INSERTs don't work by ido50 (Scribe) on Nov 09, 2004 at 20:29 UTC
I have managed to gather up that the INSERT does work, but the thing that causes the problem is actually the fetching (or the dumping of the results). When using any of the following DBI functions: `fetchrow_array, fetchrow_arrayref, fetchrow_hashref, dump_results`, the program exits silently. Any thoughts on this one? ------------------------- Live fat, die young	[reply] [d/l]
Re: SQL INSERTs don't work by erix (Prior) on Nov 08, 2004 at 14:25 UTC
fwiw (not much), your code works on win2k/perl 5.8.4./DBI 1.45/SQLite 1.07. As expected, it says: `'Fred Bloggs', '233-7777' 'Sanjay Patel', '999-4444' 2 rows` [download] With all error checking added it also works. I tried hard to let it fail but couldn't. ;) erm... :(	[reply] [d/l]