Simplificar Consultas Ação por código

Já teve que criar um processo de importação/atualização/exportação usando os métodos Execute ou RunSQL em lote?

Autor: Dev Hashish

Se já, então conhece as dores de ter que voltar ao seu código SQL para atualizá-lo. Sendo bem honesto, já cheguei bem próximo de arrancar os cabelos em diversas ocasiões.

Até que um dia me toquei que podia usar uma tabela Access e um pouco de lógica não apenas para "aparar" possíveis centenas de linhas de código SQL e VBA, além de fazer com que as próprias consultas sejam autoducumentadas. Podemos continuar a alterar as consultas criadas com o assistente e a grade sem ter que ficar se preocupando com a concatenação de todo o SQL em código.

Defini uma tabela assim:

O campo DescricaoSQL representa o conteúdo real de cada campo. A idéia por trás desse processo é salvar os comandos SQL na ordem em que devem ser executados, assegurando sua identidade por mei do número em SequenciaSQL. Se tivermos diversas funções que usam as consultas ação, elas ainda podem ser salvas na mesma tabela desde que seja usado o campo ChamadoDaProc para categorizar os comandos SQL. Anguma entradas típicas na tabela seriam mais ou menos assim:

Criamos um formulário simples, vinculado a esta tabela, contendo todos os campos. SQLID pode ficar de fora. Este formulário vai fornecer os meio para salvar, alterar e ver os comandos SQL salvos na tabela.

Os passos requeridos para executar estas consultas em seqüência como o seriam se as consultas estivessem ordenadas no código é, na verdade, bastante simples.

Precisamos, basicamente, dos seguintes passos:

• Identificar o procedimento SQL que necessitamos que seja executado em um procedimento em particular.
• Encontrar o primeiro comando SQL na seqüência (SequenciaSQL) para todos os comandos encontrados para o procedimento identificado.
• Substituir todas as aspas por Chr(34) no comando SQL.
• Executar a consulta ação.

Com uma rotina de erro para tratar os probleminhas de sintaxe e uma função para substituir as aspas, temos tudo para continuar. Uma amostra simples da utilização deste conceito é o código a seguir:

'*************** Code Start ***************
' Este código foi escrito originalmente por Dev Ashish. 
' Ele não deve ser alterado ou distribuíso, 
' exceto como parte de um aplicativo. 
' Use-o livremente em seus aplicativos,  
' desde que esta nota de copyright permaneça inalterada.
'
' Código cortesia de
' Dev Ashish
'
Sub sSomeSubRoutine()
Dim lodb As Database, strSQL As String
Dim loSQLRS As Recordset, lngCurrent As Long
Dim varTmp As Variant
Const cERR_GRACEFUL_EXIT = vbObjectError + 20

    On Local Error GoTo Err_handler
    varTmp = SysCmd(acSysCmdSetStatus, "Iniciando o processo em lote...")
    Set lodb = CurrentDb
    Set loSQLRS = lodb.OpenRecordset("Select * from tblSQL where" _
                    & " ChamadoDaProc='sIniciaProcessoExterno'", dbOpenSnapshot)
    lngCurrent = 1
    With loSQLRS
        .FindFirst "SequenciaSQL=" & lngCurrent
        If .NoMatch Then Err.Raise cERR_GRACEFUL_EXIT
        Do While Not .NoMatch
            strSQL = adhHandleQuotes(!SQLString)
            lodb.Execute strSQL, dbFailOnError
            lngCurrent = lngCurrent + 1
            .FindFirst "SequenciaSQL=" & lngCurrent
        Loop
    End With
    
Exit_Here:
    varTmp = SysCmd(acSysCmdClearStatus)
    Set loSQLRS = Nothing
    Set lodb = Nothing
    Exit Sub
