mozilla

Revision 365647 of Sqlite.jsm

  • Revision slug: Mozilla/JavaScript_code_modules/Sqlite.jsm
  • Revision title: Sqlite.jsm
  • Revision id: 365647
  • Created:
  • Creator: gps
  • Is current revision? No
  • Comment

Revision Content

{{ gecko_minversion_header("20.0") }}

Sqlite.jsm JavaScript code module is a promise-based wrapper around the Storage/SQLite interface. Sqlite.jsm offers some compelling advantages over the low-level Storage XPCOM interfaces:

  • Automatic statement management. Sqlite.jsm will create, manage, and destroy statement instances for you. You don't need to worry about caching created statement instances, destroying them when you are done, etc. This translates to fewer lines of code to talk to SQLite.
  • All operations are asynchronous. Use of synchronous Storage APIs is discouraged because they block the main thread. All the functionality in Sqlite.jsm is asynchronous.
  • Easier memory management. Since Sqlite.jsm manages statements for you, it can perform intelligent actions like purge all cached statements not in use - freeing memory in the process. There is even a shrinkMemory API that will minimize memory usage of the connection automatically.
  • Simpler transactions. Sqlite.jsm exposes a transaction API built on top of Task.jsm that allows transactions to be written as procedural JavaScript functions (as opposed to a series of callback driven operations). If the function throws, the transaction is automatically rolled back. This makes code easy to write and read.
  • JavaScript-y API. Sqlite.jsm feels like a pure JavaScript module. The complexities of XPCOM are mostly hidden from view. JavaScript programmers should feel right at home using Sqlite.jsm.

{{ note("The Sqlite.jsm JavaScript code module can only be used from chrome -- that is, from within the application itself or an add-on.") }}

Before you can use this module, you need to import it into your scope:

Components.utils.import("resource://gre/modules/Sqlite.jsm")

Obtaining a Connection

Sqlite.jsm exports the Sqlite symbol. This symbol is an object with a single function: openConnection. This function takes an object defining connection options:

path
(required) The database file to open. This can be an absolute or relative path. If a relative path is given, it is interpreted as relative to the current profile's directory. If the path does not exist, a new SQLite database will be created. The value typically ends with .sqlite.
sharedMemoryCache
(optional) Boolean indicating whether multiple connections to the database share the same memory cache. Sharing the memory cache likely results in less memory utilization. However, sharing also requires connections to obtain a lock, possibly making database access slower. Defaults to true.
shrinkMemoryOnConnectionIdleMS
(optional) If defined, the connection will attempt to minimize its memory usage after this many milliseconds of connection idle. The connection is idle when no  statements are executing. There is no default value which means no automatic memory minimization will occur. Please note that this is not a timer on the idle service and this could fire while the application is active.

openConnection(options) returns a promise that resolves to an opened connection instance or is rejected if an error occurred while opening the database.

Here is an example:

Components.utils.import("resource://gre/modules/Sqlite.jsm");

Sqlite.openConnection({path: "myDatabase.sqlite", sharedMemoryCache: false}).then(
  function onConnection(connection) {
    // connection is the opened SQLite connection (see below for API).
  },
  function onError(error) {
    // The connection could not be opened. error is an Error describing what went wrong.
  }
);

Working with Opened Connections

Opened connections are what you will interface most with in Sqlite.jsm. The following sections detail the API of an opened connection instance.

Connection Management

These APIs are used to manage and inspect the state of the connection.

connectionReady

This property returns a boolean indicating whether the connection is available to perform operations.

close()

Close this database connection. This must be called on every opened connection or else application shutdown will fail due to waiting on the opened connection to close (Gecko doesn't force close connections because it doesn't know that you are really done with them).

This function returns a promise that will be resolved when the database has closed.

If a transaction is in progress at the time this function is called, the transaction will be forcibly rolled back.

If statements are in progress at the time this function is called, they will be cancelled.

Callers should not attempt to use the connection after calling this method as the connection will be unusable.

transactionInProgress

