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 developer advocate for HERE Technologies. He focuses on JavaScript, serverless 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