This page contains examples for the major functions SequelSphereDB performs. Watch this space for new examples using connectors for different javascript frameworks. To see the code for an example, do a View Source on the page and search for "BEGIN EXAMPLE #" followed by the example number.

Example #1: Load Database

Before the Sequel Sphere API can be used, the database must be initialized. This is done by calling db.catalog.createTables(array) or db.catalog.createTable(obj). See the getData function for the expected data format. Both create functions can operate on either a properly formatted javascript object or a string containing JSON that evaluates to the same format.

The source below shows the script needed to initialize the data. The example creates the data directly, but in most applications this would be a call to a web service to query a server-side database and return a subset of the database for client-side processing.

HTML Example #2: Scalar Query

Most queries are executed using the db.query(sql) function. This returns both scalar, single-row and multi-row returns. The returned object contains the data in a field named "data". This contains an array of rows; each row is an array of column values. Click execute to perform the query.

    SELECT AVG(age) FROM EMPL

Execute

HTML Example #3: Multi-row Cursor

Multi-row queries can be performed with the db.query function or by the db.cursor function to be returned a cursor. The cursor supports next() functionality and functions for accessing the current row and column metadata.

Click on the Execute button to perform the following query:

    SELECT e.EMPL_ID, e.NAME, e.AGE, e.DEPT_ID, d.NAME as DEPT_NAME FROM EMPL e JOIN DEPT d ON (d.DEPT_ID=e.DEPT_ID)

Execute

HTML Example #4: Single-row Query

A query that expects a single multi-column return does not require use of the cursor function, but can access the column values directly. The returned data still is in a array of arrays.

Click on the Execute button to perform the following query:

    SELECT * FROM EMPL e WHERE EMPL_ID = 3

Execute

EMPL_ID
NAME
AGE
DEPT_ID

HTML Example #5: Graph Query Results

This example demonstrates integrating a Sequel Sphere query with the amcharts.js charting tool.

Click on the Execute button to chart the following query:

    SELECT NAME, AGE FROM EMPL e

Execute

HTML Example #6: Insert a row

The SQL INSERT statement is not supported at this time, however, SequelSphereDB provides a simple mechansism for inserting rows in a database table. The table is simply an array of rows, so adding a row is no more than adding a javascript object to the array of rows.

To insert a row, do the following:

  1. Use tab = db.catalog.getTable("empl"); to get a reference to the table
  2. Create an array with the desired column values, such as var row = [10, "Frodo", 33, 3]; The values must be in the correct order.
  3. Use tab.insertRow(row); to insert the row

Click on the Execute button to insert a row.

Execute

HTML Example #7: Delete a row

The SQL DELETE statement is not supported at this time, however, SequelSphereDB provides a simple mechansism for deleting rows in a database table.

To delete a row, do the following:

  1. Create a copy of the row, or query the table to get a copy.
  2. Get a reference to the table with var tab = db.catalog.getTable("EMPL");
  3. Use tab.deleteRow(row); to delete the actual row

Click on the Execute button to delete the row that was created in Example #6.

Execute