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?