I created the database using:
dbish dbi:SQLite2:trapdlog.db
create table trapdlog (epochtime, trap_category, trap_create_time,
ip_hostname, trap_source, description, status);
create index trapd_idx on trapdlog (ip_hostname,epochtime);
I am able to connect to it with DBIsh and sqlite and it shows the table
exists. Also, I am able to do inserts into it with a test script:
#!/usr/local/bin/perl
# $Id $
use DBI;
my $dbh = DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","");
my $sql = "INSERT INTO trapdlog (epochtime, trap_category,
trap_create_time, ip_hostname, trap_source, description, status) values
('epoc','trapc','trapcxi','ip','tsrc','moocow','CLEAR')";
my $sth = $dbh->prepare($sql) or die("$DBI::errstr\n");
$sth->execute;
Also, another weird error is that if I specify the full path to the
database and I have DBI->trace(1); on, it says it can't connect to the
database. Without the full path, It just says the trapdlog doesn't
exist..
-----Original Message-----
From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Friday, October 08, 2004 3:13 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] still having problems with DBD::SQLite2
The problem you are having is that, while your sqlite_connect() code
assumes it is fine to just create a database file if it doesn't exist
(which is what SQLite does automatically), your other code always
assumes that the database file did exist before.
Your other code is trying to update or insert into a table without
first checking that the table exists. And the table won't exist if
the database didn't exist; newly created databases have no tables in
them.
You need to issue a "create table trapdlog ..." statement if the
database was newly created just now, and the table doesn't exist yet,
prior to doing any inserts or updates.
-- Darren Duncan
At 1:02 PM -0500 10/8/04, Freeman, Michael wrote:
>I am still having problems with a script trying to use SQLite2. My 4
>line test script works fine, but my other code keeps giving me DBI
>errors saying it can't find the table. Here is what I get in the DBI
>trace.
>
> !! ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
>(err#0)
>
> <- execute('1094662322' '3' ...)= undef at logwiz.pl line 377
>
>DBD::SQLite2::st execute failed: no such table: trapdlog(1) at dbdimp.c
>line 412 at ./logwiz.pl line 377.
>
>no such table: trapdlog(1) at dbdimp.c line 412 at ./logwiz.pl line
377.
>
>The code I'm using:
>
>my $lite_dbh = &sqlite_connect();
>
># prepare the update statement
>
>my $lite_sth_update = $lite_dbh->prepare( q{ UPDATE trapdlog SET status
>= ? WHERE node = ? } )
>
>
>sub sqlite_connect {
>
> # need to add code in here to check that the database exists. if
it
>does not we
>
> # will create it. *thought*.
>
> my $sqlite_dbh =
>DBI->connect("dbi:SQLite2:dbname=trapdlog.db","","")
>
> or die $DBI::errstr;
>
> return $sqlite_dbh;
>
>}
