Two SQLite tool recommendations

This post is more than 2 years old.

In regards to my post yesterday on AIR and SQLite I thought I'd share two tools that can help developers.

I've been using a cool AIR based SQLite tool from Christophe Coenraets. You can find it here. It works rather nicely and I like that it has a 'history' feature since I always have trouble finding the exact location AIR stores my DB file. (I typically trace the nativepath of my File object when I forget.)

Then a commenter named Gareth pointed out that you can find a Firefox plugin as well. (About the only thing you can't do in a Firefox plugin is peace in the Middle East.) I installed the SQLite Manager today and it runs pretty well.

I'm not sure which tool I'll give preference too, and I'm sure there are others as well.

And lastly, if any AIR/SQLite experts out there want to help me with my date problem, let me know.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Anuj Gakhar posted on 1/10/2008 at 8:33 PM

Not an expert Ray but would like to hear about your date problem.

Comment 2 by Raymond Camden posted on 1/10/2008 at 8:35 PM

I'm getting confusing feedback on the AIR side if AIR+SQLite supports true date columns. It seems like it does. And I know the tools I use to look at my db say my column is a date.

But I'm trying to do a simple date comparison. Ie, get * from hours where date > today and nothing I try seems to work.

Comment 3 by Anuj Gakhar posted on 1/10/2008 at 8:44 PM

hmm, Is date the name of your column by any chance? sounds like a reserved word to me..

Comment 4 by Raymond Camden posted on 1/10/2008 at 8:46 PM

I thought so as well. Thing is - I can select date form hours and it works.

Comment 5 by Anuj Gakhar posted on 1/10/2008 at 8:49 PM

http://www.sqlite.org/datat...

Look at this, it says Sqlite is Typeless. Everything is considered as a string. It might be best to save dates as numerics and then do a comparison.

Comment 6 by Anuj Gakhar posted on 1/10/2008 at 9:04 PM

var now:Date = new Date();
var currentTime:Number = now.getTime();

that should give you the number of seconds since January 1, 1970. Pretty good to use that for date comparisons.

Comment 7 by Erki Esken posted on 1/10/2008 at 10:38 PM

With SQLite JDBC driver from http://www.zentus.com/sqlit... I actually use my favourite SQL editor Aqua Data Studio to edit AIR embedded databases. Works great. Same JDBC driver should be able to get your local CF to talk to AIR DBs as well.

To get it to work you add SQLite JDBC driver to your java path, and then datasource URL is something like "jdbc:sqlite:/path/to/airapp.db" and driver is "org.sqlite.JDBC".

Comment 8 by mloncaric posted on 1/10/2008 at 11:55 PM

Check this project:
http://code.google.com/p/as...

Instead of writing sql statements all by yourself, those classes can ganerate sql statements for you. It's still work in progress, so expect changes - more classes for larger sql support.

Have a nice day.

Comment 9 by shag posted on 1/11/2008 at 12:42 AM

sqlite is typeless with suggestions, but supports suggested types. my only experiece with sqlite is within trac, so I am not sure if the date store was specific to trac implementation or sqlite. either way, here is how I got similar request to work:

where datetime(t.time, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')

the site that documents the datetime function is here:
http://www.sqlite.org/cvstr...
hope this helps

Comment 10 by Raymond Camden posted on 1/11/2008 at 12:50 AM

shag - that throws an error for me when I use the SQLite AIR app:

Error:

near "unixecpoch": syntax error

My precise sql:

select *
from hours
where datetime(hours.date, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')

Comment 11 by J posted on 1/11/2008 at 4:08 AM

Another tool I have found useful is: -

http://www.sqlmaestro.com/p...

Christophe's tool is also useful (as u mentioned)

Comment 12 by shag posted on 1/11/2008 at 9:21 AM

sorry ray. i am working on some assumptions here. did you try this already?

select * from hours
where date > date('now')

if it doesn't work, i would ask for your create table statement and/or what your data looks like when you select it. i feel certain this is in the format of your data.

also, to one of the other posts, aqua data is a great tool. it natively connects to all the major db's, and will connect to anything else with an odbc or jdbc driver. it is a very sweet tool.

Comment 13 by Raymond Camden posted on 1/11/2008 at 7:28 PM

Shag - can you download my app? You can find it here:

http://www.coldfusionjedi.c...

Enter a project, client, then a few hours, and you will have sample data.

Comment 14 by Gareth posted on 1/12/2008 at 1:32 AM

Hey Ray,
Looking at your date values, it looks like they're getting entered strangely into the dB. It looks like it's adding milliseconds to the date. It shows up in SQLite Manager as 1199941200000, but when converting back to a string date, this will not convert properly. However, if you remove the last 3 zeros, it comes back correctly at 2008-10-1.

Comment 15 by Raymond Camden posted on 1/12/2008 at 1:33 AM

I thought maybe I was entering data wrong. Let me play with that.

Comment 16 by Raymond Camden posted on 1/12/2008 at 1:40 AM

That seems to have doen it. Now I need to figure out how to get stuff for today. date > date('now') doesn't seem to work.

Comment 17 by Gareth posted on 1/12/2008 at 1:58 AM

How about this

SELECT * from hours
where date('now') <= date(date, 'unixepoch')

Comment 18 by Raymond Camden posted on 1/12/2008 at 2:07 AM

No go. No error, but no results. Even if I switch < to >.

Comment 19 by Gareth posted on 1/12/2008 at 2:24 AM

It looks like the time might be a little screwy. If you enter 1200000000 as the date, it says that it is 2008-1-10 (correct), but if you enter 1200014000, it says that it is 2008-1-11 (incorrect, actually 2008-1-10 20:13:20), so I'm not sure if it's doing something with GMT or something weird like that. 2008-1-11 starts from 1200027600.

Comment 20 by Gareth posted on 1/12/2008 at 2:28 AM

Ah, ha! Looks like that is what it is :) If you use this date 1200027600 (2008-1-10 19:00:00) it shows as 2008-1-11. But change that to 1200027599, 1 second earlier, and it returns 2008-1-10.

Comment 21 by Gareth posted on 1/12/2008 at 2:52 AM

I apologize to all of the subscribers for all of my posts.

Try this
SELECT *
FROM hours
WHERE date('now', 'localtime') <= date(date, 'unixepoch', 'localtime')

This will add 5 hours to the date time (if you are EST) to account for the time difference from GMT.

Comment 22 by Raymond Camden posted on 1/12/2008 at 2:55 AM

Still now working for me, but maybe I'm still storing it bad. When I do a generic select with no filter, I have

1/11/08

Should I have store 2008-11-1 instead?

Comment 23 by shag posted on 1/12/2008 at 3:40 AM

ok, after much fumbling around (you wouldn't believe the self inflicted issues i had with this), i finally got this up and going. i was about to ask for your changes after @gareth's post. then realized, i could just drop the 0's myself. that having been said, here you are:

select * from hours
where datetime(hours.date, 'unixepoch', 'localtime') > date('now')

if this doesn't work, then please post/send the updated code.

Comment 24 by shag posted on 1/12/2008 at 3:46 AM

sorry, the 'localtime' isn't necessary. i also just realized I used > vs >=, and it worked. looks like sqlite uses different tie breaking logic. i would suggest >= in case future changes to sqlite.

select * from hours
where datetime(hours.date, 'unixepoch') >= date('now')

Comment 25 by Gareth posted on 1/12/2008 at 4:42 AM

@Ray,
I'm not sure why it's not working. If it returns the date correctly, I'm guessing that it should be working. What do you get if you type in

select date('now') from hours

(just to see what is returned when you select "now")?

Another thing to try is messing around with the unixepoch dates and see if you can get it to return something. I went to this page http://www.esqsoft.com/java...
and grabbed some converted dates and put them into the query to test against and see what was returned.