Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
At 12:49 AM +0100 9/12/04, Matt Sergeant wrote: This is just because Mac OSX is fussy - Linux won't complain and will let the latterly loaded symbol supercede. But it's a valid bug in DBD::SQLite2, so I'll fix it in the next release (should be simple). Matt. Now, unless you have a Mac OS X box of your own to test against, please send me a copy of the changed source files (the whole files, not diffs) asap, and I'll test your current distro with those changes. This way, if something else comes up, I can have tested that it works good prior to you uploading it to CPAN. I'll be waiting for them. -- Darren Duncan
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
On Sat, 11 Sep 2004, Darren Duncan wrote: > And the results: > > [S0106000393c33758:Documents/Perl Distributions/devworld] > darrenduncan% ../perl58 dbd_load_test.pl > dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout > /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle > > definition of _sqlite_busy_timeout > /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle > > definition of _sqlite_busy_timeout > Trace/BPT trap > > The error messages are the same as before, which is the important > part. Perl dies hard; this isn't a trappable error. > > Does the above code sample work on your machine? This is just because Mac OSX is fussy - Linux won't complain and will let the latterly loaded symbol supercede. But it's a valid bug in DBD::SQLite2, so I'll fix it in the next release (should be simple). Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
[sqlite] Re: symbol conflict in v3 with v2 - using DBD::SQLite/2
On Sat, 11 Sep 2004, Darren Duncan wrote: > However, this SQLite v2 and SQLite v3 can not be used simultaneously > as they have symbol conflicts. The one flagged was > _sqlite_busy_timeout, but from a quick scan of the offending files > there seem to be more conflicts. It all looks like a number of > 'sqlite' not being changed to 'sqlite3'. [snip] > Since I don't know whether the problem is in the core or in the Perl > bindings, should I file a ticket on SQLite.org for this? Looks like it's probably a "bug" in the perl bindings. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
At 4:05 PM -0700 9/11/04, Scott Leighton wrote:
I'm not seeing any such problem here. The following code works
perfectly with DBD::SQLite2 v0.33 and DBD::SQLite v1.05.
You don't 'use' the DBD modules, you use DBI; and it handles loading
of the modules specified in the connect.
Scott
I know how one normally invokes DBD modules. I only used them
directly in order to give the simplest test case. They originally
failed when I tried to open both using the normal DBI interface.
Here's a pared-down example of what I was actually trying. And this
did work before, when the only SQLite on the system was DBD::SQLite
0.31.
use strict; use warnings;
use DBI;
my @working_dbi_drivers = ();
foreach my $dbi_driver (DBI->available_drivers()) {
eval { DBI->install_driver( $dbi_driver ); }; $@ and next;
push( @working_dbi_drivers, $dbi_driver );
}
And the results:
[S0106000393c33758:Documents/Perl Distributions/devworld]
darrenduncan% ../perl58 dbd_load_test.pl
dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout
/Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle
definition of _sqlite_busy_timeout
/Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle
definition of _sqlite_busy_timeout
Trace/BPT trap
The error messages are the same as before, which is the important
part. Perl dies hard; this isn't a trappable error.
Does the above code sample work on your machine?
-- Darren Duncan
P.S. The above code is part of a larger routine that auto-detects
what data sources are available via all DBI drivers. It calls
DBI->data_sources() for each driver that passes the load test.
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
[sqlite] symbol conflict in v3 with v2 - using DBD::SQLite/2
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. The one flagged was _sqlite_busy_timeout, but from a quick scan of the offending files there seem to be more conflicts. It all looks like a number of 'sqlite' not being changed to 'sqlite3'. Since the libraries are dynamically linked, you only encounter the problem when trying to dynamically load both of them. Loading just one or the other is fine, doing them both results in an error, no matter which order they are loaded, following the second one. Below this email I give the exact error in a simplest reproduction case. I'm not sure if this problem is in the SQLite core or in the Perl bindings. Has anyone tried to dynamically link the cores both SQLite versions into the same single C program; did similar problems come up? 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. Note that I tried searching the source code for "_sqlite_busy_timeout" but that only appears in the .o files following compilation; I don't know what names in the source files correspond to those, since they obviously were transformed. Since I don't know whether the problem is in the core or in the Perl bindings, should I file a ticket on SQLite.org for this? Thank you. -- Darren Duncan -- [S0106000393c33758:Documents/Perl Distributions/devworld] darrenduncan% ../perl58 use DBD::SQLite2; use DBD::SQLite; dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle definition of _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle definition of _sqlite_busy_timeout Trace/BPT trap [S0106000393c33758:Documents/Perl Distributions/devworld] darrenduncan% ../perl58 use DBD::SQLite; use DBD::SQLite2; dyld: ../perl58 multiple definitions of symbol _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite/SQLite.bundle definition of _sqlite_busy_timeout /Volumes/Programming/Perl/lib/perl5/site_perl/5.8.5/darwin/auto/DBD/SQLite2/SQLite2.bundle definition of _sqlite_busy_timeout Trace/BPT trap
Re: [sqlite] temp files
On Fri, 10 Sep 2004 15:58:24 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote: OK. My workaround trick didn't work afterall. Looks like you are stuck with a temporary file when doing an UPDATE or a mass INSERT inside a transaction. yes. update may be very simple. See the following log: SQLite version 3.0.6 Enter ".help" for instructions sqlite> create table ITEMS2(children INTEGER, refcount INTEGER); sqlite> insert into ITEMS2(children, refcount) values(1,2); sqlite> insert into ITEMS2(children, refcount) values(2,3); sqlite> insert into ITEMS2(children, refcount) values(3,4); sqlite> insert into ITEMS2(children, refcount) values(4,5); sqlite> insert into ITEMS2(children, refcount) values(5,6); sqlite> select ROWID from ITEMS2; 1 2 3 4 5 sqlite> begin ...> ; sqlite> update ITEMS2 set children=0 where ROWID=3; -- breakpoint triggering here -- sqlite> commit; The OR COMMIT(ROLLBACK) options give me the same results. -- With best regards, Dmytro Bogovych
Re: [sqlite] Storing text in sqlite vs. external flat file
Ed Porter wrote: I found that performance began to fail miserably as the blob size increased above 500 bytes (has anyone else experienced this problem?). I just ran a test where I do "count" INSERTs of a BLOB of different sizes. Here's the result: size=100 count=100 time=26374 microseconds per iteration size=200 count=100 time=24480 microseconds per iteration size=400 count=100 time=31388 microseconds per iteration size=1000 count=100 time=37764 microseconds per iteration size=2000 count=100 time=56178 microseconds per iteration size=4000 count=100 time=85590 microseconds per iteration size=8000 count=100 time=137930 microseconds per iteration size=1 count=100 time=168661 microseconds per iteration size=2 count=100 time=288281 microseconds per iteration size=4 count=100 time=429003 microseconds per iteration size=10 count=100 time=877089 microseconds per iteration size=100 count=1 time=1128732 microseconds per iteration size=200 count=5000 time=824365 microseconds per iteration size=400 count=2500 time=515504 microseconds per iteration size=1000 count=1000 time=269727 microseconds per iteration size=2000 count=500 time=243470 microseconds per iteration size=4000 count=250 time=206263 microseconds per iteration size=8000 count=125 time=170581 microseconds per iteration size=1 count=100 time=164367 microseconds per iteration size=2 count=50 time=156277 microseconds per iteration size=4 count=25 time=150406 microseconds per iteration size=10 count=10 time=151819 microseconds per iteration In the first group of tests, I hold the count constant and just increase the blob size. Doing 100 INSERTs of a 100K blob is only 33 times slower even though it is inserting 100 times more data. In the second set of tests, the total number of bytes inserted is held constant. As the blob size increases the number of INSERTs decreases. In this test we see that it is much faster to insert a few large blobs that to insert many small blobs. I doesn't look to me like performances is "miserable" for larger blobs. How did you run your tests? The results above are on SQLite version 3.0.6 using the Tcl bindings. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] New DBD::SQLite*s
On Fri, 10 Sep 2004, Darren Duncan wrote: > With this round, I will start using the new stuff like named host parameters. Sadly named host params are still broken in sqlite 3.0.6. When I parse this SQL: SELECT user_id, fname, lname FROM users WHERE lname like :1 UNION SELECT user_id, fname, lname FROM users WHERE fname like :1 the sqlite library still thinks I have 2 bind parameters, which breaks trying to execute that with one parameter under the DBI. I suspect this could be just a bug in sqlite3_bind_parameter_count, or the way sqlite internals count the number of parameters. Plus I have yet to implement code for named parameters to work. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] [ANN] 1st pre-alpha release of the SQLite OLE/DB provider
Vladimir Vukicevic, dando pulos de alegria, escreveu : Hi yesso, On Sat, 11 Sep 2004 09:02:08 +0200, yesso <[EMAIL PROTECTED]> wrote: hi, can i use this provider in a c# project? Yes, you can use it with any language that can access ADO and/or OLE-DB databases. But as Vladimir pointed out, you may be better with a more direct driver for your specific language. The advantages of an OLE/DB provider are database engine independence, but every case can be better with/without it. Regards, ~Nuno Lucas You may want to also look at the SQLite ADO.NET provider at http://www.mono-project.com/contributing/sqlite.html , which is part of mono's class libraries, but works fine with the .NET runtime. - Vlad
Re: [sqlite] [ANN] 1st pre-alpha release of the SQLite OLE/DB provider
Hi yesso, On Sat, 11 Sep 2004 09:02:08 +0200, yesso <[EMAIL PROTECTED]> wrote: > > hi, can i use this provider in a c# project? > You may want to also look at the SQLite ADO.NET provider at http://www.mono-project.com/contributing/sqlite.html , which is part of mono's class libraries, but works fine with the .NET runtime. - Vlad
[sqlite] Howto use max() function?
HI there!
I have just migrated to my application from hsqldb to SQLite-2.8 using
the jdbc-driver from christian werner.
However I have a problem with the following statement which is used very
oftern to increment indexes.
I know this is not a cool way to archieve this, however I must be 100%
fully SQL conform so I simply "synchronized" the method so that no
multithreding problems can occur.
I use the following code:
select max("+keyName+") from "+tableName+"
rs.next();
return rs.getInt(keyName);
This worked with HSQLDB, whith SQLite I get the following error:
java.sql.SQLException: column CALKEY not found
at
SQLite.JDBC2x.JDBCResultSetMetaData.findColByName(JDBCResultSetMetaData.java:189)
at SQLite.JDBC2x.JDBCResultSet.findColumn(JDBCResultSet.java:52)
at SQLite.JDBC2x.JDBCResultSet.getInt(JDBCResultSet.java:145)
at
palme.communication.logicElements.GeneralLogic.getMaxID(GeneralLogic.java:69)
at
palme.communication.logicElements.Division.replyInsertDivision(Division.java:84)
at
palme.communication.ClientRepresentation.readRequest(ClientRepresentation.java:136)
at
palme.communication.PalmeSocketInstance.run(PalmeSocketInstance.java:47)
at java.lang.Thread.run(Thread.java:534)
any ideas how the same thing could be done using 100% standard-SQL?
Thanks a lot, lg Clemens
Re: [sqlite] Storing text in sqlite vs. external flat file
Hi Ken I found that performance began to fail miserably as the blob size increased above 500 bytes (has anyone else experienced this problem?). When I posted the problem, I think that someone stated the docs show the reasonable limit on blobs is 230 bytes. Anyways, I had to store the blobs direct to disk and use SQLite to track the addresses (no different than Oracle, MSSQL, MySQL etc). Please notify if you can get your system to work! At 01:21 PM 9/10/04 -0700, [EMAIL PROTECTED] wrote: >I am looking into using sqlite for storing some data that will be ~100,000 >records in size, where each record will contain text that has an average >size of 40k, but could be > 200k. I will likely need to encrypt (and >potentially compress) the database. > > > >My question is whether to store the text in the database, or to keep a >separate file for the text with seek pointers and lengths in the database. >My preference is for the former, since I wouldn't have to manage a separate >robust encryption/compression/deletion process, but I am concerned about the >perf and size of the insertion stress experiments I've been running, and >I've seen comments on this list suggesting the latter for BLOBs. Are there >specific tuning tweaks I can make to improve my results? > > > >Thanks, > >Ken Cooper > > Sincerely, Ed Porter
