Storage

  • Revision slug: Storage
  • Revision title: Storage
  • Revision id: 309047
  • Created:
  • Creator: KWierso
  • Is current revision? No
  • Comment

Revision Content

Storage is a SQLite database API. It is available to trusted callers, meaning extensions and Firefox components only.

The API is currently "unfrozen", which means it is subject to change at any time; in fact, it has changed somewhat with each release of Firefox since it was introduced, and will likely continue to do so for a while.

Note: Storage is not the same as the DOM:Storage feature which can be used by web pages to store persistent data or the Session store API (an XPCOM storage utility for use by extensions).

Getting started

This document covers the Storage API and some peculiarities of SQLite. It does not cover SQL or "regular" SQLite. You can find some very useful links in the See also section however. For Storage API help, you can post to mozilla.dev.apps.platform on the news server news.mozilla.org. To report bugs, use Bugzilla.

The overall procedure for use is:

  1. Get the Storage service - {{ interface("mozIStorageService") }}.
  2. Open a connection to the database of your choice - {{ interface("mozIStorageConnection") }}.
  3. Create statements to execute on the connection - {{ interface("mozIStorageStatement") }}.
  4. Bind parameters to a statement as necessary.
  5. Execute the statement.
  6. Check for errors.
  7. Reset the statement.

Opening a connection

JavaScript example of opening my_db_file_name.sqlite in the profile directory:

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

let file = FileUtils.getFile("ProfD", ["my_db_file_name.sqlite"]);
let mDBConn = Services.storage.openDatabase(file); // Will also create the file if it does not exist

Likewise, the C++ would look like this:

nsCOMPtr<nsIFile> dbFile;
rv = NS_GetSpecialDirectory(NS_APP_USER_PROFILE_50_DIR,
                            getter_AddRefs(dbFile));
NS_ENSURE_SUCCESS(rv, rv);
rv = dbFile->Append(NS_LITERAL_STRING("my_db_file_name.sqlite"));
NS_ENSURE_SUCCESS(rv, rv);
nsCOMPtr<mozIStorageService> dbService =
  do_GetService(MOZ_STORAGE_SERVICE_CONTRACTID, &rv);
NS_ENSURE_SUCCESS(rv, rv);

nsCOMPtr<mozIStorageConnection> dbConn;
rv = dbService->OpenDatabase(dbFile, getter_AddRefs(dbConn));
NS_ENSURE_SUCCESS(rv, rv);
Note: MOZ_STORAGE_SERVICE_CONTRACTID is defined in {{ Source("storage/build/mozStorageCID.h") }}.

{{ Warning("It may be tempting to give your database a name ending in '.sdb' for sqlite database, but this is not recommended. This extension is treated specially by Windows as a known extension for an 'Application Compatibility Database' and changes are backed up by the system automatically as part of system restore functionality. This can result in significantly higher overhead file operation.") }}

Closing a connection

To close a connection on which only synchronous transactions were performed, use the {{ ifmethod("mozIStorageConnection", "close") }} method. If you performed any asynchronous transactions, you should instead use the {{ ifmethod("mozIStorageConnection", "asyncClose") }} method. The latter will allow all ongoing transactions to complete before closing the connection, and will optionally notify you via callback when the connection is closed.

Statements

This section demonstrates how you can execute SQL statements on your database. For a complete reference see {{ interface("mozIStorageStatement") }}.

Creating a Statement

There are actually two ways to execute a statement. You should choose the right one based on your needs.

No Results to be Returned

{{ Storage:synchronous_io_warning() }}

If you do not need to get any results back, you can use {{ ifmethod("mozIStorageConnection", "executeSimpleSQL") }} API like this in JavaScript:

dbConn.executeSimpleSQL("CREATE TEMP TABLE table_name (column_name INTEGER)");

Similarly, the C++ looks like this:

rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TEMP TABLE table_name (column_name INTEGER)"));
NS_ENSURE_SUCCESS(rv, rv);

Results to be Returned

However, if you need to get results back, you should create the statement with the {{ ifmethod("mozIStorageConnection", "createStatement") }} API like this in JavaScript:

var statement = dbConn.createStatement("SELECT * FROM table_name WHERE column_name = :parameter");

This example uses a named placeholder called "parameter" to be bound later (described in {{ anch("Binding Parameters") }}). Similarly, the C++ looks like this:

nsCOMPtr<mozIStorageStatement> statement;
rv = dbConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE column_name = ?1"),
                             getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

This example uses the numbered placeholder indexed by zero for a parameter to be bound later (described in {{ anch("Binding Parameters") }}).

{{ Note("Numerical indexes for parameters are always one less than the number you write in the SQL. The use of numerical indexes for parameters is strongly discouraged in JavaScript where named parameters are much easier to use.") }}

{{ Note("If you need to execute a statement multiple times, caching the result of createStatement will give you a noticeable performance improvement because the SQL query does not need to be parsed each time.") }}

Binding Parameters

In order to effectively use the statements that you create, you have to bind values to the parameters you placed in the statement. A given placeholder can appear multiple times in the same statement, and all instances of it will be replaced with the bound value. If you neglect to bind a value to a parameter, it will be interpreted as NULL.

