Working with webSQL

In the current release of Smart Mobile Studio we have finally added database support. Since smart mobile development targets the browser API directly, the classes are more low-level than what we have in delphi, but the flip side is that there are a lot less elements involved and they are very easy to use.

Creating a database, a preview

So, how do we setup a database? First we start by including the database unit, which in this case is: w3dbsql. In this example we are going to use the wrapper for webSQL, which (behind the scene) is basically sqlite built into the browser. This database is supported by Safari mobile, Safari desktop, google chrome and opera. So let’s setup a database:

Procedure TForm1.HandleButtonClick(Sender:TObject);
var
  x:  Integer;
Begin
  (* create / open our database *)
  if not FDB.Active then
  Begin
    try
      FDB.DBDescription:='My Cool Database';
      FDB.DBName:='myDB';
      FDb.Active:=True;
    except
      on e: exception do
      showmessage(e.message);
    end;
  end;

  (* Create our customer table *)
  mTrans:=TW3WebSQLWriteTransaction.Create(FDB);
  try
    mTrans.Execute("create table if not exists customers
      (id integer primary key asc,
       name string);",[]);
  finally
    if mTrans.LastFailed then
    writeln(mTrans.LastError);
    mTrans.free;
  end;

  (* populate with dummy data *)
  mTrans:=TW3WebSQLWriteTransaction.Create(FDB);
  try
    for x:=1 to 10 do
    mTrans.Execute('insert into customers
      (name) values (?);',['john doe #' + IntToStr(x)]);
  finally
    if mTrans.LastFailed then
    writeln(mTrans.LastError);
    mTrans.free;
  end;

  (* populate with dummy data *)
  mTrans:=TW3WebSQLReadTransaction.Create(FDB);
  try
    mTrans.OnSuccess:=DBCallback;
    mTrans.Execute('select * from customers;',[]);
  finally
    if mTrans.LastFailed then
    writeln(mTrans.LastError);
  end;
end;

The code above is (as the name implies) the event for a button click. We start off by checking if the database instance is active, if it’s not and it’s the first click, then we just open it. The cool part is that this will automatically create a database for us. So if the database exists, it will be opened. If it does not exist, an empty database is created.

Next is creating a table. We use a class called TW3WebSQLWriteTransaction to do the job. This is the class you use to execute queries that does not return data. In other words, creating tables, inserting records and so on.

  (* Create our customer table *)
  mTrans:=TW3WebSQLWriteTransaction.Create(FDB);
  try
    mTrans.Execute("create table if not exists customers
      (id integer primary key asc,
       name string);",[]);
  finally
    if mTrans.LastFailed then
    writeln(mTrans.LastError);
    mTrans.free;
  end;

We use the same TW3WebSQLWriteTransaction class to populate the table we just created (and as the SQL shows, its only created if the table does not exist). Again this is a fairly straight forward mechanism.

  (* populate with dummy data *)
  mTrans:=TW3WebSQLWriteTransaction.Create(FDB);
  try
    for x:=1 to 10 do
    mTrans.Execute("insert into customers
      (name) values (?);",['john doe #' + IntToStr(x)]);
  finally
    if mTrans.LastFailed then
    writeln(mTrans.LastError);
    mTrans.free;
  end;

Once populated we want to read the data back, just to make sure it’s actually there. To read data rather than write, we use an instance of TW3WebSQLReadTransaction. In order to get access to the data, we have to use an event. Everything in browsers is async (non blocking) so you also have to be careful that you don’t free the object before the callback has completed. This is what the callback event looks like:

Procedure TForm1.DBCallback(Sender:TObject);
var
  mTemp:TW3WebSQLReadTransaction;
  x:  Integer;
  mRow: Variant;
Begin
  mTemp:=TW3WebSQLReadTransaction(sender);
  if assigned(mTemp) then
  Begin
    if mTemp.Dataset<>NIL then
    Begin
      for x:=0 to mTemp.Dataset.rows.length-1 do
      Begin
        mRow:=mTemp.dataset.rows.item(x);
        writeln(mRow['name']);
      end;
    end;
  end;
end;

In this code we just dump everything to the console, so you wont see anything unless you run it with debugging on (if you use an external browser). We could easily put the info into a list, a combobox or anything else you wanted.