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.

In order to get binary you must use revQueryDatabase and revDatabaseColumnNamed (no other way), for example to get the image data store in a database where images data are in a image column:

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"
Community content is available under CC-BY-SA unless otherwise noted.