sqlite_bind_param

mIRC SQLite

sqlite_bind_param
Binds a variable as a parameter for prepared statement.
Syntax
$sqlite_bind_param ( statement, param, var [, datatype ] )
/sqlite_bind_param statement param var [ datatype ]
Parameters
statement
The prepared statement identifier.
param
The parameter to bind to. Must exist in the prepared query.
var
The variable or binary variable to bind to.
datatype
Optional. Tells what datatype var is. See remarks.
Return Value
1 on success, or $null if there was an error.
Remarks
$sqlite_bind_param can be used to bind a variable to a parameter. The variable is bound as a reference and is evaluated at the time of execution. This means that by changing the variable in mIRC, you're effectively changing the bound value as well.

The param parameter can either be a numerical index, specified with a ? in the query, or a named parameter specified with a :name in the query. If binding a named parameter, you should also include the colon in the name.

The var parameter is considered as a binary variable if it starts with a &. Otherwise it's considered as a regular variable. You should not prefix the var with a %; otherwise mIRC will evaluate the variable right away. If not a binary variable, the specified variable must be a global variable because local variables only exist in scope of the alias they're declared in; mSQLite has no access to them. See example below.

If datatype is specified, it must be one of the $SQLITE_INTEGER, $SQLITE_FLOAT, $SQLITE_TEXT, $SQLITE_BLOB and $SQLITE_NULL. The datatype tells what datatype var is. If omitted, mSQLite will attempt to deduce the datatype of the variable at execution time. You should specify datatype when you want a numerical variable to act as a text for example.

For more information about parameter binding, see Prepared Statements.
Example
; Open a temporary db
var %db = $sqlite_open()

; Binds one numerical and one named parameter two times
var %sql = SELECT ?, :test
var %stmt = $sqlite_prepare(%db, %sql)
if (%stmt) {
  ; Binds %first as first parameter, and &second; as second parameter.
  ; Do not prefix the variable with a % or mIRC will evaluate the variable beforehand.
  sqlite_bind_param %stmt 1 first
  ; If datatype isn't specified the binary variable would be considered a blob by mSQLite.
  sqlite_bind_param %stmt :test &second; $SQLITE_TEXT

  ; We can declare the variables after they're bound because they aren't evaluated before the query is executed.
  set %first Hello
  bset -t &second; 1 World

  ; Execute the query and show the results
  var %result = $sqlite_query(%stmt)
  if ($sqlite_fetch_row(%result, row, $SQLITE_NUM)) {
    echo -a First execution:
    echo -a 1st: $hget(row, 1)
    echo -a 2nd: $hget(row, 2)
  }
  sqlite_free %result

  ; Change the first parameter to something else, you don't need to call sqlite_bind_param again!
  set %first Another

  ; Execute the query again and show the new results
  var %result = $sqlite_query(%stmt)
  if ($sqlite_fetch_row(%result, row, $SQLITE_NUM)) {
    echo -a Second execution:
    echo -a 1st: $hget(row, 1)
    echo -a 2nd: $hget(row, 2)
  }
  sqlite_free %result
  sqlite_free %stmt
}
else {
  echo -a Error preparing query: %sqlite_errstr
}

sqlite_close %db

; Output:
; First execution:
; 1st: Hello
; 2nd: World
; Second execution:
; 1st: Another
; 2nd: World