Using prepared statements

[EK.IceFlake]

How would we go about using prepared statements with SQLite?

[.]

I had some basic examples about using them in the original topic of this module "[WIP] Hybrid GM" when I did a performance comparison against INI. But for some reason that topic is missing from the forum.

Most likely you're already using a prepared statement. If you're using db.Query() or making SQLite.Statement instances directly then you're already using them. Any select statement requires a prepared statement. The only time you're not using one is when you use the Exec() or Queue() functions. And even then you're using one. You just don't see it or work directly with it.

What you're probably asking is how to add parameters to them. I haven't tested the following example for syntactical correctness but you should get the idea:

Code Select Expand

// Open a dummy database
local db = SQLite.Connection("test.db");
// Attempt to create a sample table
db.Exec(@"CREATE TABLE IF NOT EXISTS [Accounts] (
    [ID]            INTEGER
                        PRIMARY KEY AUTOINCREMENT
                        UNIQUE
                        NOT NULL,
    [User]          VARCHAR (64)
                        UNIQUE
                        NOT NULL,
    [Pass]          VARCHAR (128)
                        NOT NULL,
    [Money]         INTEGER
                        DEFAULT (0),
    [Ratio]         FLOAT
                        DEFAULT (0),
    [Active]        BOOLEAN
                        DEFAULT (0),
    [Joined]        INTEGER
                        DEFAULT (0)
);");
// We enter a new scope so we don't clutter the current one
{
    // Create an pre-compiled statement directly
    local query = SQLite.Statement(db, @"INSERT INTO [Accounts] (ID, User, Pass, Money, Ratio, Active, Joined)
                                            VALUES (@id, @user, @pass, @money, @ratio, @active, @joined);");
    // Same can also be achieved with
    //local query = db.Query(@"INSERT INTO [Accounts] (ID, User, Pass, Money, Ratio, Active, Joined)
    //                            VALUES (@id, @user, @pass, @money, @ratio, @active, @joined);");
    // Grab references to statement parameters to avoid lookups inside the loop
    // If you're going to do use the query multiple times in a loop or something.
    //  better to cache the parameters and avoid looking for them every time
    local p_id = query.Param("@id");
    local p_user = query.Param("@user");
    local p_pass = query.Param("@pass");
    local p_money = query.Param("@money");
    local p_ratio = query.Param("@ratio");
    local p_active = query.Param("@active");
    local p_joined = query.Param("@joined");
    // When doing a ton of changes to the database where there's a chance of failure.
    // I'd recommend using a transaction so that you can roll back changes in case of errors
    // Not only this increases the insert/update speed significantly. but also makes it safer to work with the database
    // Transactions can be used for multiple statement/queries which make them perfect agains issues when
    //  creating/initializing an account or something similar where you need to create data in database from different places
    local sqtrans = SQLite.Transaction(db);
    // Insert bulk rows into the table
    for (local i = 0; i < 10; ++i)
    {
        // Reset the statement before using it
        // Only necessary if you've used/executed the statement previously
        query.Reset();
        // NOTE: we don't do query.Clear() so any previously bound values are still there!
        // Bind the values into the query through the found oarameters
        p_id.SetInteger(i);
        p_user.SetStringF("dummy_%d", i);
        p_pass.SetString(SqHash.GetSHA256("dummy_pass_%d", i));
        p_money.SetInteger(SqRand.Integer());
        p_ratio.SetFloat(SqRand.Float());
        p_active.SetBool(SqRand.Bool());
        if (i % 2 == 0)
        {
            p_joined.SetNow();
        }
        else
        {
            p_joined.SetDatetime(SqDatetime(2016, 3, 20, 7, 35, 14));
        }
        // Attempt to execute the query/statement with the bound values
        query.Exec();
        // query.Step() for selects
    }
    // Commit database changes during the transaction
    sqtrans.Commit();
    // As usual, we reset the statement before using it since we know we used it above
    query.Reset();
    // If we know that we're going to do a single insert then we don't need to save the parameters into variables
    query.Param("@id").SetInteger(10);
    query.Param("@user").SetString("dummy_10");
    query.Param("@pass").SetString(SqHash.GetSHA256("dummy_pass_%d", i));
    query.Param("@money").SetInteger(129273);
    query.Param("@ratio").SetFloat(2.342453);
    query.Param("@active").SetBool(true);
    query.Param("@joined").SetNow();
    // Execute the query/statement
    // Notice we haven't use a transaction here since we only do a single insert
    query.Exec();
}
// We can enter a new scope again
{
    // Now we can do a select on the inserted data. Again, using parameters.
    // This example is kinda useless but is meant to show that parameters work with select statements as well
    local query = db.Query(@"SELECT * FROM [Accounts] WHERE [ID] = @id");
    // Let's cache some parameters so we don't search for them each time in the loop
    local p_id = query.Param("@id");
    // Select each row individually (not recommended in the real world)
    for (local i = 0; i <= 10; ++i)
    {
        // Reset the statement before using it
        query.Reset();
        // Bind the `id` parameter
        p_id.SetInteger(i);
        // If the .Step() method returns true then we have a row that we can access
        // Keep calling .Step() if you know there are multiple rows selected
        while (query.Step())
        {
            // Retrieve the "user" field/column (which could've cached above like the parameters)
            // and retrieve it as a string through the .String property
            printf("%d : %s", i, query.Field("User").String);\
            // the following are equivalent to the above
            //printf("%d : %s", i, query.Column("User").String);
            //printf("%d : %s", i, query.GetString("User"));
            // last one is probably faster since it doesn't have to make any temporary instances for the column wrapper/reference
        }
        // ^ should only select a row so we can probably use an if statement in this case (but as an example)
    }
}