You should never try to construct SQL statements on the fly with values inserted in them. By binding the parameters, you prevent possible SQL injection attacks since a bound parameter can never be executed as SQL.

Binding One Set of Parameters

If you only have one row to insert, or are using the synchronous API you'll need to use this method. In JavaScript, there is a useful helper object ({{ interface("mozIStorageStatementParams") }}) available ({{ gecko_minversion_inline("1.9.1") }}) that makes binding parameters much easier:

var statement = dbConn.createStatement("SELECT * FROM table_name WHERE id = :row_id");
statement.params.row_id = 1234;

You can still use this helper object by manually creating the statement wrapper, {{ interface("mozIStorageStatementWrapper") }}, which is provided in Gecko 1.9.1 and later.

Using named parameters in C++ is a lot more difficult, so it's generally accepted to use numerical placeholders instead. The example below uses {{ ifmethod("mozIStorageStatement", "bindInt32Parameter") }}. The full list of binding functions can be found with the {{ interface("mozIStorageStatement") }} documentation.

C++ example:

nsCOMPtr<mozIStorageStatement> statement;
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE id = ?1"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

rv = statement->BindInt32Parameter(0, 1234);
NS_ENSURE_SUCCESS(rv, rv);

{{ Note("Numerical indexes for parameters are always one less than the number you write in the SQL. The use of numerical indexes for parameters is strongly discouraged in JavaScript where named parameters are much easier to use.") }}

Binding Multiple Sets of Parameters

{{ gecko_minversion_header("1.9.2") }}

Starting in Gecko 1.9.2 (Firefox 3.6), there's a new, more convenient way to bind multiple sets of parameters at once prior to executing your statement asynchronously. This API is only available for asynchronous execution.

let stmt = dbConn.createStatement("INSERT INTO table_name (value) VALUES(:value)");
let params = stmt.newBindingParamsArray();
for (let i = 0; i < 10; i++) {
  let bp = params.newBindingParams();
  bp.bindByName("value", i);
  params.addParams(bp);
}
stmt.bindParameters(params);

You can attach multiple sets of bindings to a statement by adding multiple {{ interface("mozIStorageBindingParams") }} objects to the array of parameter lists, adding each one through calls to the {{ ifandmethod("mozIStorageBindingParamsArray", "addParams") }}. Once all the parameters are set up, a single call to {{ ifmethod("mozIStorageStatement", "bindParameters") }} will ensure that the parameters are bound before execution. You can then execute the statement asynchronously, and the statement will get each set of bindings bound to it before execution asynchronously.

Executing a Statement

You may execute statements either synchronously (which is supported in Firefox Gecko 1.8 and 1.9) or asynchronously (starting in Gecko 1.9.1). If your code needs to work with applications based on Gecko 1.8 or 1.9, you should the technique covered in the section {{ anch("Synchronously") }} below. Otherwise, it's strongly recommended that you use asynchronous execution, for performance reasons.

Asynchronously

{{ gecko_minversion_header("1.9.1") }}

Gecko 1.9.1 introduced support for asynchronous execution of a statement by calling {{ ifmethod("mozIStorageStatement", "executeAsync") }} on the statement. Multiple statements can be executed in a transaction by calling {{ ifmethod("mozIStorageConnection", "executeAsync") }} on the connection and passing in an array of statements. Both of these methods have similar signatures that accept an object as input that receives notifications the execution of the statement(s). A C++ example is omitted here because it would be verbose, but real-world code can be found in the Mozilla source tree ({{ lxrsearch("ident", "i", "mozIStorageStatementCallback") }}).

After you create and bind a statement, your JavaScript should look something like this to execute a statement asynchronously:

statement.executeAsync({
  handleResult: function(aResultSet) {
    for (let row = aResultSet.getNextRow();
         row;
         row = aResultSet.getNextRow()) {

      let value = row.getResultByName("column_name");
    }
  },

  handleError: function(aError) {
    print("Error: " + aError.message);
  },

  handleCompletion: function(aReason) {
    if (aReason != Components.interfaces.mozIStorageStatementCallback.REASON_FINISHED)
      print("Query canceled or aborted!");
  }
});

The call to executeAsync takes an object that implements mozIStorageStatementCallback. See its documentation for more details on each method. The callback is optional, however, so if you do not want to receive feedback, you can pass nothing.

Synchronously

{{ Storage:synchronous_io_warning() }}

If you are OK with the possibility of locking up your user interface, or if you are running on a background thread, you can use {{ ifmethod("mozIStorageStatement", "executeStep") }}. This function allows you to enumerate all the results produced by the statement.

As you step through each row, you can obtain each parameter by name through a helper object ({{ interface("mozIStorageStatementRow") }}) in JavaScript ( {{ gecko_minversion_inline("1.9.1") }}) like so:

while (statement.executeStep()) {
  let value = statement.row.column_name;
}

You can create this helper object yourself if it's not available in your version of Gecko. See {{ interface("mozIStorageStatementWrapper") }} for details.

In C++, the code would look something like this:

bool hasMoreData;
while (NS_SUCCEEDED(statement->ExecuteStep(&hasMoreData)) && hasMoreData) {
  PRInt32 value;
  rv = statement->GetInt32(0, &value);
  NS_ENSURE_SUCCESS(rv, rv);
}

