SQLBuilder_MTC talk

Add-Ons
1

Hi all. I just released SQLBuilder_MTC, available here. It’s a free, open-source project designed to help you craft sql using Xojo functions in a natural way. Take a look at the extensive README for some examples of how it works.

If you have any questions or comments, please post them here.

2

Here’s an interesting situation I encountered today. We have code that searches a Patient table with user-provided data. I’ll simplify the problem here:

dim rs as RecordSet = SQLBuilder_MTC _
  .SQLSelect("") _
  .From( "patient" ) _
  .CondWhere( ssn <> "", "ssn", ssn ) _
  .CondWhere( firstName <> "", "first_name", firstName ) _
  .CondWhere( lastName <> "", "last_name", lastName ) _
  .Prepare( db ).SQLSelect

See the problem? Well, neither did I until I tested. If the user doesn’t provide any criteria, this code will return every record because there will be no “where” clause.

(Note that the original code has six CondWhere calls, not three, and the variables I had to check were a mix of strings and arrays.)

Aside from checking each variable yourself, here are two solutions, and the second one is the one I opted for since it saves a database hit.

dim rs as RecordSet = SQLBuilder_MTC _
  .SQLSelect("") _
  .From( "patient" ) _
  .WhereRaw( "false" ) _
  .OrWhere( SQLBuilder_MTC _
  . CondWhere( ssn <> "", "ssn", ssn ) _
  . CondWhere( firstName <> "", "first_name", firstName ) _
  . CondWhere( lastName <> "", "last_name", lastName ) _
  ).Prepare( db ).SQLSelect

(This works after the bug fix I’m about to release.)

Second way:

dim whereClause as new SQLBuilder_MTC.Statement
call whereClause _
  .CondWhere( ssn <> "", "ssn", ssn ) _
  .CondWhere( firstName <> "", "first_name", firstName ) _
  .CondWhere( lastName <> "", "last_name", lastName )
if whereClause.ToString = "" then
  // Nothing in WHERE
  return nil
end if

dim rs as RecordSet = SQLBuilder_MTC _
  .SQLSelect("") _
  .From( "patient" ) _
  .Where( whereClause ) _
  .Prepare( db ).SQLSelect
3

Would it possible to extend your class for using the INSERT, DELETE and UPDATE Database functions? And is there a way, to collect 1…* INSERT, DELETE, UPDATE statements to execute them only one time? Should be faster then executing every single statement.

For now I do this with an Text array:

[code]Dim result() As Text

For Each alias As record In aliase
’ add to Aliase
result.Append(SQL_INSERT_INTO)
result.Append(TABLE_ALIASE)
result.Append(SQL_BRACKET_OPEN)
result.Append(kRecordID)
result.Append(COMMA)
result.Append(kConnectedRecord)
result.Append(SQL_BRACKET_CLOSE)

’ VALUES
result.Append(SQL_VALUES)
result.Append(SQL_BRACKET_OPEN)
result.Append(record.ID.ToText)
result.Append(COMMA)
result.Append(alias.ID.ToText)
result.Append(SQL_BRACKET_CLOSE)
result.Append(SEMICOLON)
Next

sql.Append(Text.Join(result, SPACE))[/code]

4

I’m sure these are possible. If you have an idea, submit it via Pull Request and I’ll take a look.

5

I don’t have a Github Account, but could look like this (maybe you have a more simple structure):

INSERT

[code]Dim sql as String = _
SQLBuilder_MTC.InsertInto( “table”) _
.Field( “date”, “YYYY-MM-DD [HH:MM:SS]”) _
.CondField( ssnProperty <> “” Or ssnProperty = “cool”, “ssn”, ssnProperty ) _
.CondField( amountProperty <> 0, “amount”, amountProperty ) _
.SQLExecute(db)

// INSERT INTO table (date, ssn, amount) VALUES (‘YYYY-MM-DD [HH:MM:SS]’, ‘ssnProperty’, amountProperty);

// Or

Dim sql() as String

sql.Append( SQLBuilder_MTC.InsertInto( “table”) _
.Field( “date”, “YYYY-MM-DD [HH:MM:SS]”) _
.CondField( ssnProperty <> “” Or ssnProperty = “cool”, “ssn”, ssnProperty ) _
.CondField( amountProperty <> 0, “amount”, amountProperty ) )

SQLBuilder_MTC.SQLExecute(db, sql)[/code]

UPDATE

[code]Dim sql as String = _
SQLBuilder_MTC.Update( “table” ) _
.Field( “ssn”, “newSsn” ) _
.Field( “amount”, newValue ) _
.Where( “id”, id ) _
.AndWhere( “name”, myName ) _
.OrWhere( “id”, myId ) _
.AndWhere( amount, “<”, 2 ) _
.SQLExecute(db)

// UPDATE table SET ssn = ‘newSsn’, amount = newValue WHERE id = id AND name = ‘myName’ OR id = myId AND amount < 2;[/code]

DELETE

[code]Dim sql As String = _
SQLBuilder_MTC.DeleteFrom( “table”) _
.Where( “id”, myId ) _
.OrWhere( “name”, “LIKE”, “Santana”) _
.SQLExecute(db)

// DELETE FROM table WHERE id = myId OR name ‘LIKE %Santana%’;[/code]

Thanks for the nice project :wink: