Share via

  • Статья

Эй, автор сценариев!Возвращение сценария базы данных

Программисты корпорации Майкрософт

Загрузить исходный код для этой статьи: HeyScriptingGuy2008_04.exe (151KB)

Вы знаете, если у программистов корпорации Майкрософт (ну...мы говорим «если») есть недостаток, он следующий – мы слишком беспокоимся о возможных последствиях действий, которые мы предпринимаем или не предпринимаем. Вместо того, чтобы действовать исключительно в собственных интересах, мы часто сдерживаемся и поступаем так, как, как мы думаем, будет лучше для всех остальных. И действуем так независимо от того, что это может значить для нас.

Возьмем, к примеру, статьи о написании сценариев баз данных. Если честно, программистам Майкрософт трудно писать статьи о создании сценариев баз данных. Не потому, что создавать сценарии очень сложно, на самом деле это простой процесс. Проблема заключается в том, что программистам редко предоставляется возможность поработать с базами данных; поэтому каждый раз, когда мы пишем о базах данных, нам приходится отвлекаться и обдумывать написанное. Как вы, вероятно, уже давно поняли, способность думать – определенно не является обязательным требованием для программистов Майкрософт.

Собственно говоря, если бы кто-нибудь из нас изначально действительно думал об этом, вероятно, программистов не было бы вообще.

Так что, если программистам корпорации Майкрософт так трудно писать статьи о создании сценариев баз данных, почему же мы продолжаем этим заниматься? Ответ очень простой: мы беспокоимся о том, что может произойти с остальным миром, если мы перестанем писать эти статьи. Когда сэр Артур Конан Дойль решил убить Шерлока Холмса (он упал со скалы в рассказе «Последнее дело Холмса»), он думал, что просто избавляется от персонажа, о котором больше не хочет писать. Но это вызвало возмущение во всем мире. По некоторым сообщениям, жители Лондона действительно носили черные ленты на шляпах и рукавах после того, как узнавали эти новости. Рассказ «Последнее дело Холмса» был опубликован в журнале The Strand Magazine в 1893 году, что привело к тому, что от подписки отказались 20 000 читателей.

Вот так-то.

Программисты корпорации Майкрософт обеспокоены тем, что прекращение выпуска статей под написанию сценариев баз данных приведет к схожим последствиям во всем мире. Мы не хотим причинять такую боль и страдания и, разумеется, против того, чтобы 20 000 читателей отказались от подписки на TechNet Magazine. Поэтому хотим объявить, что продолжим писать о сценариях баз данных. Когда? Ну, может быть прямо сейчас?

Добавление записей в базу данных

В выпуске журнала за этот месяц мы продемонстрируем несколько небольших замечательных хитростей при работе с базами данных. Да, это дополнение к приемам работы, о которых рассказывалось в выпуске журнала за сентябрь 2007 года (technetmagazine.com/issues/2007/09/HeyScriptingGuy); даже программисты не пойдут на то, чтобы выпустить точно такую же статью второй раз.

Ну, если не будут уверены, что это сойдет с рук.

Начнем с простого способа добавления записи в базу данных. Предположим, что имеется база данных с именем C:\Scripts\Inventory.mdb, содержащая таблицу с именем Computers. Таблица Computers включает следующие поля:

  • ComputerName
  • SerialNumber
  • UserName
  • Department

Как добавить в базу данных новый компьютер? Взгляните на код, показанный на рис. 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

Не будем останавливаться на первых строках этого сценария; при необходимости сведений о них вы можете обратиться к ресурсам центра сценариев по адресу microsoft.com/technet/scriptcenter. Достаточно сказать, что используются константы adOpenStatic и adLockOptimistic для управления типом курсора и блокировкой записей для нашего набора записей. (Да, это звучит впечатляюще, но на самом деле все довольно просто.) После создания экземпляров объектов ADODB.Connection и ADODB.Recordset мы используем следующую команду для открытия базы данных:

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

Кстати, эта команда открывает базу данных Microsoft® Access® 2003. Для открытия базы данных Access 2007 необходимо использовать другую команду:

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

Хотя мы немного отклоняемся от основной темы, для открытия базы данных SQL Server® можно использовать команду, наподобие следующей:

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

До этого момента был показан только стандартный код; как вы скоро увидите, практически все ваши сценарии баз данных будут начинаться с одинаковых строк кода. Необходимо особо подчеркнуть следующую строку:

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

Здесь используется запрос INSERT INTO для вставки новой записи в таблицу Computers. Обратите внимание, что после имени таблицы включены имена всех полей, для которых имеются значения, заключенные в скобки и разделенные запятыми.

Вы можете спросить: что подразумевают программисты под фразой «для всех полей, для которых имеются значения»? Хороший вопрос. Предположим, что для этого нового компьютера должно быть присвоено значение поля Department. Если база данных позволяет разместить в поле Department значение Null, можно просто оставить поле Department и не присваивать ему значение. Другими словами, можно написать следующий запрос:

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

Говоря о присвоении значений, посмотрим, что следует за имена полей: ключевое слово VALUES, за которым следуют значения, которые необходимо назначить для каждого поля (также заключены в скобки). Следует иметь в виду, что при перечислении имен полей они могут располагаться в любом порядке; например, поле ComputerName размещено первым, хотя оно может и не быть первым полем базы данных. Это не имеет значения для имен полей, чего нельзя сказать о значениях. Значения должны быть перечислены точно в таком же порядке, в котором перечислены поля. Если первым полем является ComputerName, первым значением должно быть имя компьютера. В противном случае мы столкнемся с проблемами. (Например, полю ComputerName будет присвоено имя пользователя или серийный номер.)

Как вы видите, присвоение значений не является сложным; необходимо только убедиться в том, что формат значений соответствует типу данных: даты и строковые значения должны быть заключены в одинарные кавычки, а числовые и логические значения не должны заключиться в одинарные кавычки.

Да, кстати, при наличии значения с собственной одинарной кавычкой (например, имя «O'Brien») необходимо отделить этот символ, удвоив его:

'O''Brien'

Странно, но таковы правила.

Удаление записей из базы данных

Это просто здорово: можно добавить запись в базу данных с помощью простого выполнения одного SQL-запроса. Может ли быть что-нибудь лучше? Теперь нам это известно.

Ну, может быть за исключением возможности удаления нескольких записей из базы данных с помощью одного запроса.

Предположим, ваша компания решила ликвидировать отдел кадров. (Никаких шуток; мы и так постоянно беспокоимся из-за отдела кадров Майкрософт.) Как удалить все компьютеры отдела кадров из базы данных инвентаризации? На рис. 2 показан один из способов сделать это.

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

Как вы видите, начало этого сценария полностью совпадает с началом сценария для добавления новой записи: определение нескольких констант, создание пары объектов и подключение к базе данных Inventory.mdb. Затем просто используется запрос DELETE для удаления всех записей (*) из таблицы Computers или хотя бы записей, для поля Department которых установлено значение Human Resources:

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

Это так же просто, как свалиться со скалы.

Хм, Шерлок Холмс, только без обид.

Обновление записей в базе данных

Без сомнения, все согласятся, как приятно удалить все следы отдела кадров. (Примечание для отдела кадров корпорации Майкрософт: мы имеем в виду, приятно для других людей их отделов кадров. Не для нас. Нам бы это совсем не понравилось.) Тем не менее, удаление всех компьютеров отдела кадров из базы данных инвентаризации может быть не лучшим из того, что вы можете сделать. Почему? Если только все сотрудники отдела кадров не скрылись со всем своим оборудованием (звучит так, как будто они могут так сделать — ладно, неважно; мы говорим серьезно) эти компьютеры все еще принадлежат вашей компании; то есть, они должны присутствовать в базе данных. С учетом этого, выполним следующее. Вместо удаления этих записей из базы данных, просто обновим каждую запись; просто изменим название отдела для всех этих компьютеров с Human Resources на None. Другими словами, мы выполним сценарий, показанный на рис. 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

И снова, в сценарии отличается только SQL-запрос. В данном случае используется запрос UPDATE для обновления записей в таблице Computers. Помимо ключевого слова UPDATE необходимо добавить два параметра, сообщающие запросу записи для обновления и способ обновления этих записей:

  • Set Department = 'None'. Указывается новое значение для поля Department. Можно одновременно установить значения для нескольких полей, используя следующий синтаксис: Set Department = 'None', UserName = 'None'. Как вы видите, для полей Department и UserName устанавливаются значения None.
  • Where Department = 'Human Resources'. Это стандартное предложение Where, указывающее записи для обновления; в данном случае это все записи, для поля Department которых установлено значение Human Resources. Оно включено по одной простой причине: необходимо изменить не все записи в базе данных, а только записи (компьютеры), которые ранее принадлежали отделу кадров.

Запросы Update предоставляют замечательную возможность. Предположим, ваша компания решила увеличить зарплату всех сотрудников в связи с повышением прожиточного минимума на 10 процентов. (Программисты корпорации Майкрософт несомненно похожи на сэра Артура Конан Дойля, по крайней мере в одном: мы тоже пишем о вымышленных мирах.) Ниже приведен сценарий, открывающий таблицу с именем Employees и изменяющий поле Salary для всех сотрудников. Какое значение устанавливается для поля Salary? Запрос устанавливает для этого поля значение, равное текущей заработной плате сотрудника, умноженной на 1.1; другими словами, заработная плата всех сотрудников увеличивается на 10 процентов. (Если бы этого было так просто!) Вот запрос:

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

Что в этом такого хорошего? Как вы видите, в запросах Update можно выполнять вычисления. Вы решили продлить срок контракта всех временных сотрудников еще на 60 дней? В этом случае может быть удобным запрос, подобный следующему:

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

Отличные способы получения данных

Говоря о полезных запросах, в заключение рассмотрим несколько отличных способов получения данных из базы данных. Например, вот простой и довольно полезный сценарий. Предположим, база инвентаризации включает поле с именем Price, которое, что довольно логично, представляет цену компьютера. Хотите знать пять самых дорогих компьютеров в вашей организации? Пример сценария на рис. 4 показывает эти данные.

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

Как вы видите, в данном SQL-запросе мы сделали две вещи: отсортировали набор записей по полю Price (Order By Price) и запросили только первые пять компьютеров (т.е., пять самых дорогих); это выполняется с помощью SELECT Top 5. Если бы нам был необходим список из 10 самых дорогих компьютеров, мы бы использовали следующий запрос:

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

Примечание. Мы также добавили код, выполняющий просмотр набора записей и отображающий имена всех компьютеров. В предыдущих сценариях этот код не требовался, поскольку возврат и отображение данных не осуществлялись.

Кроме того, первые 10 процентов можно получить с помощью следующего запроса:

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

Но что, если необходимо узнать самые дешевые компьютеры? Никаких проблем; воспользуемся этим же методом, только в этот раз выполним сортировку набора записей в порядке убывания (т.е., от наименьшей цены до наибольшей). Другими словами, мы используем этот запрос вместе с ключевым словом DESC, означающим, что набор записей будет отсортирован в порядке убывания:

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

Видите? Мы же говорили, что это очень полезно.

Вот еще два сценария, которые могут быть для вас интересными. Предположим, в базе данных имеется поле с именем Budgeted, отслеживающее денежную сумму, изначально выделенную для компьютера. Хотите сравнить фактически расходы с расходами по смете? Следующий запрос возвращает список компьютеров, стоимость которых превышает сумму, указанную в смете:

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

Следующий запрос вычисляет среднюю стоимость всех компьютеров (SELECT AVG(Price) FROM Computers) и возвращает список компьютеров, стоимость которых ниже средней:

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

Да, вы правы, мы использовали запрос SELECT в предложении Where. Как это работает? А это уже другая история для следующего раза. Но, как вы видите, он позволяет получать все типы полезных данных с помощью SQL-запросов.

Мораль истории

Как было отмечено в начале статьи, мы не были уверены, что когда-нибудь снова захотим написать статью о создании сценариев баз данных. Однако мы рады, что написали ее, отчасти потому, что некоторых из рассмотренных запросов будут для вас полезными, но также и из-за следующего: если бы мы не написали эту статью сейчас, рано или поздно на бы пришлось это сделать. В конце концов, даже сэр Артур Конан Дойль был вынужден воскресить Шерлока Холмса. Сэр Артур предпринял первые пробные шаги в этом направлении, написав новую книгу о Шерлоке Холмсе, действие которой происходило до смерти детектива. Хотя потом он уступил давлению со стороны своих читателей и состряпал довольно-таки сомнительную историю инсценировки Шерлоком Холмсом собственной смерти. Оказалось, что все, и поэтому все остальное, были в полном порядке.

Самое интересное, после выхода последней книги о Шерлоке Холмсе число подписчиков журнала The Strand Magazine увеличилось на 30 000. В ответ на это благодарные издатели сэра Артура в Великобритании и США сделали его самым высокооплачиваемым писателем в мире.

Хм...сэр Артур Конан Дойль вернул всеми любимого героя и стал самым высокооплачиваемым писателем в мире; теперь и программисты корпорации Майкрософт вернулись к популярной теме – не пришлось даже инсценировать чью-либо смерть. Хотите знать, заметили ли читатели журнала TechNet Magazine?

Головоломка-сценарий доктора Скрипто.

Задача на этот месяц потребует не только умения решать головоломки, но и навыков создания сценариев.

Апрель 2008 г.: выберите букву

В головоломке за этот месяц необходимо вставить буквы A – O в синие клетки, чтобы получить имя функции VBScript. Каждая буква (A – O) используется только один раз, буквы располагаются не в алфавитном порядке. Вставленная буква может быть началом имени функции, концом имени функции или находится в середине имени. Например, в следующую строку необходимо вставить букву D, чтобы получилось имя функции IsDate:

  (Щелкните изображение, чтобы увеличить его)

Теперь попробуйте; вставьте буквы от A до O, чтобы получить имя функции VBScript в следующей таблице:

**** (Щелкните изображение, чтобы увеличить его)

ANSWER:

Головоломка-сценарий доктора Скрипто.

Ответ: выберите букву, апрель 2008 г.

  (Щелкните изображение, чтобы увеличить его)

Программисты корпорации Майкрософт работают... ну хорошо, получают зарплату в корпорации Майкрософт. Когда они не играют в бейсбол, не тренируют бейсбольную команду, не смотрят бейсбол и не занимаются другими делами, они ведут колонку в Центре сценариев TechNet. Веб-узел проекта находится по адресу www.scriptingguys.com.

© 2008 Корпорация Майкрософт и компания CMP Media, LLC. Все права защищены; полное или частичное воспроизведение без разрешения запрещено.