6.1-roSqliteStatement

ON THIS PAGE

Firmware Version 6.1

This object is created by calling the CreateStatement() method on an roSqliteDatabase object.

ifSqliteStatement

All bind methods return True upon success.

BindByName(associative_array As Object) As Boolean

Binds the SQL variable(s) using the names contained in the SQL statement.

BindByOffset(associative_array/enumerable As Object) As Boolean

Binds the SQL variable(s) using the index contained in the SQL statement. If passed an associative array, this method will convert the keys of the associative array into numeric offsets when binding. If passed an enumerable object (e.g. roArray), it will bind the values of the enumerable in the order that they are stored.

BindText(variable/index As Object, value As String) As Boolean

Binds the SQL variable indicated by the name or index parameter to the passed string value.

BindInteger(variable/index As Object, value As Integer) As Boolean

Binds the SQL variable indicated by the name or index parameter to the passed integer value.

Run() As Integer

Runs the SQL statement immediately and waits for the integer result. The following are possible integer result codes:

100: Statement complete
101: Busy
102: Rows available

RunBackground() As Integer

Runs the SQL statement in the background. You can use roSqliteDatabase.SetPort() to set a message port that will receive an roSqliteEvent message at a later point. The RunBackground() call will result in an integer transaction ID, which will appear in the roSqliteEvent message that matches the transaction.

GetData() As Object

Returns an associative array of name/value pairs that are available after a SELECT (or similar) operation.

Finalise()

Finalizes the statement. This method should be applied to statements before the parent database is closed. The object should not be used after this method is called. Also note that objects are automatically finalized when they are deleted.

The following script inserts into a table using the BindByName() method:

insertStmt = db.CreateStatement("INSERT INTO playback (md5,path,playback_count) VALUES(:md5_param,:path_param,:pc_param);")

print insertStmt

if type(insertStmt) <> "roSqliteStatement" then
    print "We didn't get a statement returned!!"
    end
endif

params = { md5_param: "ABDEF12346",  path_param: "/foo/bar/bing/bong", pc_param: 11 }

bindResult = insertStmt.BindByName(params)

if bindResult
    print "BindByName OK"
else
    print "BindByName FAILED"
    end
endif

sqlResult = insertStmt.Run()

print sqlResult

if sqlResult = SQLITE_COMPLETE
    print "Table Insertion OK"
else
    print "Table Insertion FAILED"
endif

insertStmt.Finalise()

The following script inserts into a table in the background:

' This examples assume you have set a message port on your roSqliteDatabase instance
'

insertStmt = db.CreateStatement("INSERT INTO playback (md5,path,playback_count) VALUES(:md5_param,:path_param,:pc_param);")

print insertStmt

if type(insertStmt) <> "roSqliteStatement" then
    print "We didn't get a statement returned!!"
    end
endif

params = { md5_param: "ABDEF12348",  path_param: "/foo/bar/bing/bong", pc_param: 13 }

bindResult = insertStmt.BindByName(params)

if bindResult
    print "BindByName OK"
else
    print "BindByName FAILED"
    end
endif

expectedId = insertStmt.RunBackground()

e = mp.WaitMessage(10000)
if e <> invalid then
    if type(e) = "roSqliteEvent" then
        transId = e.GetTransactionId()
        sqlResult = e.GetSqlResult()
        print transId
        print sqlResult
        if transId <> expectedId then
            print "Incorrect transaction Id"
            end
        endif
        if sqlResult <> SQLITE_COMPLETE then
            print "SQL Insertion Failed"
            end
        endif
    else
        print "RunBackground() - Wrong event - FAILED"
        end
    endif
else
    print "RunBackground() - No Response - FAILED"
    end
endif

' You don't need to call Finalise() since that'll be done by the background processor.

The following script queries from a table:

selectStmt = db.CreateStatement("SELECT * FROM playback;")

if type(selectStmt) <> "roSqliteStatement" then
    print "We didn't get a statement returned!!"
    end
endif

sqlResult = selectStmt.Run()

print sqlResult

while sqlResult = SQLITE_ROWS
    resultsData = selectStmt.GetData()
    print resultsData;
    sqlResult = selectStmt.Run()
end while

selectStmt.Finalise()