• Artigo

Ei, Equipe de Scripts!O retorno do script de banco de dados

The Microsoft Scripting Guys

Faça download do código deste artigo: HeyScriptingGuy2008_04.exe (151KB)

Sabe, se a Equipe de Scripts tivesse um ponto fraco (veja bem, nós dissemos "se"), seria este: nós nos preocupamos muito com as possíveis conseqüências das ações que tomamos ou das que não tomamos. Em vez de agirmos em causa própria, quase sempre nos contemos e fazemos o que achamos ser melhor para os outros. E fazemos isso a despeito do que essas ações poderiam significar para nós.

Veja os artigos sobre scripts de banco de dados, por exemplo. Para sermos totalmente honestos, achamos difícil escrever artigos sobre scripts de banco de dados. Não porque scripts de banco de dados sejam difíceis; na verdade, são bem fáceis. O problema é que a Equipe de Scripts não tem muitas oportunidades de trabalhar com bancos de dados; por causa disso, sempre que escrevemos sobre banco de dados, temos de parar e pensar no que estamos fazendo. Como você já percebeu há muito tempo, pensar definitivamente não é um requisito para alguém fazer parte da Equipe de Scripts.

Para falar a verdade, se qualquer um de nós realmente pensasse sobre isso, provavelmente não haveria uma Equipe de Scripts.

Dessa forma, se artigos sobre scripts de bancos de dados são tão difíceis de escrever, por que continuamos a escrevê-los? A resposta é simples: estamos preocupados com o que poderia acontecer com o resto do mundo caso parássemos de escrever esses artigos. Quando Sir Arthur Conan Doyle decidiu matar Sherlock Holmes (fazendo-o cair de um despenhadeiro na estória "O problema final"), pensou simplesmente que estaria se livrando de um personagem sobre o qual não desejava mais escrever. Em vez disso, conseguiu gerar protestos em todo o mundo. De acordo com alguns relatos, pessoas em Londres usavam faixas negras nos chapéus e mangas, pois ficaram de luto quando souberam da notícia. "O problema final" foi publicado na The Strand Magazine em 1893; em resposta, 20 mil cancelaram suas assinaturas.

Ai!

A Equipe de Scripts está preocupada, pois matar nossos artigos sobre scripts de banco de dados poderia ter um efeito mundial semelhante. Não queremos ser a causa de tanta dor e desespero e certamente não queremos que 20 mil de vocês cancelem suas assinaturas da TechNet Magazine. Portanto, gostaríamos de anunciar que a Equipe de Scripts continuará a escrever artigos sobre scripts de bancos de dados. Quando? Bem, que tal agora mesmo?

Adicionando registros a um banco de dados

O que pensamos em fazer neste mês é mostrar a você alguns pequenos truques engenhosos para trabalhar com bancos de dados. E sim, esses truques serão um complemento dos truques mostrados em nossa coluna de setembro de 2007 (technetmagazine.com/issues/2007/09/HeyScriptingGuy); até mesmo a Equipe de Scripts não desceria tão baixo escrevendo o mesmo artigo duas vezes.

Bem, não até pensarmos em algo para não sermos descobertos.

Vamos começar mostrando uma maneira simples de adicionar um registro a um banco de dados. Vamos supor que temos um banco de dados chamado C:\Scripts\Inventory.mdb e que ele inclui uma tabela chamada Computers. A tabela Computers contém os seguintes campos:

  • ComputerName
  • SerialNumber
  • UserName
  • Departament

Agora, como podemos adicionar um novo computador a esse banco de dados? Observe o código da Figura 1.

Figure 1 Adding a record to the Computers table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

Não vamos entrar em detalhes sobre as primeiras linhas deste script; se você precisar desse tipo de informação, talvez seja melhor dar uma olhada em alguns dos recursos que temos no Script Center, microsoft.com/technet/scriptcenter. Basta dizer que usamos as constantes adOpenStatic e adLockOptimistic para controlar o tipo de cursor e o bloqueio de registros para o nosso conjunto de registros (sim, isso parece impressionante, mas na verdade é bem simples). Depois de criarmos instâncias dos objetos ADODB.Connection e ADODB.Recordset, usamos este comando para abrir o banco de dados:

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

