Hi Lemodizon,
in order for my code to make sense, please see this explanation first. I have been collecting tips and tricks for LiveCode either by arriving at solutions myself or copying other's. I regret I have no recollection where I borrowed this from and slightly edited (if you are the author please let me know), but it explains how to retrieve SQL data both ways (with a simple TSV variable
or a database cursor). I plan to create a subreddit to post these all at some point and open it to others to post clear solutions/algorithms, but for now these reside on my own machine.
When you connect to the database, you receive a connection ID (a number - 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:
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.
The second function is
revQueryDatabase, which executes the query and returns a
cursor ID (a number) - this doesn't contain the data itself, but the ID identifies 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:
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 (much like navigating cards in a stack), 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>
