RE: [sqlite] still having problems with DBD::SQLite2
At 2:21 PM -0500 10/9/04, Freeman, Michael wrote: Well in my discovery, it seems sqlite is just a flat file and cannot be updated by multiple processes/threads/whatever at the same time. You can do selects, but not anything that modifies it. My problem was that I was working in a "threaded" environment where I had multiple threads trying to do inserts at the same time, and that wasn't working out for me. I'd like to work on the DBD::Sqlite2 code sometime when I have time to try and improve its error reporting and extend some of its capabilities. Look for patches to come. Also, on that note, might as well make a DBD::SQLite3. -Mike There is already a DBD for SQLite 3; it is called "DBD::SQLite", with no numerical suffix. The newest release is v1.06, which embeds SQLite 3.0.7. By contrast, DBD::SQLite2 v0.32 embeds SQLite 2.8.15. I strongly suggest that you focus all new development on SQLite 3 (unless you have a pile of legacy v2 data files) since that's where it will benefit the community (and yourself) best. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
Le sam 09/10/2004 à 21:21, Freeman, Michael a écrit : > Look for patches to come. Also, on that note, might as well make a > DBD::SQLite3. Another DBD for sqlite 3, then ? Looks like there will be plenty to choose from ;-) -- ** [EMAIL PROTECTED] OpenPGP pubkey: http://www.amakuru.net/dmorel.asc signature.asc Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e=2E?=
RE: [sqlite] still having problems with DBD::SQLite2
Well in my discovery, it seems sqlite is just a flat file and cannot be updated by multiple processes/threads/whatever at the same time. You can do selects, but not anything that modifies it. My problem was that I was working in a "threaded" environment where I had multiple threads trying to do inserts at the same time, and that wasn't working out for me. I'd like to work on the DBD::Sqlite2 code sometime when I have time to try and improve its error reporting and extend some of its capabilities. Look for patches to come. Also, on that note, might as well make a DBD::SQLite3. -Mike -Original Message- From: Keith Herold [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 6:48 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 Well, you can always synchronize access and share the same pointer, right (in windows; using fork in unix is bad, presumably because fork() just copies all the data into the child)? It sucks if you are using sql_step, or, I imagine, precompiled queries, though. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 4:23 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: >I am pretty sure I know whats going on now. I am using POE (Perl Object >environment, I highly recommend it poe.perl.org) and what is happening >is my program is basically trying to do inserts into the database at >the same time, which I think is creating a deadlock. It can handle >doing one insert at one time, but when I fire a lot of events at it >that are kind of happening asynchronously on the server, it fails. It >would be nice if the debugging and logging output made some sort of >damn sense or would tell you these things.. I think I have had my head >up my ass all day cuz of this. I am going to try do some stuff in my >program that will "pause" all the other helper "threads" when I'm doing >a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
Well, you can always synchronize access and share the same pointer, right (in windows; using fork in unix is bad, presumably because fork() just copies all the data into the child)? It sucks if you are using sql_step, or, I imagine, precompiled queries, though. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 4:23 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: >I am pretty sure I know whats going on now. I am using POE (Perl Object >environment, I highly recommend it poe.perl.org) and what is happening >is my program is basically trying to do inserts into the database at >the same time, which I think is creating a deadlock. It can handle >doing one insert at one time, but when I fire a lot of events at it >that are kind of happening asynchronously on the server, it fails. It >would be nice if the debugging and logging output made some sort of >damn sense or would tell you these things.. I think I have had my head >up my ass all day cuz of this. I am going to try do some stuff in my >program that will "pause" all the other helper "threads" when I'm doing >a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: I am pretty sure I know whats going on now. I am using POE (Perl Object environment, I highly recommend it poe.perl.org) and what is happening is my program is basically trying to do inserts into the database at the same time, which I think is creating a deadlock. It can handle doing one insert at one time, but when I fire a lot of events at it that are kind of happening asynchronously on the server, it fails. It would be nice if the debugging and logging output made some sort of damn sense or would tell you these things.. I think I have had my head up my ass all day cuz of this. I am going to try do some stuff in my program that will "pause" all the other helper "threads" when I'm doing a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
I am pretty sure I know whats going on now. I am using POE (Perl Object environment, I highly recommend it poe.perl.org) and what is happening is my program is basically trying to do inserts into the database at the same time, which I think is creating a deadlock. It can handle doing one insert at one time, but when I fire a lot of events at it that are kind of happening asynchronously on the server, it fails. It would be nice if the debugging and logging output made some sort of damn sense or would tell you these things.. I think I have had my head up my ass all day cuz of this. I am going to try do some stuff in my program that will "pause" all the other helper "threads" when I'm doing a sql insert. -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 3:55 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 3:45 PM -0500 10/8/04, Freeman, Michael wrote: >The script and the database live and are called from in the same >directory. As an experiment, try putting the code from your two scripts into the same script. The combined script would first run the code for creating the database, then run the code for updating it. Both parts do an open() and a close(), and both use the unqalified file name. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 3:45 PM -0500 10/8/04, Freeman, Michael wrote: The script and the database live and are called from in the same directory. As an experiment, try putting the code from your two scripts into the same script. The combined script would first run the code for creating the database, then run the code for updating it. Both parts do an open() and a close(), and both use the unqalified file name. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
The script and the database live and are called from in the same directory. -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 3:44 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 3:26 PM -0500 10/8/04, Freeman, Michael wrote: >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.. I was going to bring this up too, but the other problem was more glaring. When you do not give a full path, then SQLite will look in your current working directory for the database file. If you are running your program from a shell prompt, then you need to first cd into the directory that contains your database file before running your script. If you are running your program as a web application / CGI script, then your current working directory is probably whatever actual folder corresponds to the web address you invoked to run the program; unless this is where your database file is (and it shouldn't be, for security reasons), then giving an unqualified filename won't work. Your current working directory when running your script is probably different than the one your database is in, hence your problem. Using full paths gets around this because then it doesn't matter what your cwd is. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
At 3:26 PM -0500 10/8/04, Freeman, Michael wrote: 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.. I was going to bring this up too, but the other problem was more glaring. When you do not give a full path, then SQLite will look in your current working directory for the database file. If you are running your program from a shell prompt, then you need to first cd into the directory that contains your database file before running your script. If you are running your program as a web application / CGI script, then your current working directory is probably whatever actual folder corresponds to the web address you invoked to run the program; unless this is where your database file is (and it shouldn't be, for security reasons), then giving an unqualified filename won't work. Your current working directory when running your script is probably different than the one your database is in, hence your problem. Using full paths gets around this because then it doesn't matter what your cwd is. -- Darren Duncan
RE: [sqlite] still having problems with DBD::SQLite2
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;
>
>}
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;
}
RE: [sqlite] still having problems with DBD::SQLite2
I read this guys post on the yahoo groups site,
http://groups.yahoo.com/group/sqlite/message/4528
He said this:
I've solved the "no such table: " problem.
I had a filename that was sometimes not properly NULL terminated.
Along with /path/databasename there were a few
/path/databasenameplusjunk files.
The problem is, my 4 line test script works fine, and when I try to do
the same thing in another larger script it blows up..
Help me please before I am forced to install mysql...
-Original Message-
From: Freeman, Michael
Sent: Friday, October 08, 2004 1:03 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] still having problems with DBD::SQLite2
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.
<- disconnect_all= '' at DBI.pm line 674
ERROR: 1 'no such table: trapdlog(1) at dbdimp.c line 412'
(err#0)
! <- DESTROY(DBI::st=HASH(79dd10))= undef during global destruction
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 = ? } )
or syslog_die($DBI::errstr);
# prepare the insert statement
my $lite_sth_insert = $lite_dbh->prepare( q{ INSERT INTO trapdlog
(epochtime, trap_category,
trap_create_time,
ip_hostname, trap_source,
description, status)
VALUES (?,?,?,?,?,?,?) } )
or syslog_die($DBI::errstr);
# prepare the select statement
my $lite_sth_select = $lite_dbh->prepare( q{ SELECT status FROM trapdlog
WHERE node = ? } )
or syslog_die($DBI::errstr);
$lite_sth_select->execute($node) or die $DBI::errstr;
my $status = $lite_sth_select->fetchrow;
$lite_sth_update->execute("$node","CLEAR") or die
$DBI::errstr;
$lite_sth_insert->execute("$epochtime","$trap_category",
"$t_date",
"$node", "$trap_source",
"$ndescription",
"DOWN") or die $DBI::errstr;
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;
}
*** Note new e-mail address
--
Michael J. Freeman
Netco Government Services
[EMAIL PROTECTED]
--