A propósito, esse comando abre um banco de dados do Microsoft® Access® 2003. Para abrir um banco de dados do Access 2007, use este comando:

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

E, embora estejamos começando a nos afastar um pouco do tópico principal, talvez você possa usar um comando semelhante a este para abrir um banco de dados do SQL Server®:

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

Até aqui, tudo o que mostramos é código padronizado; como você verá em breve, grande parte de nossos scripts começará com essas mesmas linhas de código. Na verdade, a parte que queremos enfatizar é esta linha:

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Aqui, estamos usando a consulta INSERT INTO para, bem, inserir um novo registro na tabela Computers. Observe que, após o nome da tabela, incluímos os nomes de todos os campos para os quais temos valores, separando-os por vírgulas e colocando-os entre parênteses.

Você pode estar se perguntando: o que a Equipe de Scripts quer dizer quando menciona "todos os campos para os quais temos valores"? Boa pergunta. Bem, digamos que esse novo computador ainda será atribuído a um departamento. Supondo que o banco de dados nos permite colocar um valor nulo no campo Department, podemos simplesmente deixar Department de fora da lista e não atribuir nenhum valor a ele. Em outras palavras, poderíamos escrever uma consulta como esta:

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

Falando em atribuir valores, veja o que vem depois dos nomes de campo: a palavra-chave VALUES, seguida pelos valores que queremos atribuir a cada campo (que também estão entre parênteses). Tenha em mente que, quando listamos os nomes de campo, podemos fazê-lo em qualquer ordem; por exemplo, colocamos ComputerName primeiro, mesmo que esse não seja o primeiro campo do banco de dados. Isso funciona bem para nomes de campos, mas não tanto para valores. Os valores devem ser listados na mesma ordem dos campos. Se o nosso primeiro campo listado for ComputerName, o nosso primeiro valor deverá ser o nome do computador. Se não for, teremos problemas (por exemplo, podemos terminar atribuindo o nome de usuário ou o número de série ao campo ComputerName).

Como você pode ver, atribuir valores não é muito complicado; basta garantir a formatação dos valores de acordo com o tipo de dados: as datas e os valores de cadeia de caracteres devem ser inseridos entre aspas simples; os valores numéricos e booleanos não devem ser inseridos entre aspas simples.

