Show me a better way: Inserting date/time strings into SQLite via HTML and Adobe AIR

This post is more than 2 years old.

I'm working on my presentation for CFUN which involves jQuery and HTML based AIR applications. (Oh, and ColdFusion too. :) I was working on a sample application that made use of SQLite to create a simple Note application. (If this sounds familiar, it is based on the Flex application I did for my cfObjective SQLite presentation.) Here is the code I tried to insert a new note record into my database.

var sqlstatement = new air.SQLStatement() sqlstatement.sqlConnection = connection var sql = "insert into notes(title, body,created)"+ "values(:title,:body,:created)";

sqlstatement.text = sql sqlstatement.parameters[":title"] = title; sqlstatement.parameters[":body"] = body; sqlstatement.parameters[":created"] = new Date();

sqlstatement.addEventListener(air.SQLEvent.RESULT,contentChange); sqlstatement.execute();

There isn't anything too crazy here. I have three columns. The first two are just strings. The third is a date. When I run this, something odd happens. When I displayed the results in a table, I got [object Object] for my created values. I thought at first that AIR was being slick and recognizing a valid JavaScript date object. I tried running .getMonths() on it and got an error that it wasn't a support method. What the heck? So I switched to Lita, an application (also AIR based!) that let's you run ad hoc queries against SQLite databases. Surprisingly, even there, the values were [object Object]!

For the heck of it, I then tried using toString() on the JavaScript date object. Here's where things got weird. I got an immediate SQL error saying that my value wasn't a date. So SQLite/AIR recognized Date() as a valid date value, but stored it (from what I could see) incorrectly. Yet when I tried a string representation, it failed. I ended up figuring out the format SQLite wanted. It's pretty pick. If your month or date has one digit you have to prepend it with 0. I wrote this little function to do it for me:

//given a date, return it so I can insert it function dbDateTimeFormat(dt) { //i had fancy trenarys here - went for simple var month = dt.getMonth()+1; if(month < 10) month = "0" + month; var day = dt.getDate(); if(day < 10) day = "0" + day; var dStr = dt.getFullYear() + "-" + month + "-" + day; var hour = dt.getHours(); if(hour < 10) hour = "0" + hour; var minute = dt.getMinutes(); if(minute < 10) minute = "0" + minute; var second = dt.getSeconds(); if(second < 10) second = "0" + second; dStr += ' ' + hour + ':' + minute + ':' + second air.trace(dStr); return dStr; }

Obviously this would be a lot slimmer if I used some ternary functions for my month/day parsing, but when I write code, I try to start off as simple as possible.

So... someone please tell me I'm wrong. I've got to believe that there is a simple way to do this. Anyone?

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 Gareth Arch posted on 7/17/2010 at 10:35 PM
Comment 2 by Jason Dean posted on 7/17/2010 at 10:44 PM

How about:

var sql =
"insert into notes(title, body,created)"+
"values(:title,:body,date(:created))";

sqlstatement.text = sql
sqlstatement.parameters[":title"] = title;
sqlstatement.parameters[":body"] = body;
sqlstatement.parameters[":created"] = "now";

One other thing to consider is that you can tell SQLite to have a default date of "now" for any new record. The caveat is that you MUST set it up when you create the Table.

Comment 3 by Raymond Camden posted on 7/17/2010 at 10:49 PM

@Gareth: That's Flex based though. You can call functions inside SWFs from HTML AIR apps (just learned about that this week!), but I don't think I want to go that way. As it stands, in a Flex app, you don't need to do this! You just pass a date (like I tried in JS) and it works.

@Jason: The literal now? No way that will work. (But I'll try it. ;)

Comment 4 by Raymond Camden posted on 7/17/2010 at 10:51 PM

@Jason: WTF. Get this. It worked... but was 5 hours off.

2010-07-17 18:51:02 (6 pm, it's 1pm for me)

Comment 5 by Raymond Camden posted on 7/17/2010 at 10:52 PM

My times are all GMT-0500, so it looks like now works but is not respecting the -5.

Comment 6 by Raymond Camden posted on 7/17/2010 at 10:53 PM

Ok, so Jason isn't crazy. "now" as a string is documented here:

http://www.sqlite.org/lang_...

So the question is - how do I store it so that when I _get_ it, it's the right TZ.

Comment 7 by Jason Dean posted on 7/17/2010 at 11:14 PM

OK, how about this:

var sql =
"insert into notes(title, body,created)"+
"values(:title,:body,datetime(:created, :modifier))";

sqlstatement.text = sql
sqlstatement.parameters[":title"] = title;
sqlstatement.parameters[":body"] = body;
sqlstatement.parameters[":created"] = "now";
sqlstatement.parameters[":modifier"] = "localtime";

Comment 8 by Jason Dean posted on 7/17/2010 at 11:24 PM

By the way, as I indicated earlier, you can skip this completely and just have the column automatically default to the current timestamp. That way any new entry is already stamped with a creation date. But you MUST do it when you create the table. As I have read it, and tried it, you cannot ALTER the table later to do it.

Here is how you would create the table.

CREATE TABLE myDates (id NUMBER, myDate DATE DEFAULT (datetime('now','localtime')));

Comment 9 by emzeth posted on 7/17/2010 at 11:37 PM

thakn's, for the information...

Comment 10 by Raymond Camden posted on 7/18/2010 at 12:07 AM

Jason - you are the rock star. Not a rock star - the rock star. I wasn't aware bound parameters could be used as values of functions in SQL. Works like a charm.

Comment 11 by Jason Dean posted on 7/18/2010 at 12:09 AM

sweeeet! Glad to hear that was what you were looking for.

Comment 12 by Murray Hopkins posted on 7/18/2010 at 2:24 AM

And just in case people dont know about it, I find this FireFox extension for managing sqlite databases invaluable:

http://code.google.com/p/sq...

Cheers,
Murray

Comment 13 by Mark posted on 7/29/2010 at 2:17 AM

Way Cool!