You can obtain other types of data by using the various methods available on {{ interface("mozIStorageValueArray") }}.

{{ Storage:type_conversion_warning() }}

Alternatively, if you do not expect any results but still need to execute a bound statement, you can simply call {{ ifmethod("mozIStorageStatement", "execute") }}. This is equivalent to calling {{ ifmethod("mozIStorageStatement", "executeStep") }} and then {{ ifmethod("mozIStorageStatement", "reset") }}.

Resetting a Statement

When you execute a statement synchronously, it is important to make sure you reset your statement. You can accomplish this by calling {{ ifmethod("mozIStorageStatement", "reset") }} on the statement. If you end up finalizing the statement (see {{ ifmethod("mozIStorageStatement", "finalize") }}) you do not need to worry about calling {{ ifmethod("mozIStorageStatement", "reset") }}. You should do this before you reuse the statement.

{{ Warning("If you fail to reset a write statement, it will continue to hold a lock on the database preventing future writes or reads. Additionally, if you fail to reset a read statement, it will prevent any future writes to the database.") }}

In JavaScript, the language makes it pretty easy to ensure that you always reset a statement. Be aware that you should always reset even if an exception is thrown, so your code should look something like this:

var statement = dbConn.createStatement("SELECT * FROM table_name");
try {
  while (statement.step()) {
    // Use the results...
  }
}
finally {
  statement.reset();
}

In C++, Storage provides a helper object in {{ Source("storage/public/mozStorageHelper.h") }}, mozStorageStatementScoper, which ensures that the statement object is reset when the object falls out of scope. Of course, if your statement is local only to the function, you do not have to worry about calling reset since the object will be destroyed.

nsresult
myClass::myFunction()
{
  // mSpecialStatement is a member variable of the class that contains a statement.
  mozStorageStatementScoper scoper(mSpecialStatement);
  // You can use mSpecialStatement without concern now.

  nsCOMPtr<mozIStorageStatement> statement;
  // mDBConn is a database connection that is stored a member variable of the class.
  nsresult rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("DELETE FROM table_name"),
                                         getter_AddRefs(statement));
  NS_ENSURE_SUCCESS(rv, rv);
  return statement->Execute();
  // Once this function returns, mSpecialStatement will be reset, and statement will
  // be destroyed.
}

{{ Note("Calling reset is not an expensive operation, and nothing bad happens if you call reset more than once.") }}

Transactions

Transactions can be used to either improve performance, or group statements together as an atomic operation. In both cases, you execute more than one statement inside of a transaction.

In JavaScript, managing transactions can be difficult when you are using the same connection on different threads, or are using a combination of asynchronous and synchronous statement execution. The best way to deal with this is to only execute your statements asynchronously using {{ ifmethod("mozIStorageConnection", "executeAsync") }}. This method will manage the transactions for you, so you don't have to worry about them.

{{ Note("The database engine does not support nested transactions, so attempting to start a transaction when one is already active will throw an exception.") }}

Transactions can be started with {{ ifmethod("mozIStorageConnection", "beginTransaction") }} or {{ ifmethod("mozIStorageConnection", "beginTransactionAs") }}. The latter takes one of three constants to describe the type of transaction:

  • {{ ifconstant("mozIStorageConnection", "TRANSACTION_DEFERRED") }}
  • {{ ifconstant("mozIStorageConnection", "TRANSACTION_IMMEDIATE") }}
  • {{ ifconstant("mozIStorageConnection", "TRANSACTION_EXCLUSIVE") }}

{{ ifmethod("mozIStorageConnection", "beginTransaction") }} is equivalent to calling {{ ifmethod("mozIStorageConnection", "beginTransactionAs") }} and passing {{ ifconstant("mozIStorageConnection", "TRANSACTION_DEFERRED") }}. In general, this is the method you want to use.

Once you start a transaction, you can either commit the changes by calling {{ ifmethod("mozIStorageConnection", "commitTransaction") }}, or rollback the changes by calling {{ ifmethod("mozIStorageConnection", "rollbackTransaction") }}.

In C++ code, there is a helper class defined in {{ Source("storage/public/mozStorageHelper.h") }}, mozStorageTransaction, that will attempt to get a transaction for you, and handle it appropriately when it falls out of scope. If a transaction is already in progress, no transaction is obtained. If your function returns without calling Commit on the helper object, the transaction will be rolled back.

nsresult
myClass::myFunction()
{
  // mDBConn is a member variable of our mozIStorageConnection.
  mozStorageTransaction transaction(mDBConn);

  // Execute some statements.  If we encounter an error, the transaction will
  // be rolled back.

  return transaction.Commit();
}

Collation (sorting)

SQLite provides several collation methods (BINARY, NOCASE, and RTRIM), but these are all very simple and have no support for various text encodings or the user's locale.

{{ gecko_minversion_header("1.9.2") }}

{{ Gecko("1.9.2") }} added support for several new collation methods:

locale
Case- and accent-insensitive collation.
locale_case_sensitive
Case-sensitive, accent-insensitive collation.
locale_accent_sensitive
Case-insensitive, accent-sensitive collation.
locale_case_accent_sensitive
Case- and accent-sensitive collation.

You can use them quite simply in your SELECT queries, like this:

