SQL Yoga version 1.1.0 build 4 :: Visit website

Printer friendly version

command sqlquery_set

Sets properties on a SQL Query Object array.

command sqlquery_set @pQueryA, pProp, pValue
Simple parameter. @pQueryA A SQL Query Object array as created using sqlquery_createObject().
Simple parameter. pProp The property to set.
Simple parameter. pValue The value to set the property to.


column delimiter: When retrieving a query as data or converting a query cursor to data the column delimiter is used. Default is TAB.

column names length: Allows you to explicitly select just column name (short), the table and column name (long) or the default behavior. The setting only applies to columns that SQL Yoga generates the SQL for. If you manually set the "select clause" property of the query object this setting will only affect any entries entered as TABLE_NAME.*.

conditions: A string representing the search conditions for the SQL query. You can use english or SQL operators. You can also use binding variables with the one caveat being that you need to put single quotes around the binding variables if you are using a string. This is necessary as some conditions in SQL WHERE statements can break if wrapped in single quotes.
A condition using english operators would look something like this: lesson.name contains ':1' AND lesson.description contains ':2' AND lesson.id is in :3 If you are using binding then pass in the binding values as parameters 4 - n, where n = the number of binding variables you use in your conditions string. Support english operators are: IS, IS NOT, IS GREATER THAN, IS GREATER THAN OR EQUAL TO, IS LESS THAN, IS LESS THAN OR EQUAL TO, IS IN, IS NOT IN, IS BETWEEN, CONTAINS, DOES NOT CONTAIN, BEGINS WITH, ENDS WITH, DOES NOT BEGIN WITH, DOES NOT END WITH

condition bindings: A numerically indexed array. The value of [1] will be used to replace :1 in the conditions, [2] for :2, and so on.

connection: Name of connection to use when querying database. Default is the default connection for the Database Object.

distinct: Set to true/false to affect whether a SQL query returns DISTINCT results.

find: Set to "first" to only return the first record in the result.

full column names [deprecated - see column names length]: Set to true to include both the table name and field name in the column name of query results.

group by: Determines how returned rows are grouped.

having: Used to filter the rows returned using group by.

limit: The number of rows to limit the result to. Useful for large record sets of which you only want a certain number. Default is empty.

offset: The number of rows to skip when returning the results. Setting this to 21 would skip records 1 to 20. Default is empty.

order by: Fields to sort records by.

[related table] joins: The related tables to include in the results. If you have defined Table Objects and Relationship Objects then you can simply pass in a string like "LEFT JOIN related_table_name". The string will be parsed and the ON portion of the JOIN will be included. If you have not defined Relationship Objects for your Table Objects then you must pass in the full join string, i.e. "JOIN related_table_name ON field = field".

report null: Set to true to return "NULL" as the column value if a column is NULL. Default is 'false' in which case empty is returned for the column value.

row delimiter: When retrieving a query as data or converting a query cursor to data the row delimiter is used. Default is CR.

select clause: Fields to retrieve from the database. The default is empty which means all fields for all tables in the query. Use the syntax TABLE.* to include all fields for a table.

sort by: alias for 'order by'.

table: The name of the primary table for the SQL Query Object. You can pass in "TABLE as ALIAS_NAME" to specify an alias for the table in the query. When setting the 'table' property the "select clause", "related table joins", "group by", "having" and "conditions" properties are reset.

table as: The alias to use for the table in the query.