Quando Err.Number non aiuta e ci si trova in situazioni imbarazzanti (VBA)

22. Giugno 2018 Riflessioni, SQL Server 0

In questi giorni mi sono trovato a dover sviluppare un’applicazione che ha come front end Excel e come repository delle tabelle su SQL Server; si tratta di un’applicazione per lo sviluppo del forecast e del budget commerciali.

Per la lettura da SQL non c’è problema perché il modello dati di Excel è più che adeguato e consente di creare oggetti tabella piacevoli e funzionali, ma se vi trovate nella necessità di dover scrivere i dati all’indietro, magari in una tabella di destinazione diversa dall’originale, la musica cambia.

ADODB è un’ottima libreria e di certo la logica seguente è quella giusta:

  1. inizio una transazione
  2. identifico la tabella e ciclo le righe
  3. per ogni riga preparo un’insert o un’update
  4. eseguo l’istruzione e traccio l’errore, se non ho rilevato errori passo alla riga successiva, se ho rilevato errori esco
  5. se ho finito senza errori committo

Fermo restando che questa logica non traccia le eventuali righe cancellate, si pone il problema della velocità: se le righe sono tante è mostruosamente lenta (io ne ho circa 180.000 da scrivere).

Come aggirare il problema?

Con un batch SQL da eseguire in un’unica istruzione (qui la soluzione che risolve anche il problema delle righe cancellate):

  • inizializzo un buffer di testo (la mia mega SQL)
  • identifico la tabella (o creo una tabella temporanea) e ciclo le righe
  • per ogni riga preparo un’insert o un update (o solo l’insert nella tabella temporanea) che aggiungo al buffer
  • alla fine delle righe accodo un’eventuale istruzione di merge tra la tabella temporanea e la tabella fisica
  • inizio una transazione
  • eseguo il batch e committo se non ho errori, altrimenti faccio un rollback

Questa cosa può essere decine di volte più veloce della precedente ma ha un piccolo problema: se in mezzo al batch ci sono errori non bloccanti (per esempio il classico errore di insert fallito per un problema di chiave), l’esecuzione del comando non fallisce e voi committate una situazione inconsistente.

Per risolvere il problema è sufficiente iniziare il batch con:

SET XACT_ABORT ON;
SET NOCOUNT ON;

Se durante l’esecuzione viene rilevata un’eccezione non bloccante, il batch si ferma e potete gestire il rollback manuale.

Di seguito il codice campione:

Set conn = New ADODB.Connection
conn.Open dbConnString
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandTimeout = 0
cmd.CommandText = "<sql da eseguire>"
conn.BeginTrans
On Error Resume Next
Err.Clear
cmd.Execute
If Err.Number <> 0 Then
    msg = Format(Err.Number) + " - " + Err.Description
    conn.RollbackTrans
    conn.Close
    On Error GoTo 0
    MsgBox ("Errore durante la creazione della stored procedure: " + msg)
    Exit Sub
End If
conn.CommitTrans
conn.Close
On Error GoTo 0