Ah, e se você tiver um valor que contenha suas próprias aspas simples (como o nome O'Brien), será preciso "escapá-la", duplicando-a:

'O''Brien'

Doido, mas é verdade.

Excluindo registros de um banco de dados

Isso foi realmente muito bom: podemos adicionar um registro a um banco de dados executando uma única consulta SQL. Poderia haver algo melhor do que isso? Nós não conhecemos.

Bem, exceto talvez sermos capazes de excluir vários registros de um banco de dados usando uma única consulta.

Digamos que a sua empresa decida eliminar seu departamento de Recursos Humanos (não, não vamos fazer piadas; normalmente já temos muitos problemas com o departamento de RH da Microsoft). Como é possível excluir todos os computadores de Human Resources do nosso banco de dados Inventory? A Figura 2 mostra um meio de fazer isso.

Figure 2 Deleting multiple records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

Como você pode ver, esse script em particular começa exatamente da mesma forma que o script para a adição de um novo registro: definindo algumas constantes, criando alguns objetos e fazendo uma conexão ao banco de dados Inventory.mdb. Depois disso, simplesmente usamos uma consulta DELETE para excluir todos os registros (*) da tabela Computers ou, pelo menos, todos os registros onde o campo Department for igual a Human Resources:

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

É tão fácil como cair de um despenhadeiro.

Hum, sem ofensas, Sherlock Holmes.

Atualizando registros em um banco de dados

Não há dúvidas de que todos concordam como seria divertido excluir todos os traços do nosso departamento de Recursos Humanos (Observação para o departamento de RH da Microsoft: queremos dizer divertido para outras pessoas e seus departamentos de RH. Não tem nada a ver conosco. Nós detestaríamos fazer isso). Todavia, excluir todos os computadores de RH do banco de dados Inventory poderia não ser a melhor coisa a se fazer. Por que não? Bem, a menos que os seus funcionários de RH tenham fugido com todo o hardware (o que parece com algo que eles fariam — bem, deixa para lá, dissemos que não faríamos piada com isso), esses computadores ainda pertencem à sua empresa; isso significa que eles ainda estariam listados no banco de dados. Sabendo disso, vamos fazer o seguinte. Em vez de excluirmos os registros do banco de dados, vamos simplesmente atualizá-los; vamos alterar o nome do departamento de cada um dos computadores de Human Resources para None (Nenhum). Em outras palavras, executaremos o script mostrado na Figura 3.

Figure 3 Updating records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

Novamente, a única diferença desse script é a consulta SQL. Neste caso, estamos usando uma consulta UPDATE para atualizar registros na tabela Computers. Além da palavra-chave UPDATE, precisamos adicionar dois parâmetros que dizem à consulta como atualizar os registros e quais deles deverão ser atualizados:

  • Set Department = 'None'. Isso simplesmente especifica o novo valor do campo Department. Agora você pode definir mais de um valor de campo por vez usando uma sintaxe como esta: Set Department = 'None', UserName = 'None'. Como você pode ver, isso definirá os campos Department e UserName como None.
  • Where Department = 'Human Resources'. Essa é uma cláusula Where que indica que recursos deverão ser atualizados; nesse caso, todos os registros que têm um campo Department igual a Human Resources. Nós a incluímos por um motivo bem simples: não queremos modificar todos os registros do banco de dados, somente aqueles (computadores) que pertenciam ao departamento de Recursos Humanos.

A seguir, algo muito interessante que pode ser feito com consultas Update. Digamos que a sua empresa tenha decidido dar um aumento de custo de vida de 10% a alguém (obviamente, a equipe de scripts se parece com Sir Arthur Conan Doyle em pelo menos um aspecto: em nossos textos, nós lidamos com mundos fictícios). A seguir, um script que abre uma tabela chamada Employees e que modifica o campo Salary de todos os funcionários. E para que ele define o campo Salary? A consulta define esse campo como o salário atual do funcionário vezes 1,1; em outras palavras, dá a cada funcionário um aumento de 10% (ah, se fosse tão fácil assim!). Esta é a consulta:

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

O que há de legal nisso? Como você pode ver, é possível fazer cálculos em suas consultas Update. Você decidiu estender o contrato de todos os seus funcionários temporários por mais 60 dias? Nesse caso, uma consulta similar à mostrada a seguir pode ser útil:

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Ótimas formas de se recuperar dados

Falando de consultas que poderiam ser úteis, vamos resumir tudo examinando algumas ótimas formas de recuperar dados de um banco de dados. Por exemplo, veja um exemplo de script simples, mas muito útil. Suponha que o nosso banco de dados Inventory inclua um campo chamado Price que — logicamente — represente o preço do computador. Deseja saber quais são os cinco computadores mais caros da sua organização? O script de exemplo da Figura 4 responderá isso para você.

Figure 4 Sorting the recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

Como você pode ver, fizemos duas coisas nessa consulta SQL: classificamos o conjunto de registros por Price (Order By Price) e solicitamos somente os cinco primeiros computadores (ou seja, os cinco mais caros); é isso que SELECT Top 5 faz. Se quiséssemos uma lista dos 10 computadores mais caros, usaríamos esta consulta:

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Observação: também adicionamos um pouco de código que faz loops pelo conjunto de registros e exibe o nome de cada computador. Não precisamos desse código em nossos scripts anteriores porque eles não retornavam nem exibiam dados.

Como alternativa, podemos obter os primeiros 10% usando esta consulta:

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

E se quiséssemos obter os computadores mais baratos? Sem problemas; simplesmente usaríamos a mesma abordagem, exceto que dessa vez classificaríamos o conjunto de registros em ordem decrescente (ou seja, do menor para o maior preço). Em outras palavras, usamos esta consulta, onde DESC indica que o conjunto de registros deve ser classificado em ordem decrescente:

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Viu? Nós dissemos que essas consultas seriam muito úteis.

A seguir, mais duas que talvez você ache interessantes. Suponha que o seu banco de dados tenha um campo chamado Budgeted que controle o valor monetário originalmente orçado para o computador. Deseja comparar o custo real ao custo orçado? Esta consulta retorna uma lista de computadores que custam mais do que o valor orçado:

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Enquanto isso, esta consulta calcula o preço médio de todos os computadores (SELECT AVG(Price) FROM Computers) e retorna uma lista de todos os computadores que custam menos do que o preço médio:

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

E sim, você está certo, nós usamos uma consulta SELECT em nossa cláusula Where. Como isso funciona? Bem, essa será outra história para outro dia. Mas, como você pode ver, ela abre uma porta para a recuperação de todos os tipos de informações legais com as suas consultas SQL.

A moral da história

Como observamos no início da coluna deste mês, não tínhamos certeza se queríamos escrever outro artigo sobre scripts de banco de dados. No entanto, estamos contentes por termos seguido em frente, parcialmente porque pensamos que você achará que algumas dessas consultas são bem úteis, mas também por isso: se não tivéssemos escrito esta coluna agora, terminaríamos por ter de escrevê-la cedo ou tarde. Afinal de contas, até mesmo Sir Arthur Conan Doyle foi forçado a trazer Sherlock Holmes de volta dos mortos. Sir Arthur tentou dar seus primeiros passos nessa direção escrevendo uma nova história de Sherlock Holmes que supostamente teria acontecido antes da morte do detetive. Entretanto, depois disso, ele simplesmente cedeu à pressão de seus leitores, tecendo uma trama de alguma forma dúbia para explicar como Sherlock Holmes forjou sua própria morte. Por fim, todos e, portanto, tudo, estava perfeitamente bem.

O interessante é que quando a última história de Sherlock Holmes foi lançada, foram feitas 30 mil novas assinaturas da The Strand Magazine. Os editores de Sir Arthur, bastante agradecidos, tanto na Grã-Bretanha como nos EUA, responderam transformando-o no escritor mais bem pago do mundo.

Hum, Sir Arthur Conan Doyle trouxe de volta um personagem muito querido e se tornou o escritor mais bem pago do mundo; agora a Equipe de Scripts trouxe de volta um tópico muito querido — e sem ter de forjar qualquer morte. Será que o pessoal da TechNet Magazine percebeu isso?

O desafio de script do Dr. Scripto

O desafio mensal que testa não apenas sua habilidade de resolver quebra-cabeças, mas também de criar scripts.

Abril de 2008: escolha uma letra

No desafio deste mês, você terá de inserir as letras de A a O nos espaços azuis para revelar o nome da função VBScript. Cada letra (de A a O) deverá ser usada somente uma vez e as letras não aparecem em ordem alfabética. A letra inserida pode estar no início do nome da função, no final do nome da função ou em qualquer lugar no meio do nome da função. Por exemplo, na linha a seguir, você iria inserir a letra D para completar o nome da função IsDate:

  (Clique na imagem para aumentar a exibição)

Agora é a sua vez; insira as letras de A a O para revelar o nome da função VBScript nesta grade:

**** (Clique na imagem para aumentar a exibição)

ANSWER:

O desafio de script do Dr. Scripto

Resposta: Escolha uma letra, abril de 2008

  (Clique na imagem para aumentar a exibição)

The Microsoft Scripting Guys trabalha para a — bem, são funcionários da — Microsoft. Quando não está jogando/treinando/assistindo beisebol (e diversas outras atividades), a equipe administra o Script Center da TechNet. Confira no site www.scriptingguys.com.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados. A reprodução parcial ou completa sem autorização é proibida..