var stmt = aConn.createStatement("SELECT * FROM foo ORDER BY name COLLATE locale ASC");
var results = [];

while (stmt.executeStep()) {
  results.push(stmt.row.t);
}
stmt.finalize();

How to Corrupt a Database

SQLite is very good about maintaining database integrity, but there are a few things you can do that can lead to database corruption. You can find out more by reading SQLite's documentation on this. There are a few simple things you can do to help make sure this doesn't happen:

  • Open more than one connection to the same file with names that aren't exactly the same as determined by strcmp. This includes "my.db" and "../dir/my.db" or, on Windows (case-insensitive) "my.db" and "My.db". Sqlite tries to handle many of these cases, but you shouldn't count on it.
  • Access a database from a symbolic or hard link.
  • Access a statement from more than one thread (discussed in {{ Anch("Thread safety") }}).
  • Call {{ ifmethod("mozIStorageService", "backupDatabaseFile") }} on a locked database, assuming this will leave your database locked. Due to {{ bug(626193) }}, locked databases get unlocked when you call this.

Thread Safety

{{ interface("mozIStorageService") }} and {{ interface("mozIStorageConnection") }} are thread safe. However, no other interface or method is, so do not use them on different threads at the same time!

If you want to use concurrency to work on your database, you should use the asynchronous APIs provided by Storage.

See also

  • {{ interface("mozIStorageConnection") }} Database connection to a specific file or in-memory data storage
  • {{ interface("mozIStorageStatement") }} Create and execute SQL statements on a SQLite database.
  • {{ interface("mozIStorageValueArray") }} Wraps an array of SQL values, such as a result row.
  • {{ interface("mozIStorageFunction") }} Create a new SQLite function.
  • {{ interface("mozIStorageAggregateFunction") }} Create a new SQLite aggregate function.
  • {{ interface("mozIStorageProgressHandler") }} Monitor progress during the execution of a statement.
  • {{ interface("mozIStorageStatementWrapper") }} Storage statement wrapper
  • {{ interface("mozIStorageService") }} Storage Service

{{ languages( { "es": "es/Almacenamiento", "fr": "fr/Storage", "ja": "ja/Storage", "pl": "pl/Storage" } ) }}

Revision Source

<p><strong>Storage</strong> is a <a class="external" href="http://www.sqlite.org/">SQLite</a> database API. It is available to trusted callers, meaning extensions and Firefox components only.</p>
<p>The API is currently "unfrozen", which means it is subject to change at any time; in fact, it has changed somewhat with each release of Firefox since it was introduced, and will likely continue to do so for a while.</p>
<div class="note">
  <strong>Note:</strong> Storage is not the same as the <a href="/en/DOM/Storage" title="en/DOM/Storage">DOM:Storage</a> feature which can be used by web pages to store persistent data or the <a href="/en/Session_store_API" title="en/Session_store_API">Session store API</a> (an <a href="/en/XPCOM" title="en/XPCOM">XPCOM</a> storage utility for use by extensions).</div>
<h2 id="Getting_started">Getting started</h2>
<p>This document covers the Storage API and some peculiarities of SQLite. It does <em>not</em> cover SQL or "regular" SQLite. You can find some very useful links in the <a href="#See_also">See also section</a> however. For Storage API help, you can post to mozilla.dev.apps.platform on the news server news.mozilla.org. To report bugs, use <a class="link-https" href="https://bugzilla.mozilla.org/enter_bug.cgi?product=Toolkit&amp;component=Storage">Bugzilla</a>.</p>
<p>The overall procedure for use is:</p>
<ol>
  <li>Get the Storage service - {{ interface("mozIStorageService") }}.</li>
  <li>Open a connection to the database of your choice - {{ interface("mozIStorageConnection") }}.</li>
  <li>Create statements to execute on the connection - {{ interface("mozIStorageStatement") }}.</li>
  <li>Bind parameters to a statement as necessary.</li>
  <li>Execute the statement.</li>
  <li>Check for errors.</li>
  <li>Reset the statement.</li>
</ol>
<h2 id="Opening_a_connection" name="Opening_a_connection">Opening a connection</h2>
<p>JavaScript example of opening <code>my_db_file_name.sqlite</code> in the profile directory:</p>
<pre class="brush: js">
Components.utils.import("resource://gre/modules/Services.jsm");
Components.utils.import("resource://gre/modules/FileUtils.jsm");

let file = FileUtils.getFile("ProfD", ["my_db_file_name.sqlite"]);
let mDBConn = Services.storage.openDatabase(file); // Will also create the file if it does not exist
</pre>
<p>Likewise, the C++ would look like this:</p>
<pre class="brush: cpp">
nsCOMPtr&lt;nsIFile&gt; dbFile;
rv = NS_GetSpecialDirectory(NS_APP_USER_PROFILE_50_DIR,
                            getter_AddRefs(dbFile));
NS_ENSURE_SUCCESS(rv, rv);
rv = dbFile-&gt;Append(NS_LITERAL_STRING("my_db_file_name.sqlite"));
NS_ENSURE_SUCCESS(rv, rv);
nsCOMPtr&lt;mozIStorageService&gt; dbService =
  do_GetService(MOZ_STORAGE_SERVICE_CONTRACTID, &amp;rv);
