SQL Record

SQL Record Objects

Using SQL Record objects SQL Yoga can create, retrieve, update and delete records from a database for you very easily. SQL Yoga can also create hierarchal SQL Record object arrays that represent the relationships in your database.

Representation

A SQL Record object is a simple LiveCode array with special keys prefixed with @. These keys help SQL Yoga locate the table in the database that the record represents among other things.

Single Record

A SQL Record object that represents a single record looks something like this:

tRecordA["@table"] = lessons
tRecordA["@connection"] = development
tRecordA["@database"] = default
tRecordA["id"] = 5
tRecordA["name"] = lesson title
tRecordA["description"] = lesson description

Multiple Records

A SQL Record object can also represent many records in the database using multi-dimensional arrays. Here is what a SQL Record object with 3 records might look like:

tRecordA[1]["@table"] = lessons
tRecordA[1]["@connection"] = development
tRecordA[1]["@database"] = default
tRecordA[1]["id"] = 5
tRecordA[1]["name"] = lesson 1 title
tRecordA[1]["description"] = lesson 1 description

tRecordA[2]["@table"] = lessons
tRecordA[2]["@connection"] = development
tRecordA[2]["@database"] = default
tRecordA[2]["id"] = 7
tRecordA[2]["name"] = lesson 2 title
tRecordA[2]["description"] = lesson 2 description

tRecordA[3]["@table"] = lessons
tRecordA[3]["@connection"] = development
tRecordA[3]["@database"] = default
tRecordA[3]["id"] = 8
tRecordA[3]["name"] = lesson 3 title
tRecordA[3]["description"] = lesson 3 description

Retrieving records from the database

You can use SQL Record objects to quickly find records in the database by using sqlrecord_find.

Create SQL Record Object By Searching (Example 1)

sqlrecord_find allows you to find records in a particular table in the database. The second parameter is what defines the search conditions. You can pass in an integer or name:value,name:value pairs in which case the primary key field of the table is searched and a single record is returned.

## Look for a lessons record that has an 'id' of 1 (id is the primary key field).
## Store the found SQL Record object in tRecordA.
## If no records are returned then the keys of tRecordA will be empty.
sqlrecord_find "lessons", 1, tRecordA

Create SQL Record Object By Searching (Example 2)

For more advanced searches you can pass in an array that contains any properties that you can set on a SQL Query object.

## Advanced search using a parameters array
## Store the found SQL Record(s) in tRecordsA as a numerically indexed array.
put "id is in :1" into tParamsA["conditions"]
put "1,2" into tParamsA["condition bindings"][1]
put "id ASC" into tParamsA["order by"]

sqlrecord_find "lessons", tParamsA, tRecordsA

Creating records in the database

You can create records in a database using sqlrecord_create.

Create SQL Record Object

The first step is to create a SQL Record object:

put sqlrecord_createObject("lessons") into tRecordA
## tRecordA is now an array that includes all fields in the 'lessons' table.

Fill In Values For Table

Now that you have a SQL Record object you can set the column values for the new record. In this example the title column and description column are being assigned values.

sqlrecord_set tRecordA, "title", "My Lesson"
sqlrecord_set tRecordA, "description", "A short lesson I made."

Create Record in Database

Once you are done assigning values to the columns you can create the record in the database.

sqlrecord_create tRecordA
put it into theAffectedRows
## In addition to the other column values, tRecordA now contains the database value for the 'id' (primary key) column.

Updating records in the database

Create SQL Record Object

The first step is to create a SQL Record object:

## Create SQL Record object by getting record from database.
## Store the found record in tRecordA
sqlrecord_find "lessons", 1, tRecordA

Fill In Values For Table

sqlrecord_set tRecordA, "title", "My New Lesson"
sqlrecord_set tRecordA, "description", "A short lesson I made and modified."

Update Record in Database

sqlrecord_update tRecordA
put it into theAffectedRows

Deleting records in the database

Create SQL Record Object

You can search for or create a SQL Record object:

## Create SQL Record object by getting record from database.
## Store the found record in tRecordA
sqlrecord_find "lessons", 1, tRecordA

put sqlrecord_createObject("lessons") into tRecordA
sqlrecord_set tRecordA, "id", 12 ## You must set the primary key column value(s)

Delete Record in Database

sqlrecord_delete tRecordA
put it into theAffectedRows
## The lesson record where id = 12 was deleted from the database