Statement

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:
Name Type Description
stmt1 number

The SQLite statement reference
db Database

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 type SQLite type
number REAL, INTEGER
boolean INTEGER
string TEXT
Array, Uint8Array BLOB
null NULL
Parameters:
Name Type Description
values Statement.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:
Name Type Attributes Description
params Statement.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:
Name Type Attributes Description
params Statement.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:
Name Type Attributes Description
values Statement.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: