Easier SQL Construction

Hi people,

I finally broke down and wrote this small utility for my own use.
It is a simple class that makes building an SQL string very clear.

I based it on the manner in which 'C' inserts variables into strings.
Further I made use of VB's typename function to format the variable values for SQL consumption. You may need to change the DATE
variable formating to use pound characters instead of aposterphies if your using a different driver.

Look at the example first then the class it uses.
Hope this is a useful tool for you all.

~Tom


Private Sub cmdBuild_Click()
' Sample of SQL Builder being used.
'Dim RS As ADODB.Recordset
Dim oSQL As clsSQL_Builder 'New Class

Dim sCustomer As String
Dim sState As String
Dim nPN As Long
Dim cBalance As Currency

' Initialize test variables
sCustomer = "LA Tools Inc"
sState = "CA"
nPN = 100031
cBalance = 32040.03

' Test of SQL builder
Set oSQL = New clsSQL_Builder

With oSQL
' Define swap identifying character
.VarID = "~"

.Add "SELECT * FROM Customers, PartMaster, Accounts"
.Add "WHERE Cust_Name = ~1"
.Add "AND Cust_Address_State = ~2"
.Add "AND PartNo = ~3"
.Add "AND Account_Balance_Total >= ~4"
.Add "ORDER BY Cust_Name ASC"

' Insert variable values.
.Insert sCustomer, sState, nPN, cBalance 'These replace ~1,~2,~3,~4

' Remove Msg this when SQL looks good
.Msg
End With

'Set RS = New ADODB.Recordset
'RS.Open oSQL.SQL, gConn1, adOpenStatic, adLockReadOnly
'Etc...

oSQL.Clear
'Create another SQL string if desired

'Set RS = Nothing 'Destroy objects
Set oSQL = Nothing
End Sub


-----------------------
-- new class follows ---
-----------------------

Option Explicit

Const def_msVarID As String = "%"

Dim msText As String
Dim msVarID As String
Dim msSQL As String

Private Sub Class_Initialize()
'Clear master strings, Init VarID
msText = ""
msSQL = ""
msVarID = def_msVarID
End Sub

Public Sub Clear()
'Clear master strings
msText = ""
msSQL = ""
End Sub

Public Sub Add(ByVal s_Text As String)
'Append to master SQL string
msText = msText & s_Text & vbNewLine
End Sub

Public Property Get Text() As String
'Return Proto text string
Text = msText
End Property

Public Property Get VarID() As String
'Return the swap identifing character(s)
VarID = msVarID
End Property

Public Property Let VarID(ByVal s_NewVarID As String)
'set the swap identifing character(s)
msVarID = s_NewVarID
End Property

Public Sub Insert(ParamArray varNames() As Variant)
'Insert the variable values from the given variable parameter list.
Dim i As Integer
Dim s_temp As String

msSQL = msText

'Convert variable value to appropriate SQL type.
For i = 0 To UBound(varNames)
Select Case TypeName(varNames(i))
Case "String"
s_temp = "'" & varNames(i) & "'"
msSQL = Replace(msSQL, msVarID & i + 1, s_temp)
Case "Date"
s_temp = "'" & varNames(i) & "'"
msSQL = Replace(msSQL, msVarID & i + 1, s_temp)
Case Else
msSQL = Replace(msSQL, msVarID & i + 1, varNames(i))
End Select
Next i
End Sub

Public Sub Msg()
'Display the master SQL string
MsgBox msSQL, vbOKOnly, "Show SQL"
End Sub

Public Property Get SQL() As String
'Return Proto text string
SQL = msSQL
End Property

I got a suggestion. Instead of making it similar to C's method, why not just make it near identical to sprintf(). Instead of using ~1, ~2, etc you could use %d %s, etc there are actually quite a few different format codes, you'd only need to implement the basic ones. And you could use ParamArray in your function declare so that you can do it all in one call, something like

strSQL = sprintf("SELECT * FROM %s WHERE %s = %d", "Customers", "CustomerID", 123)

I got a suggestion. Instead of making it similar to C's method, why not just make it near identical to sprintf(). Instead of using ~1, ~2, etc you could use %d %s, etc there are actually quite a few different format codes, you'd only need to implement the basic ones. And you could use ParamArray in your function declare so that you can do it all in one call, something like

strSQL = sprintf("SELECT * FROM %s WHERE %s = %d", "Customers", "CustomerID", 123)

Sounds good for short SQL's but my goal was the following criteria.

1) Provide a definable replacement character(s).
the default is the percent sign "%"

2) Provide a utility that constructs the correct SQL output based on
the given variable datatype. if recasting is needed it should
be done in the Insert method.

3) Keep the input on a simple line by line arrangement.
(Easier to read and debug.)

All that being said why not add a sprintf() function to the class that
works as you describe in addition to the current system. :)

What do you think?

~Tom

good points. However, are you aware that if you use SQL Server, Oracle, or any other major DBMS, you can already use paramaterized SQL statements, and use the ADODB.Command object to specify your paramaters?

good points. However, are you aware that if you use SQL Server, Oracle, or any other major DBMS, you can already use paramaterized SQL statements, and use the ADODB.Command object to specify your paramaters?


All True, However if I was connecting to a SQL server or any other major db server I would be using stored procedures and NOT
large unwieldy slow SQL's on the client. :)

This utilty was meant for those unlucky enough to not have that advantage.