This boolean property indicates whether a transaction is in progress. This is rarely needed by external callers.

shrinkMemory()

This function can be called to shrink the memory usage of the connection. This is a glorified wrapper around the PRAGMA shrink_memory statement, which tells SQLite to shrink its memory usage (by clearing caches, etc).

While calling this has the benefit of shrinking memory, it can make your database slower, especially if you will be interacting with it shortly after calling this function. This is because SQLite will need to page the database back into memory from disk. Therefore, caution should be exercised before calling this function.

This returns a promise that is resolved when the operation completes.

discardCachedStatements()

This function is used to discard cached statement instances, freeing memory in the process. Active cached statements will not be discarded. Therefore, it is safe to call this any time.

This returns an integer count of the number of cached statements that were discarded.

Table and Schema Management

These APIs deal with management of tables and database schema.

schemaVersion

The user-set version associated with the schema for the current database. This property can be both read and set. If no schema version has been set, this will return 0.

tableExists(name)

This function determines whether a table exists in the current database. It returns a promise that is resolved with a boolean indicating whether the table exists.

indexExists(name)

This functions determines whether a named index exists in the current database. It returns a promise that is resolved with a boolean indicating whether the index exists.

Statement Execution

These APIs facilitate execution of statements on the connection. These are arguably the most important APIs in this type.

executeCached(sql, params, onRow)

execute(sql, params, onRow)

These similar functions are used to execute a single SQL statement on the connection. As you might have guessed by the name, there are 2 flavors: cached and non-cached. Other than that, they behave identically.

These functions receive the following arguments:

sql
(required) String SQL statement to execute. The trailing semicolon is not required.
params
(optional) Parameters to bind to this statement. This can be an array or an object. See notes below.
onRow
(optional) Function that is called when a row has been received.

The return value is a promise that is resolved when the statement has finished execution.

When a statement is executed via executeCached(), the prepared statement object is cached inside the opened connection. The next time this same SQL statement is executed (the sql argument is identical to one passed in before), the old statement object is reused. This saves time associated with parsing the SQL statement and creating a new statement object. The downside is the cached statement object lingers in the opened connection, taking up memory.

When a statement is executed via execute(), the underlying statement object is thrown away as soon as the statement finishes execution.

executeCached() is recommended for statements that will be executed many times. execute() is recommended for statements that will be executed seldomly or once.

Please note that callers don't need to prepare statements manually before execution. Just call executeCached() and the statement will be prepared for you automatically.

Parameters can be bound to the statement by defining the params argument. This argument can be an array of positional parameters or an object of named parameters. If the statement does not contain any bound parameters, this argument can be omitted are specified as null.

{{ note("Callers are strongly encouraged to use bound parameters instead of dynamically creating SQL statements for security reasons. Bound parameters aren't prone to SQL injection like manual SQL statement construction (e.g. concatenating a raw value into the executed SQL statement).") }}

{{ note("Callers are encouraged to pass objects rather than Arrays for bound parameters because they prevent foot guns. With positional arguments, it is simple to modify the parameter count or positions without fixing all users of the statement. Objects/named parameters are a little safer because changes in the order of parameters in the statement won't change how values are bound to those parameters.") }}
 
When onRow is not defined, the full results of the operation are buffered before the caller is notified of statement completion. For INSERT, UPDATE, and DELETE statements, this is not relevant. However, it can have drastic implications for SELECT statements. If your SELECT statement could return lots of data, this buffering of returned rows could result in excessive memory usage. Therefore, it's recommended to use onRow with SELECT statements.
 
If a StopIteration is thrown during execution of an onRow handler, the execution of the statement is immediately cancelled. Subsequent rows will not be processed and no more onRow invocations will be made. The promise is resolved immediately.
 
If a non-StopIteration exception is thrown by the onRow handler, the exception is logged and processing of subsequent rows occurs as if nothing happened. The promise is still resolved (not rejected).