NS_ENSURE_SUCCESS(rv, rv);

nsCOMPtr&lt;mozIStorageConnection&gt; dbConn;
rv = dbService-&gt;OpenDatabase(dbFile, getter_AddRefs(dbConn));
NS_ENSURE_SUCCESS(rv, rv);
</pre>
<div class="note">
  Note: <code>MOZ_STORAGE_SERVICE_CONTRACTID</code> is defined in {{ Source("storage/build/mozStorageCID.h") }}.</div>
<p>{{ Warning("It may be tempting to give your database a name ending in '.sdb' for <strong>s</strong>qlite <strong>d</strong>ata<strong>b</strong>ase, but this is <em>not recommended.</em> This extension is treated specially by Windows as a known extension for an 'Application Compatibility Database' and changes are backed up by the system automatically as part of system restore functionality. This can result in significantly higher overhead file operation.") }}</p>
<h2 id="Statements" name="Statements">Closing a connection</h2>
<p>To close a connection on which only synchronous transactions were performed, use the {{ ifmethod("mozIStorageConnection", "close") }} method. If you performed any asynchronous transactions, you should instead use the {{ ifmethod("mozIStorageConnection", "asyncClose") }} method. The latter will allow all ongoing transactions to complete before closing the connection, and will optionally notify you via callback when the connection is closed.</p>
<h2 id="Statements" name="Statements">Statements</h2>
<p>This section demonstrates how you can execute SQL statements on your database. For a complete reference see {{ interface("mozIStorageStatement") }}.</p>
<h3 id="Creating_a_statement" name="Creating_a_statement">Creating a Statement</h3>
<p>There are actually two ways to execute a statement. You should choose the right one based on your needs.</p>
<h4 id="No_Results_to_be_Returned">No Results to be Returned</h4>
<p>{{ Storage:synchronous_io_warning() }}</p>
<p>If you do not need to get any results back, you can use {{ ifmethod("mozIStorageConnection", "executeSimpleSQL") }} API like this in JavaScript:</p>
<pre class="brush: js">
dbConn.executeSimpleSQL("CREATE TEMP TABLE table_name (column_name INTEGER)");
</pre>
<p>Similarly, the C++ looks like this:</p>
<pre class="brush: cpp">
rv = mDBConn-&gt;ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TEMP TABLE table_name (column_name INTEGER)"));
NS_ENSURE_SUCCESS(rv, rv);</pre>
<h4 id="Results_to_be_Returned">Results to be Returned</h4>
<p>However, if you need to get results back, you should create the statement with the {{ ifmethod("mozIStorageConnection", "createStatement") }} API like this in JavaScript:</p>
<pre class="brush: js">
var statement = dbConn.createStatement("SELECT * FROM table_name WHERE column_name = :parameter");
</pre>
<p>This example uses a named placeholder called "parameter" to be bound later (described in {{ anch("Binding Parameters") }}). Similarly, the C++ looks like this:</p>
<pre class="brush: cpp">
nsCOMPtr&lt;mozIStorageStatement&gt; statement;
rv = dbConn-&gt;CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE column_name =&nbsp;?1"),
                             getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);
</pre>
<p>This example uses the numbered placeholder indexed by zero for a parameter to be bound later (described in {{ anch("Binding Parameters") }}).</p>
<p>{{ Note("Numerical indexes for parameters are always one less than the number you write in the SQL. The use of numerical indexes for parameters is strongly discouraged in JavaScript where named parameters are much easier to use.") }}</p>
<p>{{ Note("If you need to execute a statement multiple times, caching the result of createStatement will give you a noticeable performance improvement because the SQL query does not need to be parsed each time.") }}</p>
<h3 id="Binding_parameters" name="Binding_parameters">Binding Parameters</h3>
<p>In order to effectively use the statements that you create, you have to bind values to the parameters you placed in the statement. A given placeholder can appear multiple times in the same statement, and all instances of it will be replaced with the bound value. If you neglect to bind a value to a parameter, it will be interpreted as <code>NULL</code>.</p>
<div class="warning">
  You should never try to construct SQL statements on the fly with values inserted in them. By binding the parameters, you prevent possible SQL injection attacks since a bound parameter can never be executed as SQL.</div>
<h4 id="Binding_One_Set_of_Parameters">Binding One Set of Parameters</h4>
<p>If you only have one row to insert, or are using the synchronous API you'll need to use this method. In JavaScript, there is a useful helper object ({{ interface("mozIStorageStatementParams") }}) available ({{ gecko_minversion_inline("1.9.1") }}) that makes binding parameters much easier:</p>
<pre class="brush: js">
var statement = dbConn.createStatement("SELECT * FROM table_name WHERE id = :row_id");
statement.params.row_id = 1234;
</pre>
<p>You can still use this helper object by manually creating the statement wrapper, {{ interface("mozIStorageStatementWrapper") }}, which is provided in Gecko 1.9.1 and later.</p>
<p>Using named parameters in C++ is a lot more difficult, so it's generally accepted to use numerical placeholders instead. The example below uses {{ ifmethod("mozIStorageStatement", "bindInt32Parameter") }}.<code><span style="font-family: Verdana,Tahoma,sans-serif;"> The full list of </span></code>binding functions can be found with the {{ interface("mozIStorageStatement") }} documentation.</p>
<p>C++ example:</p>
<pre class="brush: cpp">
nsCOMPtr&lt;mozIStorageStatement&gt; statement;
rv = mDBConn-&gt;CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE id =&nbsp;?1"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

