How to use the SequelSphereDB / jQuery Connector

To use the connector as part of your SequelSphereDB / jQuery application:

  1. Download the Connector
  2. Reference the connector from your code
  3. Create and populate a database
  4. Create SQL to query the database
  5. Manipulate the data in the database

How to reference (link to) the SequelSphereDB / jQuery Connector

After making the decision to either download or use the connector straight from the SequelSphereDB site, you must reference jQuery, SequelSphereDB and the connector in the HEAD section of your jQuery / SequelSphereDB application as shown below. Note that the reference to the "edge" version of SequelSphereDB.js file is to the most recent version. To reference a stable release, replace "edge" with the desired release number. The "your_token" should be replaced with your own token, acquired by registration. The literal "your_token" should be used for evaluation purposes only.

<head>
    <meta charset="UTF-8">
    <title>Your Application Title</title>

    <!--  Include reference to jQuery -->
    <script type="text/javascript"
             src="jquery.js"
    ></script>
	
    <!--  Include a reference to SequelSphereDB -->
    <script type="text/javascript"
             src="http://www.sequelsphere.com/db/edge/your_token/SequelSphereDB.js"
    ></script>

    <!--  Include reference to SequelSphereDB / jQuery plugin -->
    <script type="text/javascript"
             src="ssdb-jquery-plugin.js"
    ></script>
	
    <!--  Alternatively you can reference the SequelSphereDB / jQuery plugin from SequelSphere site 
    <script type="text/javascript"
             src=" http://www.sequelsphere.com/connectors/jQuery/1.0/ssdb-jquery-plugin.js"
    ></script>
    -->
</head>
	

How to Create and Populate SequelSphereDB Database

The creation of the SequelSphereDB is done independent of the jQuery plugin. Use the SequelSphereDB API directly. The API supports multiple persistence options, including in-memory, sessionStorage and localStorage in the base product, and connectors to other storage options. See the SequelSphereDB usage notes for more information on persistence options.

In any case, at some point the database must be created and initially populated. This is done with the db.createTables(tables) and db.createTable(table) functions. The tables argument can be a Javascript object or JSON string in the following form:

var tables = [
	{
		tableName: "EMPL",
		columns: [ "EMPL_ID", "NAME", "AGE", "DEPT_ID" ],
		primaryKey: ["EMPL_ID"],
		data: [
			[0,"Bob",32,0],
			[1,"John",37,2],
			[2,"Fred",28,1],
			[3,"Sue",26,3],
			[4,"Beth",22,99],
			[5,"Mary",27,3],
			[6,"Adrian",24,1]
		]
	},

	{
		tableName: "DEPT",
		columns: [ "DEPT_ID", "NAME", "MGR_ID" ],
		primaryKey: ["DEPT_ID"],
		data: [
			[0,"Finance",0],
			[1,"Accounting",4],
			[2,"IT",99],
			[3,"Marketing",1],
			[4,"Janitorial Services",5]
		]
	}
];
	

Create the tables as follows:

	try {
		db.catalog.createTables(tables);
		db.commit();
	}
	catch(err) {
		if (err instanceof Exception) {
			alert(err.getVerboseMessage());
		}
		else {
			alert(err);
		}
	}
	

The tables argument can be constructed literally in Javascript, as above, or could be a JSON string returned from a web service. It could include definition and data, or just the definition. Finally, createTables can be used to refresh an already populated database. How you choose to populate the database will be specific to your application.

How to Query the Database with sqlExec jQuery Plugin

The sqlExec jQuery plugin is used to query and manipulate the database. The API is:

	$(selector).sqlExec(options);
	

The options argument is used to specify four aspects of the execution of the plugin:

  • the SequelSphereDB operation to execute, such as query , queryRow and updateRow . The different query operations also dictate the form of the returned results.
  • the SQL to execute, if the operation requires SQL
  • what to do with the results, such as return them directly, call a success function, trigger an event or bind it to a selected DOM object using jQuery .data() function.
  • how to handle errors

A Simple Query

The simplest query requires only two options: the operation and the SQL to execute. Because what should be done with the results is not specified, they are returned from the function.

	var results = $("myselector").sqlExec({
		op: "query",
		sql: "SELECT * FROM EMPL"
	});
	
The default operation is "query", so this could have been simplied further to:
	var results = $("myselector").sqlExec({ sql:"SELECT * FROM EMPL" });
	

Query Operations and Results

The form of the retured results is determined by the query operation, and include the following:

Operation Results Description
query { columnNames:[ cols }, data: [ [row] ] } The results object list the names of the columns in the result set and the data as an array of row arrays.
queryCursor { next(), getValue(col), getColumnName(col) } The results object is a cursor with method for looping through the results and accessing the values.
queryObjects { columnNames:[ cols }, data: [ {row} ] } The results object lists the names of the columns in the result set and the data as a an array of objects with named properties
queryRow [ values ] The results object is an array of column values for a single row
queryRowObject { col1:col1-value, ... coln:coln-value } The results object is an object with named properties for each column value.
queryValue object The results object is a scalr value, such as a string or number.

How to Return or Process Results

In the simplest use the results are returned from the plugin function call. While convenient, this approach has limitations:

  • it does not take advantage of jQuery features, like data binding and events
  • and it does not preserve jQuery chainability in which the return of a plugin function should be the jQuery object on which it was executed
  • it assumes a synchronous call rather than asynchronous, which might not always be supported by the underlying storage mechanism.
For this reason, the plugin supports multiple approaches for handling the results. With the exception of the direct return, all the approaches maintain chainability.

The results can be returned in any of the following ways:

Options Behavior
no options returns the results directly
success: function(sel, res, opt){} The caller provides a function which is exeuted on successful completion of the query. While the query is executed once, the success function is called once for each object matching the jQuery selector. The arguments to the function are the selected object, the results object and the options used to execute the query. For example:
$("#fld-maxage").sqlExec({
	op: "queryValue",
	sql: "SELECT MAX(AGE) FROM EMPL",
	success: function(selected, maxAge) {
		selected.val(maxAge);
	}
});
		
dataKey:"my-key" The caller provides a data key which is used to bind the results to each selected object using the jQuery .data() function passing in the data key and the results. This results in jQuery triggering the "setData" event, which the application can listen for. This separates the act of querying the database from the actions of handling the results, like updating the view. For example:
$("#empl-form").on("setData", function(key, row) {
	if (key==="my-row") {
		$("#empl-form input.emplid").val(row[0]);
		$("#empl-form input.name").val(row[1]);
		$("#empl-form input.age").val(row[2]);
	}
});
$("#empl-form").sqlExec({
	op: "queryRow",
	sql: "SELECT EMPL_ID, NAME, AGE FROM EMPL WHERE EMPL_ID=1",
	dataKey: "my-row"
});
			
bindData:true This is the same as supplying a dataKey, but a default key of "results-data" is used.
eventKey:"my-event" The caller provides the name of an event to trigger on successful completion of the query. The results are passed to the event handler. This provides the same separation as the data binding, but without requiring the binding of the data to a DOM object. For example:
$("#empl-form").on("my-event", function(e, row) {
	$("#empl-form input.emplid").val(row[0]);
	$("#empl-form input.name").val(row[1]);
	$("#empl-form input.age").val(row[2]);
});
$("#empl-form").sqlExec({
	op: "queryRow",
	sql: "SELECT EMPL_ID, NAME, AGE FROM EMPL WHERE EMPL_ID=1",
	eventKey: "my-event"
});
			
triggerEvent:true This is the same as supplying an eventKey, but a default key of "query-complete" is used.

Specifying Parameters to the SQL String

The SQL can be specified as a static literal string or as a string with parameters. Two type of parameters are supported:

  • "{n}": expects the parameter values to be supplied in an array, and substitutes for the array entry n.
  • "{property}": expects the parameter values to be supplied in an object with named properties.
The options arguments used are:

	sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + emplid
	
	-or-
	
	parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={0}",
	args: [emplid]
	
	-or-
	
	parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={EMPL_ID}",
	args: { EMPL_ID:emplid }
	

Manipulation of Data

In addition to populating the tables using db.createTable and db.createTables , data can be manipulated in individual rows. The SQL INSERT, UPDATE and DELETE statements are not supported at this time, but rows can be updated using the SequelSphereDB operations insertRow , updateRow and deleteRow . In addition, a new operationavailable only on the sqlExec plugin, saveRow , will attempt to insert the row, and if a duplicate key is found, will update the existing row. The row must be provided as an array of values in the order of the table definition.

	$(selector).sqlExec({
		op: "insertRow",
		tableName: "EMPL",
		dataRow: [ vals ]
	});