The return promise will be rejected with an Error instance if the statement did not finish execution fully. The Error may have an errors property. If defined, it will be an Array of objects describing individual errors. Each object has the properties result and message. result is a numeric error code and message is a string description of the problem.

If onRow is specified, the returned promise will be resolved with null. Else, the resolved value will be an array of mozIStorageRow.

executeTransaction(func, type)

This function is used to execute a database transaction. A transaction is a series of related statements treated as one functional unit. If the transaction succeeds, all the statements contained within it are committed as one unit. If the transaction fails, the database is rolled back to its state before the transaction started.

This function receives the following arguments:

func
The function defining the transaction body.
type
The type of transaction to perform. This must be one of the TRANSACTION_* constants on the opened connection instance. Valid values are TRANSACTION_DEFERRED, TRANSACTION_IMMEDIATE, TRANSACTION_EXCLUSIVE. See the SQLite documentation for their meaning. The default is TRANSACTION_DEFERRED.

The passed function is a Task.jsm compatible generator function. When called, the function receives as its argument the current connection instance. This generator function is expected to yield promises, likely those returned by calling executeCached() and execute().

If we reach the end of the generator function without error, the transaction is committed. If an error occurs, the transaction is rolled up.

The returned value from this function is a promise that is resolved when the transaction has been committed or is rejected if the transaction was rolled back.

lastInsertRowID

This property returns the row ID from the last INSERT operation.

{{ note("Due to the asynchronous execution of statements, use of this can be unreliable, especially if multiple statements are executed in parallel. A more robust method to obtain a row ID of a recent INSERT is to SELECT details from the inserted row.") }}

affectedRows

This property returns the number of rows that were updated, deleted, or added by the last operation.

{{ note("This has the same caveats as lastInsertRowID.") }}

Examples

Open, Execute, Close

In this example, we open a connection, execute a simple statement, then close the connection.

Sqlite.openConnection({path: "MyDB.sqlite"}).then(
  function onOpen(conn) {
    conn.execute("SELECT 1").then(
      function onStatementComplete(result) {
        conn.close(function onClose() {
          print("We are done!");
        }
      }
    }
  }
}

This isn't a terrific example because it doesn't include error handling and is somewhat difficult to read.

Here is the same thing but with a Task.jsm task:

Task.spawn(function demoDatabase() {
  let conn = Sqlite.openConnection({path: "MyDB.sqlite"});

  try {
    let result = yield conn.execute("SELECT 1");
  } finally {
    yield conn.close();
  }
});

Bound Parameters

Here are some examples demonstrating bound parameters. Assume we open an opened connection in the conn variable.

let dataToInsert = [
  ["foo", "bar"],
  ["biz", "baz"],
  {"yo", "ho"],
];

Task.spawn(function doInsert() {
  for (let data of dataToInsert) {
    yield conn.executeCached("INSERT INTO myTable VALUES (?, ?)", data);
  }
});

And the same thing with named parameters.

let dataToInsert = [
  {paramA: "foo", paramB: "bar"},
  {paramA: "biz", paramB: "baz"},
  {paramA: "yo", paramB: "ho"},
];

Task.spawn(function doInsert() {
  for (let data of dataToInsert) {
    yield conn.executeCached("INSERT INTO myTable VALUES (:paramA, :paramB)", data);
  }
});

Transactions

These examples demonstrate how transactions work.

conn.executeTransaction(function simpleTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["biz", "baz"]);
});

The above will result in 2 INSERT statements being committed in a deferred transaction (assuming the inserts proceed without error, of course).

Let's do an example where we want to force a transaction rollback.

conn.executeTransaction(function complexTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  let data = yield conn.execute("SELECT * FROM myTable");
  if (data.length < 5) {
    throw new Error("We don't have the expected 5 rows to perform the next operation!");
  }

  // ...
});

Revision Source

