Working with Database Content

Getting Data: Jaxer.DB.ResultSet

To retrieve data from the database you execute an SQL SELECT query which returns a new instance of the Jaxer.DB.ResultSet class. The resultset contains all the data returned from the query, which means you can continue using it even after the connection is closed. The data in it also will not change even if the database data changes after you created the object.

Jaxer.DB.ResultSet offers multiple ways of getting at its data. A common one is through the rows property, which is an Array of objects, one per returned row. Each row is a simple JavaScript object with a property per column, the value being the value of the row's cell for that column:

var resultset = Jaxer.DB.execute("SELECT * FROM booktest");
var firstTitle = resultset.rows[0].title; // equals "Mary Poppins"

Use rows.length to get a count of the returned rows. For each row, you may alternatively want all the cell values in one array, in the order returned by the query so each row has a property called $values that holds an Array of its cells' values.

To get all the data as a rectangular array (that is, a table), the resultset's rowsAsArrays property is an Array of the rows returned by the query but with each row an Array itself containing the values of the row's cells (the same as $values, above).

var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); 
resultset.rowsAsArrays.forEach(function(row) { 	
print(row.join(" | ")); }); 

The firstRow/singleRow properties return an object containing the first row from a result set while lastRow conversely is object based on the final row of the result set. Jaxer.DB.ResultSet has matching singleRowAsArray and lastRowAsArray methods that convert the single row objects to an array with the added advantage that the methods take an array parameter which lets you filter the columns included in the return value.

var resultset = Jaxer.DB.execute("SELECT * FROM booktest"); 
resultset.singleRowAsArray([2, 3, 5, 8]).forEach(function(col) { 	
	print('Col value: '+col+'\n'); 
}); 

The singleResult property is your friend if you know a query returned a single result or you only care about the first cell in the first row; singleResult is always null if there is no data to return.

var numBooks = Jaxer.DB.execute("SELECT COUNT(*) FROM booktest").singleResult;

To determine if the resultset has any data, or to distinguish a singleResult that's null because the database had a NULL in that cell versus returned no data from the query, use the hasData property.

var resultset = Jaxer.DB.execute("SELECT * FROM booktest");
if(resultSet.hasData) {
	resultset.rowsAsArrays.forEach(function(row) { 	
	print(row.join(" | ")); });
}

The resultset's columns property contains an array of the names of the columns returned, in the order returned by the query. If you know a column's name, you can find its index using the resultset's indexOfColumn() function.

Quick Printing

Jaxer.DB.ResultSet also gives you two convenience methods: toHTML and toString. The former returns the result set as an HTML table, the latter returns the result set as a string.

Avoiding SQL Injection Attacks

One of the biggest tar pits in web application development is avoiding the bad guys and hooligans. SQL Injection is the name for a nasty security hole to which web apps can be vulnerable if the developer doesn't take the appropriate precaution. Fortunately, Jaxer makes avoiding this attack very simple: whenever you pass a SQL statement to Jaxer.DB.execute (or any of the APIs which execute SQL on the database) put a ? (question mark) in your SQL where user-supplied input goes and pass the actual values as one or more parameters. Jaxer will sanitize the input value before passing the final statement to your database.

Examples

• Selects: Jaxer.DB.execute('SELECT * FROM Table1 WHERE fieldX = ? AND fieldY = ?', value1, value2)
• Updates: Jaxer.DB.execute('UPDATE Table1 SET field1 = ?, field2 = ?, field3 = ? WHERE keyValueField = ?', valA, valB, valC, valD)
• Inserts: Jaxer.DB.execute('INSERT INTO Table1 VALUES(?, ?, ?, ?', val1, val2, val3, v4);
• Deletes: Jaxer.DB.execute('DELETE FROM Table1 WHERE keyValueField = ?', val6);

Alternative: Use the Google Gears DB API

The Jaxer.DB.ResultSet API currently has approximately 30 methods for accessing the data stored in the properties discussed here. These are convenience methods intended to simplify reading a Jaxer.DB.ResultSet. For example, say you prefer to use the Google Gears DB API; the Jaxer.DB.ResultSet also supports its access methods:

// Results shown in Studio's Jaxer Shell: 

id | title
1 | Mary Poppins
2 | Robinson Crusoe
3 | Harry Potter

A Functional Approach

Jaxer.DB includes methods to apply against results in a functional approach as well:

• every(Object fn) : Boolean
• filter(Object fn) : Array
• forEach(Object fn) : void
• map(Object fn) : Array
• reduce(Object fn, Object initialValue) : Object
• reduceRight(Object fn, Object initialValue) : Object
• some(Object fn) : Boolean

Accumulate the sum of IDs from our booktest table

var sumOfIds = conn.execute("select * from booktest").reduce(function(previousValue, currentValue) {
	return previousValue + currentValue[0];
});

Double each ID

var doubleTheIds = conn.execute("select * from booktest").map(function(row) {
	return 2 * row[0];
});

Moving Data into Forms

One of the most common database design patterns is to store lookup data, sets of valid values for a given field, in the database. Jaxer makes it simple to retrieve these values and insert them into, say, select fields on your forms, as shown in the following example.

Form Validation

Real- or near-realtime input validation has always been among the more difficult features in web application development, especially when the check requires database access. Jaxer's ability to mix between client and server execution, with the security added by minimizing over the wire database interaction, removes most of the obstacles.

Writing to the DB

Adding a Row or Rows

Updating a Row

Deleting a Row