rv = statement-&gt;BindInt32Parameter(0, 1234);
NS_ENSURE_SUCCESS(rv, rv);
</pre>
<p>{{ Note("Numerical indexes for parameters are always one less than the number you write in the SQL. The use of numerical indexes for parameters is strongly discouraged in JavaScript where named parameters are much easier to use.") }}</p>
<h4 id="Binding_Multiple_Sets_of_Parameters">Binding Multiple Sets of Parameters</h4>
<p>{{ gecko_minversion_header("1.9.2") }}</p>
<p>Starting in Gecko 1.9.2 (Firefox 3.6), there's a new, more convenient way to bind multiple sets of parameters at once prior to executing your statement asynchronously. This API is only available for asynchronous execution.</p>
<pre class="brush: js">
let stmt = dbConn.createStatement("INSERT&nbsp;INTO table_name (value) VALUES(:value)");
let params = stmt.newBindingParamsArray();
for (let i = 0; i &lt; 10; i++) {
  let bp = params.newBindingParams();
  bp.bindByName("value", i);
  params.addParams(bp);
}
stmt.bindParameters(params);
</pre>
<p>You can attach multiple sets of bindings to a statement by adding multiple {{ interface("mozIStorageBindingParams") }} objects to the array of parameter lists, adding each one through calls to the {{ ifandmethod("mozIStorageBindingParamsArray", "addParams") }}. Once all the parameters are set up, a single call to {{ ifmethod("mozIStorageStatement", "bindParameters") }} will ensure that the parameters are bound before execution. You can then <a href="/en/Storage#Asynchronously" title="en/Storage#Asynchronously">execute the statement asynchronously</a>, and the statement will get each set of bindings bound to it before execution asynchronously.</p>
<h3 id="Executing_a_statement" name="Executing_a_statement">Executing a Statement</h3>
<p>You may execute statements either synchronously (which is supported in Firefox Gecko 1.8 and 1.9) or asynchronously (starting in Gecko 1.9.1). If your code needs to work with applications based on Gecko 1.8 or 1.9, you should the technique covered in the section {{ anch("Synchronously") }} below. Otherwise, it's strongly recommended that you use asynchronous execution, for performance reasons.</p>
<h4 id="Asynchronously">Asynchronously</h4>
<p>{{ gecko_minversion_header("1.9.1") }}</p>
<p>Gecko 1.9.1 introduced support for asynchronous execution of a statement by calling {{ ifmethod("mozIStorageStatement", "executeAsync") }} on the statement. Multiple statements can be executed in a transaction by calling {{ ifmethod("mozIStorageConnection", "executeAsync") }} on the connection and passing in an array of statements. Both of these methods have similar signatures that accept an object as input that receives notifications the execution of the statement(s). A C++ example is omitted here because it would be verbose, but real-world code can be found in the Mozilla source tree ({{ lxrsearch("ident", "i", "mozIStorageStatementCallback") }}).</p>
<p>After you create and bind a statement, your JavaScript should look something like this to execute a statement asynchronously:</p>
<pre class="brush: js">
statement.executeAsync({
  handleResult: function(aResultSet) {
    for (let row = aResultSet.getNextRow();
         row;
         row = aResultSet.getNextRow()) {

      let value = row.getResultByName("column_name");
    }
  },

  handleError: function(aError) {
    print("Error: " + aError.message);
  },

  handleCompletion: function(aReason) {
    if (aReason != Components.interfaces.mozIStorageStatementCallback.REASON_FINISHED)
      print("Query canceled or aborted!");
  }
});
</pre>
<p>The call to <code>executeAsync</code> takes an object that implements <a href="/en-US/docs/MozIStorageStatementCallback" title="/en-US/docs/MozIStorageStatementCallback">mozIStorageStatementCallback</a>. See its documentation for more details on each method. The callback is optional, however, so if you do not want to receive feedback, you can pass nothing.</p>
<h4 id="Synchronously">Synchronously</h4>
<p>{{ Storage:synchronous_io_warning() }}</p>
<p>If you are OK with the possibility of locking up your user interface, or if you are running on a background thread, you can use {{ ifmethod("mozIStorageStatement", "executeStep") }}. This function allows you to enumerate all the results produced by the statement.</p>
<p>As you step through each row, you can obtain each parameter by name through a helper object ({{ interface("mozIStorageStatementRow") }}) in JavaScript ( {{ gecko_minversion_inline("1.9.1") }}) like so:</p>
<pre class="brush: js">
while (statement.executeStep()) {
  let value = statement.row.column_name;
}
</pre>
<p>You can create this helper object yourself if it's not available in your version of Gecko. See {{ interface("mozIStorageStatementWrapper") }} for details.</p>
<p>In C++, the code would look something like this:</p>
<pre class="brush: cpp">
bool hasMoreData;
while (NS_SUCCEEDED(statement-&gt;ExecuteStep(&amp;hasMoreData)) &amp;&amp; hasMoreData) {
  PRInt32 value;
  rv = statement-&gt;GetInt32(0, &amp;value);
  NS_ENSURE_SUCCESS(rv, rv);
}
</pre>
<p>You can obtain other types of data by using the various methods available on {{ interface("mozIStorageValueArray") }}.</p>
<p>{{ Storage:type_conversion_warning() }}</p>
<p>Alternatively, if you do not expect any results but still need to execute a bound statement, you can simply call {{ ifmethod("mozIStorageStatement", "execute") }}. This is equivalent to calling {{ ifmethod("mozIStorageStatement", "executeStep") }} and then {{ ifmethod("mozIStorageStatement", "reset") }}.</p>
<h3 id="Resetting_a_Statement">Resetting a Statement</h3>
<p>When you execute a statement synchronously, it is important to make sure you reset your statement. You can accomplish this by calling {{ ifmethod("mozIStorageStatement", "reset") }} on the statement. If you end up finalizing the statement (see {{ ifmethod("mozIStorageStatement", "finalize") }}) you do not need to worry about calling {{ ifmethod("mozIStorageStatement", "reset") }}. You should do this before you reuse the statement.</p>
<p>{{ Warning("If you fail to reset a write statement, it will continue to hold a lock on the database preventing future writes or reads. Additionally, if you fail to reset a read statement, it will prevent any future writes to the database.") }}</p>
<p>In JavaScript, the language makes it pretty easy to ensure that you always reset a statement. Be aware that you should always reset even if an exception is thrown, so your code should look something like this:</p>
<pre class="brush: js">
var statement = dbConn.createStatement("SELECT * FROM table_name");
try {
  while (statement.step()) {
    // Use the results...
  }
}
finally {
  statement.reset();
}
</pre>
<p>In C++, Storage provides a helper object in {{ Source("storage/public/mozStorageHelper.h") }}, <code>mozStorageStatementScoper</code>, which ensures that the statement object is reset when the object falls out of scope. Of course, if your statement is local only to the function, you do not have to worry about calling reset since the object will be destroyed.</p>
<pre class="brush: cpp">
nsresult
myClass::myFunction()
{
  // mSpecialStatement is a member variable of the class that contains a statement.
  mozStorageStatementScoper scoper(mSpecialStatement);
  // You can use mSpecialStatement without concern now.

  nsCOMPtr&lt;mozIStorageStatement&gt; statement;
  // mDBConn is a database connection that is stored a member variable of the class.
  nsresult rv = mDBConn-&gt;CreateStatement(NS_LITERAL_CSTRING("DELETE FROM&nbsp;table_name"),
                                         getter_AddRefs(statement));
  NS_ENSURE_SUCCESS(rv, rv);
  return statement-&gt;Execute();
  // Once this function returns, mSpecialStatement will be reset, and statement will
  // be destroyed.
}
</pre>
<p>{{ Note("Calling reset is not an expensive operation, and nothing bad happens if you call reset more than once.") }}</p>
<h2 id="Transactions" name="Transactions">Transactions</h2>
<p>Transactions can be used to either improve performance, or group statements together as an atomic operation. In both cases, you execute more than one statement inside of a transaction.</p>
<p>In JavaScript, managing transactions can be difficult when you are using the same connection on different threads, or are using a combination of asynchronous and synchronous statement execution. The best way to deal with this is to only execute your statements asynchronously using {{ ifmethod("mozIStorageConnection", "executeAsync") }}. This method will manage the transactions for you, so you don't have to worry about them.</p>
<p>{{ Note("The database engine does not support nested transactions, so attempting to start a transaction when one is already active will throw an exception.") }}</p>
<p>Transactions can be started with {{ ifmethod("mozIStorageConnection", "beginTransaction") }} or {{ ifmethod("mozIStorageConnection", "beginTransactionAs") }}. The latter takes one of three constants to describe the type of transaction:</p>
<ul>
  <li>{{ ifconstant("mozIStorageConnection", "TRANSACTION_DEFERRED") }}</li>
  <li>{{ ifconstant("mozIStorageConnection", "TRANSACTION_IMMEDIATE") }}</li>
  <li>{{ ifconstant("mozIStorageConnection", "TRANSACTION_EXCLUSIVE") }}</li>