<p>{{ gecko_minversion_header("20.0") }}</p>
<p><code>Sqlite.jsm </code>JavaScript code module is a promise-based wrapper around the Storage/SQLite interface. <code>Sqlite.jsm</code> offers some compelling advantages over the low-level Storage XPCOM interfaces:</p>
<ul>
  <li>Automatic statement management. Sqlite.jsm will create, manage, and destroy statement instances for you. You don't need to worry about caching created statement instances, destroying them when you are done, etc. This translates to fewer lines of code to talk to SQLite.</li>
  <li>All operations are asynchronous. Use of synchronous Storage APIs is discouraged because they block the main thread. All the functionality in <code>Sqlite.jsm</code> is asynchronous.</li>
  <li>Easier memory management. Since <code>Sqlite.jsm</code> manages statements for you, it can perform intelligent actions like purge all cached statements not in use - freeing memory in the process. There is even a <code>shrinkMemory </code>API that will minimize memory usage of the connection automatically.</li>
  <li>Simpler transactions. Sqlite.jsm exposes a transaction API built on top of <a href="/en-US/docs/Mozilla/JavaScript_code_modules/Task.jsm" title="/en-US/docs/Mozilla/JavaScript_code_modules/Task.jsm">Task.jsm</a> that allows transactions to be written as procedural JavaScript functions (as opposed to a series of callback driven operations). If the function throws, the transaction is automatically rolled back. This makes code easy to write and read.</li>
  <li>JavaScript-y API. <code>Sqlite.jsm</code> feels like a pure JavaScript module. The complexities of XPCOM are mostly hidden from view. JavaScript programmers should feel right at home using <code>Sqlite.jsm</code>.</li>
</ul>
<p>{{ note("The <code>Sqlite.jsm</code> JavaScript code module can only be used from chrome -- that is, from within the application itself or an add-on.") }}</p>
<p>Before you can use this module, you need to import it into your scope:</p>
<pre>
<span class="plain">Components.utils.import("resource://gre/modules/Sqlite.jsm")</span></pre>
<h2 id="Obtaining_a_Connection">Obtaining a Connection</h2>
<p><code>Sqlite.jsm</code> exports the <code>Sqlite</code> symbol. This symbol is an object with a single function: <code>openConnection</code>. This function takes an object defining connection options:</p>
<dl>
  <dt>
    path</dt>
  <dd>
    (required) The database file to open. This can be an absolute or relative path. If a relative path is given, it is interpreted as relative to the current profile's directory. If the path does not exist, a new SQLite database will be created. The value typically ends with <code>.sqlite</code>.</dd>
  <dt>
    sharedMemoryCache</dt>
  <dd>
    (optional) Boolean indicating<span class="cm"> whether multiple connections to the database</span><span class="cm"> share the same memory cache. Sharing the memory cache likely results</span><span class="cm"> in less memory utilization. However, sharing also requires connections</span><span class="cm"> to obtain a lock, possibly making database access slower. Defaults to</span><span class="cm"> true.</span></dd>
  <dt>
    shrinkMemoryOnConnectionIdleMS</dt>
  <dd>
    (optional)<span class="cm"> If defined, the connection</span><span class="cm"> will attempt to minimize its memory usage after this many</span><span class="cm"> milliseconds of connection idle. The connection is idle when no</span><span class="cm">&nbsp; statements are executing. There is no default value which means no</span><span class="cm"> automatic memory minimization will occur. Please note that this is</span><span class="cm"> <strong>not</strong> a timer on the idle service and this could fire while the</span><span class="cm"> application is active.</span></dd>
</dl>
<p><code>openConnection(options)</code> returns a promise that resolves to an opened connection instance or is rejected if an error occurred while opening the database.</p>
<p>Here is an example:</p>
<pre>
Components.utils.import("resource://gre/modules/Sqlite.jsm");

