SequelSphere / jQuery Connector Example

This page shows examples for using SequelSphere with the sqlExec plugin for jQuery. No jQuery plugin is needed to use SequelSphere. The API can be called directly giving the programmer full flexibility to use it as desired. However, the "sqlExec" (ssdb-jquery-plugin.js) plugin connector provides an experience more closely aligned with what may be expected in a jQuery environment.

First, you will need to reference the SequelSphere javascript file and, if using the plugin, the ssdb-jquery-plugin.js file, as in the example code below. Please register for your own SequelSphere token; the token in the example is only for evaluation purposes. There is no cost to register, but it helps us to keep track of who is using our product. The jQuery connector is no cost. You should download it to your web application according to your own standards.

Example HTML:
<script type="text/javascript" src="http://sequelsphere.com/db/edge/your_token/SequelSphereDB.js"></script>
<script type="text/javascript" src="js/ssdb-jquery-plugin.js"></script>
	

Example #1: Load Database

Before the Sequel Sphere API can be used, or the jQuery plugin, there must be a database set up to use. This example is not intended to explore all of the options for populating your local SequelSphere database. For the purposes of this example, the database uses an in-memory storage scheme, so is created as part of the example, and released when the page is released from memory. If you refresh the page, the database will be re-initialized. SequelSphere has many persistence options, and there are many ways you might consider loading your database. Please see the SequelSphere documentation for more information. The example runs automatically. The code shows a simple approach that mimics calling an Ajax service to retrieve the data.

Example database initialization Javascript code:
// Get the ball rolling with the first example.
$(document).ready(function() {

	//	Let's set our status
	$("#example-msg").trigger("example-status", "Database loading...");

	//	We call db.onready() to ensure the database has been
	//	initialized prior to performing any operations on it.
	db.onready(function() {

		//	Let's call a function (shown below) to retrieve the data and
		//	pass it to a success function.  The 'data' will contain
		//	a JSON object for specifying both table layouts and data.
		getData(function(data) {
			try
			{
				//	Now that we have our 'data', let's create the tables
				//	and populate them.
				db.catalog.createTables(data);
				$("#example1-msg").trigger("example-success", "Database loaded!");
			}
			catch(err) 
			{
				$("#example1-msg").trigger("example-failed", err);
			}
		});

	});
});

// Example 1 Support Functions

function getData(success) {
	// Get the data in a prescribed format supported by SequelSphere.
	// This includes a javascript object in the format shown below,
	// or a JSON string that evaluates into the format below.
	// For purposes of the example, the data is created locally, 
	// however for most HTML5 applications, a web service call would
	// likely be made which returns the information as a JSON string.

	var data = [
	{
		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" ],
		data: [
			[0,"Finance",0],
			[1,"Accounting",4],
			[2,"IT",99],
			[3,"Marketing",1],
			[4,"Janitorial Services",5]
			  ]
	}

	];

	success(data);
}
		

Example #2: Scalar Query with Simple Return of Result

A single jQuery plugin supporting several database operations wraps most of the SequelSphere API. In the simplest use, a single "options" argument is provided with two properties:

  • op: contains the database operation to perform.
  • sql: contains the SQL query to execute

The database operation is one of several SequelSphere "query" operations which execute an SQL query and differ only in the form of the data returned. This example shows the use of the "queryValue" operation, which returns a single scalar value, such as a string or number, using a simple, synchronous style of coding.

Click Query Value to perform the query with the following options::

  • op: "queryValue"
  • sql: "SELECT AVG(AGE)as MAXAGE FROM EMPL"

Average Age:

Example Javascript code:
$(document).ready(function() {
	$("#example2-btnex").click(function(e) {
		// Note the selector does not have to match anything
		// because the operation is executed once and returned
		// without processing the selected items.
		var avg = $().sqlExec({
			op: "queryValue",
			sql: "SELECT AVG(age) FROM EMPL"
		});
		// Set the UI control to contain the returned value
		$("#example2-results").val(""+avg);
	});
});
		

SequelSphere supports multiple return formats for programmer convenience, suitability to application architecture and to facilitate integration with application frameworks and external products. The query operation used dictates the return format, as specified in the following table see SequelSphere API document for completed information):

Operations Return Format Description
query { columnNames: [], data: [ [row] ] } Data is an array of row arrays
queryCursor { next:function(), getValue:function(col) } A cursor object for browsing the result set
queryObjects { columnNames: [], data: [ {row} ] } Data is an array of row objects
queryRow [val1, val2, ...] A single row is returned as a row array
queryRowObject {COL1:val1, COL2:val2, ...} A single row is returned as an object, with each column a separate named property
queryValue obj A scalar object, like string or number

