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
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
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
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
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] --
[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] --
[sqlite] DBD::SQLite2
How come DBD::SQLite2 does not produce a 'sqlite' binary? *** Note new e-mail address -- Michael J. Freeman Netco Government Services [EMAIL PROTECTED] --
RE: [sqlite] problems using DBD::Sqlite2
Point taken, I wrote this e-mail last night when I was very tired and sick of trying to debug this. 8) Basically my script is using POE, the perl object environment, its an event driven state machine. In Detail "POE Parcels out execution time among one or more tasks, called sessions. Sessions multitask through cooperation (at least until Perl's threads become more mainstream). That is, each session returns execution to POE as quicky as possible so it can parcel out time to the next. The POE developers and contributors have written many modules and components to POE, such as POE::Components that implement non-blocking DBI calls, so you can do multiple inserts at once or an insert and an update or whatever. My program is trying to use POE::Component::EasyDBI to make 1 insert into an oracle database, and another insert into the SQLite2 database. Someone mentioned to me yesterday that more than one process can't access the SQLite2 database, that it doesn't support that kind of stuff. -Original Message- From: Scott Leighton [mailto:[EMAIL PROTECTED] Sent: Thursday, October 07, 2004 8:35 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] problems using DBD::Sqlite2 On Thursday 07 October 2004 5:11 pm, Freeman, Michael wrote: > I put in a ticket, but I thought I'd also throw this question out to you > guys. I'm having some problems with a script that is using sqlite. I'll > cut and paste from the ticket: > > > > POE::Component::EasyDBI Got STDERR from child, which should never happen > ( <- errstr= ( 'no such table: trapdlog(1) at dbdimp.c line 412' ) [1 > items] at SubProcess.pm line 866 ) at > /usr/local/lib/perl5/site_perl/5.8.5/POE/Component/EasyDBI.pm line 678. > > I keep getting these messages when I run my perl script with > DBI->trace(1); > > I created my database and table and its very simple I'm confused, what do the examples have to do with the code above? In the examples you are using DBI, above you show an error from POE. They are two completely different interfaces. The examples run fine here, so I'd say you need to provide more information regarding the real script giving you a problem. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.108-default x86_64
[sqlite] problems using DBD::Sqlite2
I put in a ticket, but I thought I'd also throw this question out to you guys. I'm having some problems with a script that is using sqlite. I'll cut and paste from the ticket: POE::Component::EasyDBI Got STDERR from child, which should never happen ( <- errstr= ( 'no such table: trapdlog(1) at dbdimp.c line 412' ) [1 items] at SubProcess.pm line 866 ) at /usr/local/lib/perl5/site_perl/5.8.5/POE/Component/EasyDBI.pm line 678. I keep getting these messages when I run my perl script with DBI->trace(1); I created my database and table and its very simple here are the steps I use as root (my script also runs as root) dbish dbi:SQLite2:trapdlog.db create table trapdlog (epochtime, trap_category, trap_create_time, ip_hostname, trap_source, description, status); This code works: 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, descriptio n, status) values ('epoc','trapc','trapcxi','ip','tsrc','moocow','CLEAR')"; my $sth = $dbh->prepare($sql) or die("$DBI::errstr\n"); $sth->execute; I am able to go into the dbi shell and see the record, but my program doesn't seem to want to think the table exists. The database and script calling the database are in the same cwd as well. Any idears? *** Note new e-mail address -- Michael J. Freeman Netco Government Services [EMAIL PROTECTED] --