[sqlite] Direct row insert from C (without SQL) / Database speed

I'm new in using SQLite and have some questions before I start implementing the SQL lite in a project. Is there a way to insert a row of data into a table without having to convert the data to SQL ? I need to store 1024 point into a row, but converting it first to text and then let the

[sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

Hello, I'm basically a newbie, but have been plunged into not just having correct code but having to optimize a particular query. The following query works but is very slow on my database with millions of entries in one of the tables. SELECT regions.id, TOTAL(reads.data) FROM regions LEFT

Re: [sqlite] Direct row insert from C (without SQL) / Database speed

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark Flipphi wrote: > Is there a way to insert a row of data into a table without having to > convert the data to SQL ? Lookup bindings - using functions like sqlite3_bind_int: http://sqlite.org/c3ref/bind_blob.html > Can we create a object with

Re: [sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

On 9 Jun 2009, at 7:12am, Elizabeth Purdom wrote: > SELECT regions.id, TOTAL(reads.data) FROM regions LEFT OUTER JOIN > reads ON > ( regions.chr = reads.chr AND regions.strand=reads.strand > AND regions.start<=reads.start AND regions.stop>=reads.stop > ) > GROUP BY regions.id ORDER BY

Re: [sqlite] trying to optimize left outer join for large data set (multiple indices needed?)

Hi, at the first sight i'd suggest that you reorder the rows of your index : it is most likely that chr and strand will have many equal values in your example - especially chr. When chr is the first field of your Index than the path to find first differences in the btree to find the matching

[sqlite] Reducing the size of executable linked with sqlite3.c

Hi, I am using the "Amalgamation" version of SQLite. I wanted to know the compile time options (if any) to reduce the size of the executable that is linked with sqlite3.c file. Regards, chandan ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Order by term not in result set

Hi, I get the following error with SQLite 3.6.1 "1st ORDER BY term does not match any column in the result set" >From the following query "SELECT id, url, selected, FROM db1.test UNION SELECT id, url, selected, FROM db2.test ORDER BY name ASC, id DESC LIMIT 100" However the equivalent

Re: [sqlite] Order by term not in result set

Hi, the column name in the order by - clause "name" has to match one of the columns of the select statement, because it is a union. Your columns are "id", "url" ad "selected", none of which is "name". Obviously, your table does contain a column named "name", but because of the union this can

[sqlite] sqlite jdbc and rcp Eclipse

I everybody! Has anyone of you have never exerienced the integration of sqlite jdbc (zentus v.054) in an application Eclipse RCP (Ganymede)? I've tried many ways found on internet, but even the classical one (generating a plugin starting from .jar and including this plugin in my rcp application)

Re: [sqlite] sqlite jdbc and rcp Eclipse

> Has anyone of you have never exerienced the integration of sqlite jdbc > (zentus v.054) in an application Eclipse RCP (Ganymede)? Nope, but i've been using SQLite through JDBC on Netbeans recently. > I've tried many ways found on internet, but even the classical one > (generating a plugin

[sqlite] Virtual table or temp table, which is faster?

I am working on a web based kiosk system that displays images to 1 to 50, maybe 100 viewing stations. Currently I am using the file system (Windows) to store the images and to do lookups on the images. At one point I need to do a join between the images and a table (containing details about

Re: [sqlite] Working with a new DB every 5 minutes

Simon Slavin wrote: > On 8 Jun 2009, at 8:07pm, Mohit Sindhwani wrote: > > >> I'm having a problem that I'm trying to find an elegant solution >> to. I >> have a database that stores real-time information - this information >> is >> replaced by new values every 5 minutes and has about

Re: [sqlite] Slow Transaction Speed?

We are happy with the results using JFS on AIX and SUSE Linux. Also no concerns with EXT3 on various Linuxes. We have learned to avoid Windows. Christian Smith wrote: > On Wed, May 27, 2009 at 08:05:00AM -0400, pyt...@bdurham.com wrote: > >> >> In your experience, which Linux file system(s)

Re: [sqlite] synchronizing sqlite local data base to a remote data base

An SQL database is just a file. A copy will sync it, and so will a process like rsync. Real time synchronization requires that you implement some form of daemon. Mohey Eldin Hamdy wrote: > Hey got a question, > > I am trying to synchronize an sqlite local data base with a remote mssql > data

Re: [sqlite] synchronizing sqlite local data base to a remote data base

I missed the mssql reference. Disregard my suggestion to copy files. You could log the SQL used to modify the DBs and pass it between DBs...Updating Sqlite would require a daemon for real time operation. Simon Slavin wrote: > On 8 Jun 2009, at 7:30pm, Mohey Eldin Hamdy wrote: > > >> I am

Re: [sqlite] Direct row insert from C (without SQL) / Database speed

Use sqlite prepare and bind. Mark Flipphi wrote: > I'm new in using SQLite and have some questions before I start > implementing the SQL lite in a project. > > Is there a way to insert a row of data into a table without having to > convert the data to SQL ? > I need to store 1024 point into a

Re: [sqlite] Reducing the size of executable linked with sqlite3.c

http://www.sqlite.org/compile.html There are options you can use to disable/remove unused features. Some can give significant size savings. (I did this on Windows, YMMV.) Unfortunately, you can't just use these with the amalgamated source. You will probably have to rebuild it. It's not

Re: [sqlite] Working with a new DB every 5 minutes

John Elrick wrote: > Simon Slavin wrote: > >> On 8 Jun 2009, at 8:07pm, Mohit Sindhwani wrote: >> >> >> >>> I'm having a problem that I'm trying to find an elegant solution >>> to. I >>> have a database that stores real-time information - this information >>> is >>> replaced by new

Re: [sqlite] Order by term not in result set

On 9 Jun 2009, at 11:18am, Martin Engelschalk wrote: > the column name in the order by - clause "name" has to match one of > the > columns of the select statement, because it is a union. > Your columns are "id", "url" ad "selected", none of which is "name". > > Obviously, your table does

Re: [sqlite] sqlite jdbc and rcp Eclipse

I'm not working with standard java app, I succedded in creating that. But wasn't what I had to do. I've to integrate sqlite into an RCP ECLIPSE application. To do that I've to use one of the standard method of import "external jar" into the application that is more tricky than simply include jar.

Re: [sqlite] Order by term not in result set

On Tue, 9 Jun 2009, Simon Slavin wrote: >> SELECT id, url, selected, name FROM db1.test UNION >> SELECT id, url, selected, name FROM db2.test >> ORDER BY name ASC, id DESC LIMIT 100" > > I would add to Marin's answer that reading the above, I have no idea > which database the column 'name'

[sqlite] Problem with sqlite3_interrupt on Windows

When I run sqlite3_interrupt, it doesn't close existing file handles, making further searches tricky. So I wrote code which clears all normal file handles (fopen in shell.c), but... How do I close the database file too? It's not opened using fopen, but with CreateFileA (in winOpen in os_win.c)

[sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

Hi All, What was the problem with the SQLite library is builded with DSQLITE_THREADSAFE=1 but the application is using multiple threads with the same connection. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Problem with sqlite3_interrupt on Windows

On 10/06/2009 4:40 AM, Jeremy Smith wrote: > When I run sqlite3_interrupt, it doesn't close existing file handles, > making further searches tricky. Which handles? How do you know? What does "tricky" mean -- "difficult but I can cope with it" or "causes an error" (if so, which?) or something

Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham wrote: > > >Hi All, >What was the problem with the SQLite library is builded >with DSQLITE_THREADSAFE=1 but the application is using >multiple threads with the same connection. >Thanks, >JP Joannek, I think this same

Re: [sqlite] SQLite Version 2.1

Hi Mihai! I tested all versions of sqlite browser already - without success. The next step will be to compile the old sources of sqlite. Or is there a download repository of older sqlite versions? Then, I will be able to develop a little tool for reading out the contents of this older 2.X

Re: [sqlite] SQLite Version 2.1

That is not what I want. I want to develop a little tool for later investigations too, not only for this one. Marcus Haßmann Simon Slavin schrieb: > On 8 Jun 2009, at 1:50am, Shane Harrelson wrote: > > >> http://www.sqlite.org/sqlite-2.8.17.tar.gz I think is the oldest >> version still

[sqlite] sqllogictest tool - please help :-)

Hi , I wont to use the "sqllogictest" program. I'm using SQLite. I have read the "About Sqllogictest" and didn't fully understand how to work with it. Can you please help on how I can use it? I will be very grateful for your help. - Where are the files of the program? is it those three files?

Re: [sqlite] Problem with sqlite3_interrupt on Windows

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jeremy Smith wrote: > When I run sqlite3_interrupt, it doesn't close existing file handles, Why would it? sqlite3_interrupt sets a flag on the sqlite3 handle. Various operations periodically check the flag and if it is true then error out with

Re: [sqlite] sqllogictest tool - please help :-)

On Jun 9, 2009, at 10:38 AM, tsachi ofir wrote: > Hi , > I wont to use the "sqllogictest" program. Let's start with the basics. Why do you think you want to use this program? What do you think it will accomplish for you? > > I'm using SQLite. > > I have read the "About Sqllogictest" and

[sqlite] temp tables or virtual tables, which is faster?

I am working on a web based kiosk system that displays images to 1 to 50, maybe 100 viewing stations. Currently I am using the file system (Windows) to store the images and to do lookups on the images.  At one point I need to do a join between the images and a table (containing details about some

Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

Sorry Couldn't locate the email about Compite with DSQLITE_THREADSAFE=1 bu the application has multiple threads using the same connection? Would you pleas direct me to any document that has this info. Thanks, JP From: Kees Nuyt To:

Re: [sqlite] Problem with sqlite3_interrupt on Windows

John Machin wrote: > On 10/06/2009 4:40 AM, Jeremy Smith wrote: > >> When I run sqlite3_interrupt, it doesn't close existing file handles, >> making further searches tricky. >> > > Which handles? How do you know? What does "tricky" mean -- "difficult > but I can cope with it" or "causes

Re: [sqlite] Problem with sqlite3_interrupt on Windows

Jeremy Smith wrote: > John Machin wrote: >> On 10/06/2009 4:40 AM, Jeremy Smith wrote: >> >>> When I run sqlite3_interrupt, it doesn't close existing file >>> handles, making further searches tricky. >>> >> >> Which handles? How do you know? What does "tricky" mean -- "difficult >> but I can cope

Re: [sqlite] Problem with sqlite3_interrupt on Windows

On 10/06/2009 9:02 AM, Igor Tandetnik wrote: > Jeremy Smith wrote: >> John Machin wrote: >>> On 10/06/2009 4:40 AM, Jeremy Smith wrote: >>> When I run sqlite3_interrupt, it doesn't close existing file handles, making further searches tricky. >>> Which handles? How do you know? What

Re: [sqlite] temp tables or virtual tables, which is faster?

> I have now learned about the concept of virtual tables.  Am I better > of with my current approach because I can index the files in the temp > table, or would I be better off using a virtual table to scan the hard > drive for the images? Depends on how often you must reindex and how critical it

Re: [sqlite] trying to optimize left outer join for large data set, (multiple indices needed?)

Hi, I appreciate the tips about timing the indexing and the order of the variables. However, I am particularly trying to address the following documentation on the SQLite page entitled 'The SQLite Query Optimizer Overview' (http://www.sqlite.org/optoverview.html). There they say: > If an

Re: [sqlite] temp tables or virtual tables, which is faster?

On Tue, Jun 9, 2009 at 10:17 PM, Alexandre Courbot wrote: > > > I have now learned about the concept of virtual tables.  Am I better > > of with my current approach because I can index the files in the temp > > table, or would I be better off using a virtual table to scan the

Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

On Jun 10, 2009, at 5:23 AM, Joanne Pham wrote: > Sorry Couldn't locate the email about Compite with > DSQLITE_THREADSAFE=1 bu the application has multiple threads using > the same connection? > Would you pleas direct me to any document that has this info. > Thanks, > JP >

Re: [sqlite] temp tables or virtual tables, which is faster?

> Ok, here is what I am doing right now.  The idea is to return all the > images in the folder to the frontend and indicate which ones the > current customer has in his/her favorite's. If your images do not change, I guess you would get good performances by indexing ImageId and using the temp

Re: [sqlite] trying to optimize left outer join for large data set, (multiple indices needed?)

On 10 Jun 2009, at 5:15am, Elizabeth Purdom wrote: > This seems to > also imply to me that if I have inequalities in my query on c and d, > that a index (a,b,c,d) would be *used* but only for the parts dealing > with a,b, and c and that d would be manually scanned over. So if I do > EXPLAIN

Re: [sqlite] sqlite jdbc and rcp Eclipse

Hello Enrico, just to be sure: have you used the -clean option when restarting eclipse? cheers Jürgen Schwitalla On Tue, 9 Jun 2009, Enrico Piccinini wrote: I'm not working with standard java app, I succedded in creating that. But wasn't what I had to do. I've to integrate sqlite into an