As for retrieving data I wrote up something short when experimenting with Reddit (I gave it up due to lack of interest). This is the text from that particular post:
Retrieve SQL data in LiveCode
When you connect to the database, you receive a
connection ID (an integer - if not a number then contains error information) that you can use to read data from the database or manipulate the data stored therein.
There are two functions for getting information out of the database:
1. The first function is
revDataFromQuery, which returns a single variable, with the selected fields ofthe records that fit the criteria in your SQL query in TSV text format.
Example:
Code: Select all
put "SELECT cust_id,cust_name FROM Customers" into tQuery
put revDataFromQuery(return,tab,tConnectionID,tQuery) into tData
put tData into field "Table Field"
You would have a return-and-tab-delimited list like this:
000001<tab>Jane Doe<return>
000002<tab>Jeff Doe<return>
000003<tab>John Doe<return>
This function is great if you're looking to display some data easily and quickly. But it would be hard to parse out individual fields, and that's where the next function comes to play.
2) The second function is
revQueryDatabase, which executes the query and
returns a cursor ID - this doesn't contain the data itself, it's an integer that points to a 'database cursor' which itself is a collection of pointers to the records in the database that fulfil the SQL query's requirements.
You can think of this as navigating cards in a stack where the stack is identified as the cursor ID.
To determine how many records there are and which is the current one
* revNumberOfRecords(<cursor id>)
* revCurrentRecord(<cursor id>)
To navigate the records in the result set, you use the commands:
* revMoveToFirstRecord <cursor id>
* revMoveToPreviousRecord <cursor id>
* revMoveToNextRecord <cursor id>
* revMoveToLastRecord <cursor id>
To determine what fields are in those records, you use:
* revDatabaseColumnCount(<cursor id>)
* revDatabaseColumnNames(<cursor id>)
To fetch the individual fields of the current record, you use:
* revDatabaseColumnNumbered(<cursor id>,<column number>)
* revDatabaseColumnNamed(<cursor id>,<column name>)
To release the result set from memory, you use:
* revCloseCursor <cursor id>