/
Working with SQL

Working with SQL

image-20260317-112129.png
image-20260317-103604.png

Working with SQL - Supported Statements

Reference Sites

 

Consequences of Unsupported SQL

The mobile system (Bridge and App) has been architected to incorporate the use of SQL to enhance the operation of Forms and Templates. The use of non-supported SQL is discouraged due to the likely negative end-user performance impact when run on mobile operating systems which are not optimised for SQL usage.

Time and Date

Time and Date fields have been introduced as well as the ability to perform a 'Diff' between two time and date fields. All SQL associated with time and date calculations will not be supported post All On Mobile & Whitespace Mobile v4.2.1 release and not at all in WS Crew.

Supported MySQL Statements and Uses

MySQL is used in the following mobile app functionality:

  • Visibility

  • Paging

  • Off-line Databases' for dropdown menus and validation

  • Simple calculations (+ / - *)

  • Validation of a Field’s contents

Supported SQL Statement

MySQL

Examples

Notes

MySQL

Examples

Notes

SELECT, LIKE & WHERE

SELECT Employee_Name, Employee_DOB FROM Employees WHERE Employee_Name LIKE ‘Dave%’ LIMIT 20

It is important to LIMIT the results when the SELECT or LIKE functions are used. This prevents to many results from being displayed and slowing the mobile device.

NOTE:
Nested or chained SELECTS are not supported.

Where you are likely to return over 50 results use LIMIT to restrict the returned results to a manageable number.

=, !=, >, <, >=, ==, <>

  • SELECT * FROM Products WHERE Item_Id == 1

  • SELECT * FROM Products WHERE Price >= 30

  • SELECT * FROM Products WHERE Category != ‘Pet_Food’

A list of SQL Operators and their function can be found at:

https://www.w3schools.com/sql/sql_operators.asp

AND & OR

SELECT * FROM Customers WHERE Country = 'Germany' AND (City = 'Berlin' OR City = ‘München')

Remember to use parenthesis to form complex expressions otherwise you may end up with conflicting logical operators.

+ & - 

SELECT ${Price} + ${VAT} - ${Discount}

Simple addition and subtraction operators.

It should be noted that mobile devices provide different versions of SQL Lite with the OS. Therefore the use of all SQL should be thoroughly tested in multiple data scenarios and on multiple device types prior to live use.