Re: RE: [sqlite] about SQLite Explore wit h dll(3.2.7) problem
thanks for your good tool,I test it more,it cause by chinese character in the path,there maybe some mistake in the dll version,in my application,I complie source directly,it works. and more suggestion for this tool,there need some more task to do on the behavor,such as when closing the database,any info on the screen should be cleared thanks again > hi. I am the author of this program. I have not seen what you mentioned, but > in any case, I am about to release a > new major release 2.0 in a few days, which has been tested with 3.2.7 and > does not seem to have any problems. > > > > From: Huanghongdong [mailto:[EMAIL PROTECTED] > Sent: Wed 28-Sep-05 5:31 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] about SQLite Explore with dll(3.2.7) problem > > > > I notic if I change the SQLiteExplore's dll to version 3.2.7 and use it to > open the database wich created by old version dll then I get the a erro > message,it saying something like "can't open the database file",anyone have > idea? > > > > > >
[sqlite] Delay during insert - Was: [sqlite] How to cross compile sqlite-3.2.7 for arm9 s3c2410?
Hi Ron, > I have noticed that when I create a database and do a simple INSERT, (using > the sqlite3 binary), there is an appreciable delay (1-2 seconds) before > sqlite3 returns to the prompt. When doing SELECT there is no delay. Mmm, can't say that I have seen any strange delays on the arm platform, albeit that it obviously *is* a bit slower than i386 ;). But seriously, given that we're talking about a newly created db, a fairly simple table with no 'weird' triggers etc, then a plain insert is not that much work and should return almost instantly. It might be due to some locking issues, but you didn't mention accessing the db from multiple processes / threads. Also, your filesystem / kernel might have issues (re)opening the file readwrite. You could use something like strace to see what exactly is happening during the delay, given that this tool is available on the platform, but if it isn't you could built it. > I have not noticed this on my Windows or Linux machines (x86). Me neither. > Is this possibly because of not using transactions? Not likely. > The device is storing the database on a hard-disk, so it shouldn't > have to do with paged RAM or something. I wouldn't jump to the same conclusion when it was anything else than a simple insert.., but anyway. The only additional thing that comes to mind is that you were seeing a select coming from cache and that the insert triggered a powerup of the drive..? Again, something like strace could tell you what's happening. > Thanks for any suggestions, and thanks again for the good instructions! You're welcome, good luck ! -- Best, Frank.
[sqlite] Accessing SQLite from VB.NET 2002
I've been trying to access SQLite3 from VB.NET 2002 using SQLite ADO.NET data providers and I keep getting the following error message: "An unhandled exception of type 'System.IO.FileNotFoundException' occurred in system.windows.forms.dll Additional information: File or assembly name System.Data, or one of its dependencies, was not found." I've imported the system.Data namespace but there's no change. I also tried importing sqlite3.dll into my project but VB.NET 2002 will not include it because it is not recognising sqlite3.dll as a DLL or COM component. Basically, how do I embed SQLite in my VB.NET application so I can query it? K
Re: [sqlite] Tool to load a database and see all tables and fields.
Is there a version for Linux? So far I have only found slqbrowser being useful/free free Linux. Sqlitespy for windows looks pretty nice, btw. Eno Ralf Junker wrote: Hello Koen, have you tried SQLiteSpy? It has a nice schema tree and multiple, tabbed SQL edits to execute commands and queries. Data retrieval is very fast, the memory requirements low. SQLiteSpy is freeware for personal use. Commercial users are kindly asked for a small dontaion. Download your copy from: http://www.yunqa.de/delphi/sqlitespy/ Regards, Ralf I am wondering if a easy tool exsists for SQLite, that just lists the tables and fields in a database. I have tried sqlitemanager, but I didn't really liked it. -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
Re: [sqlite] How to cross compile sqlite-3.2.7 for arm9 s3c2410?
On Tue, September 27, 2005 7:39, Frank van Vugt said: > There's some info in the wiki on it, but basically the steps are: Hello, Frank - I successfully created an ARM sqlite3 (3.2.7) and it is running on my Archos PMA400 - thanks! I have noticed that when I create a database and do a simple INSERT, (using the sqlite3 binary), there is an appreciable delay (1-2 seconds) before sqlite3 returns to the prompt. When doing SELECT there is no delay. I have not noticed this on my Windows or Linux machines (x86). Is this possibly because of not using transactions? It seems a bit extreme. The device is storing the database on a hard-disk, so it shouldn't have to do with paged RAM or something. Thanks for any suggestions, and thanks again for the good instructions! -- Reva Forth for Linux and Windows -- http://ronware.org/reva/ GPG key fingerprint: 8130 734C 69A3 6542 0853 CB42 3ECF 9259 AD29 415D
RE: [sqlite] about SQLite Explore with dll(3.2.7) problem
hi. I am the author of this program. I have not seen what you mentioned, but in any case, I am about to release a new major release 2.0 in a few days, which has been tested with 3.2.7 and does not seem to have any problems. From: Huanghongdong [mailto:[EMAIL PROTECTED] Sent: Wed 28-Sep-05 5:31 PM To: sqlite-users@sqlite.org Subject: [sqlite] about SQLite Explore with dll(3.2.7) problem I notic if I change the SQLiteExplore's dll to version 3.2.7 and use it to open the database wich created by old version dll then I get the a erro message,it saying something like "can't open the database file",anyone have idea?
Re: [sqlite] Tool to load a database and see all tables and fields.
spudse bud wrote: I always was a big fan of phpmyadmin because when php/mysql errored I could check table/field names etc to see where my script goes wrong. I am wondering if a easy tool exsists for SQLite, that just lists the tables and fields in a database. I have tried sqlitemanager, but I didn't really liked it. Have you checked out the PHP section of http://www.sqlite.org/cvstrac/wiki?p=SqliteTools ? There's a listing for phpSQLiteAdmin, which is supposed to be patterned after phpmyadmin, though it appears not to be under active development.
Re: [sqlite] Tool to load a database and see all tables and fields.
Hello Koen, have you tried SQLiteSpy? It has a nice schema tree and multiple, tabbed SQL edits to execute commands and queries. Data retrieval is very fast, the memory requirements low. SQLiteSpy is freeware for personal use. Commercial users are kindly asked for a small dontaion. Download your copy from: http://www.yunqa.de/delphi/sqlitespy/ Regards, Ralf >I am wondering if a easy tool exsists for SQLite, that just lists the tables >and fields in a database. I have tried sqlitemanager, but I didn't really >liked it.
Re: [sqlite] compression
On 9/28/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: > > > This is from my Gentoo 2005.1 Linux system (home) with whatever packages > it installed a few days ago. > At work I use FreeBSD and I've not used loopback devices there, but the > FreeBSD Handbook (online for free) shows how to do it. > > > I'm using gentoo at home too so that won't be problem :) -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] Tool to load a database and see all tables and fields.
On my site i have a tool, www.hellobasic.com - Original Message - From: "Eno Thereska" <[EMAIL PROTECTED]> To: Sent: Wednesday, September 28, 2005 4:36 PM Subject: Re: [sqlite] Tool to load a database and see all tables and fields. What about sqlitebrowser? That's what I currently use. Eno spudse bud wrote: I always was a big fan of phpmyadmin because when php/mysql errored I could check table/field names etc to see where my script goes wrong. I am wondering if a easy tool exsists for SQLite, that just lists the tables and fields in a database. I have tried sqlitemanager, but I didn't really liked it. Greetings koen (spudse). -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
Re: [sqlite] compression
Jay Sprenkle wrote: On 9/28/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote: Your third statement is not true. On Linux (and FreeBSD, but FreeBSD does not have Reiser as far as I know) you can treat a regular file as if it were a filesystem and mount that fiel system via the "loop back" device. You can mount an ISO image file as an actual CD, for instance. Cool! Thanks for letting us know :) I should have mentioned the obvious though... the file must be an image of a valid file system. for example, the following will fail: dd if=/dev/zero of=file bs=4096 count=1024 losetup /dev/loop0 file mount /dev/loop0 /mnt/xxx However, the following should work: dd if=/dev/zero of=file bs=4096 count=1024 losetup /dev/loop0 file mke2fs /dev/loop0 mount /dev/loop0 /mnt/xxx You can even encrypt the entire filesystem over loop back: dd if=/dev/zero of=blob bs=1M count=1024 losetup -e AES256 /dev/loop0 blob mke3fs /dev/loop0 blob mount /dev/loop0 /mnt/crypto as usual, do a "man" on "losetup". This is from my Gentoo 2005.1 Linux system (home) with whatever packages it installed a few days ago. At work I use FreeBSD and I've not used loopback devices there, but the FreeBSD Handbook (online for free) shows how to do it.
Re: [sqlite] Case-sensitive table names
On 9/28/05, Ivo Kulms <[EMAIL PROTECTED]> wrote: > Sorry, I was a little bit unclear. > > My problem is that I have the need to treat "table1" and "TABLE1" as two > single entities. You need to treat table1 and TABLE1 as *separate* or the same? And I apologize -- at least with the version of sqlite3 that I have (3.2.1/3.2.2), "table1" and "TABLE1" are in fact treated the same. They're not *supposed* to be, although table1 and TABLE 1 are supposed to be. In code: SELECT table1 ... SELECT TABLE1 These are the same and should be treated the same. SELECT "table1" SELECT "TABLE1" These are not the same and shouldn't be treated the same. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] compression
Christian Smith wrote: On Wed, 28 Sep 2005, Sid Liu wrote: Is there a possibility that this Reiser 4 be used on a file, rather than a file system? Hopefully on Windows? Reiser FS is a filesystem. It manages files. So it cannot be used on a file. Your third statement is not true. On Linux (and FreeBSD, but FreeBSD does not have Reiser as far as I know) you can treat a regular file as if it were a filesystem and mount that fiel system via the "loop back" device. You can mount an ISO image file as an actual CD, for instance. Years ago I imaged all of my old DOS floppies. I access them via the loop back device now. In theory, you can do that with any file system that can use a block device (ntfs, iso9660, ext3, etc...) but not with nfs, smbfs, proc, etc... dd if=/dev/fd0 of=floppy_file.img # Eject floppy, don't need it anymore. losetup /dev/loop0 floppy_file.img mount -t vfat /dev/loop0 /mnt/floppy ls -l /mnt/floppy Windows NTFS already has compressed files. Right click a file or directory in exporer, select propeties, then advanced attributes. You can turn on compression there. Don't know how to do it from the command line, though.
AW: [sqlite] Case-sensitive table names
Sorry, I was a little bit unclear. My problem is that I have the need to treat "table1" and "TABLE1" as two single entities. ivo -Ursprüngliche Nachricht- Von: Austin Ziegler [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 28. September 2005 16:21 An: sqlite-users@sqlite.org; [EMAIL PROTECTED] Betreff: Re: [sqlite] Case-sensitive table names On 9/28/05, Ivo Kulms <[EMAIL PROTECTED]> wrote: > Hi there, > > Is it possible in sqlite to work with case-sensitive table- and column > names? > > (Sorry if this has been already answered somewhere in the > documentation, but I did not seem to find it.) Fortunately, sqlite is (mostly) standards based and if you want this particular abomination, you put your table and column names in double quotes. SELECT "SillyColumn" FROM "SillyTable" -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] compression
On Wed, 28 Sep 2005, Sid Liu wrote: >Is there a possibility that this Reiser 4 be used on a >file, rather than a file system? Hopefully on Windows? Reiser FS is a filesystem. It manages files. So it cannot be used on a file. Windows NTFS already has compressed files. Right click a file or directory in exporer, select propeties, then advanced attributes. You can turn on compression there. Don't know how to do it from the command line, though. > >--- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > >> If you're on Linux read about the Reiser 4 file >> system. >> They found they could compress the entire file >> system on the fly and achieve >> higher performance as well. Most CPU's can compress >> and move data faster >> because they make up the difference on the slow I/O >> channels to hard disks. >> Might be a much easier solution >> >> >> On 9/24/05, Martin Pfeifle <[EMAIL PROTECTED]> >> wrote: >> > >> > Hello, >> > does anybody know whether it is possible to >> compress >> > sqlite data on the page level. If I compress the >> > sqlite database file with zlib I get very high >> > compression rates due to the character of the >> stored >> > data. >> > I think this problem is related to the problem of >> > using encrypted databases. Perheps it is possible >> just >> > to exchange the encryption function call by a zlib >> > compression call. >> > Integrating such a call simply into the read and >> write >> > functions in the file os_win.c does not work. >> > Can anybody help me, or give me a hint? >> > Ciao Martin >> > >> > >> > >> > >> >___ >> > Was denken Sie über E-Mail? Wir hören auf Ihre >> Meinung: >> > http://surveylink.yahoo.com/wix/p0379378.aspx >> > >> >> >> >> -- >> --- >> The Castles of Dereth Calendar: a tour of the art >> and architecture of >> Asheron's Call >> http://www.lulu.com/content/77264 >> > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Tool to load a database and see all tables and fields.
What about sqlitebrowser? That's what I currently use. Eno spudse bud wrote: > I always was a big fan of phpmyadmin because when php/mysql errored I could > check table/field names etc to see where my script goes wrong. > > I am wondering if a easy tool exsists for SQLite, that just lists the tables > and fields in a database. I have tried sqlitemanager, but I didn't really > liked it. > > Greetings koen (spudse). > -- Eno Thereska - Carnegie Mellon University Parallel Data Laboratory CIC Building Cube 2221-D 5000 Forbes Ave Pittsburgh, PA 15213 Tel: 412-268-5908
[sqlite] about SQLite Explore with dll(3.2.7) problem
I notic if I change the SQLiteExplore's dll to version 3.2.7 and use it to open the database wich created by old version dll then I get the a erro message,it saying something like "can't open the database file",anyone have idea?
[sqlite] Tool to load a database and see all tables and fields.
I always was a big fan of phpmyadmin because when php/mysql errored I could check table/field names etc to see where my script goes wrong. I am wondering if a easy tool exsists for SQLite, that just lists the tables and fields in a database. I have tried sqlitemanager, but I didn't really liked it. Greetings koen (spudse).
Re: [sqlite] compression
Is there a possibility that this Reiser 4 be used on a file, rather than a file system? Hopefully on Windows? --- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > If you're on Linux read about the Reiser 4 file > system. > They found they could compress the entire file > system on the fly and achieve > higher performance as well. Most CPU's can compress > and move data faster > because they make up the difference on the slow I/O > channels to hard disks. > Might be a much easier solution > > > On 9/24/05, Martin Pfeifle <[EMAIL PROTECTED]> > wrote: > > > > Hello, > > does anybody know whether it is possible to > compress > > sqlite data on the page level. If I compress the > > sqlite database file with zlib I get very high > > compression rates due to the character of the > stored > > data. > > I think this problem is related to the problem of > > using encrypted databases. Perheps it is possible > just > > to exchange the encryption function call by a zlib > > compression call. > > Integrating such a call simply into the read and > write > > functions in the file os_win.c does not work. > > Can anybody help me, or give me a hint? > > Ciao Martin > > > > > > > > > ___ > > Was denken Sie über E-Mail? Wir hören auf Ihre > Meinung: > > http://surveylink.yahoo.com/wix/p0379378.aspx > > > > > > -- > --- > The Castles of Dereth Calendar: a tour of the art > and architecture of > Asheron's Call > http://www.lulu.com/content/77264 >
Re: [sqlite] Case-sensitive table names
On 9/28/05, Ivo Kulms <[EMAIL PROTECTED]> wrote: > Hi there, > > Is it possible in sqlite to work with case-sensitive table- and column > names? > > (Sorry if this has been already answered somewhere in the documentation, but > I did not seem to find it.) Fortunately, sqlite is (mostly) standards based and if you want this particular abomination, you put your table and column names in double quotes. SELECT "SillyColumn" FROM "SillyTable" -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] Rewriting a query
> What happens if you create the index on sCommunityID only? Does > it still do the full table scan? A bit difficult to drop the (sCommunityID, sTransactionID) index, as it's the primary key. > Also, don't overlook using UNION or UNION ALL, ugly as they > can be. Maybe something like this could be used to avoid creating > a very small temporary table: > > SELECT ... FROM (SELECT ... UNION SELECT ... UNION SELECT ...), > TransactionList WHERE ... Hmmm. Something like this works: SELECT sCommID, (SELECT sTransactionID FROM TransactionList WHERE sCommunityID = sCommID ORDER BY sTransactionID Desc Limit 1) as MaxID >From (SELECT 'a03061bFi' As sCommID UNION ALL SELECT 'a03064KDy' As sCommID UNION ALL SELECT 'a03068QhK' As sCommID) The "explain" output shows that the Community table isn't being touched now, and in fact it creates a temp table with the hard-coded values in it, similar to "IN". So the final query may run faster (it's certainly very fast with the data I have). I might be working with a few thousand communities. Is there a limit to the size of queries? In that case the "IN" clause would be better as it uses less text. Hugh
[sqlite] Case-sensitive table names
Hi there, Is it possible in sqlite to work with case-sensitive table- and column names? (Sorry if this has been already answered somewhere in the documentation, but I did not seem to find it.) Cheers, ivo
Re: [sqlite] Rewriting a query
What happens if you create the index on sCommunityID only? Does it still do the full table scan? Also, don't overlook using UNION or UNION ALL, ugly as they can be. Maybe something like this could be used to avoid creating a very small temporary table: SELECT ... FROM (SELECT ... UNION SELECT ... UNION SELECT ...), TransactionList WHERE ... --- Hugh Gibson <[EMAIL PROTECTED]> wrote: > I have this query: > > SELECT sCommunityID, max(sTransactionID) > FROM TransactionList > WHERE sCommunityID in ('a03061bFi','a03064KDy', 'a03068QhK') > GROUP BY sCommunityID > > There is an index on (sCommunityID, sTransactionID) > > This forces a table scan (perhaps improved in 3.2.6). > > I can use a LIMIT 1 clause like this, but only with one community ID: > > SELECT sCommunityID, sTransactionID > FROM TransactionList > WHERE sCommunityID = 'a03061bFi' > ORDER BY sTransactionID Desc LIMIT 1 > > Is there a way of getting the latter to work with multiple sCommunityIDs? > > Hugh __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
Re: [sqlite] Rewriting a query
The following works for me. The Community table has only one entry per community ID so it's fast to look up. SELECT sCommunityID, (SELECT sTransactionID FROM TransactionList WHERE sCommunityID = Community.sCommunityID ORDER BY sTransactionID Desc Limit 1) as MaxID >From Community WHERE sCommunityID In ('a03061bFi','a03064KDy', 'a03068QhK') Is there any way of just supplying a list of records without having to use another table? I know I can use parameterised queries but I would like to be able to use a single query to get the data, as it feels like it should be possible! Hugh
[sqlite] Rewriting a query
I have this query: SELECT sCommunityID, max(sTransactionID) FROM TransactionList WHERE sCommunityID in ('a03061bFi','a03064KDy', 'a03068QhK') GROUP BY sCommunityID There is an index on (sCommunityID, sTransactionID) This forces a table scan (perhaps improved in 3.2.6). I can use a LIMIT 1 clause like this, but only with one community ID: SELECT sCommunityID, sTransactionID FROM TransactionList WHERE sCommunityID = 'a03061bFi' ORDER BY sTransactionID Desc LIMIT 1 Is there a way of getting the latter to work with multiple sCommunityIDs? Hugh
[sqlite] Re: Patch proposal
2005/9/28, Vivien Malerba <[EMAIL PROTECTED]>: > Hi! > > Here is a small patch which modifies the returned data set of the > PRAGMA table_info() to add a new column named "auto_inc" which > contains 1 if the corresponding field has the AUTO INCREMENT in its > definition and 0 otherwise. > > Could it be included in a future version of SQLite? > > Regards, > > Vivien > Forgot the patch!
[sqlite] Patch proposal
Hi! Here is a small patch which modifies the returned data set of the PRAGMA table_info() to add a new column named "auto_inc" which contains 1 if the corresponding field has the AUTO INCREMENT in its definition and 0 otherwise. Could it be included in a future version of SQLite? Regards, Vivien
Re: [sqlite] Schemas
On 9/28/05, Vivien Malerba <[EMAIL PROTECTED]> wrote: > 2005/9/28, Firman Wandayandi <[EMAIL PROTECTED]>: > > On 9/27/05, Vivien Malerba <[EMAIL PROTECTED]> wrote: > > > Hi! > > > > > > I'm working on the SQLite interface between the Libgda library and the > > > SQLite database. For the job, I need to know information about the > > > database schema, and I'm using the PRAGMA statements regarding > > > schemas. > > > > > > However I can't find the information about the AUTO INCREMENT > > > attribute of a column. Can anyone help me? > > > > > > > here they are, > > > > http://sqlite.org/datatypes.html > > http://sqlite.org/datatype3.html > > > > I don't mean finding information about how to create AUTO INCREMENT > fields but about how to get that information for each field of an > already existing table in the database (like when using PRAGMA > table_info('table')). > Oh, sorry, you can use SELECT * FROM sqlite_master and find yourself where the auto increment column form "sql" column with "AUTOINCREMENT" as the wild card, I dunno another solution. > Thanks, > > Vivien > Hope help you, -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
Re: [sqlite] Schemas
2005/9/28, Firman Wandayandi <[EMAIL PROTECTED]>: > On 9/27/05, Vivien Malerba <[EMAIL PROTECTED]> wrote: > > Hi! > > > > I'm working on the SQLite interface between the Libgda library and the > > SQLite database. For the job, I need to know information about the > > database schema, and I'm using the PRAGMA statements regarding > > schemas. > > > > However I can't find the information about the AUTO INCREMENT > > attribute of a column. Can anyone help me? > > > > here they are, > > http://sqlite.org/datatypes.html > http://sqlite.org/datatype3.html > I don't mean finding information about how to create AUTO INCREMENT fields but about how to get that information for each field of an already existing table in the database (like when using PRAGMA table_info('table')). Thanks, Vivien