SQLite Database Access with the JAB Workbench JavaScript Recipe Library

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 3 months ago)

59 views

SQLite Database Access with the JAB Workbench
JavaScript
Recipe Library

Introduction

Database access is a feature that is not easily attained in web
-
based applications. The developer
normally has to rely on server
-
side scripting to read and write data to

a database.

Recently released
browsers implementing HTML 5 allow asynchronous access to databases, however programming around
asynchronous access, which requires the use of callback functions, can get very complicated.

JAB
Workbench provides developers
with
a freeware Java applet
,

which can easily be implemented through
the JAB Recipe Library
, to execute database queries

synchronously
without the need for server
-
side
scripts

or HTML 5’s asynchronous callback functions
. This Java applet allows the develo
per to write their
own SQL (Structured Query Language) statements to read, write, and update records in SQLite
databases. For those unfamiliar with SQL, the JAB Recipe Library also provides easy to use methods to
add records, update records, modify tables
, and much more. This article
provides an overview
of the
basic features of the SQLite Database Accessor and its related
recipe methods.

Preparation

To incorporate SQLite database access into
a
web application the JAB Recipe Library and Applets
Freeware p
ackage

must be downloaded

from the
JAB Freeware

page
.

This tutoria
l
demonstrates a
generic implementation of the SQLite Database Accessor Applet and JAB Recipe Library
.
Many specific
examples of
database accessor implementation can be found in the

Database Access Examples Freeware
Package

also available on the
JAB Freeware

page
.

This tutorial assumes familiarity with the structure of
HTML

files, JavaScript programming, and the HTML Document Object Model (DOM).

Coding

In order to implement the JAB Workbench JavaScript Recipe Library, you must first include the library in
your webpage.
To do this, add a script tag in the
<head>

section of your HTML document. For example:


Ensure that the
src

attribute points to the correc
t directory

relative to the HTML file
.

Recipe Instantiation

Next, you must create a method that instantiates the
DUIDatabaseRecipe

class of the JAB Recipe
Library. It is most useful to do this in the
onload

event handler of the
<body>

element so that data
base
access is available as soon as the page loads. Below is an example of how to instantiate this class and
initialize the applet:

Listing 1
-
1

<script type=“text/javascript” src=“../libraries/jabRecipes
-
2.1.3.js”></script>



The first line creates an instance of the
DUIDatabaseRecipe

class an
d assigns it to a global variable.

The
next seven lines set various parameters that are necessary to prepare the recipe for use:

db.host



the domain on which the database resides. In the case of SQLite databases, this will always
be 127.0.0.1 which points to your local machine.

db.user

and
db.password



credentials needed to access the database. Most SQLite databases require
no credenti
als.

db.useLicense



determines which applet to use. This should
always

be set to false in order to use the
freeware applet. Setting it to true would require you to have an applet that is not available to freeware
users and that your machine have a valid

JAB registry entry.

db.codebase



relative path to the applet *.jar file from the HTML file.

db.databaseType



the type of database to which you are connecting. The freeware applet only
supports connection to SQLite databases.

d
b.
database



the name of t
he database to which you are connecting. SQLite databases should always
include the extension *.db

Next, the
db.prepare()

method is called which does a number of things including:

Listing 1
-
2

db = new DUIDatabaseRecipe()

db.host = “127.0.0.1”

db.user = “”

db.password = “”

db.useLicense = false

db.codebase = “../JABApplets/”

db.databaseType = “SQLite”

db.database = “exampleDatabase.db”


var prepareResult = db.prepare()


if(!prepareResult) {


return false

}

if(!db.runQuery(SELECT * FROM test_table)) {


var tableResult = db.runQuery(“CREATE TABLE

test_table COLUMNS(
\



test_table_rn INT, name TEXT)”)


return tableResult

}

return true




Assembles the
<applet>

tag and attaches it to the HTML document

(if the applet does not
already exist).



Sets the database type to prepare the applet for communication to that type of database.



Connects to the database by calling the appropriate applet method.

The result of the recipe preparation is examined to determi
ne if it was successful. One of the most
common reasons for an unsuccessful preparation is that one of the applet’s methods was called before
the applet was loaded into memory. This is most common in the Chrome browser. To get around this,
you can creat
e a wrapper method around the one that sets up the database, call

the database setup
method, catch any errors that might occur at the prepare method, and set a timeout to rerun the
wrapper method if the preparation was unsuccessful. For example:


If a database of the name specific by
db.database

does not exist, the applet will automatically create a
new, empty database. A table must be added to this empty database in order to store da
ta in it. This is
what the remainder of the code in
Listing 1
-
2

does. First, it checks to see if a table by the name
test_table

exists. If it does not exist, it creates that table with two columns
: test_table_rn

and
name
. The
runQuery()

method is discu
ssed in greater detail in the next section.

SQL Execution

The most versatile method in the
DUIDatabaseRecipe

is the
runQuery()
method. Any SQL statement
can be executed using this method including
INSERT
,
SELECT
,
UPDATE
,
CREATE
, and
DELETE

statements.
Listin
g 1
-
3

function wrapper() {


var result = setupDatabase()


if(!result) {


setTimeout(wrapper,500)


return


}


else {


//...


}


}


function setupDatabase() {


//...


try {


db.prepare()


}


catch(e) {


return false


}


//...

}


Al
l you need to do is pass the statement as a string into the
runQuery()

method. If a
SELECT

query is
passed,
the method will return a JSON result with a format similar to that show
n

in Listing 1
-
4 below.


The
DUIDatabaseRecipe

class also provides many other methods to update, modify, and create tables
in your database without the need to write a SQL statement. These methods include:



addColumnToTable(tableName,columnName,columnType)



createIndex(indexName,[columns],isUnique,tabl
eName)



createTable(newTableName,[columns])



deleteTable(tableName)



getDataForTable(tableName)



getIndexes(tableName)



getTableNames()



insertIntoTable(tableName,[rows],autoIncrement,useAllHeadings)



renameTable(oldTableName,newTableName)

Conclusion

With the JAB Workbench JavaScript Recipe Library, accessing SQLite databases is made simple. All you
need to do is include the library in your webpage, instantiate the
DUIDatabaseRecipe

class
, and
execute the desired queries. Much more functionality is a
vailable to JAB Workbench
developer
s. The
DUIDatabaseRecipe

supports access to MySQL and SQLServer databases

with a valid JAB license key
.
Also,
developer
s can include the recipe library

by using JAB’s manifest editor rather than attaching a
<
script
>

tag

to the HTML document. Visit
www.jabworkbench.com

for an in
depth overview of JAB

Workbench and its capabilities including more detailed examples of SQLite database access.


Listing 1
-
4

{columnHeadings: [“test_table_rn”,“na
me”],


column
Types: [“INT”,“TEXT”],


resultRows: [[1,“Bill”],[1,“Bob”],[1,“Jane”],[1,“Britt”]
]
}