Sqlite.openConnection({path: "myDatabase.sqlite", sharedMemoryCache: false}).then(
  function onConnection(connection) {
    // connection is the opened SQLite connection (see below for API).
  },
  function onError(error) {
    // The connection could not be opened. error is an Error describing what went wrong.
  }
);
</pre>
<h2 id="Working_with_Opened_Connections">Working with Opened Connections</h2>
<p>Opened connections are what you will interface most with in <code>Sqlite.jsm</code>. The following sections detail the API of an opened connection instance.</p>
<h3 id="Connection_Management">Connection Management</h3>
<p>These APIs are used to manage and inspect the state of the connection.</p>
<h4 id="connectionReady">connectionReady</h4>
<p>This property returns a boolean indicating whether the connection is available to perform operations.</p>
<h4 id="close()">close()</h4>
<p>Close this database connection. This <strong>must </strong>be called on every opened connection or else application shutdown will fail due to waiting on the opened connection to close (Gecko doesn't force close connections because it doesn't know that you are really done with them).</p>
<p>This function returns a promise that will be resolved when the database has closed.</p>
<p>If a transaction is in progress at the time this function is called, the transaction will be forcibly rolled back.</p>
<p>If statements are in progress at the time this function is called, they will be cancelled.</p>
<p>Callers should not attempt to use the connection after calling this method as the connection will be unusable.</p>
<h4 id="transactionInProgress">transactionInProgress</h4>
<p>This boolean property indicates whether a transaction is in progress. This is rarely needed by external callers.</p>
<h4 id="shrinkMemory()">shrinkMemory()</h4>
<p>This function can be called to shrink the memory usage of the connection. This is a glorified wrapper around the <code>PRAGMA shrink_memory</code> statement, which tells SQLite to shrink its memory usage (by clearing caches, etc).</p>
<p>While calling this has the benefit of shrinking memory, it can make your database slower, especially if you will be interacting with it shortly after calling this function. This is because SQLite will need to page the database back into memory from disk. Therefore, caution should be exercised before calling this function.</p>
<p>This returns a promise that is resolved when the operation completes.</p>
<h4 id="discardCachedStatements()">discardCachedStatements()</h4>
<p>This function is used to discard cached statement instances, freeing memory in the process. Active cached statements will not be discarded. Therefore, it is safe to call this any time.</p>
<p>This returns an integer count of the number of cached statements that were discarded.</p>
<h3 id="Table_and_Schema_Management">Table and Schema Management</h3>
<p>These APIs deal with management of tables and database schema.</p>
<h4 id="schemaVersion">schemaVersion</h4>
<p>The user-set version associated with the schema for the current database. This property can be both read and set. If no schema version has been set, this will return 0.</p>
<h4 id="tableExists(name)">tableExists(name)</h4>
<p>This function determines whether a table exists in the current database. It returns a promise that is resolved with a boolean indicating whether the table exists.</p>
<h4 id="indexExists(name)">indexExists(name)</h4>
<p>This functions determines whether a named index exists in the current database. It returns a promise that is resolved with a boolean indicating whether the index exists.</p>
<h3 id="Statement_Execution">Statement Execution</h3>
<p>These APIs facilitate execution of statements on the connection. These are arguably the most important APIs in this type.</p>
<h4 id="executeCached(sql.2C_params.2C_onRow)">executeCached(sql, params, onRow)</h4>
<h4 id="execute(sql.2C_params.2C_onRow)">execute(sql, params, onRow)</h4>
<p>These similar functions are used to execute a single SQL statement on the connection. As you might have guessed by the name, there are 2 flavors: cached and non-cached. Other than that, they behave identically.</p>
<p>These functions receive the following arguments:</p>
<dl>
  <dt>
    sql</dt>
  <dd>
    (required) String SQL statement to execute. The trailing semicolon is not required.</dd>
  <dt>
    params</dt>
  <dd>
    (optional) Parameters to bind to this statement. This can be an array or an object. See notes below.</dd>
  <dt>
    onRow</dt>
  <dd>
    (optional) Function that is called when a row has been received.</dd>
