lcSQLQueryBuilder

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