Home › API › SIMPLE_SQL_PREPARED_STATEMENT

SIMPLE_SQL_PREPARED_STATEMENT

Pre-compiled SQL statement with parameter binding. Provides security against SQL injection and improved performance for repeated queries.

View Source

When to Use

Use prepared statements when executing the same query multiple times with different parameters, or when handling user input to prevent SQL injection.

Creation

make (a_db: SIMPLE_SQL_DATABASE; a_sql: READABLE_STRING_8)

Create prepared statement. SQL can contain ? placeholders.

make_with_named_params (a_db: SIMPLE_SQL_DATABASE; a_sql: READABLE_STRING_8)

Create with named parameters using :name or @name syntax.

Parameter Binding (positional)

bind_integer (a_index: INTEGER; a_value: INTEGER_64)

Bind integer value at 1-based position.

bind_real (a_index: INTEGER; a_value: REAL_64)

Bind floating-point value.

bind_text (a_index: INTEGER; a_value: READABLE_STRING_GENERAL)

Bind text value (properly escaped).

bind_blob (a_index: INTEGER; a_value: MANAGED_POINTER)

Bind binary data.

bind_null (a_index: INTEGER)

Bind NULL value.

Parameter Binding (named)

bind_named_integer (a_name: READABLE_STRING_8; a_value: INTEGER_64)

Bind integer to named parameter.

bind_named_text (a_name: READABLE_STRING_8; a_value: READABLE_STRING_GENERAL)

Bind text to named parameter.

bind_named_real (a_name: READABLE_STRING_8; a_value: REAL_64)

Bind real to named parameter.

bind_named_blob (a_name: READABLE_STRING_8; a_value: MANAGED_POINTER)

Bind blob to named parameter.

Execution

execute

Execute statement (for INSERT, UPDATE, DELETE).

execute_query: SIMPLE_SQL_RESULT

Execute and return results (for SELECT).

execute_cursor: SIMPLE_SQL_CURSOR

Execute and return lazy cursor.

reset

Reset statement for re-execution with new parameters.

clear_bindings

Clear all bound parameter values.

Lifecycle

finalize

Release SQLite resources. Always call when done.

Examples

Positional Parameters

local
    stmt: SIMPLE_SQL_PREPARED_STATEMENT
do
    create stmt.make (db, "INSERT INTO users (name, email) VALUES (?, ?)")

    stmt.bind_text (1, "Alice")
    stmt.bind_text (2, "alice@example.com")
    stmt.execute

    -- Reuse for another insert
    stmt.reset
    stmt.bind_text (1, "Bob")
    stmt.bind_text (2, "bob@example.com")
    stmt.execute

    stmt.finalize
end

Named Parameters

local
    stmt: SIMPLE_SQL_PREPARED_STATEMENT
    result: SIMPLE_SQL_RESULT
do
    create stmt.make_with_named_params (db, "SELECT * FROM orders WHERE user_id = :user AND status = :status")

    stmt.bind_named_integer ("user", 42)
    stmt.bind_named_text ("status", "pending")
    result := stmt.execute_query

    across result as row loop
        print (row.integer_value ("id").out + "%N")
    end

    stmt.finalize
end

Bulk Insert with Transaction

local
    stmt: SIMPLE_SQL_PREPARED_STATEMENT
    i: INTEGER
do
    db.begin_transaction
    create stmt.make (db, "INSERT INTO logs (message, level) VALUES (?, ?)")

    from i := 1 until i > 1000 loop
        stmt.reset
        stmt.bind_text (1, "Log message " + i.out)
        stmt.bind_integer (2, (i \\ 3) + 1)  -- Levels 1-3
        stmt.execute
        i := i + 1
    end

    stmt.finalize
    db.commit
end

Resource Management

Always call finalize when done with a prepared statement to release SQLite resources.