The Best of Both Worlds

If you've ever worked with a database, chances are you know the difference between "dynamic queries" and "parameterized quires". In the former, you just concatenate a value to your query string ("where col='" + val + "'") and cross your fingers that val isn't "'; drop database --". With the parameter approach, you let the data-access drivers take care of escaping and whatnot with the use of command and parameter objects. I'd be willing to bet, however, you haven't seen the combination of the two techniques ...

Public Function SaveApplication(ByVal varrApplicationData As Variant) As Variant
  SaveApplication = False
  On Error GoTo HandleError
  
  Dim objConn As ADODB.Connection
  Set objConn = GetConnection()

  Dim objCmd As ADODB.Command
  Set objCmd = CreateObject("ADODB.Command")

  With objCmd
    .ActiveConnection = objConn

    'Setup the Params
    .Parameters.Append .CreateParameter("APP_NUM", adVarChar, adParamInputOutput, 12)
    .Parameters.Append .CreateParameter("APP_CODE", adChar, adParamInput, 3)
    .Parameters.Append .CreateParameter("SALESPERSON_ID", adChar, adParamInput, 5)
    'ED: Snipped *80* Parameters
    .Parameters.Append .CreateParameter("PREV_EMP3_ADDR_1", adVarChar, adParamInput, 50)
    .Parameters.Append .CreateParameter("PREV_EMP3_ADDR_2", adVarChar, adParamInput, 50)
    .Parameters.Append .CreateParameter("PREV_EMP3_CITY", adVarChar, adParamInput, 25)
    .Parameters.Append .CreateParameter("PREV_EMP3_STATE", adChar, adParamInput, 2)
    .Parameters.Append .CreateParameter("PREV_EMP3_ZIP_CODE", adChar, adParamInput, 5)
  End With
  
  'Set the Param values
  objCmd("APP_NUM") = varrApplicationData(ApplicationDataFields.APP_NUM)
  objCmd("APP_CODE") = varrApplicationData(ApplicationDataFields.APP_CODE)
  objCmd("SALESPERSON_ID") = varrApplicationData(ApplicationDataFields.SALESPERSON_ID)
  'ED: Snipped *80* Parameters
  objCmd("PREV_EMP3_ADDR_1") = varrApplicationData(ApplicationDataFields.PREV_EMP3_ADDR_1)
  objCmd("PREV_EMP3_ADDR_2") = varrApplicationData(ApplicationDataFields.PREV_EMP3_ADDR_2)
  objCmd("PREV_EMP3_CITY") = varrApplicationData(ApplicationDataFields.PREV_EMP3_CITY)
  objCmd("PREV_EMP3_STATE") = varrApplicationData(ApplicationDataFields.PREV_EMP3_STATE)
  objCmd("PREV_EMP3_ZIP_CODE") = varrApplicationData(ApplicationDataFields.PREV_EMP3_ZIP_CODE)
  
  Dim objParam As ADODB.Parameter, strSQL As String

  'declare input params for Stored Proc
  strSQL = "DECLARE " & vbCrLf
  For Each objParam In objCmd.Parameters
    If objParam.Type = adVarChar Then
      strSQL = strSQL & "in" & objParam.Name & " VARCHAR2(" & objParam.Size & ");" & vbCrLf
    ElseIf objParam.Type = adChar Then
      strSQL = strSQL & "in" & objParam.Name & " CHAR(" & objParam.Size & ");" & vbCrLf
    Else
      strSQL = strSQL & "in" & objParam.Name & " VARCHAR2(" & objParam.Size & ");" & vbCrLf
    End If
  Next
  
  'set param values values
  For Each objParam In objCmd.Parameters
    strSQL = strSQL & "in" & objParam.Name & " := '" & objParam.Value & "';" & vbCrLf
  Next
  
  'build Stored Procedure call
  strSQL = strSQL & "SAVE_APP_DATA("
  For Each objParam In objCmd.Parameters
    strSQL = strSQL & "in" & objParam.Name & ","
  Next
  strSQL = Left(strSQL, Len(strSQL) - 1) & ")"

  'execute sql procedure
  objCmd.CommandText = strSQL
  objCmd.Execute

  SaveApplication = True

ExitFunction:
  If Not objConn Is Nothing Then Set objConn = Nothing
  If Not objCmd Is Nothing Then Set objCmd = Nothing
  Exit Function

HandleError:
  Dim eNbr$, eSrc$, eDsc$
  eNbr = Err.Number
  eSrc = Err.Source
  eDsc = Err.Description
    
  Err.Raise vbObjectError + eNbr, eSrc, eDsc & " " & strSQL
  
  Resume ExitFunction
End Function