Re: [sqlite] retreving the column names for a query (PERL)
On Wednesday 22 March 2006 9:43 pm, Jim Dodgen wrote:
> for a query like
>
> select * from a join b on a.x = b.z
>
> anyone know how to get all the column names of the fields that would be
> returned from the query?
>
> I am using the DBD::SQLite PERL module
>
This script shows you how to get the column info.
Scott
use DBI();
use strict;
my $dbname = $ARGV[0] || die "usage $0 \n";
my $dbh=DBI->connect("DBI:SQLite2:dbname=$dbname",
"","",
{'RaiseError'=>1});
my $sqlversion=$DBD::SQLite2::VERSION;
my $dbiversion=$DBI::VERSION;
print "Running DBI $dbiversion and DBD::SQLite2 $sqlversion\n";
my @tables = $dbh->tables();
foreach my $table (@tables) {
print "\n\n--\nTable -->$table
\n--\n";
my $sth2 = $dbh->prepare("select * from $table limit 1") or die
$dbh->errstr;
$sth2->execute;
my $fields = $sth2->{NUM_OF_FIELDS};
print "Number of columns = $fields\n";
print "Column Name Type Precision Scale Nullable?
\n";
print "-- - -
-\n\n";
### Iterate through all the fields and dump the field information
for ( my $i = 0 ; $i < $fields ; $i++ ) {
my $name = $sth2->{NAME}->[$i];
### Describe the NULLABLE value
my $nullable = ("No", "Yes", "Unknown")[ $sth2->{NULLABLE}->[$i] ];
### Tidy the other values, which some drivers don't provide
my $scale = $sth2->{SCALE}->[$i];
my $prec = $sth2->{PRECISION}->[$i];
my $type = $sth2->{TYPE}->[$i];
### Display the field information
printf "%-30s %5d %4d %4d %s\n",
$name, $type, $prec, $scale, $nullable;
}
}
$dbh->disconnect();
exit(0);
--
Vir prudens non contra ventum mingit
POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/
Linux 2.6.11.4-21.11-default x86_64
SuSE Linux 9.3 (x86-64)
Re: [sqlite] Erreur avec DBD::SQLite
On Monday 10 October 2005 2:32 pm, Cyril Scetbon wrote: > Scott Leighton wrote: > >On Monday 10 October 2005 6:03 am, Cyril Scetbon wrote: > >>No error code is returned cause the script is blocked by the instruction > >>$dbh->do($req); > > > > Change that line to > > > > $dbh->do($req) or die $dbh->errstr; > > > > to get an error displayed. > > > > Scott > > I used it but no error is printed. What is weird is that it works on > Linux with the same version of DBD but not on windows. Moreover windows > is swapping. > > I noticed another error. When I try > sqlite3 backup.db "select * from logs where date not in (select date > from logs order by date desc limit (select nlpurge from settings))" > I get Segmentation fault Try checking the windows database for corruption. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-21.9-default x86_64 SuSE Linux 9.3 (x86-64)
Re: [sqlite] Erreur avec DBD::SQLite
On Monday 10 October 2005 6:03 am, Cyril Scetbon wrote: > > No error code is returned cause the script is blocked by the instruction > $dbh->do($req); > Change that line to $dbh->do($req) or die $dbh->errstr; to get an error displayed. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-21.9-default x86_64 SuSE Linux 9.3 (x86-64)
Re: [sqlite] solving the infamous, irritating "database locked" problem
On Thursday 09 June 2005 7:15 am, Puneet Kishor wrote:
> I've been a reading a lot on the "database locked" problem, but still
> need guidance trying to locate the source of my problem.
>
> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
> Mac OS X (10.3.9).
>
> I am trying to update a table via the web. The UPDATE in question is
> the very first (and the only) DML statement. Yet, I get the "database
> locked" error.
>
> The sequence of commands is
>
> my $sql = "UPDATE statement";
> my $sth = $dbh->prepare(qq{$sql});
> $sth->execute;
> $dbh->commit;
>
> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?
>
Try this
my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql}) || die $dbh->errstr;
$dbh->begin_work;
$sth->execute || die $dbh->errstr;
$dbh->commit;
Scott
--
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.11.4-21.7-default x86_64
Re: [sqlite] LIKE returns all rows
On Saturday 23 April 2005 7:15 pm, steve wrote: > Assume a database table named Good has a column named "bob". > The following command will return ALL rows in the table regardless of their > content: > > SELECT * FROM Good WHERE bob LIKE "bob"; > > Is this by design? If so, is there a workaround for this other than > attempting to name all columns in a table to be so unique as to never be > "LIKEd"? > More to try SELECT * from Good WHERE "bob" like 'bob'; SELECT * from Good WHERE 'bob' like 'bob'; SELECT * from Good WHERE 'bob' like "bob"; See the pattern? Double quotes are used for column names, single quotes for values. Your SELECT * from Good WHERE bob like "bob"; is the same as saying SELECT * from Good WHERE 1 = 1; Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-20a-default x86_64
Re: [sqlite] LIKE returns all rows
On Saturday 23 April 2005 7:15 pm, steve wrote: > Assume a database table named Good has a column named "bob". > The following command will return ALL rows in the table regardless of their > content: > > SELECT * FROM Good WHERE bob LIKE "bob"; > > Is this by design? If so, is there a workaround for this other than > attempting to name all columns in a table to be so unique as to never be > "LIKEd"? > Try SELECT * FROM Good WHERE bob LIKE 'bob'; Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.11.4-20a-default x86_64
Re: [sqlite] Setting The Width Option
On Tuesday 07 December 2004 10:28 am, Jeff Flowers wrote: > Is it possible to set the .width option when calling the sqlite > frontend? I was surprised that there wasn't a options to do this, > something like: > > sqlite3 -column -header -width '30 30 5 5' dbname > > You didn't mention your OS, but I know on Linux you can set whatever defaults you want in .sqliterc and the commandline utility uses them. Not sure if this is also true on the Win platform tho. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.111.5-default x86_64
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
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
On Saturday 11 September 2004 3:19 pm, Darren Duncan wrote:
> Just now I installed the newest versions of DBD::SQLite v1.05 (3.06)
> and DBD::SQLite2 v0.33 (2.8.15). They both tested and installed with
> no problems, along with DBI v1.43 and Perl v5.8.5, all using GCC 3.3
> on Mac OS X 10.2.8.
>
> However, this SQLite v2 and SQLite v3 can not be used simultaneously
> as they have symbol conflicts.
I'm not seeing any such problem here. The following code works
perfectly with DBD::SQLite2 v0.33 and DBD::SQLite v1.05.
-
use DBI;
my $dbh = DBI->connect('dbi:SQLite2:dbname=popfile2.db','','') || die
$dbh->er
rstr;
my $dbh1 = DBI->connect('dbi:SQLite:dbname=popfile.db','','') || die
$dbh1->er
rstr;
my $sth=$dbh->prepare('select * from words limit 5;') || die $dbh->errstr;
$sth->execute() || die $dbh->errstr;
while (my $row = $sth->fetchrow_arrayref) {
print $row->[0] . "\n";
}
$sth->finish();
$sth=$dbh1->prepare('select * from words limit 5;') || die $dbh1->errstr;
$sth->execute() || die $dbh1->errstr;
while (my $row = $sth->fetchrow_arrayref) {
print $row->[0] . "\n";
}
$sth->finish();
$dbh->disconnect;
$dbh1->disconnect;
--
No errors, no problems.
>
> Matt, please publish a newer DBD::SQLite as soon as you can get the
> pair to pass the following simple test without errors: "use
> DBD::SQLite2; use DBD::SQLite;"; that's also a good test for any
> subsequent releases.
You don't 'use' the DBD modules, you use DBI; and it handles loading
of the modules specified in the connect.
Scott
--
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.5-7.108-default x86_64
Re: [sqlite] SQLite3 -journal [SOLVED]
On Sunday 08 August 2004 8:32 pm, Scott Leighton wrote: > >Solved! Just to close the loop on this for the benefit of any other > DBD::SQLite users who may be trying to upgrade from v 0.31 to v 1.x.x. > Watch out for cases where your former working code left unfinished SELECT > statements since under SQLite v 3.x.x they will hold locks on the database > causing subsequent SQL calls to fail. > >In my case, the code had several instances where we knew there could > only be one result to the query so we basically grabbed that result and > moved on, e.g., > >$sth = $dbh->prepare($statement); >$sth->execute; >return $sth->fetchrow_arrayref; > >This left the SELECT statement in a not finalized state and the dataset > locked. Worked fine under SQLite 2.8.xx but it fails under 3.x.x. We had to > change the code to, > >$sth = $dbh->prepare($statement); >$sth->execute; >my $result= $sth->fetchrow_arrayref; >$sth->finish(); >return $result; > >to solve the problem by finalizing the SELECT so the lock came off the > database. > > Hope that helps someone else from banging their head against the wall > searching for the problem . Again, a big thanks to D. Richard Hipp for > pointing me in the right direction! > Follow-up to this thread. The issue with DBD::SQLite 1.00 - 1.03 has been resolved as of DBD::SQLite 1.04 so the above information is no longer applicable. If you are reading this in the archives, ignore this thread. The 1.04 version will act the same as the earlier SQLite 2 versions of DBD::SQLite ( 0.3x versions). Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] Update in SQLite
On Saturday 14 August 2004 10:28 am, cai yuan wrote: > Hi, > I would like to test the SQLite in some linux embedded system. > I read the limitation document and it says > "To change a table you have to delete it (saving its contents to a > temporary table) and recreate it from scratch." (Stated in > http://www.hwaci.com/sw/sqlite/omitted.html) That limitation refers to altering the structure of a table, not changing the data in a table. > Does it mean when I use the > "UPDATE ..." SQL statement, the table will actually be deleted and > recreated by SQLite automatically? Thanks! No, update is an SQL statement used to change values stored in a row or rows in the table. It works on the 'data' stored in the table, not the table structure itself. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 6:26 pm, D. Richard Hipp wrote: > Scott Leighton wrote: > > Whenever you start a SELECT statement (by calling sqlite3_step()) but have > not finalized that statement (using sqlite3_finalize() or sqlite3_reset()) > the statement is probably holding a lock on the table(s) that are being > queried. Other SELECT statements can read those tables, but no other > statements can INSERT, UPDATE or DELETE those tables until after all the > SELECTs have been finalized. Once again, thank-you! That makes complete sense and explains exactly what I am seeing. You are a lifesaver, I was really struggling with this, now I have a lead to solve it! Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 3:53 pm, Scott Leighton wrote: > On Sunday 08 August 2004 3:42 pm, D. Richard Hipp wrote: > > You should start looking for problems. The -journal file should be > > deleted the moment a transaction commits. In fact, transaction commit is > > defined as the moment when the journal file is deleted. If the journal > > is never deleted, then the commit never occurs. > > Thanks, I was afraid of that. Off to search for the cause. > OK, I've located my problem. I think I need a better understanding of the 'locking'. Looks like my commits are failing due to earlier statements remaining 'in progress'. What I don't understand is this 'database table is locked' stuff. This is a single process (no threads) accessing the database, what is locking it? A previous SQL statement that hasn't finished or something? The identical code worked fine with SQLite 2.8.x so the problem has to be something related to the change in locking in 3.x.x but I'm not seeing it. BTW, this is using the just release 3.04 version. Scott POPFile Engine v0.21.1 running DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::db commit failed: cannot commit transaction - SQL statements in progress(1) at dbdimp.c line 540 at /usr/local/bin/popfile/Classifier/Bayes.pm line 1289. DBD::SQLite::db begin_work failed: Already in a transaction at /usr/local/bin/popfile/Classifier/Bayes.pm line 1260. DBD::SQLite::db commit failed: cannot commit transaction - SQL statements in progress(1) at dbdimp.c line 203 at /usr/local/bin/popfile/Classifier/Bayes.pm line 1289. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. DBD::SQLite::st execute failed: database table is locked(1) at dbdimp.c line 371 at /usr/local/bin/popfile/Classifier/Bayes.pm line 2916. helphand:/var/log/popfile # -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] SQLite3 -journal
On Sunday 08 August 2004 3:42 pm, D. Richard Hipp wrote: > > You should start looking for problems. The -journal file should be deleted > the moment a transaction commits. In fact, transaction commit is defined > as the moment when the journal file is deleted. If the journal is never > deleted, then the commit never occurs. Thanks, I was afraid of that. Off to search for the cause. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
[sqlite] SQLite3 -journal
I have finally succeeded in getting DBD::SQLite v 1.02 compiled and installed on my AMD64 and have therefore started working with SQLite3. After reading the documentation about locking and concurrency here http://www.sqlite.org/lockingv3.html I am left with the impression that the life of the -journal file on disk should be relatively limited. IOW, once the transactions are committed, the -journal is deleted from disk. I'm not seeing that here, the -journal file seems to sit there throughout the life of the program (in this case, a deamon that runs in the background). In fact, it remains on disk even after the program is terminated and only disappears after the program is restarted (only to come back again, presumably after a change to the db). Is this the correct behavior or should I start looking for a problem with either the perl program itself or DBD::SQLite? Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
Re: [sqlite] Compiling sqlite3.0.3 on dec-osf1 with gcc 3.04 failed
On Wednesday 04 August 2004 7:27 am, D. Richard Hipp wrote: > Detlef Groth wrote: > > Hello, > > > > I could recently compile the sqlite2.x code without problems but now I > > can't compile the the 3.0.3 branch. > > > > The main problem is in: > > sqliteInt.h:129: parse error before "uptr" > > sqliteInt.h:129: warning: data definition has no type or storage class > > Try adding '-DUINTPTR_TYPE=u64' to the compiler command line. Please let > me know if that helps. The tarball for 3.03 doesn't include fixes for 64 bit. The OP needs to grab the latest CVS and compile it, it compiles clean on my AMD64 whereas 3.03 doesn't. I'm running into this same problem with DBD::SQLite, which grabs the tarball. It would be nice if a new release would be issued soon. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.104-default x86_64
[sqlite] Re: Compile Help - conflicting types for `sqlite3UnlinkAndDeleteIndex'
On Monday 26 July 2004 8:14 pm, you wrote: > I'm trying to compile the latest cvs version of 3.0 on my > AMD64 system and I'm running into this problem. > Well, scratch that, my mistake. Apparently I screwed up my local copy of CVS. I just blew it away and refreshed with a clean checkout and everything compiles clean. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
[sqlite] Compile Help - conflicting types for `sqlite3UnlinkAndDeleteIndex'
I'm trying to compile the latest cvs version of 3.0 on my AMD64 system and I'm running into this problem. gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/btree.c -fPIC -DPIC -o .libs/btree.o gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/btree.c -o btree.o >/dev/null 2>&1 ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/build.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -c ../sqlite/src/build.c -fPIC -DPIC -o .libs/build.o ../sqlite/src/build.c:219: error: conflicting types for `sqlite3UnlinkAndDeleteIndex' ../sqlite/src/sqliteInt.h:1278: error: previous declaration of `sqlite3UnlinkAndDeleteIndex' make: *** [build.lo] Error 1 [EMAIL PROTECTED]:~/bld> I'm pretty clueless when it comes to compiles that bomb out, can anyone point me in the right direction to fix this? Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
Re: [sqlite] where is config.h?
On Monday 26 July 2004 6:09 pm, Dennis Volodomanov wrote: > Hello all, > > I just downloaded the whole repository and tried to recompile (v3), but > in sqliteInt.h there's a header include "config.h" and this file is not > present anywhere :-( > > Am I missing something? I was able to compile fine before... > > TIA > >Dennis It gets created during the .../sqlite/configure process. Scott > > // > Software for animal shelters > http://www.smartpethealth.com > // -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
Re: [sqlite] first DBD::SQLite for SQLite 3.x released
On Thursday 22 July 2004 8:56 pm, Darren Duncan wrote: > At 11:22 PM -0400 7/22/04, [EMAIL PROTECTED] wrote: > >Scott Leighton <[EMAIL PROTECTED]> writes: > > > OK, but what happens to existing sqlite databases we're already > >> > >> using with Perl? From what I can see, if I update to the new > >> DBD:SQLite, I'll end up with v 3.x even though all my existing > >> db's are v 2.8. Will it auto-detect and upgrade them? > > > >And what of read-only databases (e.g. those databases which have been > > written to CD or DVD) and can't be upgraded? Can they still be queried? > > The simplest answer for both of these is to use DBD::SQLite 0.31 for > version 2 databases, and DBD::SQLite 1.0 for version 3 databases. I don't see that as a viable option. > If > you mean, use them both simultaneously, then that's more complicated, > and I won't go into it now. -- Darren Duncan I think it will be a problem if they can't coexist somehow. Otherwise, the poor user would have to locate and convert any SQLite databases on their machine (and in their archives) before one could successfully upgrade to the new DBD::SQLite without breaking things. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.95-default x86_64
