SqlJs Statement

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

Constructor

new Statement(stmt1, db)

Parameters:
NameTypeDescription
stmt1number

The SQLite statement reference

dbDatabase

The database from which this statement was created

Source
See

Methods

["bind"](values) → {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

Javascript typeSQLite type
numberREAL, INTEGER
booleanINTEGER
stringTEXT
Array, Uint8ArrayBLOB
nullNULL
Parameters:
NameTypeDescription
valuesStatement.BindParams

The values to bind

Source
See
Throws:

SQLite Error

Type
String
Returns:

true if it worked

Type: 
boolean
Examples

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]);

["free"]() → {boolean}

Free the memory used by the statement

Source
Returns:

true in case of success

Type: 
boolean

["freemem"]()

Free the memory allocated during parameter binding

Source

["get"](paramsopt) → {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:
NameTypeAttributesDescription
paramsStatement.BindParams<optional>

If set, the values will be bound to the statement before it is executed

Source
Returns:

One row of result

Type: 
Array.<Database.SqlValue>
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}));

["getAsObject"](paramsopt) → {Object.<string, Database.SqlValue>}

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

Parameters:
NameTypeAttributesDescription
paramsStatement.BindParams<optional>

If set, the values will be bound to the statement, and it will be executed

Source
See
  • Statement.get
Returns:

The row of result

Type: 
Object.<string, Database.SqlValue>
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}

["getColumnNames"]() → {Array.<string>}

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

Source
Returns:

The names of the columns

Type: 
Array.<string>
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']

["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.

Source
Returns:

The normalized SQL string

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

["getSQL"]() → {string}

Get the SQL string used in preparing this statement.

Source
Returns:

The SQL string

Type: 
string

["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.

Source

["run"](valuesopt)

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

Parameters:
NameTypeAttributesDescription
valuesStatement.BindParams<optional>

Value to bind to the statement

Source

["step"]() → {boolean}

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

Source
Throws:

SQLite Error

Type
String
Returns:

true if a row of result available

Type: 
boolean

Type Definitions

BindParams

Type:
Source