Prepares a single SQL statement.
slStatement ( db-handle , SQL-statement [, values] )
(s) db-handle: SQLite extender database object handle.
(s) SQL-statement: one plain text SQL statement.
(m/a) values: [optional] An array or a map of statement parameter values.
(s) stm-handle: An extender handle to an SQLite statement object.
This function prepares one SQL statements by compiling it into byte-code and creates a statement object handle for the compiled statement. Use the returned statement object handle with the slStmExec, slStmExecAll, slStmExecMany, or slStmReset extender functions. A compiled statement is faster because the statement does not need to be parsed every time it used. Optionally, the function can add a set of initial parameter values to the compiled statement. See below for more information about SQLite parameters in SQL statements
DB-Handle
The unique identifier returned from a call to the slConnect function.
SQL-statements
A single valid plan-text SQL statements that can contain SQLite parameters. SQLite parameters are similar to WIL substitution variables in WIL script lines except SQLite uses special SQLite parameters syntax instead of percent signs surrounded WIL variable names. When a compiled SQL statement is processed by this or any other extender function that accepts parameter values, these functions replace any unattached parameters with the provided values.
Values
An optional function argument that contains either an array or map of values used to replace SQL parameters in the SQL compiled statement.
The SQLite extender supports two types of parameters:
When statement parameter values are contained in a WIL array use the question mark + number syntax for SQL parameter placeholders. For example, the parameter "?1" means substitute the first element of the WIL array for "?1" in the passed-in SQL statement. Since WIL arrays are zero-based and SQLite parameters are one-based, the array element used has an index that is one less than the parameter number.
When statement parameter values are contained in a WIL map use the colon + name syntax as parameter placeholders. For example, the parameter ":somekey" means substitute the map value associated map key named "somekey" in the passed-in SQL statement.
SQLite allows a parameter wherever a string literal, blob literal, numeric constant, or NULL is allowed in queries or data modification statements. Parameters may not be used for column or table names, or as values for constraints or default values.
Note: You do not need to provide values to statements with embedded parameters. You can add them later using the slStmReset or slStmExecMany functions.
This example use the colon + name parameter syntax with a WIL map. Notice that the parameter values can contain SQL wildcard characters when appropriate for the purposes of the query.
; Create a db object handle to Northwind DB db = slConnect("C:\Examples\northwind.db") SQL = "SELECT SUM(ProductSales) FROM [Sales by Category] WHERE ProductName LIKE :name1 OR ProductName LIKE :name2;" ; Create a map of SQL parameter values and compile the ; statement containing placeholder parameters ; Note use of "%%" for SQL wildcard character "%" mparam["name1"] = "%%stout%%" mparam["name2"] = "%%ale%%" stm = slStatement(db, SQL, mparam) ; Execute the statement result = slStmExecAll(stm, @slRankOne) Message("Combined Stout and Ale Sales", "$":result[0])
This example use the question mark + number parameter syntax with a WIL array.
; Create a db object handle to Northwind DB db = slConnect("C:\Examples\northwind.db") SQL = "SELECT SUM(ProductSales) FROM [Sales by Category] WHERE ProductName LIKE ?1 OR ProductName LIKE ?2;" ; Create an array of SQL parameter values and compile the ; statement containing placeholder parameters. ; Again note use of "%%" for SQL wildcard character "%" aparam[0] = "%%stout%%" aparam[1] = "%%ale%%" stm = slStatement(db, SQL, aparam) ; Execute the statement result = slStmExecAll(stm, @slRankOne) Message("Combined Stout and Ale Sales", "$":result[0])