Contents
- qbInit
- qbSetDbType
- qbSetSanitize
- qbTable
- qbSetDistinct
- qbLimit
- qbOffset
- qbOrderBy
- qbWhere
- qbAndWhere
- qbOrWhere
- qbOn
- qbJoin
- qbSelect
- qbInsert
- qbInsertSubstituted
- qbUpdate
- qbDelete
- qbCreateDatabase
- qbDropDatabase
- qbSelectDatabase
- qbCreateTable
- qbDropTable
- qbExecute
- qbExecuteSubstituted
- qbExecuteAndReturnData
- qbExecuteAndReturnDataArray
- qbSanitize
- qbSQLConnect
- qbSQLDisconnect
qbInit
Summary
Initializes all internal variables
Name
lcSQLQueryBuilder
Card
Stack
Example
qbInit
Description
This command positions the elements on the card
This command initializes all internal variables to empty.
qbSetDbType
Card
Stack
qbSetSanitize
Card
Stack
qbTable
Summary
Adds a single table or multiple tables to query
Name
lcSQLQueryBuilder
Parameters
- pTable String,Array table name, table attributes and values
Card
Stack
Example
put empty into tTable
put "a" into tTable["address"]
put "u" into tTable["user"]
put empty into tParams
put "u.username" into tParams[1]
put "a.user_id" into tParams[2]
qbTable tTable
qbWhere "u.username", "=", "''oscar''"
qbAndWhere "u.rowid", "=", "a.user_id"
put qbSelect(tParams) into tQuery
Description
This commands adds a single table or multiple tables to the SQL query.
A single table can be parsed as a string parameter. Multiple tables have
to be parsed as an array.
qbSetDistinct
Summary
Display only unique column values
Name
lcSQLQueryBuilder
Parameters
- pBool Boolean true or false
Card
Stack
Example
qbTable "user"
qbSetDistinct true
put qbSelect("username") into tQuery
Description
Unique column values may be turned on or off by passing TRUE or FALSE
respectively, to the qbSetDistinct() command. The default is FALSE.
qbLimit
Summary
Sets a LIMIT to the query
Name
lcSQLQueryBuilder
Parameters
- pNum Integer limit number
Card
Stack
Example
qbTable "user"
qbLimit 2
qbOffset 0
put qbSelect() into tQuery
Description
This command sets a LIMIT clause to the query with the limit number
as parameter.
qbOffset
Summary
Sets an offset to the query
Name
lcSQLQueryBuilder
Parameters
- pNum Integer offset number
Card
Stack
Example
qbTable "user"
qbLimit 2
qbOffset 0
put qbSelect() into tQuery
Description
This command sets an OFFSET clause to the query with the offset number
as parameter.
qbOrderBy
Summary
Adds an ORDER BY clause to the query
Name
lcSQLQueryBuilder
Parameters
- pColName String column name
- pDirection String ASC or DESC
Card
Stack
Example
qbTable "address"
qbOrderBy "country", "ASC"
qbOrderBy "city", "ASC"
put qbSelect() into tQuery
Description
This command sets an ORDER BY clause to the query. You can specify
multiple ORDER BY clauses if you need to.
qbWhere
Summary
Adds a WHERE clause to the query
Name
lcSQLQueryBuilder
Parameters
- pValue String value
- pKey String attribute
- pOperator String operator
Card
Stack
Example
qbTable "user"
qbWhere "name", "LIKE", "''%Smi%''"
qbOrWhere "name", "LIKE", "''%Sparr%''"
put qbSelect() into tQuery
Description
This command sets a WHERE clause to the query.
qbAndWhere
Summary
Adds an AND WHERE clause to the query
Name
lcSQLQueryBuilder
Parameters
- pValue String value
- pKey String attribute
- pOperator String operator
Card
Stack
Example
qbTable "user"
qbWhere "name", "LIKE", "''%Smi%''"
qbOrWhere "name", "LIKE", "''%Sparr%''"
put qbSelect() into tQuery
Description
This command sets an AND WHERE clause to the query.
qbOrWhere
Summary
Adds an OR WHERE clause to the query
Name
lcSQLQueryBuilder
Parameters
- pValue String value
- pKey String attribute
- pOperator String operator
Card
Stack
Example
qbTable "user"
qbWhere "name", "LIKE", "''%Smi%''"
qbOrWhere "name", "LIKE", "''%Sparr%''"
put qbSelect() into tQuery
Description
This command sets an OR WHERE clause to the query.
qbOn
Summary
Adds an ON clause to the query
Name
lcSQLQueryBuilder
Parameters
- pValue String value
- pKey String attribute
- pOperator String operator
Card
Stack
Example
put empty into tParams
put "user.username" into tParams[1]
put "address.city" into tParams[2]
qbTable "user"
qbOn "user.rowid", "=", "address.user_id"
qbJoin "address", "LEFT JOIN"
qbWhere "user.name", "=", "''Oscar Smith''"
put qbSelect(tParams) into tQuery
Description
This command sets an ON clause to the query. You can combine the JOIN clause
with an ON clause. Just make sure that qbOn is used before qbJoin as seen in the
example above.
qbJoin
Summary
Adds a JOIN clause to the query
Name
lcSQLQueryBuilder
Parameters
- pTable String table name
- pJoinType String join type
Card
Stack
Example
put empty into tParams
put "user.username" into tParams[1]
put "address.city" into tParams[2]
qbTable "user"
qbOn "user.rowid", "=", "address.user_id"
qbJoin "address", "LEFT JOIN"
qbWhere "user.name", "=", "''Oscar Smith''"
put qbSelect(tParams) into tQuery
Description
This command sets a JOIN clause to the query. You can combine the JOIN clause
with an ON clause. Just make sure that qbOn is used before qbJoin as seen in the
example above. You can specify any string as pJoinType that exists for your database.
qbSelect
Summary
Creates a select query
Name
lcSQLQueryBuilder
Parameters
- pParams Array with parameters and values to select
Returns
tQuery String with select query
Card
Stack
Example
put empty into tParams
put "user.username" into tParams[1]
put "address.zipcode" into tParams[2]
put "address.city" into tParams[3]
qbTable "user"
qbOn "user.rowid", "=", "address.user_id"
qbJoin "address", "CROSS JOIN"
put qbSelect(tParams) into tQuery
Description
This function creates and returns a select query.
qbInsert
Summary
Creates an insert query
Name
lcSQLQueryBuilder
Parameters
- pParams Array with parameters and values to insert
Returns
tQuery String with insert query
Card
Stack
Example
put empty into tParams
put 1 into tParams["user_id"]
put "Teststreet 3" into tParams["street"]
put "9999" into tParams["zipcode"]
put "Zurich" into tParams["city"]
put "Switzerland" into tParams["country"]
qbTable "address"
put qbInsert(tParams) into tQuery
Description
This function creates and returns an insert query.
qbInsertSubstituted
Summary
Creates a substituded insert query
Name
lcSQLQueryBuilder
Parameters
- pParams Array with parameters and values to insert
Returns
Array array[“query”] contains string with query, array[“params”] contains they values in an array with sequential numbers
Card
Stack
Reference
http://forums.livecode.com/viewtopic.php?f=12&t=19050
qbUpdate
Summary
Creates an update query
Name
lcSQLQueryBuilder
Parameters
- pParams Array with parameters and values to update
Returns
tQuery String with update query
Card
Stack
Example
put empty into tParams
put "San Antonio" into tParams["city"]
qbTable "address"
qbWhere "user_id", "=", "1"
qbAndWhere "city", "=", "''New York''"
qbAndWhere "country", "=", "''USA''"
put qbUpdate(tParams) into tQuery
Description
This function creates and returns an update query.
qbDelete
Summary
Create delete query
Name
lcSQLQueryBuilder
Returns
tQuery String with delete query
Card
Stack
Example
qbTable "address"
qbWhere "country", "=", "''Switzerland''"
put qbDelete() into tQuery
Description
This function creates and returns a delete query.
qbCreateDatabase
Summary
Create database query
Name
lcSQLQueryBuilder
Parameters
- pName String database name
Returns
tQuery String create database query
Card
Stack
Example
qbCreateDatabase "MyDatabase"
Description
This function returns a create database query.
qbDropDatabase
Summary
Drop database query
Name
lcSQLQueryBuilder
Parameters
- pName String database name
Returns
tQuery String drop database query
Card
Stack
Example
qbDropDatabase "MyDatabase"
Description
This function returns a drop database query.
qbSelectDatabase
Summary
Gets the use database query
Name
lcSQLQueryBuilder
Parameters
- pName String database name
Returns
tQuery String with query
Card
Stack
Example
qbSelectDatabase "MyDatabase"
Description
This function returns a use database query.
qbCreateTable
Summary
Create a new table
Name
lcSQLQueryBuilder
Parameters
- pName String table name
- pParams Array attributes and values
Returns
String with query
Card
Stack
Example
put empty into tParams
put "INT(5) NOT NULL" into tParams["user_id"]
put "VARCHAR(255) NOT NULL" into tParams["name"]
put "VARCHAR(255) NOT NULL" into tParams["username"]
put "VARCHAR(255) NOT NULL" into tParams["password"]
put qbCreateTable("mytable", tParams) into tQuery
Description
This function returns a create table query.
qbDropTable
Summary
Delete a table
Name
lcSQLQueryBuilder
Parameters
- pName String table name
Returns
String drop table query
Card
Stack
Example
qbDropTable "mytable"
Description
This function returns a drop table query.
qbExecute
Summary
Executes a query
Name
lcSQLQueryBuilder
Parameters
- pConID Integer database connection id
- pQuery String the query
Returns
Integer,String integer if successful, string with error if not successful
Card
Stack
Example
put qbExecute(tConID, tQuery) into tResult
if tResult is not a integer then
answer "Error in Query: " & tResult
exit qbTestQueries
end if
Description
This function executes a query and returns the result. This function
does not return any data.
qbExecuteSubstituted
Summary
Executes a substituded query
Name
lcSQLQueryBuilder
Parameters
- pConID Integer database connection id
- pQuery String the query
Returns
Integer,String integer if successful, string with error if not successful
Card
Stack
qbExecuteAndReturnData
Summary
Executes a query and returns data as string
Name
lcSQLQueryBuilder
Parameters
- pConID Integer database connection id
- pQuery String the query
Returns
Integer,String integer if successful, string with error if not successful
Card
Stack
Example
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
Description
This function executes a query and returns the result. It returns
data as a string if the query is successful. Or a string that starts
with revdberr and the error message if there was an error.
qbExecuteAndReturnDataArray
Summary
Executes a query and returns data as array
Name
lcSQLQueryBuilder
Parameters
- pConID Integer database connection id
- pQuery String the query
Returns
Integer,String integer if successful, string with error if not successful
Card
Stack
Example
put qbExecuteAndReturnDataArray(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
Description
This function executes a query and returns the result. It returns
data as an array if the query is successful. Or a string that starts
with revdberr and the error message if there was an error.
qbSanitize
Summary
Sanitizes a value
Name
lcSQLQueryBuilder
Parameters
- pStr String un-sanitized string
Returns
String sanitized string
Card
Stack
Description
This function sanitizes a value and can be used before adding the value to the query.
qbSQLConnect
Summary
Connects to a SQL database
Name
lcSQLQueryBuilder
Parameters
- pDbParams Array database parameters
Returns
tConID Integer database connection id
Card
Stack
Example
put empty into tDbParams
put "localhost" into tDbParams["db_host"]
put "MySQL" into tDbParams["db_type"]
put "MyDB" into tDbParams["db_name"]
put "mydbuser" into tDbParams["db_user"]
put "mydbpassword" into tDbParams["db_pass"]
put qbSQLConnect(tDbParams) into tConID
Description
This function connects to an SQL database. Supported databases are:
MySQL, SQLite, PostgreSQL, ODBC and Oracle. There is one thing that
is special about SQLite. There is an additional array parameter called
tDbParams[“db_folder”] which specifies the full path of the folder in
which the SQLite database file will be stored. The connection will abort
if the directory can”t be found. You”ll have to create it first.
qbSQLDisconnect
Summary
Disconnect from a SQLite database
Name
lcSQLQueryBuilder
Parameters
- pConID Integer database connection id
Card
Stack
Example
put qbSQLDisconnect("MyDatabase.sqlite") into tConID
qbSQLDisconnect tConID
Description
This function disconnects from a SQLite database.