Class: Statement

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: api.js, line 276
See: Database.html#prepare-dynamic
https://en.wikipedia.org/wiki/Prepared_statement

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: api.js, line 330
See: http://www.sqlite.org/datatype3.html
http://www.sqlite.org/lang_expr.html#varparam

Throws:

SQLite Error
Type String

Returns:

true if it worked

Type:: boolean

Examples

Bind values to named parameters

CODE

     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

CODE

 // 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: api.js, line 681

Returns:

true in case of success

Type:: boolean

["freemem"]()#

Free the memory allocated during parameter binding

Source: api.js, line 671

["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: api.js, line 426

Returns:

One row of result

Type:: Array.<Database.SqlValue>

Example

Print all the rows of the table test to the console

CODE

     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: api.js, line 492
See: Statement.get

Returns:

The row of result

Type:: Object.<string, Database.SqlValue>

Example

CODE

 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: api.js, line 467

Returns:

The names of the columns

Type:: Array.<string>

Example

CODE

 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: api.js, line 522

Returns:

The normalized SQL string

Type:: string

Example

CODE

 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: api.js, line 506

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: api.js, line 662

["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: api.js, line 531

["step"]() → {boolean}#

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

Source: api.js, line 348

Throws:

SQLite Error
Type String

Returns:

true if a row of result available

Type:: boolean

Type Definitions#

BindParams#

Type:

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

Source: api.js, line 287

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

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

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

	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}

	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']

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