Example #3: Query Rows with Success Function

While receiving a simple return is quick and easy, it might not be consistent with your approach to building applications or able to be used when the underlying storage mechanism favors an asynchronous style of operation. This example introduces both a more complex return type and a caller-supplied callback function to be executed when the query is completed successfully. The query is performed once, but the callback function is executed once for each selected object. The example uses the following properties on the options object:

  • op: "query"
  • sql: "SELECT * FROM EMPL e, DEPT d WHERE (e.DEPT_ID = d.DEPT_ID)"
  • success: function(selected, results, options)

The selected object is the selected object on which to apply the results. The results object is the results in the form dictated by the operation. In this example, it is a cursor object with functions for browsing the results set. It incluces methods such as next() for positioning the cursor on the next row, and getValue(col) for getting the value of a column value on the current row. The options object contains the options that were used in performing the query. It is not the original object provided by the caller, but a copy that has all fields set with validated and default values.

Click on the Query button to perform the query and populate a table with the results. There are many ways you can populate the table. This example builds up the html and replaces the inner html completely, but you may dynamically add DOM objects.

$(document).ready(function() {
	$("#example3-btnex").click(function(e) {
	
		$("#example3-results").sqlExec({
			op: "query",
			sql: "SELECT * FROM EMPL e, DEPT d WHERE (e.DEPT_ID = d.DEPT_ID)",
			success: function(selected, results, options) {
				var html = resultsToHtml(results);
				selected.html(html);
			}			
		});
	});
});

function resultsToHtml(results) {
	var resultsHtml = [];
	appendHeadersToResultsHtml(results, resultsHtml);
	for(var r = 0 ; r < results.data.length ; ++r) {
		var row = results.data[r];
		appendRowToResultsHtml(row, resultsHtml);
	}
	
	var html = "<table>" + resultsHtml.join("\n\t") + "</table>";
	return html;
}

function appendHeadersToResultsHtml(results, resultsHtml) {
    var colNames = [];
    for (var col = 0; col < results.columnNames.length; ++col) {
        colNames.push(results.columnNames[col]);
    }
    resultsHtml.push("<tr><th> ");
    resultsHtml.push(colNames.join(" </th><th> "));
    resultsHtml.push(" </th></tr>");
}
		

Example #4: Single-row Query with SQL Parameters and Binding to jQuery Data

This example shows four different features:

  • the ability to return a single row;
  • the ability to return the row as an array or object;
  • the ability to specify an SQL string with parameters;
  • binding the result to a DOM object using the jQuery .data() function.