</ul>
<p>{{ ifmethod("mozIStorageConnection", "beginTransaction") }} is equivalent to calling {{ ifmethod("mozIStorageConnection", "beginTransactionAs") }} and passing {{ ifconstant("mozIStorageConnection", "TRANSACTION_DEFERRED") }}. In general, this is the method you want to use.</p>
<p>Once you start a transaction, you can either commit the changes by calling {{ ifmethod("mozIStorageConnection", "commitTransaction") }}, or rollback the changes by calling {{ ifmethod("mozIStorageConnection", "rollbackTransaction") }}.</p>
<p>In C++ code, there is a helper class defined in {{ Source("storage/public/mozStorageHelper.h") }}, <code>mozStorageTransaction</code>, that will attempt to get a transaction for you, and handle it appropriately when it falls out of scope. If a transaction is already in progress, no transaction is obtained. If your function returns without calling <code>Commit</code> on the helper object, the transaction will be rolled back.</p>
<pre class="brush: cpp">
nsresult
myClass::myFunction()
{
  // mDBConn is a member variable of our mozIStorageConnection.
  mozStorageTransaction transaction(mDBConn);

  // Execute some statements.  If we encounter an error, the transaction will
  // be rolled back.

  return transaction.Commit();
}
</pre>
<h2 id="Collation_(sorting)">Collation (sorting)</h2>
<p>SQLite provides several collation methods (<code>BINARY</code>, <code>NOCASE</code>, and <code>RTRIM</code>), but these are all very simple and have no support for various text encodings or the user's locale.</p>
<p>{{ gecko_minversion_header("1.9.2") }}</p>
<p>{{ Gecko("1.9.2") }} added support for several new collation methods:</p>
<dl>
  <dt>
    <code>locale</code></dt>
  <dd>
    Case- and accent-insensitive collation.</dd>
  <dt>
    <code>locale_case_sensitive</code></dt>
  <dd>
    Case-sensitive, accent-insensitive collation.</dd>
  <dt>
    <code>locale_accent_sensitive</code></dt>
  <dd>
    Case-insensitive, accent-sensitive collation.</dd>
  <dt>
    <code>locale_case_accent_sensitive</code></dt>
  <dd>
    Case- and accent-sensitive collation.</dd>
