Manatee v2.0

Appearance

Db ​

The Db module has functionality for connecting to databases. It currently supports sqlite, mssql, msaccess, oracle and postgresql databases.

Requires Manatee v2.0 or greater

This version of the Db module cannot be used with Manatee v2.0 or earlier.

Connect ​

The connect method initialises a connection to a given database and returns a Database object.

Parameters ​

  • type the type of the database, currently this should be “mssql”, “sqlite”, “msaccess”, “oracle” or “postgresql”.
  • connection the connection-string which contains information about how to connect to the database in question
  • options an optional object with additional options:
    • noDispose with default false. If set to true, the connection will not be disposed when the script ends. This is useful when you want to keep the connection open for background tasks.
    • noReuse with default false. If set to true, any matching connections (based on the connection string) will not be reused. This is useful when you want to ensure that a new connection is created each time.

Example ​

javascript
var db = Db.connect("sqlite", "Data Source=C:\\MyFolder\\Test.db;Version=3;");

Database ​

The database object returned from a Db.connect(...) invocation represents a database connection. It has two primary methods for interacting with a database; query and exec.

Exec ​

The exec method will execute a non-query (e.g. INSERT, UPDATE) and return the number of affected rows.

Example ​
javascript
var affectedRows = db.exec("CREATE TABLE Test (id int, name string)");

Also supports db parameters:

javascript
Db.exec("INSERT INTO Mammals (name, species) VALUES (@name, @species)", {
  "@name": "John",
  "@species": "Seacow",
});

The arguments in the 2nd argument must be prefixed with “@”.

Query ​

The query method is used for queries (e.g. SELECT etc) and returns an array of objects representing the result of the query.

Example ​
javascript
var rows = db.query("SELECT id, name from Test");
for (var i = 0; i < rows.length; i++) {
  Debug.showDialog("id=" + rows[i].id + ", name=" + rows[i].name);
}

The query method also supports db parameters:

javascript
var rows = db.query("SELECT * FROM Mammals WHERE species = @species", {
  "@species": "Seacow",
});

Begin ​

The begin() method is used to initiate a transaction.

Example ​
javascript
var tx = db.begin();

Close ​

You can use the close() method to close the database connection.

javascript
db.close();

IsClosed ​

The isClosed property returns true if the connection is closed, otherwise false.

javascript
if (db.isClosed) {
  // Connection is closed
}

Transaction ​

A transaction object is conceptually similar to the database object. It has the same query and exec methods, but will delay the execution of the query or command until commit() is invoked and of course maintains transactional integrity. If the rollback() method is invoked the query and exec operations already made are discarded.

Commit ​

A commit() invocation will commit the tx to the db.

Example ​
javascript
tx.exec("INSERT INTO Test (id, name) VALUES (1, 'John')");
tx.exec("INSERT INTO Test (id, name) VALUES (2, 'Jane')");
// Commit John and Jane
tx.commit();

Rollback ​

A rollback() invocation will rollback the tx.

Example ​
javascript
tx.exec("INSERT INTO Test (id, name) VALUES (1, 'John')");
tx.exec("INSERT INTO Test (id, name) VALUES (2, 'Jane')");
// John and Jane are not needed anyways
tx.rollback();