lcSQLQueryBuilder Reference

Contents

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.

Leave A Reply