Contents
- What can I do with it?
- Supported databases
- Supported Features
- License
- Tested with
- Download
- Method Reference
- Loading the library
- Example Queries
- Query 1: Simple Example
- Query 2: Two Tables And WHERE Clauses
- Query 3: Distinct Example
- Query 4: Offset And Limit
- Query 5: OrderBy Example
- Query 6: Multiple OrderBy Example
- Query 7: AND WHERE Example
- Query 8: OR WHERE Example
- Query 9: JOIN ON Example
- Query 10: CROSS JOIN Example
- Query 11: INSERT Example
- Query 12: UPDATE Example
- Query 13: DELETE Example
- Database functions and commands
What can I do with it?
With lcSQLQueryBuilder you can build and execute SQL queries (obvious wasn’t it). This is a LiveCode library that can be used to build SQL queries with less coding effort spent.
Supported databases
- MySQL
- SQLite
- PostgreSQL
- ODBC
- Oracle
Supported Features
- create database, drop database, create table, drop table
- select, insert, update, delete
- where, and where, or where
- limit, offset
- order by
- left join, right join, inner join, cross join, on
- distinct
License
- Community: GPL
Tested with
- LiveCode 7
- LiveCode 6.7
Download
- Community: Download on GitHub
Method Reference
See reference
Loading the library
In your stack file in the “on openStack” message add following lines of code:
on openStack
local tStackfiles
put "lcSQLQueryBuilder,lib/lcSQLQueryBuilder.livecode" & cr into tStackfiles
set the stackfiles of this stack to tStackfiles
start using stack "lcSQLQueryBuilder"
end openStack
This will load the library in to your stack file when you open the stack.
Example Queries
Query 1: Simple Example
on mouseUp
qbTable "user"
qbWhere "username", "=", "'martin'"
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM user WHERE username = ‘martin’;
Query 2: Two Tables And WHERE Clauses
on mouseUp
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
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT u.username, a.user_id FROM address AS a, user AS u WHERE u.username = ‘oscar’ AND u.rowid = a.user_id;
Query 3: Distinct Example
on mouseUp
qbTable "user"
qbSetDistinct true
put qbSelect("username") into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT DISTINCT username FROM user;
Query 4: Offset And Limit
on mouseUp
qbTable "user"
qbLimit 2
qbOffset 0
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM user LIMIT 2 OFFSET 0 ;
Query 5: OrderBy Example
on mouseUp
qbTable "user"
qbOrderBy "username", "ASC"
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM user ORDER BY `username` ASC;
Query 6: Multiple OrderBy Example
on mouseUp
qbTable "address"
qbOrderBy "country", "ASC"
qbOrderBy "city", "ASC"
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM address ORDER BY `country` ASC, `city` ASC;
Query 7: AND WHERE Example
on mouseUp
qbTable "user"
qbWhere "username", "LIKE", "'osc%'"
qbAndWhere "name", "LIKE", "'%Smi%'"
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM user WHERE username LIKE ‘osc%’ AND name LIKE ‘%Smi%’;
Query 8: OR WHERE Example
on mouseUp
qbTable "user"
qbWhere "name", "LIKE", "'%Smi%'"
qbOrWhere "name", "LIKE", "'%Sparr%'"
put qbSelect() into tQuery
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT * FROM user WHERE name LIKE ‘%Smi%’ OR name LIKE ‘%Sparr%’;
Query 9: JOIN ON Example
on mouseUp
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
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT user.username, address.city FROM user LEFT JOIN address ON user.rowid = address.user_id WHERE user.name = ‘Oscar Smith’;
Query 10: CROSS JOIN Example
on mouseUp
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
put qbExecuteAndReturnData(tConID, tQuery) into tResult
if tResult begins with "revdberr" then
answer "Error in Query: " & tResult
exit mouseUp
end if
end mouseUp
Result: SELECT user.username, address.zipcode, address.city FROM user CROSS JOIN address ON user.rowid = address.user_id;
Query 11: INSERT Example
on mouseUp
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
put qbExecute(tConID, tQuery) into tResult
if tResult is not a integer then
answer "Error in Query: " & tResult
exit qbTestQueries
end if
end mouseUp
Result: INSERT INTO address (city,user_id,country,zipcode,street) VALUES (“Zurich”,”1″,”Switzerland”,”9999″,”Teststreet 3″);
Query 12: UPDATE Example
on mouseUp
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
put qbExecute(tConID, tQuery) into tResult
if tResult is not a integer then
answer "Error in Query: " & tResult
exit qbTestQueries
end if
end mouseUp
Result: UPDATE address SET `city`=’San Antonio’ WHERE user_id = 1 AND city = ‘New York’ AND country = ‘USA’;
Query 13: DELETE Example
on mouseUp
qbTable "address"
qbWhere "country", "=", "'Switzerland'"
put qbDelete() into tQuery
put qbExecute(tConID, tQuery) into tResult
if tResult is not a integer then
answer "Error in Query: " & tResult
exit qbTestQueries
end if
end mouseUp
Result: DELETE FROM address WHERE country = ‘Switzerland’;
Database functions and commands
Connect to a database and create tables
Following databases are supported: MySQL, SQLite, PostgreSQL, ODBC and Oracle.
on mouseUp
put empty into tDbParams
put "localhost" into tDbParams["db_host"]
put "mysql" into tDbParams["db_type"]
put "test_tables" into tDbParams["db_name"]
put "testuser" into tDbParams["db_user"]
put "1234" into tDbParams["db_pass"]
put specialfolderPath("documents") into tDbParams["db_folder"]
put qbSQLConnect(tDbParams) into tConID
if tDbParams["db_type"] is "sqlite" then
put empty into tTable
put "VARCHAR(255) NOT NULL" into tTable["name"]
put "VARCHAR(255) NOT NULL" into tTable["username"]
put "VARCHAR(255) NOT NULL" into tTable["password"]
else
put empty into tTable
put "INT(11) NOT NULL" into tTable["id"]
put "VARCHAR(255) NOT NULL" into tTable["name"]
put "VARCHAR(255) NOT NULL" into tTable["username"]
put "VARCHAR(255) NOT NULL" into tTable["password"]
end if
put qbDropTable("user") into tQuery
put qbExecute(tConID, tQuery) into tResult
put qbCreateTable("user", tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
if tDbParams["db_type"] is "sqlite" then
put empty into tTable
put "INT NOT NULL" into tTable["user_id"]
put "VARCHAR(255) NOT NULL" into tTable["street"]
put "VARCHAR(255) NOT NULL" into tTable["zipcode"]
put "VARCHAR(255) NOT NULL" into tTable["city"]
put "VARCHAR(255) NOT NULL" into tTable["country"]
else
put empty into tTable
put "INT(11) NOT NULL" into tTable["user_id"]
put "VARCHAR(255) NOT NULL" into tTable["street"]
put "VARCHAR(255) NOT NULL" into tTable["zipcode"]
put "VARCHAR(255) NOT NULL" into tTable["city"]
put "VARCHAR(255) NOT NULL" into tTable["country"]
end if
put qbDropTable("address") into tQuery
put qbExecute(tConID, tQuery) into tResult
put qbCreateTable("address", tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
if tDbParams["db_type"] is "sqlite" then
put 1 into tTable["rowid"]
else
put 1 into tTable["id"]
end if
put "Oscar Smith" into tTable["name"]
put "oscar" into tTable["username"]
put "testpass1" into tTable["password"]
qbTable "user"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
if tDbParams["db_type"] is "sqlite" then
put 2 into tTable["rowid"]
else
put 2 into tTable ["id"]
end if
put "Martin Noseman" into tTable["name"]
put "martin" into tTable["username"]
put "testpass2" into tTable["password"]
qbTable "user"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
if tDbParams["db_type"] is "sqlite" then
put 3 into tTable["rowid"]
else
put 3 into tTable["id"]
end if
put "Jack Sparrow" into tTable["name"]
put "jack" into tTable["username"]
put "testpass3" into tTable["password"]
qbTable "user"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
put 1 into tTable["user_id"]
put "Teststreet 1" into tTable["street"]
put "1111" into tTable["zipcode"]
put "Haven" into tTable["city"]
put "USA" into tTable["country"]
qbTable "address"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
put 1 into tTable["user_id"]
put "Teststreet 2" into tTable["street"]
put "2222" into tTable["zipcode"]
put "New York" into tTable["city"]
put "USA" into tTable["country"]
qbTable "address"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
put 2 into tTable["user_id"]
put "Teststreet 3" into tTable["street"]
put "3333" into tTable["zipcode"]
put "Chicago" into tTable["city"]
put "USA" into tTable["country"]
qbTable "address"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
put empty into tTable
put 3 into tTable["user_id"]
put "Teststreet 4" into tTable["street"]
put "4444" into tTable["zipcode"]
put "Seattle" into tTable["city"]
put "USA" into tTable["country"]
qbTable "address"
put qbInsert(tTable) into tQuery
put qbExecute(tConID, tQuery) into tResult
qbSQLDisconnect tConID
end mouseUp