DatabasesContents
A key advantage of an Ajax server is access to shared databases. The Jaxer DB API attempts to strike a balance between extreme ease of use and having enough power to get almost any job done. Jaxer offers two types of DB access:
Your applications execute queries using Jaxer.DB and access returned data using Jaxer.DB.ResultSet. If you use the preconfigured static access, Jaxer.DB and Jaxer.DB.ResultSet are the only two classes you'll need. If you use object-oriented access, you'll also use the database-specific classes, Jaxer.DB.SQLite and Jaxer.DB.MySQL. Managed AccessThe easiest way to access the DB functionality is with the pre-configured, static (non-object-oriented) API which works out of the box. Jaxer ships with the SQLite3 database built in, and with an SQLite3 driver as well as a driver for MySQL 5. The default configuration of Jaxer uses the built-in SQLite3 so when you fire up Jaxer, you can access the DB right away:
Jaxer.DB.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)");
Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins");
Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Robinson Crusoe");
Jaxer.DB.execute("INSERT INTO booktest (title) VALUES (?)", "Harry Potter");
var resultset = Jaxer.DB.execute("SELECT * FROM booktest");
Under the cover, Jaxer creates a single (per Jaxer process) connection to the database specified in config.js, and uses it to execute your queries and return any data as a Jaxer.ResultSet. It's called managed access because the connection is managed for you: it's automatically opened when needed, and closed according to configuration parameters, either after each query or after each page, or only when the server shuts down. Because the connection itself holds essentially no data (other than lastInsertId or lastInsertRowId), it's safe to share it. If you need to access this default connection, which should rarely be true, call: Jaxer.DB.defaultConnections.DEFAULT To configure the managed access differently, you edit the configApps.js file found in the local_jaxer folder; the file holds the connection parameters for your database. Sample connection parameters for SQLite3
var sqliteParams = {
IMPLEMENTATION: "SQLite",
PATH: "c:\\path\\to\\my\\data.sqlite",
CLOSE_AFTER_EXECUTE: false,
CLOSE_AFTER_REQUEST: true,
MAX_NUM_TRIES: 100,
MILLIS_BETWEEN_TRIES: 37
};
Sample connection parameters for MySQL 5
var mysqlParams = {
IMPLEMENTATION: "MySQL",
HOST: "127.0.0.1",
PORT: 3306,
SOCKET: null, // (or use /path/to/socket)
NAME: "demos",
USER: "root",
PASS: "",
CLOSE_AFTER_EXECUTE: false,
CLOSE_AFTER_REQUEST: true,
};
Next, point the default database connection to your database. For example, to use the example SQLite connection, add the following to configApps.js: Config.DEFAULT_APP.db = sqlite; Connection parameters common to both connection types
The only other connection parameter specific to SQLite is PATH—the full path to the SQLite data file. MySQL connections have a few more parameters: HOST (or SOCKET) and PORT parameters refer to the MySQL server, USER and PASS are the credentials used to log on to the MySQL server, AUTORECONNECT determines what to do when the connection to the MySQL server is dropped (default is true) and NAME is the name of the database to use. Per-Application Managed AccessWhen you have multiple applications running within a Jaxer instance, you'll generally want to have a separate database for each application. You can configure static managed access at the application level in configApps.js. The easiest way to configure per-application databases is to use Jaxer's "smart" DB logic. Supply basic connection parameters by editing configApps.js and setting Config.DEFAULT_DB to your connection parameters. Config.DEFAULT_DB = mysqlParams; Jaxer will then overwrite the PATH or NAME connection parameters (for SQLite or MySQL, respectively) to application-specific values. Jaxer will automatically select the values. Dynamic, Configurable AccessWhen you need more flexibility than a single, preconfigured managed connection for database access, use the dynamic Jaxer DP API using either Jaxer.DB.SQLite or Jaxer.DB.MySQL. Create connection objects and call their methodsSQLite
// Use default settings for MAX_NUM_TRIES and MILLIS_BETWEEN_TRIES
var connectionParams = {IMPLEMENTATION: "SQLite",
PATH: Jaxer.Dir.combine(Jaxer.Config.DEFAULT_PATH_BASE, "appdata.sqlite")};
Jaxer.DB.SQLite.createDB(connectionParams); // Only needed if the DB file doesn't exist yet
var conn = new Jaxer.DB.SQLite.Connection(connectionParams);
conn.open(); // optional: executing a query automatically opens the connection if needed
conn.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)");
conn.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins");
var resultset = conn.execute("SELECT * FROM booktest");
conn.close();
The createDB call is necessary only if the database does not yet exist; otherwise, you can just create the connection using the Connection constructor. Opening the connection is optional because execute methods will open the connection if needed. MySQL
var connectionParams =
{
HOST: "127.0.0.1",
PORT: 4417, // Differs from the default of 3306 to minimize conflicts with existing installs
NAME: "demo", // The name of the database instance
USER: "root",
PASS: "",
};
// Next line only needed if the DB instance doesn't exist yet
Jaxer.DB.MySQL.createDB(connectionParams);
var conn = new Jaxer.DB.MySQL.Connection(connectionParams);
conn.open(); // This is optional: executing a query automatically opens the connection if needed
conn.execute("CREATE TABLE IF NOT EXISTS booktest (id INTEGER PRIMARY KEY AUTO_INCREMENT, title TEXT)");
conn.execute("INSERT INTO booktest (title) VALUES (?)", "Mary Poppins");
var resultset = conn.execute("SELECT * FROM booktest");
conn.close();
Since you're managing when these connections open and close, there's no need to specify the CLOSE_AFTER_EXECUTE and CLOSE_AFTER_REQUEST parameters. Also, remember that the names of the connectionParams properties are case-sensitive. Jaxer.DBThe API itself is extremely simple. The Jaxer.DB.execute(sql, params) method returns either a Jaxer.DB.ResultSet, an array of Jaxer.DB.ResultSet, or a number. The execute method returns a Jaxer.DB.ResultSet for SELECT statements. For INSERT, UPDATE, and DELETE statements the number of rows that are affected is returned. The SQL parameter is mandatory but you may optionally use "?" to indicate prepared statement parameters (placeholders for data). This is generally a better practice than embedding parameter values directly in the SQL string—especially if the values come from user data—since that offers a line of defense against SQL injection attacks. If you use "?" in your SQL, pass a params object as the second argument to execute(). If you have multiple placeholders in your SQL statement, params should be an array of values with values in the same order as the question marks in your statement; when you have only a single escaped value pass it as itself without wrapping it in an array. MySQL supports passing multiple statements in a single execute call; this returns an array of Jaxer.DB.ResultSet objects, one ResultSet per statement. In the next example, two SQL statements are executed in one call so an array containing two ResultSet objects is returned.
var conn = new Jaxer.DB.MySQL.Connection(connectionParams);
var rs = conn.execute("SELECT * FROM booktest WHERE id = 1; SELECT * FROM booktest WHERE id = 2");
print(rs);
// Output is: Columns: id | title Row 0: 1 | Mary Poppins, Columns: id | title Row 0: 2 | Robinson Crusoe You can also invoke MySQL procedures, which similarly returns an array containing a ResultSet for each DML statement passed, along with the number of rows affected. For example, the following code creates (in MySQL only) a stored procedure, invokes it, and then prints out the result set:
var conn = new Jaxer.DB.MySQL.Connection(connectionParams);
conn.execute("CREATE PROCEDURE demo_proc() BEGIN select * from booktest; END");
conn.execute("call demo_proc()");
for each (var r in rs) {
print('Result set:');
print(r);
}
// Result set: Columns: id | title Row 0: 1 | Mary Poppins Row 1: 2 | Robinson Crusoe Result set: 0 When you need to execute one SQL statement repeatedly but with different parameters, use mapExecute. Most commonly used when there's a single SQL statement you need to invoke with a large list of different bind parameters, mapExecute prepares a SQL statement only once to significantly boost performance. In the following example an array of parameters is passed on each invocation, where each entry in the array is an array of values to use on a single execution of the statement, then the mapExecute method then returns an array of Jaxer.DB.ResultSet objects:
var rs = conn.mapExecute("SELECT * FROM booktest WHERE id = ? AND title = ?",
[[2, "Robinson Crusoe"],
[3, "Harry Potter"]]);
print(rs);
// Result set: Columns: id | title Row 0: 2 | Robinson Crusoe, Columns: id | title Row 0: 3 | Harry Potter The mapExecute method returns a Jaxer.DB.ResultSet array with an entry for each set of parameters. You can also flatten the returned array into one ResultSet by setting the flatten option:
var rs = conn.mapExecute("SELECT * FROM booktest WHERE id = ? AND title = ?",
[[2, "Robinson Crusoe"],
[3, "Harry Potter"]],
{flatten: true});
print(rs);
Columns: id | title Row 0: 2 | Robinson Crusoe Row 1: 3 | Harry Potter The last item in the Jaxer.DB API is Jaxer.DB.lastInsertId (synonym: Jaxer.DB.lastInsertRowId), which returns the value of AUTO_INCREMENT column automatically set by the database on the most recent insert into a given table. You should retrieve the lastInsertId right after you execute the INSERT statement because otherwise the behavior of lastInsertId may differ based on the database implementation. For example, SQLite always returns the rowid of the last successfully inserted row, whether or not it had an INTEGER PRIMARY KEY AUTO_INCREMENT column. Data TypesThe execute() method attempts to preserve JavaScript datatypes and data across trips to and from the database for all supported database implementations. If you store a JavaScript Date value in a table with a compatible column type, and retrieve it, you will get back the same JavaScript Date value. The details of how this works vary with the implementation. JavaScript values storage in SQLiteThe SQLite 3 database is fundamentally untyped: unlike most other relational databases, you can store any SQLite data type in any column, regardless of its declared data type. For more information, see the SQLite 3 datatype documentation.
|
© 2009 Aptana Inc.