SQL Query

SQL Query Object

SQL Query objects are arrays that represent an object reference and the object's properties. This object defines what a database query will do. You create a SQL Query object by passing the name of a table to sqlquery_create. SQL Queries are stored within variables and typically only persist while the application is running.

Querying the database

Create A SQL Query Object

put sqlquery_createObject("lessons") into tQueryA

Set Search Conditions

You can specify the WHERE condition of a query by setting the conditions property. You can even use english words rather such as contains:

put the text of field "SearchCriteria" into tUserSearchString

sqlquery_set tQueryA, "conditions", "lessons.title contains ':1'", tUserSearchString

Set Sorting Field

sqlquery_set tQueryA "order by", "lessons.title"

Get Data From A SQL Query Object

Use any of the following commands to retrieve data from a SQL Query object. You can get data as an array, raw data or as records. The second parameter to each command is passed by reference and will contain the data from your database after the call has completed.

If you aren't familiar with what records are in SQL Yoga you can refer to the documentation on SQL Record objects.

sqlquery_retrieveAsArray tQueryA, tArrayA

sqlquery_retrieveAsData tQueryA, tData

sqlquery_retrieveAsRecords tQueryA, tRecordsA

For further information see the documentation for sqlquery_retrieveAsArray, sqlquery_retrieveAsData and sqlquery_retrieveAsRecords.

Creating records in the database

A SQL Query object provides a quick interface for creating data in a database.

Importing: When using sqlquery_create none of the validations or callbacks you've defined for Table objects will be triggered. You must use SQL Record objects in order to use those features.

Create An Array

The first step is to fill in an array variable with the values you want to insert into the database. The array keys should be the names of the columns in your database table.

put "My Dog's Car" into tRowA["title"]
put false into tRowA["draft"]

Create The Record

Once you have created the array you just pass it as a parameter to sqlquery_create along with the name of the table you want to create the record in. SQL Yoga will generate the query for you.

sqlquery_create "lessons", tRowA
put it into tAffectedRows

Deleting records in the database

A SQL Query object provides a quick interface for deleting data in a database.

Importing: When using sqlquery_delete none of the validations or callbacks you've defined for Table objects will be triggered. You must use SQL Record objects in order to use those features.

Create An Object

The first step is to create a SQL Query object that can be passed to sqlquery_delete.

put sqlquery_createObject("lessons") into tQueryA

Define Conditions

When calling sqlquery_delete, the where clause property of the SQL Query object will be used to perform the delete. You define this property by setting the conditions property using sqlquery_set.

## Specify that lesson with an id of 12 should be deleted.
sqlquery_set tQueryA, "conditions", "id is :1", 12

Delete

## Perform the delete.
sqlquery_delete tQueryA
put it into tAffectedRows

Using cursors with a SQL Query object

After setting properties for a SQL Query object you can use sqlquery_retrieveAsArray, sqlquery_retrieveAsData and sqlquery_retrieveAsRecords to retrieve your data without having to fuss with a database cursor. You can also access the database cursor directly when needed.

sqlquery_retrieve

A SQL Query object can open a database cursor using sqlquery_retrieve. This command generates a SQL query using the SQL Query object properties and then opens the database cursor. You are then responsible for closing the database cursor using sqlquery_close.

You can access the RevDB cursor id through the cursor property:

put sqlquery_get(theQueryA, "cursor") into tCursor

## Do something with the cursor...

sqlquery_close theQueryA

Navigating the Cursor

Once you have opened a database cursor there are a couple of SQL Query object handlers that help navigate the cursor. They are:

• sqlquery_moveToFirstRecord
• sqlquery_moveToLastRecord
• sqlquery_moveToNextRecord
• sqlquery_moveToPreviousRecord
• sqlquery_moveToRecord

Here is an example:

put sqlquery_createObject("lessons") into tQueryA
sqlquery_set tQueryA, "conditions", "id < 4"

sqlquery_retrieve tQueryA

sqlquery_moveToNextRecord tQueryA
if the result is true then
  ## You are now on the second record in the result set.
end if