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.
sqlstatement.text = sql
sqlstatement.parameters[":title"] = title;
sqlstatement.parameters[":body"] = body;
sqlstatement.parameters[":created"] = new Date(); sqlstatement.addEventListener(air.SQLEvent.RESULT,contentChange);
sqlstatement.execute();
var sqlstatement = new air.SQLStatement()
sqlstatement.sqlConnection = connection
var sql =
"insert into notes(title, body,created)"+
"values(:title,:body,:created)";
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?
Archived Comments
flexdateutils.riaforge.org ?
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.
@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. ;)
@Jason: WTF. Get this. It worked... but was 5 hours off.
2010-07-17 18:51:02 (6 pm, it's 1pm for me)
My times are all GMT-0500, so it looks like now works but is not respecting the -5.
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.
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";
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')));
thakn's, for the information...
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.
sweeeet! Glad to hear that was what you were looking for.
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
Way Cool!