SQLite

From Pragma
Jump to: navigation, search

Name: wv_sqlite

State:

SHARED

Description

By loading this module, the following classes and enums are made available:

As well as the sqlite library.

Examples

This example will create a database in memory, create a new table, add some data and then read all of the data inside the table and print it to the console.

local r = engine.load_library("wv_sqlite")
if(r ~= true) then
	print("WARNING: An error occured trying to load the 'wv_sqlite' module: ",r)
	return
end
local file = ":memory:" -- Database will only exist in RAM
local r,con = sqlite.open(file)
if(r ~= sqlite.RESULT_OK) then
    print("Unable to open database: ",sqlite.result_to_string(r))
    return
end

-- Exec always runs synchronously
local r = con:Exec([[CREATE TABLE player (
    playerid INT AUTO_INCREMENT,
    name VARCHAR(255),
    data BLOB
);]])
if(r == sqlite.RESULT_OK) then
    -- PrepareStatement is asynchronous if a function callback is provided, otherwise synchronous
    con:PrepareStatement("INSERT INTO player(name,data) VALUES(?,?);",function(r,statement)
        if(r == sqlite.RESULT_OK) then
            statement:BindText(1,"Silverlan")

            -- Blob data requires a DataStream
            local ds = util.DataStream()
            ds:WriteFloat(32.0)
            ds:WriteInt32(55)
            ds:WriteString("Mr. Jackpots")
            statement:BindBlob(2,ds)

            -- Executes the statement
            statement:Step()
            -- Frees the statement. If this isn't called, the statement will eventually be garbage collected
            statement:Finalize()

            -- Statement containing an error
            r,statement = con:PrepareStatement("SELCT * FROM player;")
            if(r == sqlite.RESULT_ERROR) then
                -- Print a description about the SQL error
                print("SQL Statement Error: ",con:GetResultMessage())
            end

            -- Synchronous statement execution
            r,statement = con:PrepareStatement("SELECT * FROM player;")
            if(r == sqlite.RESULT_OK) then
                local resultId = 1
                -- We'll iterate through all found data sets (table rows)
                while(statement:Step() == sqlite.RESULT_ROW) do
                    print("Result #" .. resultId .. ":")
                    for i=0,statement:GetCount() -1 do -- Number of columns in the set
                        print("Column: ",i +1)
                        print("Column Name: ",statement:GetColumnName(i))
                        local type = statement:GetType(i) -- The data type for this column in the set
                        print("Type: ",type)
                        if(type == sqlite.TYPE_INTEGER) then print("Value: ",statement:GetInt(i))
                        elseif(type == sqlite.TYPE_TEXT) then print("Value: ",statement:GetText(i))
                        elseif(type == sqlite.TYPE_BLOB) then
                            local ds = statement:GetBlob(i)
                            -- Read the blob data we've written previously
                            print("Values: ",ds:ReadFloat(),ds:ReadInt32(),ds:ReadString())
                        end
                    end
                    resultId = resultId +1
                end
                statement:Finalize()
            else print("Unable to select data: ",sqlite.result_to_string(r)) end
        else print("Unable to insert data: ",sqlite.result_to_string(r)) end
        -- Closes the database connection. If this isn't called, the connection will eventually be garbage collected
        con:Close()
    end)
else print("Unable to create table: ",sqlite.result_to_string(r)) end

See Also