Statement

Source: api.js:285

Represents a prepared statement. Prepared statements allow you to have a template sql string, that you can execute multiple times with different parameters.

You can't instantiate this class directly, you have to use a Database object in order to create a statement.

Warnings

1. When you close a database (using db.close()), all its statements are closed too and become unusable.
2. After calling db.prepare() you must manually free the assigned memory by calling Statement.free(). Failure to do this will cause subsequent 'DROP TABLE ...' statements to fail with 'Uncaught Error: database table is locked'.

Statements can't be created by the API user directly, only by Database::prepare

Alias: Statement

• See:
  • Database#prepare
  • https://en.wikipedia.org/wiki/Prepared_statement

Constructor

Parameters

• stmt1 (number) — The SQLite statement reference
• db (Database) — The database from which this statement was created

Instance Methods

bind(values: Statement.BindParams): boolean

Bind values to the parameters, after having reseted the statement. If values is null, do nothing and return true.

SQL statements can have parameters, named '?', '?NNN', ':VVV', '@VVV', '$VVV', where NNN is a number and VVV a string. This function binds these parameters to the given values.

Warning: ':', '@', and '$' are included in the parameters names

Value types

Parameters

• values (Statement.BindParams) — The values to bind

Returns

• boolean — true if it worked

Throws

• String — SQLite Error

Example

Bind values to named parameters

var stmt = db.prepare(
        "UPDATE test SET a=@newval WHERE id BETWEEN $mini AND $maxi"
    );
    stmt.bind({$mini:10, $maxi:20, '@newval':5});

Bind values to anonymous parameters

// Create a statement that contains parameters like '?', '?NNN'
var stmt = db.prepare("UPDATE test SET a=? WHERE id BETWEEN ? AND ?");
// Call Statement.bind with an array as parameter
stmt.bind([5, 10, 20]);

• See:
  • http://www.sqlite.org/datatype3.html
  • http://www.sqlite.org/lang_expr.html#varparam

step(): boolean

Execute the statement, fetching the the next line of result, that can be retrieved with Statement.get.

Returns

• boolean — true if a row of result available

Throws

• String — SQLite Error

get(params?: Statement.BindParams): Array.<Database.SqlValue>

Get one row of results of a statement. If the first parameter is not provided, step must have been called before.

Parameters

• params (Statement.BindParams, optional) — If set, the values will be bound to the statement before it is executed

Returns

• Array.<Database.SqlValue> — One row of result

Example

Print all the rows of the table test to the console

var stmt = db.prepare("SELECT * FROM test");
    while (stmt.step()) console.log(stmt.get());

    <caption>Enable BigInt support</caption>
    var stmt = db.prepare("SELECT * FROM test");
    while (stmt.step()) console.log(stmt.get(null, {useBigInt: true}));

getColumnNames(): Array.<string>

Get the list of column names of a row of result of a statement.

Returns

• Array.<string> — The names of the columns

Example

var stmt = db.prepare(
        "SELECT 5 AS nbr, x'616200' AS data, NULL AS null_value;"
    );
    stmt.step(); // Execute the statement
    console.log(stmt.getColumnNames());
    // Will print ['nbr','data','null_value']

getAsObject( params?: Statement.BindParams, ): Object.<string, Database.SqlValue>

Get one row of result as a javascript object, associating column names with their value in the current row.

Parameters

• params (Statement.BindParams, optional) — If set, the values will be bound to the statement, and it will be executed

Returns

• Object.<string, Database.SqlValue> — The row of result

Example

var stmt = db.prepare(
            "SELECT 5 AS nbr, x'010203' AS data, NULL AS null_value;"
        );
        stmt.step(); // Execute the statement
        console.log(stmt.getAsObject());
        // Will print {nbr:5, data: Uint8Array([1,2,3]), null_value:null}

• See:
  • Statement.get

getSQL(): string

Get the SQL string used in preparing this statement.

Returns

• string — The SQL string

getNormalizedSQL(): string

Get the SQLite's normalized version of the SQL string used in preparing this statement. The meaning of "normalized" is not well-defined: see the SQLite documentation.

Returns

• string — The normalized SQL string

Example

db.run("create table test (x integer);");
     stmt = db.prepare("select * from test where x = 42");
     // returns "SELECT*FROM test WHERE x=?;"

run(values?: Statement.BindParams)

Shorthand for bind + step + reset Bind the values, execute the statement, ignoring the rows it returns, and resets it

Parameters

• values (Statement.BindParams, optional) — Value to bind to the statement

reset()

Reset a statement, so that its parameters can be bound to new values It also clears all previous bindings, freeing the memory used by bound parameters.

freemem()

Free the memory allocated during parameter binding

free(): boolean

Free the memory used by the statement

Returns

• boolean — true in case of success

Other

BindParams: Array.<Database.SqlValue> | Object.<string, Database.SqlValue> | null