</dl>
<p>The return value is a promise that is resolved when the statement has finished execution.</p>
<p>When a statement is executed via <code>executeCached()</code>, the prepared statement object is cached inside the opened connection. The next time this same SQL statement is executed (the <code>sql </code>argument is identical to one passed in before), the old statement object is reused. This saves time associated with parsing the SQL statement and creating a new statement object. The downside is the cached statement object lingers in the opened connection, taking up memory.</p>
<p>When a statement is executed via <code>execute()</code>, the underlying statement object is thrown away as soon as the statement finishes execution.</p>
<p><code>executeCached()</code> is recommended for statements that will be executed many times. <code>execute()</code> is recommended for statements that will be executed seldomly or once.</p>
<p>Please note that callers don't need to prepare statements manually before execution. Just call <code>executeCached()</code> and the statement will be prepared for you automatically.</p>
<p>Parameters can be bound to the statement by defining the <code>params </code>argument. This argument can be an array of positional parameters or an object of named parameters. If the statement does not contain any bound parameters, this argument can be omitted are specified as null.</p>
<p>{{ note("Callers are strongly encouraged to use bound parameters instead of dynamically creating SQL statements for security reasons. Bound parameters aren't prone to SQL injection like manual SQL statement construction (e.g. concatenating a raw value into the executed SQL statement).") }}</p>
<div class="line" id="LC381">
  <span class="cm">{{ note("Callers are encouraged to pass objects rather than Arrays for bound parameters because they prevent foot guns. With positional arguments, it is simple to modify the parameter count or positions without fixing all users of the statement. Objects/named parameters are a little safer because changes in the order of parameters in the statement won't change how values are bound to those parameters.") }}</span></div>
<div class="line">
  &nbsp;</div>
<div class="line">
  <span class="cm">When <code>onRow </code>is not defined, </span>the full results of the operation are buffered before the caller is notified of statement completion. For <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code> statements, this is not relevant. However, it can have drastic implications for <code>SELECT </code>statements. If your <code>SELECT </code>statement could return lots of data, this buffering of returned rows could result in excessive memory usage. Therefore, it's recommended to use <code>onRow </code>with <code>SELECT </code>statements.</div>
<div class="line">
  <div class="line">
    &nbsp;</div>
  <div class="line" id="LC396">
    <span class="cm">If a <code>StopIteration </code>is thrown during execution of an <code>onRow</code> handler, the execution of the statement is immediately cancelled. Subsequent rows will not be processed and no more <code>onRow </code>invocations will be made. The promise is resolved immediately.</span></div>
  <div class="line" id="LC397">
    &nbsp;</div>
  <div class="line" id="LC400">
    <span class="cm">If a non-<code>StopIteration </code>exception is thrown by the <code>onRow </code>handler, the exception is logged and processing of subsequent rows occurs as if nothing happened. The promise is still resolved (not rejected).</span></div>
  <p><span class="cm">The return promise will be rejected with an <code>Error </code>instance if the statement</span><span class="cm"> did not finish execution fully. The <code>Error </code>may have an <code>errors</code> property.</span><span class="cm"> If defined, it will be an Array of objects describing individual errors.</span><span class="cm"> Each object has the properties <code>result</code> and <code>message</code>. <code>result</code> is a</span><span class="cm"> numeric error code and <code>message </code>is a string description of the problem.</span></p>
  <p>If <code>onRow </code>is specified, the returned promise will be resolved with null. Else, the resolved value will be an array of <a href="/en-US/docs/MozIStorageRow" title="/en-US/docs/MozIStorageRow"><code>mozIStorageRow</code></a>.</p>
  <h4 id="executeTransaction(func.2C_type)">executeTransaction(func, type)</h4>
  <p>This function is used to execute a database transaction. A transaction is a series of related statements treated as one functional unit. If the transaction succeeds, all the statements contained within it are committed as one unit. If the transaction fails, the database is rolled back to its state before the transaction started.</p>
  <p>This function receives the following arguments:</p>
  <dl>
    <dt>
      func</dt>
    <dd>
      The function defining the transaction body.</dd>
    <dt>
      type</dt>
    <dd>
      The type of transaction to perform. This must be one of the TRANSACTION_* constants on the opened connection instance. Valid values are <code><span class="nx">TRANSACTION_DEFERRED</span></code><span class="p">,</span> <code><span class="nx">TRANSACTION_IMMEDIATE</span></code><span class="o">,</span> <code><span class="nx">TRANSACTION_EXCLUSIVE</span></code><span class="o">. See the SQLite documentation for their meaning.</span> The default is <code>TRANSACTION_DEFERRED</code>.</dd>
  </dl>
  <p>The passed function is a Task.jsm compatible generator function. When called, the function receives as its argument the current connection instance. This generator function is expected to yield promises, likely those returned by calling <code>executeCached()</code> and <code>execute()</code>.</p>
  <p>If we reach the end of the generator function without error, the transaction is committed. If an error occurs, the transaction is rolled up.</p>
  <p>The returned value from this function is a promise that is resolved when the transaction has been committed or is rejected if the transaction was rolled back.</p>
  <h4 id="lastInsertRowID">lastInsertRowID</h4>
  <p>This property returns the row ID from the last INSERT operation.</p>
  <p>{{ note("Due to the asynchronous execution of statements, use of this can be unreliable, especially if multiple statements are executed in parallel. A more robust method to obtain a row ID of a recent INSERT is to SELECT details from the inserted row.") }}</p>
  <h4 id="affectedRows">affectedRows</h4>
  <p>This property returns the number of rows that were updated, deleted, or added by the last operation.</p>
  <p>{{ note("This has the same caveats as lastInsertRowID.") }}</p>
  <h2 id="Examples">Examples</h2>
  <h3 id="Open.2C_Execute.2C_Close">Open, Execute, Close</h3>
  <p>In this example, we open a connection, execute a simple statement, then close the connection.</p>
  <pre>
