roSqliteStatement
Â
This object is created by calling the CreateStatement()
 method on an roSqliteDatabase object. The JavaScript equivalent is to use the IndexedDB.
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.
Examples
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()
Â
Â
Â
Â
Â
Â