</dl>
<p>You can use them quite simply in your <code>SELECT</code> queries, like this:</p>
<pre>
var stmt = aConn.createStatement("SELECT&nbsp;*&nbsp;FROM foo ORDER&nbsp;BY name COLLATE locale ASC");
var results = [];

while (stmt.executeStep()) {
  results.push(stmt.row.t);
}
stmt.finalize();
</pre>
<h2 id="How_to_corrupt_your_database" name="How_to_corrupt_your_database">How to Corrupt a Database</h2>
<p>SQLite is very good about maintaining database integrity, but there are a few things you can do that can lead to database corruption. You can find out more by reading <a class="external" href="http://www.sqlite.org/lockingv3.html" title="http://www.sqlite.org/lockingv3.html">SQLite's documentation on this</a>. There are a few simple things you can do to help make sure this doesn't happen:</p>
<ul>
  <li>Open more than one connection to the same file with names that aren't exactly the same as determined by <code>strcmp</code>. This includes "my.db" and "../dir/my.db" or, on Windows (case-insensitive) "my.db" and "My.db". Sqlite tries to handle many of these cases, but you shouldn't count on it.</li>
</ul>
<ul>
  <li>Access a database from a symbolic or hard link.</li>
</ul>
<ul>
  <li>Access a statement from more than one thread (discussed in {{ Anch("Thread safety") }}).</li>
</ul>
<ul>
  <li>Call {{ ifmethod("mozIStorageService", "backupDatabaseFile") }} on a locked database, assuming this will leave your database locked. Due to {{ bug(626193) }}, locked databases get unlocked when you call this.</li>
</ul>
<h2 id="Thread_safety" name="Thread_safety">Thread Safety</h2>
<p>{{ interface("mozIStorageService") }} and {{ interface("mozIStorageConnection") }} are thread safe. However, no other interface or method is, so do not use them on different threads at the same time!</p>
<p>If you want to use concurrency to work on your database, you should use the asynchronous APIs provided by Storage.</p>
<h2 id="See_also" name="See_also">See also</h2>
<ul>
  <li>{{ interface("mozIStorageConnection") }} Database connection to a specific file or in-memory data storage</li>
  <li>{{ interface("mozIStorageStatement") }} Create and execute SQL statements on a SQLite database.</li>
  <li>{{ interface("mozIStorageValueArray") }} Wraps an array of SQL values, such as a result row.</li>
  <li>{{ interface("mozIStorageFunction") }} Create a new SQLite function.</li>
  <li>{{ interface("mozIStorageAggregateFunction") }} Create a new SQLite aggregate function.</li>
  <li>{{ interface("mozIStorageProgressHandler") }} Monitor progress during the execution of a statement.</li>
  <li>{{ interface("mozIStorageStatementWrapper") }} Storage statement wrapper</li>
  <li>{{ interface("mozIStorageService") }} Storage Service</li>
</ul>
<ul>
  <li><a href="/en/Storage/Performance" title="en/Storage/Performance">Storage:Performance</a> How to get your database connection performing well.</li>
  <li><a class="link-https" href="https://addons.mozilla.org/en-US/firefox/addon/3072">Storage Inspector Extension</a> Makes it easy to view any sqlite database files in the current profile.</li>
  <li><a class="external" href="http://www.sqlite.org/lang.html">SQLite Syntax</a> Query language understood by SQLite</li>
  <li><a class="external" href="http://sqlitebrowser.sourceforge.net/">SQLite Database Browser</a> is a capable free tool available for many platforms. It can be handy for examining existing databases and testing SQL statements.</li>
  <li><a class="link-https" href="https://addons.mozilla.org/en-US/firefox/addon/5817">SQLite Manager Extension</a> helps manage sqlite database files on your computer.</li>
</ul>
<p>{{ languages( { "es": "es/Almacenamiento", "fr": "fr/Storage", "ja": "ja/Storage", "pl": "pl/Storage" } ) }}</p>
Revert to this revision