SQLite is embedded in LiveCode, so you can act directly on an SQLite database file or create a new one just with Livecode.
To connect to a database or create a new one, just use revOpenDatabase function, like this:
put revOpenDatabase("sqlite", "./myDB.sqlite", , , , ) into connID
You have just to indicate the database path and and a variable in which to store the connection ID. It's recommended that the connection ID (it's just an integer) be stored in a custom property.
Then you can retrieve data with the revDataFromQuery function:
put "SELECT * from users ; " into tSQL put revDataFromQuery(tab,return,connID,tSQL) into tRecords
notice that specifing TAB and return as delimeter, you'll obtain a TABBED grid (text) that you may use directly in table fields.
You can use any SQL command with the revExecuteSQL message:
put "INSERT into users (name,surname) VALUES ('Jack','Sparrow')" into tSQL revExecuteSQL connID,tSQL
TIPS: the filter command removes empty lines, it can be handy when user may add too many empty lines in a table:
filter mytext without empty
Working with binariesEdit
In order to upload binaries, you need to use the variable in the revExecuteSQL.
The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon (:). The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called "valueX" and "valueY", you can use a SQLStatement that includes placeholders as follows:
revExecuteSQL myID, "insert into mytable values(:1,:2)", "valueX","valueY"
when using binaries, you have to insert the *b prefix in variable name; so if you variable containing a binary is "valueX", the correct code is:
revExecuteSQL connID, "insert into mytable values(:1)", "*bvalueX"
Since the revExecuteSQL command strips the binary marker "*b" and passes it to the database as binary data, rather than text data.
put revQueryDatabase(connID, "SELECT * FROM images WHERE id=" & field "id" & ";") into tRecordSet put revDatabaseColumnNamed(tRecordSet, "image", tImage) into tError #tImage contains data, tError contains errors revCloseCursor tRecordSet
Example with UPDATE:
put "UPDATE flags SET logo=:1 WHERE ID="& tID &" ;" into tSQL revExecuteSQL connID,tSQL,"*blogo"