Re: [sqlite] When to close a db?

Roger, I am 100% Windows:( I agree with you that it is "99 point lots of nines percent" my program, not SQLite. Especially because I added the SQLITE_DEBUG and it isn't catching anything extra. I am 99.9% sure the issue is NOT calling close on the db twice, I wrapped that in a C++ class so the

Re: [sqlite] optimizing use of extension function

On 21 Jun 2010, at 3:16am, P Kishor wrote: > The above brings up a related issue... At least Pg docs suggest that > LIMIT should always be used with ORDER BY. See > http://developer.postgresql.org/pgdocs/postgres/sql-select.html > > "When using LIMIT, it is a good idea to use an ORDER BY clause

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 9:21 PM, Sam Carleton wrote: > SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED >  WHERE case when instertedon > julianday(@time) >   then findLargeImage(@path, FolderId, ImageId) >   else 0 end; I think Igor wants you to add the

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 9:56 PM, Igor Tandetnik wrote: > Sam Carleton wrote: >> On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote: >> >>> In what way did the statement I gave you, exactly as written, fail to >>> satisfy

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 8:55 PM, Pavel Ivanov wrote: >> I think that is a common assumption, but a wrong one. Think about it >> -- the sql engine has to get the entire result set back before it can >> apply the limit clause. > > Puneet, are you 100% sure about that or are you

Re: [sqlite] When to close a db?

On 21 Jun 2010, at 2:37am, Jay A. Kreibich wrote: > On Sun, Jun 20, 2010 at 11:07:27PM +0100, Simon Slavin scratched on the wall: > >> Instead check the return value. If you get SQLITE_OK then you >> need to close the connection. If you don't, you don't. > > Check the return value, but call

Re: [sqlite] When to close a db?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/20/2010 05:55 PM, Sam Carleton wrote: > Ok, that is what i am doing, checking the value of db and closing if > it has a value. So why might closing the db cause a crash? It is 99 point lots of nines percent certain that you have a

Re: [sqlite] When to close a db?

> The debugger clams that all the values of the db struct are "Error: > expression cannot be evaluated". Which most definitely means that you call sqlite3_close() on the same connection handler twice - that will or will not cause a crash depending on system memory allocator and whether

Re: [sqlite] optimizing use of extension function

Sam Carleton wrote: > On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote: > >> In what way did the statement I gave you, exactly as written, fail to >> satisfy your requirements? > > Igor, > > When I put in EXACTLY what you gave me: > >

Re: [sqlite] optimizing use of extension function

> I think that is a common assumption, but a wrong one. Think about it > -- the sql engine has to get the entire result set back before it can > apply the limit clause. Puneet, are you 100% sure about that or are you just telling your opinion? Just recently there was another thread where

Re: [sqlite] When to close a db?

On Sun, Jun 20, 2010 at 6:07 PM, Simon Slavin wrote: > > On 20 Jun 2010, at 10:17pm, Sam Carleton wrote: > >> I am getting some strange behavior out of my app, which happens to be >> both an Apache module and some Axis2/C Web Services which run under >> Apache.  From time to

Re: [sqlite] When to close a db?

On Sun, Jun 20, 2010 at 05:17:52PM -0400, Sam Carleton scratched on the wall: > I am getting some strange behavior out of my app, which happens to be > both an Apache module and some Axis2/C Web Services which run under > Apache. From time to time, it is VERY inconsistent, when the code > calls

Re: [sqlite] When to close a db?

On Sun, Jun 20, 2010 at 11:07:27PM +0100, Simon Slavin scratched on the wall: > > From reading the documentation, it looks to me like sqlite3_close() > > should be called if *ppDb has a value, irregardless of the result code > > from the sqlite3_open_v2() call. Is there more to it? > > The ppDb

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 7:41 PM, P Kishor wrote: > Once again, did you try Igor's suggestion? Exactly as he suggested? > Don't know if it will work or not, but this I can say -- if there is > one person in the entire SQL world you should listen to very > carefully, it is

Re: [sqlite] where to find an examples of writing a extension?

See extension-functions.c in http://sqlite.org/contrib. On Sat, Jun 19, 2010 at 10:52 PM, Sam Carleton wrote: > I need to add a new where clause to my query, one that checks to see > if the file actually exits.  I am thinking the correct solution is a > user defined

[sqlite] A DLL for my WinXP

Where can I find the DLL file for my winxp? TIA -- This email is for the sole use of the intended recipient and may contain confidential or privileged information. Unauthorized use of its contents is prohibited. If you have received this email in error, please notify the sender immediately via

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 7:53 PM, Simon Slavin wrote: > > On 21 Jun 2010, at 12:41am, P Kishor wrote: > >> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In >> other words, the entire result set is returned, and then it is >> LIMITed. So, the behavior is

Re: [sqlite] When to close a db?

On Jun 20, 2010, at 7:33 PM, Roger Binns wrote: > > That is bad advice! The return code of open is irrelevant. If ppDb > is set > to non-NULL then you need to call sqlite3_close on it. ppDb will > almost > always be set to something. One example of when it is not is

Re: [sqlite] optimizing use of extension function

On 21 Jun 2010, at 12:41am, P Kishor wrote: > iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In > other words, the entire result set is returned, and then it is > LIMITed. So, the behavior is correct. That does agree with what Sam is reporting. However, I am surprised at this

Re: [sqlite] When to close a db?

On Sun, Jun 20, 2010 at 7:37 PM, Simon Slavin wrote: > > On 21 Jun 2010, at 12:33am, Roger Binns wrote: > > > On 06/20/2010 03:07 PM, Simon Slavin wrote: > >> The ppDb value is about allocating memory. It's possible that the file > might be open and no memory allocated.

Re: [sqlite] Question About SQLITE and AIR efficiency

On Sun, Jun 20, 2010 at 6:11 PM, Felipe Aramburu wrote: > I have a query that I can execute in about 150ms in a sqlite tool like > sqlite expert professional that takes 1200ms when I execute the query from > AIR. > > This might be because AIR is using an older version of

Re: [sqlite] Question About SQLITE and AIR efficiency

On Sun, Jun 20, 2010 at 5:18 PM, Simon Slavin wrote: > > On 20 Jun 2010, at 11:11pm, Felipe Aramburu wrote: > >> I have a query that I can execute in about 150ms in a sqlite tool like >> sqlite expert professional that takes 1200ms when I execute the query from >> AIR > >

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 5:16 PM, Simon Slavin wrote: > > On 20 Jun 2010, at 11:08pm, Sam Carleton wrote: > >> Simon, >> >> this is a direct CUT and PASTE from my code: >> >> #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \ >>       "SELECT FolderId, ImageId, instertedon " \ >>      

Re: [sqlite] When to close a db?

On 21 Jun 2010, at 12:33am, Roger Binns wrote: > On 06/20/2010 03:07 PM, Simon Slavin wrote: >> The ppDb value is about allocating memory. It's possible that the file >> might be open and no memory allocated. Or that memory was allocated and the >> file not opened. I forget which. >> >>

Re: [sqlite] When to close a db?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/20/2010 03:07 PM, Simon Slavin wrote: > The ppDb value is about allocating memory. It's possible that the file might > be open and no memory allocated. Or that memory was allocated and the file > not opened. I forget which. > > Instead

Re: [sqlite] Question About SQLITE and AIR efficiency

On 20 Jun 2010, at 11:11pm, Felipe Aramburu wrote: > I have a query that I can execute in about 150ms in a sqlite tool like > sqlite expert professional that takes 1200ms when I execute the query from > AIR What is AIR ? > I am using prepared statements, synchronous connection. Does anyone

Re: [sqlite] optimizing use of extension function

On 20 Jun 2010, at 11:08pm, Sam Carleton wrote: > Simon, > > this is a direct CUT and PASTE from my code: > > #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \ > "SELECT FolderId, ImageId, instertedon " \ > "FROM V_FAVORITES_SELECTED " \ > "WHERE instertedon > julianday(@time) AND

[sqlite] Question About SQLITE and AIR efficiency

I have a query that I can execute in about 150ms in a sqlite tool like sqlite expert professional that takes 1200ms when I execute the query from AIR. I am using prepared statements, synchronous connection. Does anyone have any idea why a query takes 8 times longer in air? Felipe

Re: [sqlite] optimizing use of extension function

Simon, this is a direct CUT and PASTE from my code: #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \ "SELECT FolderId, ImageId, instertedon " \ "FROM V_FAVORITES_SELECTED " \ "WHERE instertedon > julianday(@time) AND findLargeImage(@path, FolderId, ImageId) = 1 " \ "LIMIT 1" When

Re: [sqlite] When to close a db?

On 20 Jun 2010, at 10:17pm, Sam Carleton wrote: > I am getting some strange behavior out of my app, which happens to be > both an Apache module and some Axis2/C Web Services which run under > Apache. From time to time, it is VERY inconsistent, when the code > calls sqlite3_close() the Apache

Re: [sqlite] optimizing use of extension function

On 20 Jun 2010, at 10:40pm, Sam Carleton wrote: > I am getting the EXACT same result, it calls findImage on for EVERY > row in the result set. The goal is to have the findImage() > short-circuit the where clause so that once it returns 1 one time, it > stops. Aka lots of 0's can be returned,

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote: > In what way did the statement I gave you, exactly as written, fail to satisfy > your requirements? Igor, When I put in EXACTLY what you gave me: SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED WHERE

Re: [sqlite] optimizing use of extension function

Sam Carleton wrote: > On Sun, Jun 20, 2010 at 4:52 PM, P Kishor wrote: >> >> No. WHERE clause is a completely different part of the statement, >> different from WHEN which is a part of the CASE construct. "CASE.. >> WHEN .. THEN .. ELSE .. END"

[sqlite] When to close a db?

I am getting some strange behavior out of my app, which happens to be both an Apache module and some Axis2/C Web Services which run under Apache. From time to time, it is VERY inconsistent, when the code calls sqlite3_close() the Apache server crashes. I don't recall the error right off. >From

Re: [sqlite] optimizing use of extension function

Lord, my response was so scrambled... my apologies. Let me try again... CASE.. WHEN.. THEN.. ELSE.. END is an expression. You can use it in lieu of simple columns, and you can use it wherever columns can be used. So, you can use it where you have <> below SELECT <> FROM table(s) WHERE <> Go

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 4:52 PM, P Kishor wrote: > > No. WHERE clause is a completely different part of the statement, > different from WHEN which is a part of the CASE construct. "CASE.. > WHEN .. THEN .. ELSE .. END" is one construct, an expression, and > applies to the

Re: [sqlite] optimizing use of extension function

On Sun, Jun 20, 2010 at 3:47 PM, Sam Carleton wrote: > Erin and Igor, > > I simply cannot wrap my head around the correct syntax.  BAsed on the > documentation, I believe I should be looking to make it fit this > pattern: > > CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2

Re: [sqlite] unexpected large journal file

Black, Michael (IS) wrote: > Hmmma 6.5X speed diff between RAM and disk? Sounds pretty good to > me. Not sure why you expect better. I don't expect better. As I said, I'm "not really sure what I should expect here, or how to find out what I should expect". > 10,000/sec is crappy?

Re: [sqlite] unexpected large journal file

Eric Smith wrote: > I'd like to show it to the forum -- is it possible to send emails with > attachments here? It's a 60kb jpg file. God bless the gnuplot developers, who provided an ascii output option: time (minutes) to insert 2m records 10

Re: [sqlite] optimizing use of extension function

Erin and Igor, I simply cannot wrap my head around the correct syntax. BAsed on the documentation, I believe I should be looking to make it fit this pattern: CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END Does the WHERE clause get replaced with the WHEN clause like this: CASE SELECT

Re: [sqlite] optimizing use of extension function

Not literally enough, in fact, as Erin ably explained. Igor Tandetnik Sam Carleton wrote: > You lost me;) I think I might be taking you too literally, here is > what I tried: > > SELECT FolderId, ImageId, instertedon > FROM V_FAVORITES_SELECTED > WHERE instertedon

