SQLBuilder

Xojo classes to build a SQL statement

The Goal

Talking to a database involves creating text-based "scripts" in SQL, and these can range from very simple to quite complex. In the latter case, the code to construct the text can get involved and require tracking of values and parameters. SQLBuilder_MTC seeks to alleviate some of this burden by allowing you to create smart SQL in code similar to how you might type it out in a text editor.

Advantages

There are several advantages to this approach.

• By using this builder, you can keep your SQL close to your code rather than in, for example, a constant, and that makes it easier to debug.
• Mixing languages can be problematic in any environment. With this, you can use pure Xojo code to craft your queries, making it easier to read, debug, and maintain.
• By supplying values in the individual clauses, you can avoid tracking and matching the parameters of a prepared statement to its value.
• Where your SQL will depend on outside factors that determine whether certain clauses are included, you can use "conditional where" clauses and avoid messy if constructs. You also avoid parameter tracking.
• You can use the builder to closely match SQL syntax with auto-complete helping at each stage.
• You can generate prepared statement for any type of database without worrying about that database engine's parameter requirements ("$1" vs. "?", for example).
• If you choose to convert your statement to text, it will be formatted for you in an easy-to-read format.
• When supplying a nil value in a Where clause, the code will substitute "IS NULL" for "=" and "IS NOT NULL" for "<>" or "!=". Conversely, it will replace "IS" with "=" and "IS NOT" with "<>" where the given value is not nil.

Limitations

Even though I've endeavored for maximum flexibility, there are some situations where using this module will be insufficient or unwieldy. In other words, it was not designed to meet every need, just the majority of needs.

There is only limited error checking so it you want to do something that makes no sense at all, this module won't try to stop you.

How To Use It

Open the included project and copy the SQLBuilder_MTC module into your project.

To construct SQL, you can either instantiate a new SQLBuilder_MTC.Statement or call SQLBuilder_MTC.SQLSelect( "*" ). Each function in the SQLBuilder_MTC.Statement will return itself so you can chain the calls together.

Table Of Contents

• Examples
• Functions
• A Word About Parameters
• A Word About Placeholders
• Who Did This?
• FAQ
• Release Notes

Examples

Simple

Let's take a simple SQL statement like SELECT * FROM table WHERE i = 1. You can use any of these to create a RecordSet.

dim sb as new SQLBuilder_MTC
dim rs as RecordSet = _
  sb.SQLSelect( "*" ) _
  .From( "table" ) _
  .Where( "i", 1 ) _
  .Prepare( db ).SQLSelect

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLSelect( "" ) _
  .From( "table" ) _
  .Where( "i", 1 ) _
  .Prepare( db ).SQLSelect

dim sb as new SQLBuilder_MTC
call sb.SQLSelect( "*" ) _
  .From( "table" ) _
  .Where( "i", 1 )
dim rs as RecordSet = sb.Prepare( db ).SQLSelect

Conditional Where

To get a bit trickier, suppose you have a function that lets the user search your database for a person based on some combination of first name, last name, and zip code. For the purposes of this example, we know that at least one of these is always given. To do this for PostgreSQL where parameters are given in the form "$1", your code would look something like this:

dim whereClause() as string
dim values() as variant

if firstName <> "" then
  values.Append firstName
  whereClause.Append "first_name = $" + str( values.Ubound + 1 )
end if

if lastName <> "" then
  values.Append lastName
  whereClause.Append "last_name = $" + str( values.Ubound + 1 )
end if

if zipCode <> "" then
  values.Append zipCode
  whereClause.Append "zip = $" + str( values.Ubound + 1 )
end if

dim sql as string = _
  "SELECT * FROM person WHERE " + _
  join( whereClause, " AND " )

dim ps as PreparedSQLStatement = db.Prepare( sql )
for i as integer = 0 to values.Ubound
  ps.Bind i, values( i )
next

dim rs as RecordSet = ps.SQLSelect

The same result with SQLBuilder_MTC:

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLSelect( "*" ) _
  .From( "person" ) _
  .CondWhere( firstName <> "", "first_name", firstName ) _
  .CondWhere( lastName <> "", "last_name", lastName ) _
  .CondWhere( zipCode <> "", "zip", zipCode ) _
  .Prepare( db ).SQLSelect

Where Clause

You can use SQLBuilder_MTC to construct just a Where clause, and that comes in handy when you need to enclose parts of your statement in parenthesis. For example, take the statement:

SELECT * FROM table WHERE (i = 1 or b > 2) AND c BETWEEN 5 AND 6

The SQLBuilder_MTC code:

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLSelect( "" ) _
  .From( "table" ) _
  .Where( SQLBuilder_MTC _
  . Where( "i", 1 ) _
  . OrWhere( "b", ">", 2 ) _
  ) _
  .WhereBetween( "c", 5, 6 ) _
  .Prepare( db ).SQLSelect

Joins

You can create as many Joins as you'd like:

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLSelect( "" ) _
  .From( "table t" ) _
  .LeftJoin( "another_table a", "a.id = t.another_table_id" ) _
  .CrossJoin( "something" ) _
  .Prepare( db ).SQLSelect

With

Sometimes it's handy to use a With clause.

dim rs as RecordSet = _
  SQLBuilder_MTC.SQLWith( "some_data", SQLBuilder_MTC _
  . SQLSelect( "id" ) _
  . From( "related_table" ) _
  . Where( "some_column", 3 ) _  
  ) _
  .SQLSelect( "" ) _
  .From( "table ") _
  .WhereInQuery( "id", SQLBuilder_MTC.SQLSelect( "" ).From( "some_data" ) ) _
  .Prepare( db ).SQLSelect

