✔ Programação GLOBAL®: PIECE OF CAKE - Sempre Use Stored Procedures

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

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