PIECE OF CAKE - Sempre Use Stored Procedures - Always Use Stored Procedures


Todos temos muitos dados no MS Excel que queremos guardar em nossas tabelas do banco de dados SQL Server, por exemplo. Faço muito isso, e a maneira CORRETA de fazê-lo é criando uma Proc - Stored Procedure - para cada operação, executando-as a partir do VBA.

Mas também existe a maneira mais RÁPIDA de se fazer, e também a menos correta, construindo uma cadeia de código String SQL, executando-a. Mas, como pôde imaginar pelo título, escolhi a maneira CORRETA, certo? Errado, escolhi a maneira RÁPIDA e por isso estamos aqui, para aprender com os erros.

Aqui estão, ambas as formas. A mais longa (e CORRETA) e a mais curta (e RÁPIDA) de se fazer isso.


Para ajudar, como sempre acontece, alguns números tinham sido formatados como datas. Havia algum código que se parecia com isso:
vaData = lo.DataBodyRange.ValuesSql = "INSERT INTO Blend (ManifestID) VALUES (" & vaData(i,1) & ")"adConn.Execute sSql

O campo ManifestID é um BIGINT e vaData (i, 1) um campo com valores no formato 4/15/2023. Mas no campo ManifestID continha valores como 45031, alguém (eu) se confundiu, com a data, que perdeu a formatação. Assim, percebi que várias dezenas de entradas colocaram zero no campo ManifestID. O SQL Server obedientemente executou 2023/04/15, fazendo uma divisão (dividindo 4 por 15, dividido por 2023), surgindo diversos zeros, que foram colocados nos campos.

Depois de alguma auto-flagelação, me perguntava se uma Proc teria pego esse erro. Conclui que ao tentar passar uma data como parâmetro para o BIGINT, o código teria dado errado e teria evitado toda essa confusão. Mas estava errado. Em vez disso, a Proc teria convertido a data para o seu valor inteiro - ao invés de dividí-lo como no método SQL String. O Excel armazena datas com números de dias desde 1899/12/31. E isso não é tudo, pois tenho certeza de que o SQL Server não armazena dessa forma. E como o ADO ou T-SQL sabem que precisam convertê-lo dessa forma?

Assim, fiz um teste. Primeiro criei uma tabela:

CREATE TABLE [dbo].[TestDateBigInt]( [c1] [BIGINT] NULL

Depois criei uma Proc para inserir registros:

CREATE PROCEDURE [dbo].[spTestDateBigInt] @BigInt AS BIGINTAS    INSERT  INTO dbo.TestDateBigInt            ( c1 )    VALUES  ( @BigInt  -- c1 - bigint              )

Então escrevi algum código para inserir linhas:

Sub TestDateBigInt()
    
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim pm As ADODB.Parameter
    Dim rs As ADODB.Recordset
    Dim sSql As String
    Dim i As Long
    Dim vaFormats As Variant
    Dim vaData As Variant
    
    On Error GoTo ErrH
    
    Set cn = New ADODB.Connection
    cn.Open msCONN
    
    Let vaFormats = Split("General m/d/yyyy")
    
    For i = 0 To 1
        'Change the format
        Let Sheet1.Range("G1").NumberFormat = vaFormats(i)
        Let vaData = Sheet1.Range("G1:G2").Value
        'Insert record
        Let sSql = "INSERT INTO TestDateBigInt (c1) VALUES (" & vaData(1, 1) & ")"
        cn.Execute sSql
        
        'Insert record via stored procedure
        Set cmd = New ADODB.Command
        Let cmd.ActiveConnection = cn
        Let cmd.CommandText = "spTestDateBigInt"
        Let cmd.CommandType = adCmdStoredProc
        Set pm = cmd.CreateParameter("@BigInt", adBigInt, adParamInput)
        Let pm.Value = Sheet1.Range("G1").Value
        cmd.Parameters.Append pm
        
        cmd.Execute
    Next i
    
ErrH:
    On Error Resume Next
        Set rs = cn.Execute("SELECT * FROM TestDateBigInt")
        Debug.Print rs.GetString

        rs.Close
        cn.Close

        Set rs = Nothing
        Set cmd = Nothing
        Set cn = Nothing    
End Sub

No código, defini dois formatos gerais para a matriz: m / d / aaaa. Ao percorrer essa matriz e aplicar os formatos da célula G1 onde estão os número inteiros insuspeitos.

Na primeira passagem, ele é formatado como geral, aparecendo como um número inteiro adequado. Então preparei a string INSERT INTO SQL e a executei logo após a conexão. Assim, ainda dentro do loop, faço tudo da maneira certa: Crio um objeto, adiciono um parâmetro, e o executo.

Na segunda iteração do loop, a célula G1 fica formatada como data, e então, tudo acontece novamente.


Estava esperando um erro, então tinha um manipulador de erros que mostraria toda a tabela sempre que algo não ocorresse bem. Mas tudo ocorreu bem. Foi muito bem executado.
4500045000045000

Com o número inteiro formatado, tanto o método SQL String como o método Proc inserem os dados corretamente. 

Fiquei feliz por compreender todo o código mais claramente e ter aprendido a lição, prometendo sempre usar Procs como um bom menino.


#A&A #ADO #MSEXCEL #EXCEL #PIECEOFCAKE #SQL #SQLServer #SQLString #StoredProcedures #TSQL #VBA #POC


Defina a Latitude e a Longitude - Find Latitude and Longitude of any address using Google Map API and VBA


VBA Excel - Traduzindo Planilhas - MS Excel VBA Script to Translate worksheets using the Google Translate API
VBA Excel - Traduzindo Planilhas - MS Excel VBA Script to Translate worksheets using the Google Translate API

Excel - Manipule o Google Maps em sua Planilha - Put a Google Map in your Spreadsheet
Excel - Manipule o Google Maps em sua Planilha - Put a Google Map in your Spreadsheet

Convertendo Texto em Imagem - Convert Text to an Image using the VBA Windows API
Convertendo Texto em Imagem - Convert Text to an Image using the VBA Windows API

Correção de Métricas - For Subscripts, Superscripts and Common Typos
Correção de Métricas - For Subscripts, Superscripts and Common Typos

MS Access - Cinco Formas Manuais de Reparo
MS Access - Cinco Formas Manuais de Reparo

MS Access e MS Word - Técnica de Automação
MS Access e MS Word - Técnica de Automação

Microsoft Access - Removendo Prefixo das Tabelas
Microsoft Access - Removendo Prefixo das Tabelas

Sempre Use Stored Procedures - Always Use Stored Procedures
Sempre Use Stored Procedures - Always Use Stored Procedures

A&A - Dados ou Informações?
A&A - Dados ou Informações?

Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛
• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 


⬛◼◾▪ CONTATO ▪

Nenhum comentário:

Postar um comentário

diHITT - Notícias