Functions

Sections

• With
• Select
• From
• Joins
• Where
• Conditional Where
• Group By, Having, Order By, Limit, Offset
• Union, Intersect, Except
• ToString, Prepare
• SQLBuilder_MTC

The SQLBuilder_MTC module is designed to help you with SQL construction by using auto-complete to lead you through it. For example, it wouldn't make sense to follow a FROM clause with SELECT, so you will only see what's possible. The tables describes these functions in the order they might appear in your SQL statement.

Note: Prior to Xojo 2017r1.1, auto-complete does not work across line breaks like in the examples above.

With

function	parameters
SQLWith	alias As String, subQuery As SQLBuilder_MTC.StatementInterface

Select

function	parameters
SQLSelect	expression As String, ParamArray values() As Variant
SQLSelectDistinct	expression As String, ParamArray values() As Variant

From

function	parameters
From	expression As String, ParamArray values() As Variant or subQuery As SQLBuilder_MTC.StatementInterface

Joins

function	parameters
Join LeftJoin RightJoin InnerJoin OuterJoin FullJoin	table As String, onCondition As String, ParamArray values() As Variant or table As String, subQuery As SQLBuilder_MTC.StatementInterface
CrossJoin	table As String
JoinRaw	expression As String, ParamArray values() As Variant

Where

function	parameters
Where OrWhere	expression As String, value As Variant or expression As String, comparison As String, value As Variant or statement As SQLBuilder_MTC.StatementInterface
WhereBetween OrWhereBetween WhereNotBetween OrWhereNotBetween	expression As String, lowValue As Variant, highValue As Variant
WhereExists OrWhereExists WhereNotExists OrWhereNotExists	subQuery As SQLBuilder_MTC.StatementInterface
WhereIn OrWhereIn WhereNotIn OrWhereNotIn	expression As String, ParamArray values() As Variant
WhereInQuery OrWhereInQuery WhereNotInQuery OrWhereNotInQuery	expression As String, subQuery As SQLBuilder_MTC.StatementInterface
WhereNot OrWhereNot	statement As SQLBuilder_MTC.StatementInterface
WhereNull OrWhereNull WhereNotNull OrWhereNotNull	expression As String
WhereRaw OrWhereRaw	expression As String, ParamArray values() As Variant

Conditional Where

Each Where clause has a corresponding "conditional" where clause that will only be included if the given condition is true. To use the conditional version, prefix "Cond" to the function name and supply a boolean as the first parameter. For example:

.Where( "i", 3 )
.CondWhere( boolCondition, "i", 3 ) // Included only if boolCondition is true

.OrWhereIn( "a", 1, 2, 3 )
.CondOrWhereIn( boolCondition, "a", 1, 2, 3 )

Group By, Having, Order By, Limit, Offset

function	parameters
Group By	ParamArray columns() As String
Having	expression As String, ParamArray values() As Variant or subQuery As SQLBuilder_MTC.StatementInterface
Order By	ParamArray columnIndexes() As Integer or ParamArray expression() As String
Limit	limit As Integer
Offset	offset As Integer

Union, Intersect, Except

You can join multiple statements together using Union, Interect, and Except.

function	parameters
Union Intersect Except	nextStatement As SQLBuilder_MTC.StatementInterface, isDistinct As Boolean = True

ToString, Prepare

Once the statement is complete, you will want to do something with it. These functions will process it for you.

function	parameters	returns
ToString	db As Database or phType As SQLBuilder_MTC.PHTypes = SQLBuilder_MTC.PHTypes.QuestionMark	String
Prepare	db As Database	PreparedSQLStatement

Note: The PHTypes enum will let you specify a placeholder type if you don't have a database instance handy and just want to view the statement as a string.

SQLBuilder_MTC

As a convenience, you can initiate most statements directly from the module so you do not have to create a separate Statement object. The module functions include the SQLWith, SQLSelect, Where, and Conditional Where clauses.

A Word About Parameters

In many of these function you are asked for a ParamArray of Variant values. In those cases, you can supply values individually or supply an array. SQLBuilder_MTC will intelligently extract the right values. For example, these will be treated the same:

call SQLBuilder_MTC.WhereIn( "a", 1, 2, 3 )

dim arr() as integer = array( 1, 2, 3 )
call SQLBuilder_MTC.WhereIn( "a", arr )

A Word About Placeholders

When creating prepared statements, different engines have different requirements. For instance, MySQL takes its paramters as "?" while PostgreSQL requires the form "$1". To maintain compatibility across engines, SQLBuilder_MTC expects only "?". For example:

.WhereRaw( "i = ? and j between ? and ?" )

The placeholders will be replaced correctly when processed against a database with either ToString or Prepare.

Who Did This?

This project was designed and implemented by:

• Kem Tekinay (ktekinay at mactechnologies.com)

FAQ

• No FAQ yet

Release Notes

1.0.2 (____, 2017)

• During debug, sql and values are logged to System.DebugLog.
• When values are bound, doubles are converted to strings with PostgreSQL databases. (Bypasses a bug in Xojo.)

1.0.1 (May 16, 2017)

• Empty WHERE subqueries will be skipped.

1.0 (May 1, 2017)

• Initial release.