Re: [sqlite] unexpected large journal file

On Sat, Jun 19, 2010 at 12:58:45PM -0400, Eric Smith scratched on the wall: > Jay A. Kreibich wrote: > > I think the use case will usually be (only) writes followed by (only) > reads. There may be incremental writes later, but they will hopefully > be small compared to the initial seed. I

Re: [sqlite] unexpected large journal file

On Fri, Jun 18, 2010 at 07:01:25PM -0700, Scott Hess scratched on the wall: > On Fri, Jun 18, 2010 at 5:24 PM, Eric Smith wrote: > > Jay A.  Kreibich wrote: > >> Yes.  Hence the "and this is the important part" comment.  Most of > >> the time when people are building

Re: [sqlite] optimizing use of extension function

You missed the "case when" part. See "The CASE Expression" : http://www.sqlite.org/lang_expr.html On Mon, Jun 21, 2010 at 2:28 AM, Sam Carleton wrote: > Igor, > > You lost me;)  I think I might be taking you too literally, here is > what I tried: > > SELECT FolderId,

Re: [sqlite] optimizing use of extension function

Igor, You lost me;) I think I might be taking you too literally, here is what I tried: SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED WHERE instertedon > julianday(@time) THEN findImage(@rootPath, FolderId, ImageId) ELSE 0 END; The prepare statement didn't much like

Re: [sqlite] optimizing use of extension function

Sam Carleton wrote: > This works great. The issue is that the image returned might NOT > exist anymore, so I created an extension function to return 1 if it > exists, 0 if it doesn't, findImage( RootPath, FolderId, ImageId). > Then I added to the select statement like

[sqlite] optimizing use of extension function

I am working on a slide show feature. The logic is based on time, there are a set of image metadata (folderId and filename) add to the DB, the insertion time is also saved. The client calls the getNextSlideShow() method to get the next image. The current select statement is: SELECT FolderId,

Re: [sqlite] unexpected large journal file

Hmmma 6.5X speed diff between RAM and disk? Sounds pretty good to me. Not sure why you expect better. 10,000/sec is crappy? And you think this because Several things you need to provide. #1 What OS are you on? There are numerous disk speed testing programs depending on your

Re: [sqlite] where to find an examples of writing a extension?

See SQLite source tree for upstream extensions. Or, as example, the unofficial repository http://sqlite.mobigroup.ru/dir?ci=a3386b467d6ffd67=ext 2010/6/20 Sam Carleton : > Where might I find some examples of creating user defined functions in SQLite? -- Best regards,