Use the operation to specify a single row return and the format of the row as an array (each column value is a separate entry in the array, in the order specified in the query's SELECT clause) or a row object (each column value is a named property on the row object), as follows:

  • op: queryRow // returns a row array
  • op: queryRowObject // return a row object

The SQL can also be specified as string with substitutable parameters. Two parameter formats are supported: {n} specifies an index into an arguments array, and {property} specifies a property on an arguments object. A parameterized SQL string is specified as follows, in place of the "sql" option:

  • parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={0}"
  • args: [3]

Or,

  • parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={EMPL_ID}"
  • args: { EMPL_ID:3}

jQuery supports binding data to a jQuery object using the .data(key, value) function. The SequelSphere jQuery plugin supports this capability by allowing the caller to have the results bound to the selected object by simply supplying the key. To do this, supply the following property on the options object with a custom key:

  • dataKey: "empl-row"

When the query is complete, the results will be bound to the selected objects. This can be used regardless of the result format. In this example, the result is a single row array, such as might be used on a form for editing. Using this approach further separates the processing and display of the data from accessing if from the database. Actions on UI components are triggered by the setting of the data on the object, rather than the completion of the query. In this way, the same actions can be triggered by setting the data directly, breaking the connection between the UI object and SequelSphere.

In this regard, an event handler should be established for the jQuery "setData" that will be triggered when the results are bound to the selected object(s).

To execute this example, first type a number from 0-6 in the EMPL_ID field, then click on the Query Row Array or Query Row Object button to perform a query that returns the specified EMPL row.

Search EMPL_ID
EMPL_ID
NAME
AGE
DEPT_ID
Search EMPL_ID
Example Javascript code:
$(document).ready(function() {
	$("#example4-btnrow").click(function(e) {
		// Handle Query Row Array button click
		var emplidstr = $("#example4-fld-search").val();
		var emplid = Number(emplidstr);
		// Clear the other fields
		$(".example4-fld").val("...");
		// Execute query and bind resulting row object 
		// to results controls as data
		$("#example4-results").sqlExec({
			op: "queryRow",
			//sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + emplidstr,
			parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={0}",
			args: [emplid],
			dataKey:"empl-row"
		});
	});
	$("#example4-btnrowobj").click(function(e) {
		// Handle Query Row Object button click
		var emplidstr = $("#example4-fld-search").val();
		var emplid = Number(emplidstr);
		$(".example4-fld").val("...");
		// Execute query and bind resulting row object 
		// to results controls as data
		$("#example4-results").sqlExec({
			op: "queryRowObject",
			//sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + emplidstr,
			parameterizedSQL: "SELECT * FROM EMPL WHERE EMPL_ID={EMPL_ID}",
			args: { EMPL_ID:emplid },
			dataKey:"empl-row"
		});
	});
	// The display of the data is separated from the database access
	// and is triggered by the setting of the data on the results object.
	// Perform the action when the jQuery "setData" event is triggered
	// on the results object.  Note: check for the matching key.
	$("#example4-results").on("setData", function(e, key, row) {
		if (key === "empl-row") {
			if (typeof row === "undefined" || row == null) {
				row = [ null, null, null, null ];
			}
			// row can be a row array (queryRow) with each
			// column value a separate entry in array, or a
			// row object (queryRowObect) with each column 
			// value named property.
			// handle each case
			var isArray = row instanceof Array;
			var emplid = row[isArray ? 0 : "EMPL_ID"];
			var name = row[isArray ? 1 : "NAME"];
			var age = row[isArray ? 2 : "AGE"];
			var deptid = row[isArray ? 3 : "DEPT_ID"];
			// Populate the UI controls
			var fldIdPrefix = "#example4-fld-";
			$(fldIdPrefix + "emplid").val(formatValue(emplid));
			$(fldIdPrefix + "name").val(formatValue(name));
			$(fldIdPrefix + "age").val(formatValue(age));
			$(fldIdPrefix + "deptid").val(formatValue(deptid));
		}
	});
});

function formatValue(val) {
	return typeof val === "undefined" || val == null ? "" : (""+val);
}
		

Example #5: Graph Query Results

The ability to return data in multiple forms helps gives greater flexibility when integrating with other tools such as charting tools. While charting is not a capability of SequelSphere or the sqlExec jQuery plugin, the results of a query are easily passed to a tool such as amcharts.js. amcharts.js requires the data to be an array of row objects (not an array of arrays), so when passing the results of a query to amcharts.js, use the queryObjects operation. The required options are:

  • op: "queryObjects"
  • sql: "SELECT NAME, AGE FROM EMPL"
  • success: function(selected, results, options)

The success function passes the data to the amcharts.js tool.

Click on the Chart Results button to chart the results.

Example Javascript code:
$(document).ready(function() {
	$("#example5-btnex").click(function(e) {
		var sql = "SELECT NAME, AGE FROM EMPL";
		
		$("#example5-chart").sqlExec({
			op:"queryObjects",
			sql:sql,
			success: function(selected, res, options) {		
				// Get the data.
				var chartData = res.data;
				// Create a chart
				var chart = new AmCharts.AmSerialChart();
				chart.dataProvider = chartData;
				chart.categoryField = "NAME";
				chart.angle = 30;
				chart.depth3D = 15;
				// Create a chart graph
				var graph = new AmCharts.AmGraph();
				graph.valueField = "AGE"
				graph.type = "column";
				graph.balloonText = "[[category]]: [[value]]";
				graph.lineAlpha = 0;
				graph.fillAlphas = 0.8;
				chart.addGraph(graph);
				// Draw the chart/graph
				chart.write("example5-chart");
			}
		});
	});
});
		

Example #6: Save a row

Data is not modified in the database using SQL INSERT and UPDATE statements. Rather, the following database operations are used:

  • saveRow will insert the row, if it does not exist, and update it if it does
  • insertRow will only attempt to insert the row
  • updateRow will only attempt to update the row

In each case, two properties on the options object are required to specify the row to be updated:

  • dataRow: [ column values ]
  • tableName: "EMPL" (for example)

The dataRow must be a complete row containing all columns in the column order of the table. If the table has a primary key defined, it will be checked to ensure the row is not a duplicate. Insert operations do not require a primary key, but update operations do, using the key fields to identify which row to update. As such, primary key fields cannot be updated.

Click on each button to insert, update or save a row. After the operation, the row will be queried from the database and displayed on the right. Click multiple times on insertRow and saveRow to see the difference in behavior.

EMPL_ID
NAME
AGE
DEPT_ID
Example Javacript code:
$(document).ready(function() {
	$("#example6-btnins").click(function(e) {
		// Insert a new row
		$("#example6-results").sqlExec({
			op: "insertRow",
			dataRow: [ 10, "Frodo", 33, 3 ],
			tableName: "EMPL",
			success: function(selected, options) {
				// If successful, query database to check it is there
				selected.sqlExec( {
					op: "queryRow",
					sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + options.dataRow[0],
					dataKey: "empl-row"
				});
			},
			error: function(err, options) {
				alert( (err instanceof Exception) ? err.getVerboseErrorMessage() : (""+err) );
			}
		});
	});
	$("#example6-btnupd").click(function(e) {
		// First, get a row to update
		// Use of SELECT * ensures row is complete and columns are in correct order
		var row = $().sqlExec({
			op: "queryRow",
			sql: "SELECT * FROM EMPL WHERE EMPL_ID=1"
		});
		// Make a simple modification to the row
		row[1] = row[1].substr(0, 3) + "-" + (new Date().getTime());
		// Update the row with updateRow
		$("#example6-results").sqlExec({
			op: "updateRow",
			dataRow: row,
			tableName: "EMPL",
			success: function(selected, options) {
				// If successful, re-query from database to check update was done
				selected.sqlExec( {
					op: "queryRow",
					sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + options.dataRow[0],
					dataKey: "empl-row"
				});
			},
			error: function(err, options) {
				alert( (err instanceof Exception) ? err.getVerboseErrorMessage() : (""+err) );
			}
		});
	});
	// Update a row using saveRow
	$("#example6-btnsav").click(function(e) {
		var row = [11, "New", 98, 1];
		row[1] = row[1].substr(0, 3) + "-" + (new Date().getTime());
		// Save the row
		$("#example6-results").sqlExec({
			op: "saveRow",
			dataRow: row,
			tableName: "EMPL",
			success: function(selected, options) {
				// If successful, re-query from database to check
				selected.sqlExec( {
					op: "queryRow",
					sql: "SELECT * FROM EMPL WHERE EMPL_ID=" + options.dataRow[0],
					dataKey: "empl-row"
				});
			},
			error: function(err, options) {
				alert( (err instanceof Exception) ? err.getVerboseErrorMessage() : (""+err) );
			}
		});
	});
	// Re-display when data is bound 
	$("#example6-results").on("setData", function(e, key, row) {
		if (key === "empl-row") {
			$("#example6-fld-emplid").val(row[0]);
			$("#example6-fld-name").val(row[1]);
			$("#example6-fld-age").val(row[2]);
			$("#example6-fld-deptid").val(row[3]);
		}
	});
});
		

Example #7: Delete a row

As with the save operations, rows are deleted with the "deleteRow" operation. The row should be selected first to be sure it is complete and containing the columns in the correct order. The same properties are required on deleteRow as the other save operations.

Click on the Delete Row button to delete the first row. The remaining rows before and after the delete will be displayed to the right.

Example Javascript code:
$(document).ready(function() {
	$("#example7-btnex").click(function(e) {
		// First,
		$("#example7-results").sqlExec({
			op: "query",
			sql: "SELECT * FROM EMPL",
			dataKey: "results"
		});
		var results = $("#example7-results").data("results");
		if (results != null && results.data.length > 0 && confirm("Delete row with EMPL_ID=" + results.data[0][0] + "?") {
			$("#example7-results").sqlExec({
				op: "deleteRow",
				dataRow: results.data[0],
				tableName: "EMPL"
			});
		}
		$("#example7-results").sqlExec({
			op: "query",
			sql: "SELECT * FROM EMPL",
			dataKey: "results"
		});
	});
	$("#example7-results").on("setData", function(selected, key, results) {
		if (key === "results") {
			var html = [];
			html.push("Remaining rows:<br/>");
			for (var r = 0 ; r < results.data.length ; ++r) {
				var row = results.data[r];
				html.push(row[0] + ", " + row[1]);
			}
			$(this).html(html.join("<br/>"));
		}
	});
});