Re: [sqlite] Fw: Re: SQlite JDBC driver - need help
Dev, Do you understand that SQLite is not a server application and it has no access control facilities? It is just some library code that helps accessing files that have some well-defined internal format. So your application will be able to access any database accessible by the user running your executable. In case of JSP page it will be whatever user account your IIS server is running under. And database path like '//ipaddress/spiceworks/db/spiceworks_prod.db' most probably means that you try to access a file located in the network at a shared folder spiceworks of the server ipaddress. So is that path accessible to the user running IIS? I'm not sure though what "out of memory" errors mean. Probably just badly written jdbc driver. Pavel On Wed, Aug 25, 2010 at 3:14 PM, Dev Movva wrote: > Hello, > > Can some one please help me how do I connect to SQLite from a JSP page with > windows authentication mode? > > Thank you, > > Dev > > --- On Wed, 8/25/10, Richard Hipp wrote: > > > From: Richard Hipp > Subject: Re: SQlite JDBC driver - need help > To: "Dev Movva" > Date: Wednesday, August 25, 2010, 3:02 PM > > > jdbc is maintained separately by Chris Werner. I don't know anything about > it. If you post on sqlite-users@sqlite.org you might be able to get somebody > there to help you. > > > On Wed, Aug 25, 2010 at 2:47 PM, Dev Movva wrote: > > > > > > > Dear Richard, > > I am trying to connect to SQLite from my JSP page and not sure where I am > not doing it right but no luck with it. I posted it on the google forums at > http://groups.google.com/group/sqlitejdbc/browse_thread/thread/841538d7b3679678# > > I am wondering if you can shed some light on my issue and help me wiht this. > > All I need is how do I connect to SQLite from a JSP page using windows > authentication (like integratedSecurity=true while connecting to a SQL > server). All our users should be able to see view the data with their windows > authentication. Any help is greately appreciated. > > Thank you in advance, > > Dev > > > > -- > D. Richard Hipp > d...@sqlite.org > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] test DBD::SQLite 1.30_04 - write-ahead logging
All, I am pleased to announce that DBD::SQLite (Self Contained RDBMS in a Perl DBI Driver) version 1.30_04 has been released on CPAN (by Adam Kennedy). http://search.cpan.org/~adamk/DBD-SQLite-1.30_04/ This developer release bundles the brand-new SQLite version 3.7.2, which (since 3.7.0) adds support for write-ahead logging (WAL). See http://sqlite.org/wal.html for the details of the WAL support that SQLite now has. WAL is an alternative method to how SQLite implements atomic commit and rollback than to its rollback journal method. It offers much improved concurrency and performance in many circumstances, such as because database readers and writers don't block each other. There are also trade-offs. By default, SQLite and DBD::SQLite will continue to use the older rollback journal method, and you can use the new WAL method with the SQL command: PRAGMA journal_mode=WAL; There are also numerous other additions, changes, or fixes in either DBD::SQLite or SQLite itself since the last production DBD::SQLite release 1.29 of 2010 January, which bundles SQLite 3.6.22. For the change details since then, see http://sqlite.org/changes.html or http://search.cpan.org/src/ADAMK/DBD-SQLite-1.30_04/Changes as appropriate. TESTING NEEDED! Please bash the hell out of the latest DBD::SQLite and report any outstanding bugs on RT. Test your dependent or compatible projects with it, which includes any DBMS-wrapping or object persistence modules, and applications. This 1.30_04 release will probably be released as a production 1.31 within a week if no show-stopper problems are found. Please note the compatibility caveats of using pre-3.7.x versions of SQLite on databases that had been used with WAL mode on. In order to use an older SQLite on the database, the database must have last been used by a 3.7.x in journal mode. See http://sqlite.org/wal.html for details. Please note that, if you receive nondescript "disk I/O error" errors from your code after the update, see if the failing code involves a process fork followed by unlinking of the database, such as if it was temporary for testing. The DBD::SQLite test suite had needed an update to act more correctly, which the update to 3.7.x from 3.6.x exposed; 3.6.x didn't complain about this. If you want in to DBD::SQLite development, then join the following email/IRC forums which MST created (the mailing list, I am administrating): http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite #dbd-sqlite on irc.perl.org And the canonical version control is at: http://svn.ali.as/cpan/trunk/DBD-SQLite/ Patches welcome. Ideas welcome. Testing welcome. If you feel that a bug you find is in SQLite itself rather than the Perl DBI driver for it, the main users email forum for SQLite in general is at: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ... where you can report it as an appropriate list post (the SQLite issue tracking system is no longer updateable by the public; posting in the list can cause an update there by a registered SQLite developer). Please do not reply to me directly with your responses. Instead send them to the forums or file with RT as is appropriate. Thank you. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fw: Re: SQlite JDBC driver - need help
Hello, Can some one please help me how do I connect to SQLite from a JSP page with windows authentication mode? Thank you, Dev --- On Wed, 8/25/10, Richard Hipp wrote: From: Richard Hipp Subject: Re: SQlite JDBC driver - need help To: "Dev Movva" Date: Wednesday, August 25, 2010, 3:02 PM jdbc is maintained separately by Chris Werner. I don't know anything about it. If you post on sqlite-users@sqlite.org you might be able to get somebody there to help you. On Wed, Aug 25, 2010 at 2:47 PM, Dev Movva wrote: Dear Richard, I am trying to connect to SQLite from my JSP page and not sure where I am not doing it right but no luck with it. I posted it on the google forums at http://groups.google.com/group/sqlitejdbc/browse_thread/thread/841538d7b3679678# I am wondering if you can shed some light on my issue and help me wiht this. All I need is how do I connect to SQLite from a JSP page using windows authentication (like integratedSecurity=true while connecting to a SQL server). All our users should be able to see view the data with their windows authentication. Any help is greately appreciated. Thank you in advance, Dev -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug with large file support in IBM AIX
It seems that IBM's AIX is finicky with large file support, at least on the system I'm working on. Out of the box, large file support will be disabled for aix when compiling the sqlite3 amalgamation. I had to make the following changes in sqlite3.c: #ifndef SQLITE_DISABLE_LFS # define _LARGE_FILE 1 //the define for AIX is plural. Go figure. # define _LARGE_FILES 1 # ifndef _FILE_OFFSET_BITS # define _FILE_OFFSET_BITS 64 # endif # define _LARGEFILE_SOURCE 1 #endif Seems the define _LARGE_FILE should be plural for aix. My aix system: AIX aix 3 5 0003D44AD300 Thanks! - Nasron ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL: Stitching together Text Files into a New Database
You didn't say Windows or Unix Here's Unix #!/bin/sh FILES="data1 data2 data3" rm -f data.db echo "create table t (file varchar,i1 int, i2 int, i3 int);" |sqlite3 data.db for f in $FILES do exec<$f while read line do v=`echo $line |awk '{ print $1 "," $2 "," $3 }'`; sql="insert into t values('$f',$v);"; echo $sql|sqlite3 data.db; done done ==> data1 <== 123 1000 199 124 1001 200 ==> data2 <== 123 1100 188 124 1101 189 ==> data3 <== 125 800 805 126 801 806 Result: CREATE TABLE t (file varchar,i1 int, i2 int, i3 int); INSERT INTO "t" VALUES('data1',123,1000,199); INSERT INTO "t" VALUES('data1',124,1001,200); INSERT INTO "t" VALUES('data2',123,1100,188); INSERT INTO "t" VALUES('data2',124,1101,189); INSERT INTO "t" VALUES('data3',125,800,805); INSERT INTO "t" VALUES('data3',126,801,806); If you need windows a small C program will generate the SQL for you which you can pipe into sqlite. Just run this like this: data data1 data2 data3 | sqlite3 data.db #include #include int main(int argc,char *argv[]) { int i; FILE *fp; char buf[256],sql[256]; printf("CREATE TABLE t (file varchar,i1 int, i2 int, i3 int);\n"); for(i=1;ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
On Aug 25, 2010, at 11:04 PM, Pavel Ivanov wrote: >> Prior to version 3.6.5 SQLite used to delay committing the >> transaction until all SELECT statements had finished. But that >> behavior was deemed to be less intuitive. > > But this is the current 3.7.1 documentation > (http://www.sqlite.org/lockingv3.html): > > > If multiple commands are being executed against the same SQLite > database connection at the same time, the autocommit is deferred until > the very last command completes. For example, if a SELECT statement is > being executed, the execution of the command will pause as each row of > the result is returned. During this pause other INSERT, UPDATE, or > DELETE commands can be executed against other tables in the database. > But none of these changes will commit until the original SELECT > statement finishes. > > > Is this documentation too old? I think so. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
> Prior to version 3.6.5 SQLite used to delay committing the > transaction until all SELECT statements had finished. But that > behavior was deemed to be less intuitive. But this is the current 3.7.1 documentation (http://www.sqlite.org/lockingv3.html): If multiple commands are being executed against the same SQLite database connection at the same time, the autocommit is deferred until the very last command completes. For example, if a SELECT statement is being executed, the execution of the command will pause as each row of the result is returned. During this pause other INSERT, UPDATE, or DELETE commands can be executed against other tables in the database. But none of these changes will commit until the original SELECT statement finishes. Is this documentation too old? Pavel On Wed, Aug 25, 2010 at 11:53 AM, Dan Kennedy wrote: > > On Aug 25, 2010, at 10:40 PM, Pavel Ivanov wrote: > >> Nikolaus, >> >> I've traced your application a bit (with SQLite 3.6.18 sources) and >> it looks like SQLite does some nasty thing nobody in this thread >> expected. For some reason while doing first delete SQLite actually >> commits transaction and degrades lock to SHARED. Then of course second >> delete cannot be executed because update has already PENDING lock. >> That's pretty strange and indeed reading sources of sqlite3VdbeHalt I >> didn't find any condition for committing other than successfulness of >> the statement. Maybe somebody somewhere forgot to set autocommit to 0 >> when select started executing? >> >> Dan, can you shed some light on this strange behavior? > > When you commit a transaction, SQLite upgrades to an EXCLUSIVE > lock so that it can write to the database file. Once it has > finished committing the transaction it drops back to a SHARED > lock if there are still active SELECT statements running, or > to no lock at all if there are no other active SELECTs. > > In the case where there are active SELECT statements remaining > when a transaction is committed, SQLite cannot drop all locks, > as this would leave the SELECT statements reading from the > database file holding no lock at all. > > Prior to version 3.6.5 SQLite used to delay committing the > transaction until all SELECT statements had finished. But that > behavior was deemed to be less intuitive. > > Dan. > > >> >> >> Pavel >> >> On Wed, Aug 25, 2010 at 10:39 AM, Nikolaus Rath >> wrote: >>> Hi, >>> >>> I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835 >>> , >>> was there anything else? >>> >>> -Nikolaus >>> >>> >>> Gerry Snyder >> re5jqeeqqe8avxtiumw...@public.gmane.org> writes: Er, did you not see Dan Kennedy's comments a fed days ago?? On 8/24/10, Nikolaus Rath wrote: > Nikolaus Rath > > writes: >> Still no one able to clarify the issues raised in this thread? >> >> Let me try to summarize what I still don't understand: >> >> - Will SQLite acquire and release an EXCLUSIVE lock while >> keeping a >> SHARED lock if one executes a UPDATE query with one cursor >> while a >> different cursor is in the middle of a SELECT query, >> >> -or- >> >> will the EXCLUSIVE lock be held until the SELECT query >> finishes? >> >> - Is there a way to prevent SQLite from keeping the SHARED lock >> while waiting for an EXCLUSIVE lock if doing so would result >> in a >> deadlock (because another connection holding a SHARED lock >> needs to >> get an EXCLUSIVE lock before it can release the SHARED lock)? > > > Hmm. Still no answer. But thanks to Simon I know at least that some > people are reading this thread :-). > > > So different question: does anyone know how to get this thread to > the > attention of an SQLite developer who might be able to help? > > > Best, > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E > 425C > ___ > sqlite-users mailing list > sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> -Nikolaus >>> >>> -- >>> »Time flies like an arrow, fruit flies like a Banana.« >>> >>> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-
Re: [sqlite] Why the deadlock?
On Aug 25, 2010, at 10:40 PM, Pavel Ivanov wrote: > Nikolaus, > > I've traced your application a bit (with SQLite 3.6.18 sources) and > it looks like SQLite does some nasty thing nobody in this thread > expected. For some reason while doing first delete SQLite actually > commits transaction and degrades lock to SHARED. Then of course second > delete cannot be executed because update has already PENDING lock. > That's pretty strange and indeed reading sources of sqlite3VdbeHalt I > didn't find any condition for committing other than successfulness of > the statement. Maybe somebody somewhere forgot to set autocommit to 0 > when select started executing? > > Dan, can you shed some light on this strange behavior? When you commit a transaction, SQLite upgrades to an EXCLUSIVE lock so that it can write to the database file. Once it has finished committing the transaction it drops back to a SHARED lock if there are still active SELECT statements running, or to no lock at all if there are no other active SELECTs. In the case where there are active SELECT statements remaining when a transaction is committed, SQLite cannot drop all locks, as this would leave the SELECT statements reading from the database file holding no lock at all. Prior to version 3.6.5 SQLite used to delay committing the transaction until all SELECT statements had finished. But that behavior was deemed to be less intuitive. Dan. > > > Pavel > > On Wed, Aug 25, 2010 at 10:39 AM, Nikolaus Rath > wrote: >> Hi, >> >> I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835 >> , >> was there anything else? >> >> -Nikolaus >> >> >> Gerry Snyder > re5jqeeqqe8avxtiumw...@public.gmane.org> writes: >>> Er, did you not see Dan Kennedy's comments a fed days ago?? >>> >>> On 8/24/10, Nikolaus Rath >>> wrote: Nikolaus Rath >>> > writes: > Still no one able to clarify the issues raised in this thread? > > Let me try to summarize what I still don't understand: > > - Will SQLite acquire and release an EXCLUSIVE lock while > keeping a >SHARED lock if one executes a UPDATE query with one cursor > while a >different cursor is in the middle of a SELECT query, > >-or- > >will the EXCLUSIVE lock be held until the SELECT query > finishes? > > - Is there a way to prevent SQLite from keeping the SHARED lock >while waiting for an EXCLUSIVE lock if doing so would result > in a >deadlock (because another connection holding a SHARED lock > needs to >get an EXCLUSIVE lock before it can release the SHARED lock)? Hmm. Still no answer. But thanks to Simon I know at least that some people are reading this thread :-). So different question: does anyone know how to get this thread to the attention of an SQLite developer who might be able to help? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> -- >>> Sent from my mobile device >>> ___ >>> sqlite-users mailing list >>> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -Nikolaus >> >> -- >> »Time flies like an arrow, fruit flies like a Banana.« >> >> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
Nikolaus, I've traced your application a bit (with SQLite 3.6.18 sources) and it looks like SQLite does some nasty thing nobody in this thread expected. For some reason while doing first delete SQLite actually commits transaction and degrades lock to SHARED. Then of course second delete cannot be executed because update has already PENDING lock. That's pretty strange and indeed reading sources of sqlite3VdbeHalt I didn't find any condition for committing other than successfulness of the statement. Maybe somebody somewhere forgot to set autocommit to 0 when select started executing? Dan, can you shed some light on this strange behavior? Pavel On Wed, Aug 25, 2010 at 10:39 AM, Nikolaus Rath wrote: > Hi, > > I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835, > was there anything else? > > -Nikolaus > > > Gerry Snyder writes: >> Er, did you not see Dan Kennedy's comments a fed days ago?? >> >> On 8/24/10, Nikolaus Rath wrote: >>> Nikolaus Rath >>> writes: Still no one able to clarify the issues raised in this thread? Let me try to summarize what I still don't understand: - Will SQLite acquire and release an EXCLUSIVE lock while keeping a SHARED lock if one executes a UPDATE query with one cursor while a different cursor is in the middle of a SELECT query, -or- will the EXCLUSIVE lock be held until the SELECT query finishes? - Is there a way to prevent SQLite from keeping the SHARED lock while waiting for an EXCLUSIVE lock if doing so would result in a deadlock (because another connection holding a SHARED lock needs to get an EXCLUSIVE lock before it can release the SHARED lock)? >>> >>> >>> Hmm. Still no answer. But thanks to Simon I know at least that some >>> people are reading this thread :-). >>> >>> >>> So different question: does anyone know how to get this thread to the >>> attention of an SQLite developer who might be able to help? >>> >>> >>> Best, >>> >>> -Nikolaus >>> >>> -- >>> »Time flies like an arrow, fruit flies like a Banana.« >>> >>> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C >>> ___ >>> sqlite-users mailing list >>> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> -- >> Sent from my mobile device >> ___ >> sqlite-users mailing list >> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Length=10
On Wed, Aug 25, 2010 at 10:07:54AM -0500, P Kishor scratched on the wall: > On Wed, Aug 25, 2010 at 10:05 AM, Ted Rolle Jr. wrote: > > I have a table of UPCs with lengths varying from 6 to 12. ?I'd like to > > print those with length=10 to a file for printing. > > SELECT * > > ?FROM UPCs > > ?WHERE LENGTH(UPC)=10; > > works just fine. ?But when I export the table I naturally get the whole > > table. ?Would a View help here? ?Or create a temporary table. > > I believe there's a simple solution, but it eludes me. ?For now. > > in the sqlite3 shell, set output mode to file,... ...via the ".output " command... > ...and then select as > above. All your select output will go to your file. You can also modify the format of the output using the .mode command. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Length=10
So simple. I never thought of using the command-line sqlite3... > in the sqlite3 shell, set output mode to file, and then select as > above. All your select output will go to your file. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Length=10
On Wed, Aug 25, 2010 at 10:05 AM, Ted Rolle Jr. wrote: > I have a table of UPCs with lengths varying from 6 to 12. I'd like to > print those with length=10 to a file for printing. > SELECT * > FROM UPCs > WHERE LENGTH(UPC)=10; > works just fine. But when I export the table I naturally get the whole > table. Would a View help here? Or create a temporary table. > I believe there's a simple solution, but it eludes me. For now. in the sqlite3 shell, set output mode to file, and then select as above. All your select output will go to your file. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Length=10
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a table of UPCs with lengths varying from 6 to 12. I'd like to print those with length=10 to a file for printing. SELECT * FROM UPCs WHERE LENGTH(UPC)=10; works just fine. But when I export the table I naturally get the whole table. Would a View help here? Or create a temporary table. I believe there's a simple solution, but it eludes me. For now. Ted - -- +-+ | 3.14159 26535 89793 23846 26433 83279 50288 May the Spirit| | 41971 69399 37510 58209 74944 59230 78164of pi spread | | 06286 20899 86280 34825 32411 70679 82148 around the world. | | 08651 32823 06647 09384 46095 50582 ... PI VOBISCUM! | +-+ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQIcBAEBAgAGBQJMdTFKAAoJENEtY9EvFm6JUsoP/2yv45tWN+a9iZeI0OT7Ny5J rGf8/E6S9m11i87DZxFAw0wctjDjQIN0zwNqBVcsX1C32LtxK0MmoY0smsrOeLfE neLx2NifFUnsLvXlDcbKeCtDyHaGFhMQqC1EigUeCvqqopMBAEIzha//nlwQk036 alwC+Fs+rI59194/aRvQmR4IAhnl4byQ39Adjb/2/cbnt4/nlUQG1XLLidMdelpM tLeLE4ivUYoGOYwmN3jmRnAHwPlW53YiXkx++vU3CIFVZtyHNhnxzyWHZtLyYIWJ rOBZ/WyOXEqLvh9jqWqQ1Dy22Tr4Nyrw+QOoTPx6U8tLHBd2gdESYQDwKHIHpD1j 99FiboC3O/IwZ5hyfheC94bk53XsGlL8x/VIL+ia1ORIL80DL84cASBsqU0MoMsM fW5Fh1bVyms7sz89IfOnZt3dOC1Vuk4aqt4rdRb7AFtIGI8wdSeoZAodhdUu5xsd uq0twEngryFp4nycHBsu+jwxTYO0PX35sIPDKVLp3hZtAoblFhYsyjmjQBbxMaAB TYzzfhlE0KANo+DYzq0YioafFIkd4qW4Wg734znpKjjZY7kDsxeN2LYIdueoaGzt MziIprBqcq8gfcnBqA4WlX19LD6trb8jsR78hlqdJskYBUaqXvWz0/qjzxh6qvRX yritK3uMqSsasn4vdUPM =yJdP -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
Doug Currie writes: > On Aug 24, 2010, at 10:57 AM, Nikolaus Rath wrote: > >> Nikolaus Rath >> >> writes: >>> Still no one able to clarify the issues raised in this thread? >>> >>> Let me try to summarize what I still don't understand: >>> >>> - Will SQLite acquire and release an EXCLUSIVE lock while keeping a >>> SHARED lock if one executes a UPDATE query with one cursor while a >>> different cursor is in the middle of a SELECT query, >>> >>> -or- >>> >>> will the EXCLUSIVE lock be held until the SELECT query finishes? > > If you want the main thread to hold an exclusive lock until the work [...] Thanks for the recipe, but that still doesn't really answer my question. Or did I miss something? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
Hi, I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835, was there anything else? -Nikolaus Gerry Snyder writes: > Er, did you not see Dan Kennedy's comments a fed days ago?? > > On 8/24/10, Nikolaus Rath wrote: >> Nikolaus Rath >> writes: >>> Still no one able to clarify the issues raised in this thread? >>> >>> Let me try to summarize what I still don't understand: >>> >>> - Will SQLite acquire and release an EXCLUSIVE lock while keeping a >>>SHARED lock if one executes a UPDATE query with one cursor while a >>>different cursor is in the middle of a SELECT query, >>> >>>-or- >>> >>>will the EXCLUSIVE lock be held until the SELECT query finishes? >>> >>> - Is there a way to prevent SQLite from keeping the SHARED lock >>>while waiting for an EXCLUSIVE lock if doing so would result in a >>>deadlock (because another connection holding a SHARED lock needs to >>>get an EXCLUSIVE lock before it can release the SHARED lock)? >> >> >> Hmm. Still no answer. But thanks to Simon I know at least that some >> people are reading this thread :-). >> >> >> So different question: does anyone know how to get this thread to the >> attention of an SQLite developer who might be able to help? >> >> >> Best, >> >>-Nikolaus >> >> -- >> »Time flies like an arrow, fruit flies like a Banana.« >> >> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C >> ___ >> sqlite-users mailing list >> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > -- > Sent from my mobile device > ___ > sqlite-users mailing list > sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.x for Pocket PC 2003 SDK (VS2005) does not compile
There have been several posts on this issue just this week, and suggested workarounds. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joachim Wickman Sent: Wednesday, August 25, 2010 12:37 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite 3.7.x for Pocket PC 2003 SDK (VS2005) does not compile Hello, FYI, Compiling for Pocket PC 2003 stopped to work since version 3.7.1 and 3.7.2. So using 3.6.23.1 for now until it gets fixed. 1>Compiling... 1>sqlite3.c 1>..\..\Common\sqlite3\sqlite3.c(30471) : error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier 1>..\..\Common\sqlite3\sqlite3.c(30478) : warning C4013: 'UnlockFileEx' undefined; assuming extern returning int Br, Joachim Wickman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stitching together Text Files into a New Database
1. Define a separate file in SQLite for each campaign, with the three columns you need: CAMPAIGN1 Time number (assuming the values are always numbers; use TEXT if they contain a mix of letters/numbers) TagA number TagB number CAMPAIGN2 Time TagA TagB Etc. 2. Import each of your text files into the appropriate SQLite table. N.B. How are columns demarcated in your text files? Tabs? Spaces? Commas? You may need to clean the text files up before importing so that a single character is the column delimiter. 3. Create an amalgamation table where all of the data from the separate campaign tables can later be merged: CAMPAIGNS id INTEGER PRIMARY KEY AUTOINCREMENT, Source text Time number TagA number TagB number 4. After you have imported the individual campaign text files into their respective CAMPAIGN# tables, you can copy the data from each of those tables into your amalgamated CAMPAIGNS table by executing this query: insert into CAMPAIGNS (source, time, TagA, TagB) select 'C1' as source, time, tagA, TagB from CAMPAIGN1 5. Repeat step #4 for each CAMPAIGN# table, changing the [source] column value in your select-clause : 'C1', 'C2','C3', etc. At the end of the process, your CAMPAIGNS table will have five columns and look like this (hypothetical data): id source time taga tagb 1 C11231000 199 . . . 123478 C7 188 4567885 You can afterwards create indexes on the columns to speed up queries. E.g. you might want an index on source if you frequently need to ask a question about the rows from the a particular campaign. There are a variety of GUI tools available for SQLite. The one I use most often is a plug-in for Firefox and is found here: http://code.google.com/p/sqlite-manager/ Regards Tim Romano Swarthmore PA On Wed, Aug 25, 2010 at 8:42 AM, Lorenzo Isella wrote: > Dear All, > I am quite new to databases in general and sqlite in particular. > I have a number of data files which are nothing else than text files with a > pretty simple simple structure: there are only 3 columns of integer numbers, > something along these lines > > 123 1000 199 > 123 1100 188 > 125 800 805 > > and so on. > The first column contains only non-decreasing times. > Each of these text files corresponds to a different data collection > campaign (let us call them A,B,C etc...). > I would like (with a minimal effort) to merge them into an sqlite database > where each column now has a name (time, ID tag A, ID tag B) and each record > is also marked according to its original dataset (i.e. looking at any entry, > I must be able to tell the the original data file it belongs to). > Any suggestion is really appreciated > > Lorenzo > > P.S.: of course in the future I may get some new datafiles to merge, hence > it is important that new data can be added effortlessly. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stitching together Text Files into a New Database
Lorenzo Isella writes: [...] if you've no idea where a db can help you and if you don't have time to investigate this and the possibilities a db offers you my suggestion is: import your 3 or 5 files into a spreadsheet, add a column for your different campaign and that's it (time: ~ 1min or less per file) Oliver ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Stitching together Text Files into a New Database
Dear All, I am quite new to databases in general and sqlite in particular. I have a number of data files which are nothing else than text files with a pretty simple simple structure: there are only 3 columns of integer numbers, something along these lines 123 1000 199 123 1100 188 125 800 805 and so on. The first column contains only non-decreasing times. Each of these text files corresponds to a different data collection campaign (let us call them A,B,C etc...). I would like (with a minimal effort) to merge them into an sqlite database where each column now has a name (time, ID tag A, ID tag B) and each record is also marked according to its original dataset (i.e. looking at any entry, I must be able to tell the the original data file it belongs to). Any suggestion is really appreciated Lorenzo P.S.: of course in the future I may get some new datafiles to merge, hence it is important that new data can be added effortlessly. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.7.x for Pocket PC 2003 SDK (VS2005) does not compile
Hello, FYI, Compiling for Pocket PC 2003 stopped to work since version 3.7.1 and 3.7.2. So using 3.6.23.1 for now until it gets fixed. 1>Compiling... 1>sqlite3.c 1>..\..\Common\sqlite3\sqlite3.c(30471) : error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier 1>..\..\Common\sqlite3\sqlite3.c(30478) : warning C4013: 'UnlockFileEx' undefined; assuming extern returning int Br, Joachim Wickman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users