Err_handler:
    Dim strXX As String
    Select Case Err.Number
        Case 3065:      'SQL é um comando SELECT
            strXX = "Apenas consultas ação podem ser executadas pelo método Execute."
            strXX = strXX & vbCrLf & "Altere o comando SQL para um comando ação."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Erro em comando SQL"
        Case 3075:      'Erro de sintaxe no comando SQL
            strXX = "O comando SQL a seguir tem um erro de sintaxe."
            strXX = strXX & vbCrLf & "Verifique a string SQL e tente novamente."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Erro em comando SQL"
        Case cERR_GRACEFUL_EXIT:
        Case Else:
            strXX = "Proc: sSomeSubRoutine"
            strXX = strXX & vbCrLf & "Erro Nº: " & Err.Number
            strXX = strXX & vbCrLf & "Descrição: " & Err.Description
            If Not (loSQLRS Is Nothing) And Not (lodb Is Nothing) Then
                strXX = strXX & vbCrLf & "A última consulta ação " & vbCrLf & vbCrLf & _
                        loSQLRS!SQLString & vbCrLf & vbCrLf & "afetou " & _
                        lodb.RecordsAffected & " registros"
            End If
            MsgBox strXX, vbExclamation + vbOKOnly, "Erro de execução"
    End Select
    Resume Exit_Here
End Sub


Public Function adhHandleQuotes(strValue As String) As String
    ' Corrige todas as ocorrências de aspa em uma string
    ' dividindo-a e inserindo Chr$(34) sempre que
    ' encontrar aspas dentro da string.  Desta forma o Jet pode
    ' tratar as strings nas consultas.
    '
    ' Encontrado em Access 97 Developer's Handbook
    ' por Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997.  Todos os direitos reservados.
    '
    ' Solução sugerida por Jurgen Welz, um leitor diligente.

    ' Entrada:
    '   strValue:   Valor a ser corrigido.
    ' Saida:
    '   Return value: O texto com as aspas corrigidas.
    ' Exige:
    '   adhReplace (ou alguma outra função que substitua
    '       uma string por outra)
    '
    ' Exemplo:
    '    adhHandleQuotes("John "Big-Boy" O'Neil") returns
    '     "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

   Const QUOTE As String = """"

   adhHandleQuotes = QUOTE & adhReplace(strValue, QUOTE, _
    QUOTE & " & Chr$(34) & " & QUOTE) & QUOTE
 End Function

 Function adhReplace(ByVal varValue As Variant, _
  ByVal strFind As String, ByVal strReplace As String) As Variant

     ' Substitui todas as instâncias de strFind por strReplace em varValue.

     ' Encontrado em Access 97 Developer's Handbook
     ' por Litwin, Getz, and Gilbert (Sybex)
     ' Copyright 1997.  Todos os direitos reservados.

     ' Entrada:
     '    varValue: Valor a ser modificado
     '    strFind: String a localizar
     '    strReplace: String para substituir strFind     '
     ' Saida:
     '    Return value: varValue, com todas as ocorrências de strFind
     '     substituídas por strReplace.

     Dim intLenFind As Integer
     Dim intLenReplace As Integer
     Dim intPos As Integer

     If IsNull(varValue) Then
         adhReplace = Null
     Else
         intLenFind = Len(strFind)
         intLenReplace = Len(strReplace)

         intPos = 1
         Do
             intPos = InStr(intPos, varValue, strFind)
             If intPos > 0 Then
                 varValue = Left(varValue, intPos - 1) & _
                  strReplace & Mid(varValue, intPos + intLenFind)
                 intPos = intPos + intLenReplace
             End If
         Loop Until intPos = 0
     End If
     adhReplace = varValue
 End Function
'****************** Final do Código *****************

Os benefícios deste trabalho:

• O comando SQL está pronto para ser colado no modo SQL de um objeto consulta. Não é mais necessário excluir sublinhados e operadores de concatenação.
• Precisa que um comando SQL seja executado em uma ordem diferente? Simplesmente altere SequenciaSQL para um procedimento em particular!
• Mantenha as entradas em DescricaoSQL atualizadas e vai descobrir que o código fica bem mais fácil de entender quando precisar voltar a ele mais tarde. Pode, até, criar um relatório da tabela, classificado por ChamadoDaProc e SequenciaSQL e a documentação está pronta!!
• Preciso mencionar as páginas de código das quais você se livrou? Tudo é controlado por um único procedimento de controle.
• Deseja testar um certo número de procedimentos? Simplesmente coloque os procedimentos que você não quer executar "fora da seqüência". Isto é, se você deseja executar os procedimentos 1 a 10 mas não 11 e 12, mude a SequenciaSQL dos comandoa 11 e 12 para, digamos 20 e 21. Como a lógica do procedimento mestre procura por valores sequencialmente incrementais de SequenciaSQL, o código vai para após a execução do décimo comando.