Sqlite.openConnection({path: "MyDB.sqlite"}).then(
  function onOpen(conn) {
    conn.execute("SELECT 1").then(
      function onStatementComplete(result) {
        conn.close(function onClose() {
          print("We are done!");
        }
      }
    }
  }
}
</pre>
  <p>This isn't a terrific example because it doesn't include error handling and is somewhat difficult to read.</p>
  <p>Here is the same thing but with a Task.jsm task:</p>
  <pre>
Task.spawn(function demoDatabase() {
  let conn = Sqlite.openConnection({path: "MyDB.sqlite"});

  try {
    let result = yield conn.execute("SELECT 1");
  } finally {
    yield conn.close();
  }
});</pre>
  <h3 id="Bound_Parameters">Bound Parameters</h3>
  <p>Here are some examples demonstrating bound parameters. Assume we open an opened connection in the <code>conn </code>variable.</p>
  <pre>
let dataToInsert = [
  ["foo", "bar"],
  ["biz", "baz"],
  {"yo", "ho"],
];

Task.spawn(function doInsert() {
  for (let data of dataToInsert) {
    yield conn.executeCached("INSERT INTO myTable VALUES (?, ?)", data);
  }
});</pre>
</div>
<p>And the same thing with named parameters.</p>
<pre>
let dataToInsert = [
  {paramA: "foo", paramB: "bar"},
  {paramA: "biz", paramB: "baz"},
  {paramA: "yo", paramB: "ho"},
];

Task.spawn(function doInsert() {
  for (let data of dataToInsert) {
    yield conn.executeCached("INSERT INTO myTable VALUES (:paramA, :paramB)", data);
  }
});</pre>
<h3 id="Transactions">Transactions</h3>
<p>These examples demonstrate how transactions work.</p>
<pre>
conn.executeTransaction(function simpleTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["biz", "baz"]);
});</pre>
<p>The above will result in 2 INSERT statements being committed in a deferred transaction (assuming the inserts proceed without error, of course).</p>
<p>Let's do an example where we want to force a transaction rollback.</p>
<pre>
conn.executeTransaction(function complexTransaction() {
  yield conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  let data = yield conn.execute("SELECT * FROM myTable");
  if (data.length &lt; 5) {
    throw new Error("We don't have the expected 5 rows to perform the next operation!");
  }

  // ...
});</pre>
Revert to this revision