Using SQLite Databases with AIR - Part 4 - Simple CRUD

So in case you you've been living under a rock for the last several years, you know that CRUD stands for Create, Read, Update and Delete. Which is what we are going to look at today, doing simple SQL statements with SQLite databases in Adobe AIR using JavaScript.

We saw in my last couple posts how do do simple CREATE statements using both synchronous and asynchronous connections. I will paste them here again so that we have the reference all on one page.

Synchronous connection


    // Create a new SQLStatement object that can be reused for each statement
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="create table if not exists todoLists (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, priority INTEGER);"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

Asynchronous connection


    // Create a new SQLStatement object that can be reused for each statement
    var statment = new SQLStatement();

    
    // Add the SQL text to the statement object's text property
    statement.text = "create table if not exists todoLists (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, priority INTEGER);"

    // Tell the SQLStatement object which connection object to use
    statement.sqlConnection = dbConn;

    // Execute the statement
    statement.execute();

    // Set up a listener to do something when the result of the query is announced.
    statement.addEventListener(air.SQLEvent.RESULT, function(e){
        $(document).trigger("tableCreated");
    };

Both of these examples assume that you have already set up and opened a SQLConnection object using either the open() or openAsync() methods. If you need a refresher on this, please refer to the posts I linked earlier.

You'll notice that the primary difference between these two statements is the event listener on the second statement. Remember that because an asynchronous database call is done asynchronously, the application is not waiting for the SQL execution to finish before it moves on to other things, so we need to set up something to "listen" for it to announce that it is done.

SELECT Statements

SELECT statements are the bread-and-butter of apps that use a database. As a developer they are the most commonly used statements. So let's look at how we would do one in AIR. Again, I am making the assumption that a SQLConnection object has already been created.

Synchronous


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="SELECT firstname, lastname from customers;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    air.Introspector.Console.log(stmt.getResult());

In this synchronous SQL statement, we are simply grabbing firstname and lastname from the customers table. As with our CREATE scripts, and all of our future scripts, we create a SQLStatement object and assign an SQL statement to its text property. Then we assign the SQLConnection object that we already made to the sqlConnection property. Then we execute the statement.

Now you'll see the first new item here. In a SELECT statement, we are actually getting a result that we want to do something with. Here we are getting a list of all of our customers' first and last names. To get the results of a synchronous query, we call the getResult() method on the statement after we have executed it.

We are going to use the AIR Introspector to view the results of this query. The AIR Introspector is an AWESOME tool that allows you to dig through objects in AIR so you can see what is going on. For those familiar with <CFDUMP> in ColdFusion or with using FireBug in FireFox, you will appreciate the value of a tool like this.

Now we'll look at the same query when done asynchronously.

Asynchronous


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="SELECT firstname, lastname from customers;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    stmt.addEventListener(air.SQLEvent.RESULT, function(e){
        air.Introspector.Console.log(e.target.getResult());
    }

In our asynchronous statement, the only difference is in the last line. We can no longer call getResult() on the statement, because with an asynchronous call, we would end up making the statement before the result was returned. So we need to set up something to listen for the result. I have attached an event listener to the statement to listen for the air.SQLEvent.RESULT event. When that event occurs, we are going to call the anonymous function that we are passing as the second argument to addEventListener().

Notice that the anonymous function is receiving an argument that we are calling "e". This the Event object. And the Event object will have the SQL results neatly packaged inside of it. And we can get those out by calling e.target.getResult(). Again, we'll dump those to the Introspector for viewing.

Here are the results of dumping either result into the Introspector:

The SQLResult object contains a data element, which contains an Array. Each element of the array contains a record from the SELECT query. As you can see, the first record contains firstname='Jason' and lastname='Dean'.

INSERT, UPDATE and DELETE

As you may have guessed, INSERT, UPDATE and DELETE statements are pretty much the same. I am going to paste some examples below, but I am not going to take the time to explain them further. These examples are very simple, we will look at some more advanced samples next time when we look at parameterizing queries. One thing you might list to note in the SQLResult object for INSERT, UPDATE and DELETE queries is the rowAffected property that is returned. Take a look when you have a chance.

Synchronous - INSERT


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="INSERT INTO customers (firstname, lastname) VALUES ('Dan','Vega');"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    air.Introspector.Console.log(stmt.getResult());

Asynchronous - INSERT


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="INSERT INTO customers (firstname, lastname) VALUES ('Dan','Vega');"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    stmt.addEventListener(air.SQLEvent.RESULT, function(e){
        air.Introspector.Console.log(e.target.getResult());
    }

Synchronous - UPDATE


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="UPDATE customers SET active = 1;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    air.Introspector.Console.log(stmt.getResult());

Asynchronous - UPDATE


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="UPDATE customers SET active = 1;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    stmt.addEventListener(air.SQLEvent.RESULT, function(e){
        air.Introspector.Console.log(e.target.getResult());
    }

Synchronous - DELETE


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="DELETE FROM customers;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    air.Introspector.Console.log(stmt.getResult());

Asynchronous - DELETE


    // Create a new SQLStatement object
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="DELETE FROM customers;"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    stmt.addEventListener(air.SQLEvent.RESULT, function(e){
        air.Introspector.Console.log(e.target.getResult());
    }

Comments
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner