quinta-feira, 23 de abril de 2009

Fazendo o Access acessar o SQL Server com eficácia



Foi muito tempo de pesquisa para chegar até aqui. Vamos lá.

Esqueça tabelas vinculadas. A vantagem dos bancos de dados cliente/servidor sobre os bancos de dados desktop é que eles trafegam pela rede somente os dados necessários e não as tabelas inteiras. Por isso que eles são a única alternativa para grandes quantidades de dados e para operações que funcionam pela internet. Tabelas vinculadas são extremamente lentas, não tirando nenhuma vantagem de uma arquitetura cliente/servidor.

São três técnicas diferentes: uma para formulários (fonte de dados atualizável, editável), outra para consultas ODBC (consulta de passagem de dados, pass-through queries) e outra para comandos (stored procedures para SELECT, INSERT, UPDATE, DELETE).

Você vai precisar criar uma fonte de dados ODBC.

'source do formulário
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'JESUS IS GREAT.
'create a new ADO Connection object
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.Properties("Data Source").Value = ODBC;DSN=ODBC_banco;UID=usuario;PWD=senha1;DATABASE=banco
.Open
End With

'Create an instance of the ADO recordset class, and set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM ContasReceber WHERE CódigoCompleto = '0'"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With

'set the form's Recordset propery to the ADO recordset
Set Me.Recordset = rs
Me.UniqueTable = "ContasReceber"
Set rs = Nothing
Set cn = Nothing


Sequência de conexão ODBC para consultas
ODBC;DSN=ODBC_banco;SERVER=SERVIDOR\SQLEXPRESS;UID=usuario;PWD=senha1;


Código para stored procedures
Dim cnn1136 As New ADODB.Connection, cmd1136_1 As New ADODB.Command, cmd1136_2 As New ADODB.Command
cnn1136.ConnectionTimeout = 1200
cnn1136.Open "Provider=MSDASQL.1;Persist Security Info=false;Initial Catalog=banco;Data Source=ODBC_banco;UID=usuario;PWD=senha1;"


cmd1136_1.CommandText = "DELETE Fornecedores_Par WHERE Usuário = '" & Forms!Login!Usuário & "'"
cmd1136_1.CommandType = adCmdText
cmd1136_1.CommandTimeout = 1200
cmd1136_1.ActiveConnection = cnn1136
cmd1136_1.Execute
Set cmd1136_1 = Nothing


Dim SQL As String
SQL = "INSERT INTO Fornecedores_Par (CódigoCompleto, Usuário, Nome, Fantasia, Imprimir) "
SQL = SQL & "SELECT DISTINCT Fornecedores.CódigoCompleto, '" & Forms!Login!Usuário & "', "
SQL = SQL & "Fornecedores.Razão_Social, Fornecedores.Nome_Fantasia, 'N' "
SQL = SQL & "FROM Fornecedores "
SQL = SQL & "WHERE Fornecedores.Unidade = " & Forms!Unidades_AutoExec!UnidadeAtual & " "
SQL = SQL & "AND Fornecedores.DeadFile = 'N' "

cmd1136_2.CommandText = SQL
cmd1136_2.CommandType = adCmdText
cmd1136_2.CommandTimeout = 1200
cmd1136_2.ActiveConnection = cnn1136
cmd1136_2.Execute
Set cmd1136_2 = Nothing
Set cnn1136 = Nothing

Intervalo de Datas no SQL Server

SELECT * FROM ContasReceber WHERE DataVcto BETWEEN '2007-11-10' and '2007-11-10'

Isso pegará todo os lançamentos de 10 de novembro de 2007, certo? Infelizmente, nem sempre é assim e você acaba tendo um Sistema que não é confiável.

Quando existe uma porção de hora, como no exemplo abaixo, a última data do intervalo da SELECT não é considerada, excluindo o registro da consulta.
2007-11-10 08:00:00.000

Pesquisando no Google, achei um ótimo artigo com várias soluções para esse problema, dentre as quais a que achei mais viável é esta função:
CAST(FLOOR(CAST(DataVcto AS FLOAT))AS DATETIME)

Vamos estudar os 3 componentes dessa função simples e genial?
O primeiro
CAST(DataVcto AS FLOAT) converte a data num número.
39394,3333333333

O que vem depois da vírgula é a parte da hora. Veja que 8 horas é um terço de um dia, o que corresponde exatamente ao ,
3333333333.

A segunção função,
FLOOR, arredonda o número:
FLOOR(CAST(DataVcto AS FLOAT))
Se você consultar a ajuda dessa função (eu só uso o Google) você verá que ela arredonda sempre para o inteiro mais baixo, assim você não tem que se preocupar se passar de ,5.
39394

Esse número inteiro é justamente a data sem a parte da hora.
E agora você já entendeu, né? Basta pegar esse número arredondado de fracionário para inteiro e converter para data de novo.
CAST(FLOOR(CAST(DataVcto AS FLOAT))AS DATETIME)
Dessa forma, 2007-11-10 08:00:00.000 fica 2007-11-10 00:00:00.000

Então, a SELECT acima precisa ser reescrita, ficando assim:
SELECT * FROM ContasReceber WHERE CAST(FLOOR(CAST(DataVcto AS FLOAT))AS DATETIME) BETWEEN '2007-11-10' and '2007-11-10'

Esse é um detalhe crucial para ser cuidado se você quiser ter um sistema confiável.


Por isso, que programação é para louco. Pessoas normais não servem para essa profissão.




Regras de campos texto no SQL Server

É uma coisa de deixar muita gente bobo. Qual a diferença entre um campo nulo (NULL) e um campo vazio ("" ou '') se ambos estão em branco?
Na prática, um campo nulo é aquele que nunca foi preenchido e um campo vazio é aquele que foi preenchido e depois apagado.
Mesmo que você defina o ALLOW NULLS como não, se o campo for digitado e depois apagado, ficando vazio, o SQL Server aceitará! A solução para isso é criar uma constraint do tipo

use bancoxxx;
go

alter table tabela1
add constraint tabela1_campo1_regra_nãoaceitavazio
check (campo1 <> '')

Assim você assegura que seus campos obrigatórios realmente sejam obrigatórios em qualquer situação.

Trabalho com o Microsoft Access XP (VBA) acessando SQL Server e quando descobri isso meu banco já tinha dezenas de tabelas e cada tabela com vários campos, perfazendo um total de milhares de campos sujeitos a isso que considero uma falha ridícula do SQL Server, mesmo que tecnicamente seja considerado correto.

A solução? Bolei uma forma muito louca de fazer um loop por todas as tabelas através do sp_columns @table_name, pegar só os campos nvarchar e aplicar a regra, mandando stored procedures em massa pro SQL. Com 1 ou 2 horas de programação realizei um trabalho que levaria dias de tédio para ser concluído se fosse feito manualmente.

Triggers no SQL Server

veja o trigger abaixo:

create trigger

[dbo].[ControleFaturasUnificadas_Recebimentos_DeletaMovContábil_Receb]

on [dbo].[ControleFaturasUnificadas_Recebimentos]

for delete

as

declare @CodCompleto nvarchar(20)

select @CodCompleto = CódigoCompleto

from deleted

delete MovimentoContábil

where CódigoFaturaUnificada_Recebimento = @CodCompleto


Tudo certo, né?
Na verdade, tudo errado, porque se você deletar vários registros da tabela ControleFaturasUnificadas_Recebimentos ao mesmo tempo, só o último registro deletado da tabela primária provocará a exclusão do registro correspondente na tabela secundária. Ou seja, inúmeros registros que deveriam ser deletados continuarão no banco de dados.

A solução é fazer um JOIN entre a tabela primária (tabela Deleted, uma tabela virtual dos registros deletados) com a tabela secundária. Veja o mesmo exemplo remodelado:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER trigger

[dbo].[ControleFaturasUnificadas_Recebimentos_DeletaMovContábil_Receb]

on [dbo].[ControleFaturasUnificadas_Recebimentos]

for delete

as

delete MovimentoContábil

from MovimentoContábil, Deleted

where CódigoFaturaUnificada_Recebimento = Deleted.CódigoCompleto


Agora o negócio funciona!