Библиотека: А. Горев, С. Макашарипов, Р. Ахаян. Эффективная работа с СУБД

7.1. Организация ввода данных, их поиска и редактирования

Работа с данными в Visual FoxPro

Работа с данными в Microsoft Access

7.2. Создание SQL-запросов

Запросы выборки

Запросы добавления

Запросы обновления

Запросы удаления

7.3. Изменение структуры данных с помощью SQL

7.4. Запросы и локальные представления в Microsoft Visual FoxPro

7.5. Запросы в Microsoft Access

Запрос добавления

Запрос - Создание таблицы

Запрос удаления

Запрос обновления

Перекрестный запрос

7.6. Работа с данными в локальной сети

Visual FoxPro

Несколько советов по увеличению производительности при работе в сети в приложениях MicroxPro

Microsoft Access

    Постоянно сталкиваясь с громадным потоком информации, которая обрушивается на нас, не давая передышки ни дома, ни на работе, мы привыкли к невзгодам информационного взрыва. Кажется, что тут сложного, вот она, информация! Поглощай ее глазами с экрана телевизора, лови из радиоприемника, читай, записывай, запоминай. Громадные массивы данных стали заполонять все возможные устройства, которые способны хоть в каком-то виде хранить данные. Почему так быстро растет значение информации? Да потому, что без нее не обойтись не только при управлении государством, маленькой фирмой, без информации трудно надеяться на успех даже не очень дорогой покупки.
    Информация нужна всем, но больше всего нужна обработанная информация. Согласитесь, что вам гораздо больше пригодился бы список наиболее низких цен на телевизоры в ближайших к вам магазинах, чем толстый справочник всех магазинов вашего города. Если вы согласны, то перейдем к делу, поговорим об обработке данных.

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

• о наиболее эффективных методах поиска данных;
• о способах модернизации данных в отдельных полях и группах записей;
• о том, каким образом в поиске данных могут помочь фильтры;
• и как использовать для работы с данными объекты DAO.

    Данные, для того чтобы отредактировать, необходимо найти. Самый простой способ - вывести данные в табличной форме, например, в окно Browse или воспользоваться объектом Grid. После этого, воспользовавшись клавишами навигации, найти нужную запись и изменить ее. Этот способ вполне пригоден для небольших таблиц, имеющих количество записей, измеряемое двузначным числом. Когда их количество составляет тысячи или даже миллионы, такой поиск затрудняется. В этом случае рекомендуется использовать команды и функции поиска. Одни из них работают без индексов, другие обязательно требуют их создания и подключения.
    Одна из наиболее универсальных команд - LOCATE - работает без индексов. В то же время наличие индекса по выражению, по которому проводится поиск, без его подключения, значительно повышает скорость поиска. При наличии неподключенного индекса Visual FoxPro использует технологию Rushmore. Эта технология представляет собой специальный метод, существенно ускоряющий процесс поиска нужных данных. В то же время необходимо учитывать, что наличие большого количества индексов требует времени на их обновление.
    Для проверки скорости поиска можно использовать следующую процедуру:

* В таблице Customer существует индекс по полю

* key_customer.

* Отключая на всякий случай индексы, мы используем

* технологию Rushmore, что дает нам ускорение поиска

* более чем в 150 раз при работе с таблицей, имеющей

* больше 100000 записей.

     SELECT Customer

     SET ORDER TO

     nPeriod = SECONDS()

     LOCATE FOR key_customer = 45004

     nPeriod_1 = SECONDS()

     ? nPeriod_1 - nPeriod

    Еще более солидный выигрыш в скорости вы можете получить при работе с символьными полями.
    Команда LOCATE имеет следующий синтаксис:

LOCATE FOR lExpression1

     [Scope]

     [WHILE lExpression2]

     [NOOPTIMIZE]

    Команда LOCATE часто используется в связке с командой CONTINUE, которая позволяет продолжить поиск по установленному критерию поиска до тех пор, пока не будут найдены все записи.
    Другой способ поиска всех нужных данных - использование фильтров, которые устанавливаются с помощью команды SET FILTER. Установка фильтров также дает выигрыш в скорости при наличии индекса по выражению поиска.
    Команда SEEK позволяет проводить поиск по выражению, для которого имеется индекс и который подключен в момент поиска. Это самый быстрый метод поиска, но требующий постоянного переключения индексов в случае, если необходимо производить поиск по разным выражениям. При использовании этой команды для поиска нужных данных нет необходимости указывать поле, по которому проводится поиск. Таким полем автоматически объявляется активный в данный момент индекс.

SELECT Customer

SET ORDER TO key_customer

t=SECONDS()

SEEK 45004

t1=SECONDS()

? t1-t

    Если вам необходимо найти значение какого-то поля и вам известно значение одного из полей в соответствующей записи, то используйте функцию LOOKUP()

LOOKUP(ReturnField, eSearchExpression, SearchedField

[, cTagName])

    Аргументы в этой функции имеют следующее назначение:

• ReturnField - поле, значение которого возвратит функция LOOKUP();
• eSearchExpression - значение, по которому производится поиск;
• SearchedField - поле, в котором производится поиск;
• cTagName - имя тега, если есть возможность использовать подключенный индекс.

    По скорости поиска функция LOOKUP() вполне сравнима с командой SEEK. Она также дает выигрыш в скорости при наличии индекса за счет использования технологии Rushmore. После успешного поиска указатель записи переводится в ту запись, в которой найдено искомое значение.
    Пример использования функции LOOKUP():

CLEAR

t=SECONDS()

SET ORDER TO

mlook=LOOKUP(lastname,45004,Key_customer)

t1=SECONDS()

? t1-t

? Mlook

    Когда вам необходимо отредактировать одну запись, что, как правило, является обычным условием работы, используйте прямое редактирование полей с помощью форм или окон Browse. Если вы хотите, чтобы данные в полях записей редактировались без участия пользователя, то используйте команду REPLACE.
    Иной раз возникают задачи глобального изменения значений какого-нибудь из полей. К примеру, раньше все были гражданами СССР, а теперь стали гражданами России.
    Чтобы одновременно поменять все значения в поле, часто применяется следующая команда:

REPLACE ALL citizenship WITH "Россия"

    Тем, кто знаком с SQL, возможно, больше по душе следующая конструкция:

UPDATE kadry SET citizenship = "Россия"

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

SELE kadry

SET ORDER TO rabId

     FOR I=1 TO 100000

     SEEK I

     REPLACE citizenship WITH "Россия"

NEXT

    Данная программа выполнит замену в три раза быстрее, чем программа, использующая команду REPLACE с опцией ALL. Единственный ее недостаток - это необходимость строгого соблюдения правил присваивания идентификатора каждой записи. В противном случае надо обрабатывать еще и ошибку, которая может возникнуть, если не будет найдена подходящая запись. Если глобальный поиск выполняется часто, можем посоветовать создать индекс по выражению RECNO(), то есть по функции, которая возвращает номер записи по порядку.
    При обсуждении с заказчиком проекта приложения, структуры базы данных и каждой отдельной таблицы необходимо постоянно думать о предстоящем поиске данных. Учитывая важность этой проблемы при поиске данных, мы сделаем вид, что ничего не писали в первых двух главах и рассмотрим пример влияния структуры таблицы на эффективность поиска данных. Естественно, вы выносите дублирующуюся информацию в отдельные таблицы. Вряд ли имеет смысл хранить технические характеристики автомобиля в таблице счетов. Вы выносите их в отдельную таблицу и при этом каждой записи присваиваете уникальный идентификатор. Если вам надо изменить какую-нибудь из характеристик автомобиля, придется менять данные всего в одной записи одного поля.

    В приведенной выше таблице сразу видна избыточность данных. Информация в полях swept_volume, quantity_drum, capacity явно дублируется; представьте, сколько записей придется вам редактировать, если характеристики одного из автомобилей надо изменить хотя бы потому, что перед этим они были введены с ошибкой. Разумнее разделить эту таблицу на две, в одной хранить информацию о автомобиле, характерную для каждого отдельного экземпляра, а в другой - общие характеристики автомобилей. Одна таблица Automobil_passenger_car будет выглядеть примерно так:

    Вторая таблица - MODEL - вот так:

    Для связи таблицы используется поле key_model. С помощью связующего поля мы легко можем получить любую информацию, которая хранится в этих таблицах.
    Вам может понадобиться изменить идентификатор одного из автомобилей в таблице Model. Как сделать, чтобы записи не остались одинокими в первой таблице - Automobil_passenger_car? Можно провести замену с помощью команды REPLACE, то есть найти записи с этим идентификатором и заменить на новое значение. Но лучше использовать ссылочную целостность и написать триггер для выполнения изменений данных в таблице Model. В нем следует написать код, который будет искать все соответствующие записи в таблице Automobil_passenger_car. Теперь у пользователя будет создаваться впечатление, что умный компьютер сам знает, что нужно поменять идентификатор автомобиля во всех таблицах, в которых он присутствует. Если вам не хочется писать триггеры самостоятельно, то в некоторых случаях Visual FoxPro сможет вам помочь, если вы воспользуетесь диалоговым окном Referential Integrity, которое можно вызвать из меню Database.

    Для ввода и редактирования данных в Microsoft Access используются формы, которые могут иметь несколько режимов, таблицы и запросы. При открытии формы, по умолчанию, если вы не подключили свое меню, становится активным меню Форма, в котором имеются команды для поиска, сортировки и фильтрации данных. Аналогичные команды присутствуют в меню, связанном с таблицами и запросами. Как правило, при простом редактировании данных в таблицах этих средств более чем достаточно.
    Среди средств поиска наиболее простым является использование диалогового окна Поиск, с помощью которого вы можете последовательно находить нужные вам записи. Это диалоговое окно позволяет искать данные как в текущем поле, так и в других полях используемого источника данных. Вы можете последовательно находить несколько записей, удовлетворяющих выражению или его части, введенному в строке образца. Поиск можно проводить как вверх по таблице от текущей записи, так и вниз, либо по всей таблице. Но совершенно очевидно, что таким образом поиск можно производить только по одному полю, либо при случайном стечении обстоятельств по нескольким, к примеру, слово "Петрович" может быть и отчеством и фамилией.
    В форме вы можете с помощью Мастера кнопок построить кнопку, которая будет служить для поиска записей. После того как вы создадите подобную кнопку с характерным рисунком бинокля или надписью "Поиск записи", откройте окно кода для события Click. Скорее всего там будут следующие строчки:

Sub Кнопка40_Click()

On Error GoTo Err_Кнопка40_Click

     Screen.PreviousControl.SetFocus

     DoCmd.DoMenuItem acFormBar, acEditMenu, 10, ,_ acMenuVer70

Exit_Кнопка40_Click:

     Exit Sub

Err_Кнопка40_Click:

     MsgBox Err.Description

     Resume Exit_Кнопка40_Click

End Sub

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

• Строка меню - Форма
• Название меню - Правка
• Команда - Найти

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

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Выполнить этот макрос, когда не активна строка меню Форма, невозможно, впрочем, как невозможно воспользоваться и методом объекта DoCmd DoMenuItem из процедуры Мастера. Мы все время зависим от наличия на экране меню Форма. Единственное преимущество кода события по сравнению с нашим макросом заключается в том, что первый обработает ошибку, когда выяснит, что не может выполниться. Будет выдано сообщение, связанное с этой ошибкой, а наш макрос просто "повиснет", выведя на экран окно обработчика ошибок макросов. Впрочем, даже если меню присутствует на экране, то совсем не обязательно, что будет найдена запись, соответствующая введенному образцу.
    Если нужная запись не будет найдена, на экран будет выведено стандартное сообщение Access: "Поиск записей в приложении `Microsoft Access' завершен. Элемент не найден"
    В качестве примера организации поиска данных рассмотрим создание своей собственной формы, которая будет возвращать то сообщение, которое мы хотим получить в случае неудачного поиска.
    Пример формы "ПОИСК" вы найдете в файле базы данных AUTOSTORE.MDB, который находится на дискете, прилагаемой к данной книге. Форма "AUTOMOBIL_PASSENGER_CAR" имеет кнопку для поиска данных в текущем текстовом поле. При ее нажатии вызывается форма "ПОИСК".
    В модуле Bookmodule объявляются две переменные: LastForm типа Form и lastcntr типа Control:

Dim LastForm As Form

Dim lastcntr As Control

    Первая служит для передачи переменной формы, а вторая для передачи переменной последнего активного элемента управления для использования их в форме "ПОИСК".
    В событии Click кнопки Поиск формы "AUTOMOBIL_PASSENGER_CAR" записываем следующий код:

Sub Кнопка40_Click()

     Set LastForm = Screen.ActiveForm

     Set lastcntr = Screen.PreviousControl

     mybook=LastForm.Bookmark

     PoiskSub

End Sub

    Процедура PoiskSub служит для установки флажка "В текущем поле". Дело в том, что последний элемент управления, который был активным до нажатия на кнопку Поиск, может быть не связанным ни с каким полем - для него будет отсутствовать понятие "текущее поле". Следовательно, флажок нужно сделать недоступным, так как его значение используется для дальнейшего поиска:

Public Sub PoiskSub()

     DoCmd.OpenForm "Поиск"

     Select Case lastcntr.ControlType

Case 109, 106, 110, 111

If Not IsNull(lastcntr.ControlSource) Then

     Forms![Поиск].[Flag3] = True

Else

     Forms![Поиск].[Flag2] = False

     Forms![Поиск].[Flag3] = False

End If

Case Else

     Forms![Поиск].[Flag2] = False

     Forms![Поиск].[Flag3] = False

     End Select

End Sub

    Таким образом мы передали в процедуру обработки события Click последний активный элемент и последнюю активную форму как параметры и при этом выяснили, был ли последний активный элемент управления связанным или нет.
    Для поиска мы используем методы из серии Find объекта RecordSet и метод FindRecord объекта DoCmd.
    Набор данных для формы мы создаем с помощью свойства RecordSource. Это может быть таблица, запрос или выражение SQL. С помощью свойства RecordsetClone мы получаем ссылку на объект Recordset, указанный в свойстве RecordSource. Если источник данных для формы "AUTOMOBILE PASSENGER CAR" - таблица с тем же именем, то с помощью свойства RecordsetClone формы мы ссылаемся на объект, который создается с помощью следующей конструкции:

Dim myDb As DataBase, rst As Recordset

     Set Mydb = dbEngine.Workspaces(0).Databases(0)

Set rst = MyDb.OpenRecordset("AUTOMOBIL PASSENGER CAR", _ dbOpenDynaset)

    Учтите, что объект Recordset функционирует сам по себе, то есть перемещение с помощью методов серии Move и Find не влияет на текущую отображаемую в форме запись. С целью синхронизации перемещения необходимо использовать свойство Bookmark, которое определяет текущую запись. Для этого свойство Bookmark формы сохраняют в переменной строкового типа, а потом присваивают это значение свойству Bookmark объекта Recordset, на который ссылаются с помощью свойства RecordsetClone формы.
    В коде кнопки Поиск формы "AUTOMOBIL PASSENGER CAR" имеется следующая строчка:

mybook=LastForm.Bookmark

    В коде кнопки Найти для формы "ПОИСК" свойство Bookmark устанавливается следующим образом:

LastForm.RecordsetClone.Bookmark=mybook

    После поиска, если он удачен, значение свойства Bookmark объекта Recordset присваивается свойству Bookmark формы.
    Как вы помните, весь этот процесс мы затеяли для того, чтобы выводить свое собственное сообщение при неудачном поиске. При этом мы рассмотрели вопросы взаимодействия формы с объектами доступа к данным (DAO).
    Как правило, скорость работы в Access понижается с увеличением числа записей в таблице, что, впрочем, происходит и во многих других приложениях. Для того чтобы снизить негативные последствия этого явления, в Access предлагается использовать развитую систему фильтров. Фильтры можно устанавливать при работе с формой с помощью меню, макрокоманд или методов объекта DoCmd в программном коде.
    Например, используя опцию "Фильтр по выделенному", вы можете, последовательно выделяя поля, устанавливать достаточно сложные фильтры. Последовательность действий выглядит следующим образом:

1. Открытие формы.
2. Выделение любого поля или части поля.
3. Выполнение команды Фильтр по выделенному из меню Записи или нажатие кнопки с тем же названием на панели инструментов Режим Формы.
4. Повторение вышеприведенной последовательности действий с другими полями.

    Можно открыть форму в режиме "Форма с установленным фильтром". Это делается с помощью макрокоманды OpenForm или с помощью метода OpenForm объекта DoCmd:

DoCmd.OpenForm "automobile passenger car",,,"key_model=1"

    Кроме того, вы можете установить источником данных для формы параметрический запрос, который позволяет значительно уменьшить количество записей, обрабатываемых в текущем сеансе работы формы.
    При работе с объектами DAO можно динамически изменять размер выборки, тем самым значительно ускоряя доступ к нужным вам данным. В качестве примера приведем две процедуры. Одна ищет запись в неотфильтрованном наборе данных, другая использует фильтр.
    Процедура поиска в неотфильтрованном наборе данных:

Public Sub findexp()

*** В данной процедуре ищется запись, и в окне отладки

*** выводится время поиска в секундах

*** Вы можете подставить свою таблицу

*** и свои поля

*** При этом имеет смысл экспериментировать

*** на таблице с количеством записей более 1000,

*** либо использовать WINAPI для фиксирования времени

*** поиска

     Dim myDb As DATABASE

     Dim myRst As Recordset

     Dim t1 As Double

     Dim t2 As Double

     Set myDb = DBEngine.Workspaces(0).Databases(0)

     *** Можете подставить имя вашей таблицы здесь

     Set myRst = myDb.OpenRecordset("first", _ dbOpenDynaset)

     t1 = Now()

     *** Соответственно поле и критерий поиска

     *** тоже необходимо подставить свои

     myRst.FindFirst "first like 'Чф*'"

     t2 = Now()

     If Not myRst.NoMatch Then

     Debug.Print myRst!First, myRst!Third

     Else

     Debug.Print "Пролет"

     End If

     *** Здесь можете использовать свою

     *** функцию для форматирования вывода времени поиска

     Debug.Print DateDiff("s", t1, t2)

End Sub

    Поиск в отфильтрованном наборе данных:

Public Sub findexpFilt()

Dim myDb As DATABASE

     Dim myRst As Recordset, NmyRst As Recordset

     Dim t1 As Double

     Dim t2 As Double

     Dim i As Long

     Set myDb = DBEngine.Workspaces(0).Databases(0)

     Set myRst = myDb.OpenRecordset("first",_ dbOpenDynaset)

     myRst.Filter = "third >> 56700 and third << 58000"

     Set NmyRst = myRst.OpenRecordset()

     t1 = Now()

     NmyRst.FindFirst "first like 'Чф*'"

     t2 = Now()

     If Not NmyRst.NoMatch Then

     Debug.Print NmyRst!First, NmyRst!Third

     Else

     Debug.Print "Пролет"

     End If

     Debug.Print DateDiff("s", t1, t2)

End Sub

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

• сферы наиболее эффективного применения языка SQL при разработке систем обработки данных;
• основные виды запросов, которые могут быть составлены для работы с данными;
• назначение основных составляющих элементов команд SQL;
• особенности составления команд SQL в рассматриваемых средствах разработки.

    К сожалению (а может быть наоборот), рынок программного обеспечения развивается не по принципам единого планирования. Каждый продукт, который использует SQL, применяет его диалект, как правило, отличающийся от ANSI-стандарта этого языка. Обидно, - иначе бы мы могли одинаково обращаться к данным любого продукта, используя одинаковые языковые конструкции. Но в принципе, все еще может быть, а пока приходится довольствоваться тем, что есть. А есть масса приложений для разработки баз данных, которые при этом данные хранят в своем формате, как правило, секрета не представляющем, и все без исключения соревнуются друг с другом в как можно большем количестве отличий от общепринятых стандартов ANSI SQL. Однако общаться программам с данными чужих форматов необходимо, и существет несколько путей для этого общения. Перечислим некоторые из них.
    Вы покупаете продукт, который поддерживает несколько форматов. Или - создаете продукт, который поддерживает несколько форматов. Есть прекрасные примеры: Lotus Approach, Microsoft Access, продукты фирмы Borland Dbase и Paradox читают форматы друг друга. Но почти наверняка вы не найдете приложения, которое поддерживает все форматы. А на практике работать с данными иного формата приходится очень часто. Даже если вы очень упорно будете избегать встречи с другими форматами, она все равно когда-нибудь произойдет. Бесспорно, можно воспользоваться следующим способом для работы с внешними форматами, но он таит свои сюрпризы.
    Вы используете операции экспорта или импорта. Опять же необязательно, что у вас будут в наличии все необходимые конверторы. Но все же допустим, что они есть. Вы импортировали данные. Отредактировали. Сколько вы поставите на то, что изменения отразились в исходных данных, то есть в файле, где они хранятся в родном формате? Готовы с вами поспорить на любую сумму. Можно, конечно, провести обратную операцию и экспортировать отредактированные данные в исходный формат. Но, согласитесь, что это очень непродуктивный путь для редактирования одной записи в базе данных, где их 100000, а ведь очень часто их бывает много больше. Кроме этого, вам становятся недоступными все триггеры, бизнес-правила и хранимые процедуры для данных внешнего формата, если они, конечно, используются.
    Способ, который звучит наиболее современно и который делает вас поистине всесильным, но оставляющий все же место для всевозможных придирок по отношению к себе. Это, само собой разумеется, - OLE 2.0. А в OLE 2.0 нас больше всего интересует его составная часть OLE Automation. Термин, который редко переводится, и мы тоже не возьмемся за это. Придирки здесь могут возникнуть со стороны консервативных любителей DOS. Дело в том, что технологию OLE Automation невозможно реализовать в рамках этой операционной оболочки. Позволим себе лирическое отступление. Вам наверняка придется слышать патетические высказывания, изрекаемые достаточно образованными людьми. Они будут гласить, что на их программах работают сотрудники, которым надо только стучать по клавиатуре. Или что наращивание мощности техники для обработки информации - это от лукавого. Не слушайте их. Нам нужны различные, мощные, еще мощнее, самые мощные средства обработки данных. При этом неважно, где вы работаете. Это может быть ЖЭК, банк, спортклуб. Каждый грамм информации имеет право на существование, даже количество бензина, которое вы сегодня израсходовали, отлучившись на служебной машине к вашей любовнице. Не слушайте их, приземленных противников прогресса, которым легче обслуживать свои любительские творения, чем осваивать новые вершины самой нужной профессии современности - специалиста по обработке информации. Бесспорно, на смену OLE придет нечто более совершенное, но главное, что это не будет шагом назад. OLE Automation - способ управлять и считывать информацию об объектах внешнего по отношению к вашему приложения. Впрочем, разговор об этом чуть ниже.
    Следующий способ имеет меньше ограничений. Мы говорим об ODBC (Open Database Connectivity), что можно перевести как Открытый Доступ к Базам Данных. Конечно, ваше приложение должно поддерживать эту технологию. Установив связь с исходной базой данных, далее вы используете набор SQL pass-through функций для выборки, обработки и модификации исходных данных. Наборы этих функций для разных продуктов неодинаковы, но в целом можно говорить о наборе различных реализаций SQL pass-through функций. Изучив их реализацию в одном языке, вы легко можете воспользоваться аналогичной в другом. Теперь вы очень близки к тому, что во внешней базе данных будут отражаться любые изменения, сделанные в таблицах при редактировании данных. Во всяком случае, необходимый инструмент у вас есть. Настоятельно рекомендуем попробовать. Далее мы обязательно обсудим эту технологию более подробно. Скажем прямо, она-то и является краеугольным камнем всей этой книги. В противном случае это была бы уже совсем другая книга.
    Теперь поговорим о реализации SQL языка в каждом приложении. Для общения с данными внешнего формата на его родном языке была изобретена технология ODBC. Впрочем, то же самое мы можем сказать и об OLE 2.0. Запутанно? Осталось только набраться терпения. А дальше мы будем говорить о SQL языке.
    В наше время только ленивый не пытается программировать. Тем более, что основные операторы любого из языков достаточно просты, что вполне логично. Быстренько набросали схему данных, загнали все в простую плоскую таблицу, запустили Мастер и пошли искать заказчиков. Дальше может быть хуже, а может быть лучше. Поток информации разрастается, одной таблицы мало (при этом кто-то должен подсказать, что данные из разных таблиц можно связывать), и, когда звучит простой вопрос вашего начальника "А сколько африканского кофе мы продали в Тьмутараканскую область по цене между 6000 и 7000 рублей?", - вы берете листок бумаги, счеты (как наиболее надежное средство учета) и ручку. Правильно ли это? Каждая точка зрения имеет право на существование. В том числе и вашего начальника - в вас начинают сомневаться. Но не пугайтесь, у вас в руках наша книга. Почти 90% дела вы сделали, осталось только проявить чуточку усердия и терпеливо дочитать до конца.
    Вспомним, что, несмотря на наличие стандарта SQL, существуют различные реализации или диалекты этого языка, которые являются либо вспомогательными инструментами, либо основой различных систем управления базами данных. Есть и более сложные варианты, когда некоторые СУБД имеют специальные библиотеки для доступа к своим данным для таких языков, как C или Visual Basic с помощью SQL команд приложения. В большинстве случаев к данным любого приложения надо обращаться на его родном SQL диалекте. То есть, находясь в среде FoxPro, вам будет сложно сделать запрос SQL к таблице формата Paradox, не используя ODBC и SQL pass-through. Хотя есть приятные исключения, то есть приложения, которые незаметно для вас проводят всю тяжелую работу по представлению чужих данных в нужном для запроса формате.
    В самом простом варианте команда, которая выбирает все записи из одной таблицы, выглядит так:

SELECT mytable.* FROM mytable

    Впрочем, можно еще короче:

SELECT * FROM mytable

    Таким образом, выбраны все записи из таблицы Mytable.
    В зависимости от того, какое приложение вы выбрали для изучения SQL, вас ожидают совершенно разные пути ввода данной команды в память вашего компьютера.
    Рассмотрим более сложные варианты выбора нужных данных.

    Начнем с Visual FoxPro. Здесь у нас есть целых три варианта выполнения команд SQL:

1. Набрать команду в окне Command.
2. Создать программу, в которую включить нужную команду. Этот способ более эффективен, так как у вас есть возможность сохранить возможно высшей степени изощренную команду для дальнейшего использования.
3. Способ, наиболее привлекательный для начинающих программистов, - использовать Relation Query By Example (RQBE) - Реляционный запрос по образцу.

    Для того чтобы использовать RQBE, вам достаточно выполнить команду New из меню File, а затем выбрать тип создаваемого файла - Query. RQBE - это интерактивная среда, в которой вы формируете запрос в основном с помощью мыши, перетаскивая нужные вам поля в определенные области диалогового окна.
    Результатом будет запрос, оформленный в виде файла с расширением QPR. При этом выполнить запрос вы сможете с помощью команды DO. Если вы изучаете SQL для использования в FoxPro, то пользуйтесь любым из вышеизложенных способов, но если вам нравится RQBE, то все равно заглядывайте в полученный код, потому что это поможет вам лучше понять язык. Разбирайте каждое слово в полученной команде, и успех в недалеком будущем вас будет ожидать всенепременно.
    В среде СУБД Microsoft Access нет командной строки в том понятии, в котором она существует в Visual FoxPro. Для того чтобы создать запрос, в контейнере БД выберите страницу Запросы, нажмите на кнопку Создать. На экране появится Конструктор запросов. При этом вы легко будете переходить из Конструктора запросов в окно редактирования SQL или к результатам выполнения запроса.
    Среди рассматриваемых средств разработки СУБД Microsoft Access имеет наиболее мощные средства визуального конструирования запросов. Здесь вы можете создать не только запрос на выборку, но и запросы обновления, удаления, создания таблиц, добавления. Об этих запросах мы поговорим ниже.
    Visual Basic использует для работы с данными процессор баз данных Microsoft Jet, такой же, как и Access. Но в связи с тем, что Visual Basic является универсальным средством разработки, здесь не присутствуют такие развитые средства визуального проектирования, как в Microsoft Access. В то же время, так как все объекты DAO - объекты по доступу к данных - доступны и из Visual Basic, то мы вполне можем добиться функциональности, которой добиваемся в Access. Другое дело, какой ценой мы этого достигнем.
    Напомним, что в Microsoft SQL Server для того, чтобы выполнить команду SQL, необходимо запустить приложение iSQLW. Вы можете набрать требуемую команду и запустить ее на выполнение. При этом на вкладке Query Results вы увидите полученный результат.
    Зная SQL, вы можете значительно увеличить ваши возможности при работе с электронными таблицами Microsoft Excel. Несмотря на то, что вы можете непосредственно читать некоторые форматы данных, иногда полезно немного сократить в объеме количество записей перед обработкой в Excel. В этом вам может помочь приложение Microsoft Query, которое можно использовать для построения запросов к данным внешнего и внутреннего формата Excel с целью уменьшения объема данных, необходимых для текущего сеанса работы.
    Итак, везде присутствует SQL. Где-то как основное средство работы, где-то как вспомогательное.
    Мы думаем, что теперь вы вполне готовы к разговору о более сложных запросах. Посмотрим еще раз на команду SQL, о которой мы уже говорили:

SELECT * FROM mytable

    Заменим абстрактную таблицу Mytable таблицей из примера к этой книге. Одна из таблиц называется Model, в ней хранятся данные о всех моделях автомобилей, которые задействованы в реселлерской деятельности компании. К примеру, нам не нужны для дальнейшей обработки все поля из этой таблицы. Чтобы выбрать нужные, мы должны их просто перечислить:

SELECT name_model, swept_volume, quantity_drum FROM model

    Может случиться, что вам захочется иметь более наглядные заголовки вместо часто абстрактных наименований английскими буквами или даже на английском языке. Тогда мы используем ключевое слово AS после названия колонки. Перепишем наш запрос в другом виде:

SELECT name_model AS наименование, ;

swept_volume AS рабочий_объем, ;

quantity_drum AS кол_цилиндров ;

FROM model

    Этот синтаксис верен для Visual FoxPro и Microsoft Access. При работе с Microsoft SQL Server подобная задача выполняется немного по-другому:

SELECT 'наименование'= name_model, :

     'рабочий_объем'= swept_volume,;

     'кол_цилиндров'= quantity_drum ;

     FROM model

    Итоговый запрос может содержать дублирующие друг друга по всем полям записи. Если вам не нужна избыточная информация, то введите после SELECT ключевое слово DISTINCT:

SELECT DISTINCT 'наименование'= name_model, :

     'рабочий_объем'= swept_volume,;

     'кол_цилиндров'= quantity_drum ;

     FROM model

    Противоположным по смыслу DISTINCT ключевым словом является ALL, как правило, оно является значением по умолчанию и его можно не приводить. Но это уже зависит от вашего стиля программирования. Иногда считается правильным указывать все значения и ключевые слова для лучшей "читабельности" кода.
    Порядок, в котором поля выводятся в итоге, зависит только от того, в какой последовательности вы перечислите их в своем запросе. Если необходимо во второй колонке вывести количество цилиндров, то следует изменить порядок указания полей в списке выводимых результатов:

SELECT name_model, quantity_drum ,swept_volume FROM model

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

SELECT IIF(MOD(RECNO(),3)=0,"Y","N"),RECNO(),cost;

FROM "automobile passenger car"

    При этом в каждой третьей записи в результирующем курсоре в первой колонке появится буква "Y".
    В запросе можно использовать пользовательскую функцию и выводить ее результаты в курсор. Правда, здесь накладывается некоторое ограничение: желательно, чтобы функция не перемещала указатель записи в таблице, из которой выбираются данные.
    До сих пор мы рассматривали организацию запросов к одной таблице. В реальной жизни это довольно редкий случай. Как правило, информацию нужно получать их двух и более таблиц. При этом нужно учитывать факт, что таблицы должны быть связаны между собой, при этом необязательно каждая с каждой, можно связать две таблицы через одного или нескольких посредников.
    Если вы хотите узнать, в какой стране находится штаб-квартира фирмы, автомобиль которой приобрел каждый покупатель, то нужно создать вот такой запрос:

SELECT Customer.name_customer, Country.country_name, ;

     Model.name_model,;

     Firm.name_firm;

     FROM "auto store!customer", "auto store!account",;

     "auto store!automobile passenger car" Automobile_passenger_car,;

     "auto store!model", "auto store!firm",; "auto store!country";

WHERE Customer.key_customer = Account.key_customer;

     AND Automobile_passenger_car.key_auto = Account.key_auto;

AND Model.key_model = ;

Automobile_passenger_car.key_model;

AND Firm.key_firm = Model.key_firm;

AND Country.key_country = Firm.key_country

    В итоге получится следующая выборка:

Automobile_passenger_car.key_auto = Account.key_auto

    ограничит нашу выборку только теми записями, которые имеют одинаковые значения и в таблице Automobile_passenger_car, и в таблице Account по полю key_auto. Проведя соответствующее исследование для других связующих пар, мы поймем, каким образом ограничивается набор выбранных данных. Вышеприведенный синтаксис правилен для любого диалекта SQL, используемого в рассматриваемых средствах разработки.
    Рассмотрим более простой пример. Нам хочется узнать для автомобиля название его фирмы-производителя. Запрос будет выглядеть так:

SELECT model.name_model,firm.name_firm ;

FROM model,firm ;

WHERE model.key_firm = firm.key_firm

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

SELECT DISTINCTROW firm.name_firm, model.name_model

     FROM firm INNER JOIN model ON firm.key_firm = model.key_firm;

    В предложении FROM появилась конструкция:

firm INNER JOIN model ON firm.key_firm = model.key_firm

    что в дословном переводе означает: внутренняя связь таблицы Firm с таблицей Model по полю key_firm. Почему нельзя просто все записать в предикат, составленный с помощью предложения WHERE? Можно. Но полученный запрос не будет модифицирующим, то есть вы не сможете изменять данные в запросе, а соответственно и редактировать данные в исходной таблице посредством запроса.
    Ядро базы данных Microsoft Jet - достаточно гибкое средство в отношении типов и форматов SQL выражений, используемых для создания связей между таблицами. В дополнение к связям, которые вы можете указать в предикате, составляемом с помощью предложения WHERE, Microsoft Jet SQL может использовать предложение JOIN, чтобы указать внутреннюю, левую внешнюю или правую внешнюю связь, а также неэквивалентные связи, в которых связующим критерием является не равенство значений в связующих полях двух таблиц, а выражение условия, которое должно возвращать истину.
    Внутренняя связь может быть сложная, когда вы связываете несколько таблиц, например:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm ;

    В вышеприведенном примере мы ищем штаб-квартиру фирмы-производителя каждой модели.
    Помимо внутреннего (INNER JOIN) объединения, вы можете использовать внешние объединения, при этом различаются левые внешние и правые внешние объединения: LEFT JOIN и RIGHT JOIN. Используя левое внешнее объединение, вы получаете в итоговом запросе все записи из левой таблицы и только те записи из правой таблицы, которые имеют соответствующие значения по связующему полю в левой таблице.

SELECT DISTINCTROW [automobile passenger car].date_issue, account.account

FROM [automobile passenger car] LEFT JOIN account ON [automobile passenger car].key_auto = account.key_auto;

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

SELECT DISTINCTROW [automobile passenger car].date_issue, account.account

FROM account LEFT JOIN [automobile passenger car] ON account.key_auto = [automobile passenger car].key_auto;

    Microsoft Jet возвращает специальное значение NULL во всех полях для записей внешней таблицы, не содержащих одинаковых значений. Вы можете использовать это свойство для проверки целостности связи в зависимости от присутствия значения типа NULL во внешней таблице. Если в итоговом запросе присутствуют значения типа NULL, вы можете быть уверены в наличии несвязанных записей в дочерней таблице, или, наоборот, есть значения в родительской таблице, для которых отсутствуют соответствующие значения в дочерней таблице. Если вы хотите узнать, с каким поставщиком у вас не было сделок, например, за последний месяц, то лучший и самый быстрый способ это сделать - использовать внешнее объединение.
    Для сокращения объема выборки, то есть для получения в итоговом курсоре только тех данных, которые вас интересуют в конкретный момент, вы должны использовать предикат, составляемый с помощью предложения WHERE, который служит не только для установления связей, но и для накладывания фильтров на выбираемые данные.
    Допустим, мы хотим выбрать все названия моделей и производящих их фирм в пределах для конкретной страны, например Италии:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name="Италия" ;

    В SQL вы можете использовать логические операторы AND, OR и логическое отрицание NOT. Добавив всего лишь один оператор NOT перед выражением

country.country_name = "Италия"

    мы получаем совершенно противоположную выборку, в которой будут присутствовать фирмы всех стран, кроме Италии.

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE NOT country.country_name="Италия"

    При необходимости получить данные для двух стран мы используем оператор OR. Если нам не нужны данные по автомобилям из Франции и Италии, мы используем следующий запрос:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE NOT (country.country_name="Италия" OR country.country_name = "Франция")

    В этом случае необходимо верно расставить скобки, чтобы оператор NOT относился и к Италии и к Франции.
    Пока мы использовали только один оператор сравнения - равно (=), - на самом деле их несколько больше:

• >> - больше;
• << - меньше;
• >>= - не меньше;
• <<= - не больше;
• <<>> - не равно.

    Еще раз вернемся к предыдущему запросу, оставив прежнюю цель - не выбирать модели из Франции и Италии, но при этом используя совсем другие операторы:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name<<>>"Италия" AND country.country_name <<>> "Франция"

    Обратите внимание, что мы используем AND вместо OR, иначе в итоговом курсоре были бы выбраны все записи.
    Операторы сравнения могут обрабатывать не только числовые значения, но и символьные. При этом обрабатывается ASCII-код символа. При необходимости выполнить запрос, в котором мы хотим выбрать страны, названия которых начинаются на букву "И" и последующие буквы в порядке алфавита, мы можем использовать следующие команды:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name>>="И"

    В предикате, который формируется с помощью предложения WHERE, можно и нужно использовать помимо вышеприведенных еще несколько операторов, а именно: IN, BETWEEN, LIKE и IS NULL.
    Использование оператора IN позволяет нам по-другому выполнить запрос по выборке данных по Италии и Франции. Вспомните запрос, в котором мы собирали данные для всех стран, кроме Италии и Франции. Теперь мы можем записать его с помощью оператора IN

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name NOT IN ("Италия", "Франция");

    Оператор BETWEEN, строго следуя своему дословному переводу, выводит нам данные в промежутке между значениями, которые мы укажем в качестве его аргументов. Если вам нужно выбрать информацию по странам, которые расположены в алфавитном порядке между Италией и Францией, то используйте запрос, подобный нижеприведенному.

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name BETWEEN "Италия" AND "Франция"

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

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name NOT IN ("Италия", "Франция") AND (country.country_name BETWEEN "Италия" AND "Франция")

    Оператор LIKE позволяет использовать в критерии поиска шаблоны. Если необходимо выбрать только модели из стран, названия которых начинаются на букву "И", то можно использовать следующий запрос:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE country.country_name LIKE "И*'"

    И наконец, оператор IS NULL позволяет обнаружить значения типа NULL в указанном поле и соответственно определить, выводить проверенную запись в итоговый курсор или нет. Оператор IS NULL нельзя непосредственно использовать в Visual FoxPro и Microsoft Access. Но в этих языках есть функция ISNULL(). Поэтому можно написать команду следующим образом:

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE NOT isnull(country.country_name)

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

SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name

FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON model.key_firm = firm.key_firm

WHERE NOT isnull(country.country_name)

ORDER BY firm.name_firm

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

ORDER BY firm.name_firm DESC

    Если бы SQL-запросы позволяли нам выполнять только то, что мы успели рассмотреть, то и этого было бы достаточно много, но это еще не все. Мы можем использовать набор агрегатных функций.
    Этот набор может различаться в отдельных продуктах. В ANSI SQL присутствуют следующие функции агрегирования:

• COUNT(*) - возвращает число выбранных записей;
• COUNT(ALL Expression) - возвращает количество непустых значений;
• COUNT(DISTINCT Expression) - возвращает количество неповторяющихся значений по указанному в Expression выражению;
• MAX(Expression) - максимальное значение по указанному выражению;
• MIN(Expression) - минимальное значение по указанному выражению;
• SUM(ALL/DISTINCT Expression) - сумма по всем или только по неповторяющимся значениям;
• AVG(ALL/DISTINCT Expression) - среднее по всем или только по неповторяющимся значениям.

    В Microsoft Access и Visual FoxPro, в дополнение к вышеприведенным функциям, вы можете вычислить еще и стандартное отклонение, дисперсию и т. д.
    В следующем запросе приводится пример вычисления сумм продаж по конкретным наименованиям автомобилей:

SELECT DISTINCTROW Sum(account.sum) AS Sum_sum, model.name_model

FROM model INNER JOIN (account INNER JOIN [automobile passenger car] ON account.key_auto = [automobile passenger car].key_auto) ON model.key_model = [automobile passenger car].key_model

GROUP BY model.name_model

    Полученный результат приведен в следующей таблице:

    Если внимательно посмотреть на текст запроса, то в конце выражения можно увидеть предложение GROUP BY. В стандартном SQL вам просто необходимо группировать запрос по всем выбираемым полям, либо использовать поля как выражения функций агрегирования. Совершенно аналогичные ограничения накладываются на запросы в Microsoft Access. В Visual FoxPro и Microsoft SQL Server таких ограничений нет, и вам не обязательно группировать данные по всем выводимым полям.
    При создании запросов перед вами обязательно возникнет необходимость отфильтровать данные по какому-нибудь наименованию. Например, по модели M3 3.0. В принципе, вы можете использовать следующую строку, сформированную с помощью предложения WHERE:

WHERE model.name_model = "M3 3.0"

    Но это будет не совсем правильно с точки зрения стандарта SQL. Правильно построенный запрос будет выглядеть следующим образом:

SELECT DISTINCTROW Sum(account.sum) AS Сумм, model.name_model

FROM model INNER JOIN (account INNER JOIN [automobile passenger car] ON account.key_auto = [automobile passenger car].key_auto) ON model.key_model = [automobile passenger car].key_model

GROUP BY model.name_model

HAVING model.name_model = "M3 3.0"

    Результат этого запроса будет выглядеть следующим образом:

    Имейте в виду, что критерии, устанавливаемые с помощью WHERE, делают выборки, проверяя запись за записью, а предложение HAVING отбирает всю группу или агрегат целиком.
    Иногда оказывается, что простых запросов недостаточно для получения нужного результата. В таком случае приходится использовать объединение двух запросов или же запросы с подзапросами. Правда, не всегда имеет смысл сразу обращаться к подзапросам, так как они выполняются медленнее. В некоторых случаях лучше приложить усилия для поиска более оптимального решения.
    Подзапросы присоединяются к основному запросу через операторы IN, EXIST, SOME, ANY, ALL.
    Рассмотрим пример использования оператора IN. Допустим, у нас есть две таблицы с одинаковой структурой. Необходимо вывести данные из первой таблицы при условии, что по полю kto у выводимых записей нет совпадающих значений.

SELECT kto, skolko FROM first WHERE kto NOT IN ;

(SELECT DISTINCT kto FROM second)

    Пример использования оператора EXIST. Оператор EXIST - единственный из операторов, не требующий выражения между ключевым словом WHERE и самим собой. Он возвращает истину в зависимости от того, есть ли хоть одна запись в выборке подзапроса. Рассмотрим два решения одной задачи. Нам необходимо выбрать все модели автомобилей, которые стоят больше 25000 и которые мы ухитрились продать хотя бы один раз. Код записан в синтаксисе FoxPro.

SELECT DISTINCT Model.name_model,; Automobile_passenger_car.cost;

     FROM "auto store!model",;

     "auto store!automobile passenger car" ; Automobile_passenger_car ;

     WHERE Model.key_model = ; Automobile_passenger_car.key_model AND ;

     Automobile_passenger_car.cost >>=25000 AND Exist ;

     (SELECT * FROM account,;

     "auto store!automobile passenger car" Automobile_passenger_car ;

     WHERE account.key_auto = ; Automobile_passenger_car.key_auto)

SELECT DISTINCT Model.name_model, ; Automobile_passenger_car.cost ;

     FROM "auto store!model",;

     "auto store!automobile passenger car" ; Automobile_passenger_car;

     WHERE Model.key_model = Automobile_passenger_car.key_model AND ;

     Automobile_passenger_car.cost >>=25000 ;

And Automobile_passenger_car.key_auto NOT IN;

     (SELECT DISTINCT account.key_auto FROM account)

    Возможно, вы найдете еще более короткое решение данной задачи, не корите нас, так как мы думаем еще и об учебных целях приводимых примеров. В первом решении с помощью оператора EXIST мы просто проверяем таблицу Account на наличие записей во внутреннем подзапросе. Во втором решении нас интересует просто список значений, которые у нас имеются, после выполнения внутреннего запроса по полю key_auto.
    Второе решение дает правильный ответ, а первое неправильный. Почему? Обратимся к теории. Внутренний запрос выполняется только один раз, и внешний запрос при своей работе обращается только к его итогу, раз за разом проходя по всем записям. Естественно, что оператор EXIST здесь оказывается совершенно бесполезным. Есть ли выход из положения? Безусловно. Согласно теории, если мы свяжем внешний и внутренний запрос, получив при этом связанный подзапрос, то вынудим внешний запрос обращаться к внутреннему каждый раз во время обработки записей при решении, выводить ли выбранные поля в итоговый курсор. Поэтому мы выводим во внешнем запросе еще одно поле Automobile_passenger_car.key_auto и связываем внешний и внутренний запрос по этому полю. Теперь правильный запрос с использованием оператора EXIST выглядит так:

SELECT Model.name_model, ; Automobile_passenger_car.cost, ;

Automobile_passenger_car.key_auto;

     FROM "auto store!model",;

"auto store!automobile passenger car" Automobile_passenger_car;

WHERE Model.key_model = Automobile_passenger_car.key_model AND ;

     Automobile_passenger_car.cost >>=25000 And Exist ;

     (SELECT * FROM account WHERE ;

     Automobile_passenger_car.key_auto=account.key_auto)

    Не надо после всех вышеприведенных манипуляций пессимистически относиться к оператору EXIST. Ведь теперь мы получили внутренний подзапрос в полное управление и можем развить свой запрос еще больше, помня о том, что его результат будет пересматриваться раз за разом после обработки очередной записи во внешнем запросе.
    Пример использования операторов ANY и SOME. Использование операторов ANY и SOME приводит к совершенно одинаковым результатам. В качестве примера решим предыдущую задачу:

SELECT Model.name_model, Automobile_passenger_car.cost;

FROM "auto store!model",;

"auto store!automobile passenger car" Automobile_passenger_car;

WHERE Model.key_model = Automobile_passenger_car.key_model AND ;

Automobile_passenger_car.cost >>=25000 And Automobile_passenger_car.key_auto= ;

ANY (SELECT DISTINCT account.key_auto FROM account);

    Пример использования операторов ALL. Оператор ALL используется с операторами сравнения >> и << и подзапросом. Оператор ALL с оператором >> перед ним означает, что сравниваемое значение должно быть больше любого значения в списке значений, полученных в подзапросе. В противоположном случае - соответственно меньше любого значения в списке.

SELECT Model.name_model, Automobile_passenger_car.cost;

FROM "auto store!model",;

"auto store!automobile passenger car" Automobile_passenger_car ;

WHERE Model.key_model = Automobile_passenger_car.key_model and ;

Automobile_passenger_car.cost >>=ALL ;

(SELECT DISTINCT summa FROM account ;

WHERE summa>>=30000)

    Не стоит особо пропагандировать необходимость добавления записей в таблицы в системах обработки данных. Они ведь и становятся настоящими таблицами, когда в них появляются записи. За этот процесс в языке SQL отвечает команда INSERT. Она имеет два варианта использования. В первом случае вы добавляете одну запись с конкретными данными в конкретные поля. Во втором случае вы можете добавить одну и более записей, набор которых формируется запросом. Необходимо отметить, что второй вариант синтаксиса не реализован в версии Visual FoxPro 3.0. Тем не менее его легко реализовать в два шага. Рассмотрим конкретные примеры.
    В таблицу Account добавим одну запись:

INSERT INTO account VALUES (106,6,100007,{12.07.96},.T.,26000)

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

INSERT INTO Account (account,key_customer,key_auto,date_write)

VALUES (106,6,100007,{12.07.96})

    Какой информацией заполнятся поля для добавленной записи, очень сильно зависит от продукта, в котором выполняется операция, и от того, как спроектирована таблица. Это может быть значение по умолчанию для данного поля, значение типа NULL или пустая строка для символьных полей и 0 для числовых. Поля можно, как правило, перечислять в произвольном порядке, при этом значения тоже должны соответствовать порядку следования полей в перечислении.
    Следующий вариант использования команды INSERT ведет к добавлению в таблицу одной и более записей. В нашем случае мы просто добавляем записи из одной таблицы в другую, при этом обе таблицы имеют одинаковую структуру.

INSERT INTO mytable ( kto, skolko )

SELECT DISTINCTROW acmytable.kto, acmytable.kto

FROM Acmytable

WHERE (((acmytable.kto)="cd"))

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

    Запросы обновления, которые начинаются с ключевого слова UPDATE, служат для замены значений в полях таблицы в записях, которые выбираются по определенному критерию. Если вы не задаете никакого критерия, то обновляются все записи по указанному полю. Вы можете обновлять одно или несколько полей, но только одной таблицы. Есть некоторые различия в синтаксисе этой команды для приложений, которые мы рассматриваем. Самый описательный синтаксис в Microsoft SQL Server

UPDATE [[database.]owner.]{table_name | view_name}

SET [[[database.]owner.]{table_name. | view_name.}]

column_name1 = {expression1 | NULL | (select_statement)}

[, column_name2 = {expression2 | NULL | (select_statement)}...]

[FROM [[database.]owner.]{table_name | view_name}

[, [[database.]owner.]{table_name | view_name}]...]

[WHERE search_conditions]

    Из приведенного синтаксиса видно, что вы можете связывать изменения в таблице в зависимости от связи с конкретной таблицей. Притом указывать это в предикате, связанном с предложением FROM. Таким образом, мы можем сформировать следующую строку

UPDATE Account SET summa=summa*1.25

FROM Account, sale

WHERE Account.account=Sale.account AND Sale.date_sale>>{10.01.96}

    В Microsoft Access, несмотря на отсутствие в синтаксисе предложения FROM, мы тем не менее можем легко связывать две таблицы в запросе и обновлять данные в зависимости от значения в другой таблице.

UPDATE DISTINCTROW account

INNER JOIN sale ON account.account = sale.account

SET account.summa = summa*1.25

WHERE (((sale.summa)>>20000));

    В Visual FoxPro мы можем связать таблицы в предложении WHERE и в принципе добиться того же результата.

UPDATE Account ;

SET Summa=summa*1.25

WHERE Account.account=sale.account AND Account.date_sale

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

DELETE FROM Account WHERE Account.key_auto = 100008

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

    Язык SQL может помочь программисту не только более эффективно организовать получение пользователем приложения нужных данных, но и программно создавать и изменять структуру данных.
    В этом параграфе вы научитесь программным путем:

• создавать таблицы и индексы;
• изменять структуру существующих таблиц;
• создавать связи между таблицами.

    Data Definition Language (DDL) - это раздел языка SQL, который служит для создания таблиц, изменения их структуры или их удаления. Сюда же входит создание индексов. Несмотря на то, что практически во всех настольных СУБД имеются собственные средства создания таблиц, как правило, вы можете создавать таблицы и с помощью языка SQL. При этом за внешним визуальным прикрытием скрываются те же SQL команды. Поэтому логичней использовать возможность докопаться до сути происходящих процессов.
    Следующим аргументом для изучения этого раздела SQL может служить задача решения проблемы перевода своей информации с технологии одиноко стоящих компьютеров, обменивающихся информацией посредством дискет, либо технологии файл-серверов, на технологию клиент-сервер.
    Рассмотрим в качестве невероятного примера такую простую задачу, как переход вашей организации с платформы Visual FoxPro на Microsoft Access. Несмотря на всю кажущуюся легкость этого процесса, вы понесете немало потерь. Ни одно из свойств таблицы - заголовок поля, значение по умолчанию, правила проверки ввода значений уровня поля и уровня записи - не переносится. Вам все придется создавать заново. В то же время команды SQL Data Definition Language могут обеспечить управление импортом данных в Access или экспортом их из FoxPro. Как правило, любое мало-мальски приличное современное средство управления базами данных имеет набор функций SQL pass-through, которые позволяют управлять сторонними приложениями с помощью диалекта языка SQL самого стороннего приложения.
    Команда CREATE TABLE служит для создания таблиц. Ее полный синтаксис выглядит так:

1. Visual FoxPro

   CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]

   (FieldName1 FieldType [(nFieldWidth [, nPrecision])]

   [NULL | NOT NULL]

   [CHECK lExpression1 [ERROR cMessageText1]]

   [DEFAULT eExpression1]

   [PRIMARY KEY | UNIQUE]

   [REFERENCES TableName2 [TAG TagName1]]

   [NOCPTRANS]

   [, FieldName2 ...]

   [, PRIMARY KEY eExpression2 TAG TagName2

   |, UNIQUE eExpression3 TAG TagName3]

   [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP]

   REFERENCES TableName3 [TAG TagName5]]

   [, CHECK lExpression2 [ERROR cMessageText2]])

   | FROM ARRAY ArrayName

2. MS Access

   CREATE TABLE таблица

   (поле1 тип [(размер)] [индекс1] [, поле2 тип [(размер)]

   [индекс2] [, ...]] [, составной_индекс [, ...]])

3. MS SQL Server

   CREATE TABLE [[database.]owner.]table_name

   (column_name datatype [NOT NULL | NULL]

   [, column_name datatype [NOT NULL | NULL]]...)

   [ON segment_name ]

    В зависимости от приложения синтаксис может различаться, но те элементы, которые мы можем создавать с помощью вышеприведенного синтаксиса, можно создавать с помощью других команд. То есть команда CREATE TABLE как бы размывается по нескольким командам, что в значительной мере зависит от внутренней архитектуры используемого процессора баз данных. Синтаксис SQL в Microsoft Access ближе к Microsoft SQL Server, в то время как Visual FoxPro стоит несколько особняком. Хотя в конечном итоге все элементы, которые мы можем создать для таблиц в Access, содержит и MS SQL Server.
    Иногда перед вами может стать задача изменения структуры таблицы. Для этого используется команда ALTER TABLE. Естественно, что никто не лишает вас возможности сделать это визуальными средствами СУБД, в которой вы проектируете свое прикладное приложение. Очень сложно придумать ситуацию, когда вам необходимо использовать команду ALTER TABLE внутри СУБД, где вы проектируете приложение, но, возможно, вам придется менять структуру таблицы, для которой на компьютере нет создающего ее приложения. Но есть драйвер ODBC (иначе ситуация фатальная). Или другой случай, когда вам необходимо с рабочей станции, например, из среды приложения, написанного на Visual FoxPro, добавить поле (колонку) в таблицу, хранящуюся на MS SQL Server.
    Самый насыщенный ключевыми словами ситаксис этой команды в Visual FoxPro:

ALTER TABLE TableName1

     ADD | ALTER [COLUMN] FieldName1

     FieldType [(nFieldWidth [, nPrecision])]

     [NULL | NOT NULL]

     [CHECK lExpression1 [ERROR cMessageText1]]

     [DEFAULT eExpression1]

     [PRIMARY KEY | UNIQUE]

     [REFERENCES TableName2 [TAG TagName1]]

     [NOCPTRANS]

ALTER TABLE TableName1

ALTER [COLUMN] FieldName2

     [NULL | NOT NULL]

     [SET DEFAULT eExpression2]

     [SET CHECK lExpression2 [ERROR cMessageText2]]

     [DROP DEFAULT]

     [DROP CHECK]

ALTER TABLE TableName1

[DROP [COLUMN] FieldName3]

[SET CHECK lExpression3 [ERROR cMessageText3]]

[DROP CHECK]

[ADD PRIMARY KEY eExpression3 TAG TagName2]

[DROP PRIMARY KEY]

[ADD UNIQUE eExpression4 [TAG TagName3]]

[DROP UNIQUE TAG TagName4]

[ADD FOREIGN KEY [eExpression5] TAG TagName4

     REFERENCES TableName2 [TAG TagName5]]

[DROP FOREIGN KEY TAG TagName6 [SAVE]]

[RENAME COLUMN FieldName4 TO FieldName5]

[NOVALIDATE]

ALTER TABLE таблица {ADD {COLUMN поле тип[(размер)] [CONSTRAINT индекс] I

CONSTRAINT составной_индекс} |

DROP {COLUMN поле I CONSTRAINT имя_индекса} }

ALTER TABLE [[database.]owner.]table_name ADD column_name datatype NULL [, column_name datatype NULL...]

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

    В этом параграфе мы подробно рассмотрим визуальные и программные методы создания запросов и представлений в Visual FoxPro.
    Расширения команд xBase командами SQL, которые появились, начиная с версии FoxPro 2.0, принесли, несомненно, ему очень большую пользу. Главным достоинством можно считать значительное сокращение кода для выборки данных, причем без потери скорости, так как метод оптимизации Rushmore работает и в SQL запросах. Это единственный вариант, где указанный метод может использоваться для поиска данных в нескольких таблицах. А, например, команда INSERT-SQL работает на порядок быстрее, чем классическая конструкция

APPEND BLANK

REPLACE <<поле>> WITH <<выражение>>

• SELECT-SQL
• INSERT-SQL
• CREATE TABLE

    При этом курсоры, которые получались в результате выборки, были немодифицирующими, то есть изменения, которые вы делали в них, не отражались в таблицах, данные из которых они отражали. Тем не менее были изобретены сотни способов синхронизации изменений в курсорах, полученных с помощью команды SELECT с данными в исходных таблицах. Одни из самых простых - использование в запросе в качестве выводимых колонок функции RECNO(), которая возвращает номер записи и использование возможности SELECT-SQL создавать таблицы. У этого способа есть существенное ограничение - он работает корректно только при выборке из одной таблицы. В случае выборки из нескольких таблиц получить с помощью функции RECNO() нужный результат невозможно.
    Большое число задач требует просто просмотра или распечатки промежуточных результатов, вследствие чего язык SQL прижился и был быстро освоен программистами.
    В Visual FoxPro добавилось несколько новых команд SQL, с помощью которых мы можем менять структуру уже существующих таблиц, удалять записи и модифицировать данные. Но самое главное, - у нас теперь есть возможность создавать представления (View), которые можно хранить в базе данных. Тем самым значительно увеличились возможности организации данных. Помимо этого представление позволяет создавать курсоры, с помощью которых мы можем менять данные в исходных таблицах. При этом мы сами можем определять, какие поля разрешить для модификации, а какие нет. Возникают определенные трудности, если в исходных таблицах нет уникальных ключевых полей, но они легко преодолимы. Можно сказать, что запросы в том виде, как они существовали в предыдущих версиях и продолжают существовать сейчас, больше не нужны. Хотя бесспорно при желании можно доказать необходимость их использования. В Visual FoxPro как синтаксис команд по созданию представлений и запросов, так и конструкторы для создания этих краеугольных камней систем управления базами данных очень похожи друг на друга. Поэтому, начиная обзор Конструктора представлений, мы одновременно изучаем и Конструктор запросов. Параллельно мы будем обсуждать как визуальный способ создания представлений, так и программный.
    Вызвать на экран Конструктор представлений можно несколькими способами. Если вы работаете с использованием Project Manager, а это единственно правильный путь для построения сложных приложений, то вам необходимо перевести курсор на пункт списка Local Views и нажать кнопку New. После чего на экране появится диалоговое окно, в котором вам предложат создать представление с помощью Мастера или самостоятельно. Самостоятельно - значит с помощью Конструктора представлений, что ненамого сложней. Помимо этого вы можете щелкнуть по значку New в стандартной панели инструментов, и в появившемся диалоге среди предложенных типов файлов выбрать Local View.
    Перед тем как на экране появится Конструктор представлений, на экран будет выведено диалоговое окно для выбора таблиц или предварительно созданных представлений, в котором вы должны выбрать соответственно таблицу или представление, на основе данных из которого вы будете создавать новое представление. Если вам необходимо выбирать данные из нескольких таблиц или представлений, то, нажав правую кнопку мыши, находясь внутри Конструктора представлений, вы можете вызвать всплывающее меню и выбрать в нем команду Add Table, как это показано на рис. 7.1.

Рис. 7.1. Диалоговое окно выбора таблиц или представлений

    Конструктор представления состоит из двух частей. В первой, верхней части, размещаются графические образы таблицы и представления, данные из которых вы будете использовать для создания представления. Здесь легко можно связать две таблицы или представления, просто щелкнув мышью на названии поля и, не отпуская кнопки, переместив курсор на поле в другой таблице или представлении (рис. 7.2). Это очень важный момент, потому что, как правило, все таблицы и представления должны быть связаны, иначе результаты выборки трудно прогнозировать.

Рис. 7.2.

    Нижняя часть Конструктора состоит из пяти страниц, заголовки которых называются Selection Criteria, Fields, Order By, Group By и Update Criteria (рис. 7.3). Здесь стоит отметить, что последней страницы нет в Конструкторе запросов. Это единственное, но очень принципиальное различие между запросом и представлением - представления могут изменять исходные таблицы при изменении данных в них.

Рис. 7.3.

    На странице Selection Criteria записываются связи между таблицами и накладываются фильтры на выбираемые данные. Здесь мы фактически записываем условия предложения WHERE. Если вы еще слабо знакомы с синтаксисом SQL, то есть возможность изучать его в интерактивном режиме. В любой момент вы можете либо с помощью вызова всплывающего меню, или с помощью меню Query, или с помощью панели инструментов View Designer просмотреть SQL запрос в текстовом виде. При этом вы увидите, что как и в случае с запросами, так и с представлениями, выражение начинается с ключевого слова SELECT (рис. 7.4). Но пусть вас это не смущает, команда, создающая представления программным путем, начинается все-таки с ключевого слова CREATE SQL VIEW. Но об этом разговор чуть ниже.

Рис. 7.4. Генерируемый в представлении код SQL запроса

    Cтраница Fields служит для выбора полей из представлений и таблиц, участвующих в создании нашего представления. Можно выбрать поля другими способами, например, дважды щелкнув мышью на названии нужного вам поля или, что более эффектно, просто перетащив графический образ этого поля с образа таблицы в список Selected Output, который присутствует на страницах Fields и Selection Criteria. У визуального способа есть одно ограничение. В случае, если вам необходимо иметь в выборке не данные из поля, а, например, выражение, то вам придется воспользоваться комбинированным списком Functions/Expressions. К этому же списку вам придется обратиться в случае, если вы захотите дать колонкам свои имена с помощью опции AS.
    Если вы внимательно читали параграф о SQL командах, то помните, что в команде SELECT-SQL есть предложение ORDER BY, которое позволяет вам упорядочить данные по определенным полям. Для этого служит страница Order by в Конструкторах представлений и запросов.
    В случае использования функций агрегирования вам придется обратиться к странице Group by для группировки данных.
    Последняя страница называется Update Criteria. Именно на этой странице мы можем выбрать поля, редактирование которых в курсоре представления будет отображаться в исходных таблицах. На этой странице в списке Field Name отображаются поля из таблиц, к которым обращается запрос представления. При этом отображать можно как одновременно все поля из всех таблиц, так и только из одной таблицы, выбрав соответствующее значение в раскрывающемся списке Table (см. рис. 7.2).
    Далее для каждой таблицы необходимо выбрать ключевые поля. Обратите внимание, что рядом со значком, изображающим ключ над списком Field Name, появится значок, изображающий карандаш, и вертикальная полоска ниже этого изображения. Теперь мы можем пометить поля как модифицируемые. Ключевое поле может быть одновременно и модифицируемым. Если есть необходимость отключить ключевые поля, то нажмите кнопку Reset. Когда вы хотите сделать сразу все поля модифицируемыми, нажмите кнопку Update All. При этом поля, которые вы укажете как ключевые, помечены не будут. Затем необходимо включить переключатель SendUpdates, так как только после этого исходные таблицы будут модифицироваться. Теперь с помощью кнопок выбора SQL WHERE clause includes необходимо выбрать одно из четырех значений WhereType. Оно будет определять, каким образом станет происходить поиск записи в исходной таблице:

• При выборе значения Key Fields only данные будут обновляться в соответствии с изменением значений ключевых полей. Поэтому не забудьте, что если в выбранном для представления ключевом поле или полях не гарантируется уникальность значения, то данные могут быть изменены вместо одной сразу в нескольких записях.
• Значение Key And Updatable Fields приводит к поиску записи по тем полям, которые вы пометите как модифицируемые или разрешенные для изменений и по ключевым полям.
• Значение Key And Modified Fields обеспечивает поиск по ключевым и фактически измененным полям.
• Значение Key and Timestamp для локальных представлений не поддерживается.

    Последние две кнопки выбора позволяют вам решить, как будут производиться изменения в исходной таблице: путем удаления и вставки новой записи или путем модификации записи. Второй путь предпочтительнее, так как результат достигается много быстрее.
    Свойства WhereType и UpdateType можно установить глобально для всего Visual FoxPro, используя диалоговое окно Options на странице Remote Data. Там есть два раскрывающихся списка, объединенных общим заголовком SQL Updates, - Criteria и Method, как это показано на рис. 7.5.

Рис. 7.5. Установка параметров обновления данных

    Мы уже говорили, что создать представление можно программным путем с помощью команды CREATE SQL VIEW. Свойства, с помощью которых регулируются данные в исходных таблицах, устанавливаются также программным путем с помощью функций DBSETPROP() и CURSORSETPROP(). Узнать значения установленных свойств можно с помощью функций с похожими названиями: DBGETPROP() и CURSORGETPROP(). Отличие их в том, что функции CURSORGETPROP() и CURSORSETPROP() работают с представлениями, которые в данный момент используются в какой-либо из рабочих областей. Функции DBGETPROP() и DBSETPROP() устанавливают свойства для любого представления, которое содержится в текущей базе данных. В приводимой ниже табл. 7.1 обратите внимание на то, что многие свойства можно установить только программным путем. Свойства, которые приводятся в табл. 7.1, относятся к полям представления. То есть вторым аргументом функции DBSETPROP() или функции DBGETPROP() будет "FIELD". Например:

DBSETPROP("account_and_customer.account","FIELD","comment",; "Используйте это поле для ввода номера счета")

    Обратите особое внимание, что название поля приводится вместе с названием представления "account_and_customer.account". В противном случае Visual FoxPro не сможет найти это поле.

    В следующей таблице приводятся некоторые свойства представления, которые можно использовать как для локальных, так и внешних представлений. Для их чтения и изменения необходимо использовать функции DBGETPROP() и DBSETPROP() со вторым аргументом VIEW. В следующем примере мы устанавливаем свойство Comment для всего представления, а затем выводим его на экран:

=DBSETPROP("account_and_customer","view", "Comment",

     "Предназначено для корректировки поставок по счетам")

? DBGETPROP("account_and_customer","view", "Comment")

USE Account_and_customer

If LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0

     =CURSORSETPROP("keyfieldlist",;

     "account.account,customer.name_customer")

     ENDIF

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

USE Account_and_customer

IF LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0

     =CURSORSETPROP("keyfieldlist",;

"account,name_customer")

     ELSE

     IF LEN(ALLT(CURSORGETPROP("updatablefieldlist")=0

=CURSORSETPROP("updatablefieldlist",;

"key_customer,selled,summa,;

name_customer,address")

     ENDIF

ENDIF

USE Account_and_customer

IF LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0

     =CURSORSETPROP("keyfieldlist",;

     "account,name_customer")

     ELSE

     IF LEN(ALLT(CURSORGETPROP("updatablefieldlist")=0

=CURSORSETPROP("updatablefieldlist",;

"key_customer,selled,summa,;

     name_customer,address")

=CURSORSETPROP("updatenamelist",;

"key_customer account.key_customer,;

selled account.selled,summa account.summa,;

name_customer customer.name_customer,;

address customer.address" )

ENDIF

     ENDIF

     =CURSORSETPROP("SendUpdates",.T.)

    В следующем примере программным путем создается представление c помощью запроса к двум таблицам: Account и Customer. C помощью функции DBSETPROP() назначаются свойства установки ключевого поля и свойства, устанавливающие возможность модификации полей в исходных таблицах.

IF NOT DBUSED("auto_store")

     OPEN DATABASE "auto store"

ENDIF

CREATE SQL VIEW Account_and_customer ;

AS SELECT Account.*, Customer.name_customer, ; Customer.address;

FROM "auto store!account", "auto store!customer";

     WHERE Customer.key_customer = Account.key_customer;

     AND Account.selled = .f.

=DBSETPROP("account_and_customer.account",;

"FIELD","KeyField",.T.)

=DBSETPROP("account_and_customer.name_customer",;

"FIELD","KeyField",.T.)

=DBSETPROP("account_and_customer.date_write",;

"FIELD","Updatable",.T.)

=DBSETPROP("account_and_customer.selled",;

"FIELD","Updatable",.T.)

=DBSETPROP("account_and_customer.summa",;

"FIELD","Updatable",.T.)

=DBSETPROP("account_and_customer.address",;

"FIELD","Updatable",.T.)

USE Account_and_customer

=CURSORSETPROP("SendUpdates",.T.)

=CURSORSETPROP("WhereType",3)

GO 3

REPLACE selled WITH .T.

SKIP -1

SELECT Account

BROWSE

    В Visual FoxPro существует понятие буферизации, установку которой контролирует свойство Buffering. Значение этого свойства можно менять с помощью функции CURSORSETPROP() и читать с помошью функции CURSORGETPROP().
    Свойство Buffering может принимать пять значений:

1. - отсутствие какой-либо буферизации.
2. - пессимистическая буферизации записи. При этом значении блокируется редактируемая запись. Блокировка автоматически снимается, и изменения записываются на диск, как только пользователь переходит на другую запись или закрывает таблицу. Другим способом записи изменений на диск может служить использование функции TABLEUPDATE().
3. - оптимистическая буферизация записи. Запись блокируется только в то время, когда она записывается на диск. Для представлений эта блокировка является значением по умолчанию.
4. - пессимистическая буферизация таблицы. Как только вы начинаете редактирование, блокируется вся таблица. При этом запись на диск может произойти только при вызове функции TABLEUPDATE() или закрытии таблицы.
5. - оптимистическая буферизация таблицы. Таблица блокируется в момент записи изменений на диск. Для записи изменений на диск надо либо вызвать функцию TABLEUPDATE(), либо закрыть таблицу.

    В этом параграфе вы изучите методику построения разнообразных типов запросов в Access. Узнаете о возможностях визуального инструментария и способах использования для построения запросов макрокоманд и объектов DAO.
    Запросы, без преувеличения, являются главным инструментом работы с данными в Access. Бесспорно, вы можете создать одну таблицу с огромным количеством полей, с помощью Мастера изготовить форму для работы с ней и посчитать, что приложение готово. При этом можно сказать, что существует определенный круг задач, для которых такая технология вполне приемлема. Но мы будем рассматривать другие, более распространенные случаи и задачи, которые лучше решать с помощью запросов. И уже на основе запросов в дальнейшем мы будем строить формы.
    Access предоставляет нам несколько способов создания запросов. Самый распространенный - создание запросов с помощью Конструктора запросов. Количество видов запросов, которые мы можем создавать с помощью Конструктора, впечатляет и резко выделяет Access среди других продуктов. Из Конструктора запросов легко перейти в режим редактора SQL либо в табличный режим, где мы можем просмотреть результаты запросов. Режим редактора выражений SQL мы будем рассматривать как второй способ создания запросов. Некоторые профессионалы на начальном этапе изучают Конструктор запросов методом "от противного". То есть пишут запрос вручную, а потом выходят в режим Конструктора. Новичкам имеет смысл почаще делать обратную операцию, то есть создавать запросы в Конструкторе и переходить в режим редактора, для того чтобы лучше изучить синтаксис SQL.
    Третий способ - это программный способ создания запросов с помощью объектов доступа к данным (DAO). Для этого используется объект QueryDef, который и хранит в себе описание SQL запроса.
    Последний способ - создание строки запроса и выполнение его с помощью команды DoCmd.RunSQL. Этот способ достаточно популярен, при этом база данных становиться более компактной, так как описание запроса хранится в виде строки кода, а не в виде описания объекта. Его недостатком является то, что мы не можем получить таким образом объект Recordset. В то же время, этот способ прекрасно годится для создания и выполнения запросов действий (Update, Delete, Insert), так как часто приходится скрывать от пользователя суть динамики изменения наборов данных. Хранение запросов модификации в базе данных, если мы не хотим, чтобы пользователь без необходимости запускал эти запросы, заставляет нас думать об ограничении доступа. Поэтому логичней хранить запрос в строчках кода.
    Для создания запросов с помощью Конструктора необходимо выполнить одну из следующих последовательностей действий:

1. Перейти на страницу Запросы в Контейнере базы данных.
2. Нажать кнопку Создать.
3. В появившемся диалоге выбора таблиц выбрать нужные таблицы.
4. Связать таблицы, если они не связаны, постоянно хранимой связью, зарегистрированной в базе данных.

1. Выбрать в меню Вставка команду Запросы. При этом надо помнить, что меню Access изменяется в зависимости от того, какой объект активен в данный момент. Если, например, вы находитесь в режиме редактирования таблицы, то меню Вставка не содержит пункт Запросы.
2. Выполнить шаги 3 и 4 из предыдущей последовательности действий.

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

Рис. 7.6.

    Здесь необходимо отметить, что запросы можно создавать не только к таблицам, но и к уже существующим запросам. Если есть необходимость, то вы можете комбинировать табличные данные и данные уже существующего запроса в новом запросе. В диалоговом окне Добавление таблицы присутствуют три вкладки: Таблицы, Запросы и Таблицы и Запросы. С их помощью вы можете ограничить необходимые вам объекты только таблицами, только запросами, либо, напротив, включить в список как таблицы, так и запросы (рис. 7.7).

Рис. 7.7.

    Так же как в Visual FoxPro, Конструктор запросов состоит из двух частей. В верхней части расположены графические образы таблиц и запросов, данные из которых используются в текущем запросе. По умолчанию Конструктор запросов служит для построения запросов выборки. При этом обязательно учитывайте, что при изменении значений в записях итоговых запросов изменяются данные в исходных таблицах. Нижняя половина представляет собой таблицу, в каждую строку которой мы можем заносить необходимую информацию. Несмотря на то, что научиться пользоваться Конструктором достаточно легко, мы тем не менее позволим себе остановиться на этом моменте более подробно. В первую строку заносится название поля. Сделать это можно несколькими способами:

1. Перетащить графический образ поля из верхней части Конструктора в соответствующую колонку.
2. Дважды щелкнуть мышкой на графическом образе поля.
3. Написать название поля непосредственно в первой строке колонки.
4. Выбрать название поля из раскрывающегося списка, как показано на рис.7.8.

Рис. 7.8.

    Во вторую строчку заносим название таблицы. Это важно, если у нас больше, чем одна таблица, так как в таблицах поля могут совпадать по названию. Если вы сомневаетесь, что можете правильно занести название таблицы, то выбирайте ее из списка.
    Очень часто необходимо выводить информацию, упорядочив ее по какому-нибудь из полей. Для этого используется третья строка колонки поля.
    С помощью переключателя, расположенного в четвертой строчке, вы определяете, будет ли поле выводиться в выборке запроса или нет. Представьте ситуацию, в которой вам необходимо отсортировать итоговую выборку по некоему полю, но присутствие этого поля в ней не нужно. Тогда, установив сортировку по этому полю и отключив переключатель вывода на экран, вы добьетесь необходимой функциональности.
    Одним из главных элементов запросов, если не самым главным, является условие выборки или критерии, которые мы накладываем на исходные таблицы. Ведь совершенно нерационально выводить все записи из таблицы, где их десятки тысяч. Поэтому активно используйте критерии для ограничения числа записей. При этом, если в одной колонке вы напишете критерии в строчках "условия отбора" и "или", то они свяжутся по условию OR. Если же вам необходимо установить для одного и того же поля критерий по AND, то вы должны либо непосредственно набрать условие с использованием этого оператора, либо использовать еще одну колонку для этого поля, но с выключенным переключателем "Вывод на экран", как это показано на рис. 7.9.

Рис. 7.9. Построение критерия с использованием оператора AND. Оба выражения, связанные оператором AND, накладываются на одно и то же поле Account

    Выражения, представленные в табл. 7.3, называются совокупными функциями SQL или функциями агрегирования. Чтобы построить запрос с использованием этих функций, необходимо обязательно установить группировку по одному или более полям. Выберите в меню Вид команду Групповые операции, либо соответствующий значок на панели инструментов. После того как вы выполните эту команду, в колонках выбора полей появится строчка "Групповая операция". Теперь из раскрывающегося списка вы можете выбрать одну из функций агрегирования для поля, которое отображается в текущей колонке, либо установить группировку по данному полю. Здесь следует обратить внимание на два последних пунктах раскрывающегося списка: Выражение и Условие. Если вы выберете значение Условие, то возникнет необходимость отключить вывод на экран этого поля, в противном случае вы получите сообщение от Access, в котором вас будут просить об этом же. Основное назначение этого пункта - занести критерий, который вы укажете в этой колонке, в предложение WHERE, так как если вы пишете критерий для поля, по которому происходит группировка, то критерий заносится в предложении HAVING.

    Иногда бывает недостаточно использовать только функцию агрегирования. Тогда можно использовать выражения, созданные с помощью функций агрегирования для вывода на экран, но при этом в строку Групповые Операции необходимо вынести значение "Выражение". На рис. 7.10 приводится пример построения такого выражения. Критерий, указанный для колонки, которая будет выводить выражение, также попадет в предложение HAVING.

Рис. 7.10.

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

Рис. 7.11.

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

Forms![IknowwhatIwantIwantitnow].[account]

    превратится в параметр, и перед вашими пользователями появится диалог, в котором будет предложено ввести значение данного параметра. В то же время параметр SKOLKO зарегистрирован как параметр и всегда будет считаться параметром, даже при наличии поля с одноименным названием в какой-нибудь из форм, из которой будет вызываться запрос.

Рис. 7.12. Параметрический запрос, требующий наличия открытой формы

    В любой момент мы можем переключиться из окна Конструктора запросов в режим таблицы или режим SQL. Это очень удобно, так как часто мы знаем, что хотим получить, но никак не можем добиться нужного результата из-за неправильной логики запроса. Используя режим таблицы, мы можем корректировать наш запрос до тех пор, пока не получим нужный результат. Режим SQL полезен в плане изучения синтаксиса, кроме того, что очень удобно, если вы правильно отредактируете запрос, то ваши изменения отразятся в окне Конструктора. Существует определенный набор запросов, которые невозможно построить с помощью Конструктора. О них будет упомянуто ниже.
    Помимо запросов выборки с помощью Конструктора, можно создать запросы добавления, обновления, удаления, перекрестный и создания таблиц.

    Запросы добавления - это те запросы, которые начинаются с ключевого слова INSERT. Мы их обсуждали выше, теперь рассмотрим, как их создавать визуально в Microsoft Access. По умолчанию Конструктор создает запросы добавления с помощью предложения SELECT, то есть использует следующий синтаксис:

INSERT INTO назначение [IN внешняя_база_данных]

[(поле1[,поле2[, ...]])]

SELECT [источник.]поле1[,поле2[, ...]]

FROM выражение

    Если вы в режиме SQL создадите запрос, который использует синтаксис добавления одной записи, то при повторном открытии запроса он все равно преобразуется в запрос с вышеприведенным синтаксисом. Тем не менее на результаты запроса добавления данное преобразование никак не повлияет. Рассмотрим следующий пример. В режиме SQL был создан следующий запрос:

INSERT INTO Account ( account ) VALUES (109)

    После перехода в режим Конструктора и возврат в режим SQL запрос был преобразован к следующему виду:

INSERT INTO Account ( account )

     SELECT 109 AS Выражение1;

    Тем не менее и тот другой запрос выполняет совершенно одинаковые действия - добавляет запись в таблицу Account, у которой поле Account имеет значение 109.
    Для того чтобы перевести ваш запрос в запрос добавления, выберите в меню Запросы команду Добавление, либо выберите соответствующий значок на панели инструментов Тип запроса. После этого появится диалог, где будет предложено выбрать таблицу, в которую вы сможете добавить одну или несколько записей, как это показано на рис. 7.13.

Рис. 7.13. Выбор таблицы для добавления записей

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

Рис. 7.14.

    Для того чтобы создать запрос данного типа, необходимо выбрать в меню Запросы команду Создание таблицы. Так же как и в предыдущем типе запросов, вам будет предложено выбрать или ввести название таблицы, причем опять же она может находиться в другой базе данных. По синтаксису полученный запрос очень похож на запрос выборки, только в конце добавляется конструкция следующего вида:

...INTO <<название новой таблицы>>

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

    Выбрав команду Удаление из меню Запросы, вы можете приступить к конструированию запроса удаления. Внешний вид окна Конструктора несколько изменится. Вместо строк Сортировка и Вывод на экран теперь вы увидите только одну - Удаление, которая может принимать два значения, причем без активного участия с вашей стороны. Если выбрать вместо названия конкретного поля звездочку, то строка удаления примет значение "Из". При выборе же конкретного поля строка примет значение "Условие". Причем надо отметить, что если вы напишете условия отбора записей в колонке, где выбрана звездочка, то есть выбраны все поля, то при попытке выполнения запроса появится сообщение о том, что писать эти условия в этой колонке нельзя. То есть строка Удаления имеет направляющий смысл, никак не влияющий на сам механизм выборки данных (рис. 7.15).

Рис. 7.15.

    Запрос обновления, для создания которого необходимо повторить те же шаги, что и для предыдущих типов запросов, служит, как видно из его названия, для обновления данных в таблице или нескольких таблицах. Если сравнивать внешний вид Конструктора с предыдущим типом запросов, то вы увидите только одно изменение - строка Удаление заменилась строкой Обновление. В эту строку вам необходимо заносить новое значение поля, которое оно приобретет после выполнения запроса.

    Это последний из типов запросов, которые мы можем создавать визуально. Для этого запроса вы должны выбрать три поля, одно из которых будет содержимым первой колонки полученного набора данных, значения второго поля станут заголовками остальных колонок, а содержимое третьего поля после обработки какой-либо из функций агрегирования будет отображаться во всех остальных колонках.
    В качестве примера рассмотрим достаточно тривиальную выборку по трем полям account, key_customer и summa.
    Итоговый запрос будет содержать следующие данные:

    Теперь с помощью этих полей построим перекрестный запрос. Для этого вначале, как вы догадались, установим тип запроса с помощью меню или соответствующего значка. Далее обратимся после выбора необходимых нам полей к третьей строке.
    Мы можем выбрать для каждой колонки значение в этой строке из набора, который состоит из слов "Группировка", "Выражение", "Условие" и набора уже знакомых нам функций агрегирования. Выбор значений в этой строке накладывает определенные обязанности по выбору значений в четвертой строке, так как Access контролирует ошибки только при попытке сделать выборку. Иногда бывает очень жаль потраченных усилий.
    Таким образом, если мы выбрали для поля account значение "Группировка", то значением строки "Перекрестная таблица" лучше выбрать "Заголовки строк" или "Заголовки столбцов", либо вообще не выводить его, но при этом обязательно добавить еще одно поле, в котором значение строки "Групповая операция" должно быть "Группировка". В нашем примере, как видно из рис. 7.16, мы выбрали значение "Заголовки строк".

Рис. 7.16.

    Для поля key_customer выбираем пару значений соответственно "Группировка/Заголовки столбцов". Поле summa будет давать нам информацию о сумме покупок по конкретному счету, поэтому групповой операцией для него станет функция агрегирования SUM(), а в перекрестной таблице оно будет представлять собой искомое значение (см. рис. 7.16).
    В итоге у нас получится следующий результат.

TRANSFORM SUM(Account.summa) AS Sum_summa

SELECT Account.account

FROM Account

GROUP BY Account.account

PIVOT Account.key_customer;

    До сих пор мы рассматривали типы запросов, которые легко создать как визуально, так и в окне режима SQL. Но есть несколько типов запросов, которые невозможно создать с помощью Конструктора. В меню Запросы есть пункт Запрос SQL, который дает нам доступ к трем командам.
    Команда объединения позволяет нам создавать запрос, который будет создавать результирующий набор данных на основе результатов двух запросов. Например:

SELECt first,second

FROM tableone

UNION

SELECT first,second

FROM tabletwo

    В вышеприведенном примере две таблицы имеют поля одинакового типа (непременное условие), поэтому поочередное выполнение двух запросов и объединение их результатов с помощью оператора UNION дает нам вполне легкое решение, которое и выполняется достаточно быстро. В случае отсутствия поля подходящего типа в одной из таблиц запрос можно переписать следующим образом (предполагается, что поле second имеет тип Long):

SELECT first,second

From tableone

UNION

SELECT first,0

FROM tabletwo

    Как видите, достаточно просто подставить константу подходящего типа.
    Следующую команду - К серверу (в англоязычной версии Pass-Through) мы рассмотрим в главе 8.
    Команда Управление позволяет нам создавать SQL выражения, которые относятся к DDL разделу SQL.
    С помощью Jet SQL нельзя создавать базы данных, но можно создавать таблицы и индексы, а также изменять структуры таблиц и удалять таблицы и индексы.
    До сих пор мы использовали визуальные средства и режим SQL для создания запросов. Но очень часто, особенно если вы обращаетесь к базе данных Access из среды приложения, написанного на другом языке, например на Visual Basic или Visual C++, вам придется использовать объекты доступа к данным (DAO).
    Типичный запрос с использованием этих объектов выглядит следующим образом:

Dim db As Database, qd AS QueryDef, mySQL As String

Set db = DBEngine.Workspaces(0).Databases(0)

mySQL = "SELECT * FROM Account WHERE account=102"

Set qd =db.CreateQueryDef("Mywritebyhandquery",mySQL)

    После выполнения данной процедуры появится запрос, который будет храниться в той базе данных, для которой эта процедура будет текущей. В целом создание запросов с помощью DAO мало отличается от написания запросов с помощью SQL режима. Главной составной частью является второй аргумент метода CreateQueryDef - строка SQL.
    Для того чтобы обратиться к ранее созданному запросу в программе, можно обратиться к коллекции QueryDefs. Вы можете переписать свойство SQL объекта типа QueryDef, то есть фактически переписать содержание запроса, узнать дату последней модификации запроса и т. д. При этом мы просто обращаемся к свойствам и методам объекта QueryDef. В следующем примере для уже существующего объекта переписывается свойство SQL, и с помощью окна сообщений выводится дата последней модификации этого объекта, которая, естественно, будет совпадать с текущей системной датой.

Dim db As DATABASE, qd As QueryDef, mySQL As String

Set db = DBEngine.Workspaces(0).Databases(0)

Set qd = db.QueryDefs("Simplequery")

qd.SQL = "SELECT * FROM account WHERE date_write >>_

" _ & "#01/06/96#"

MsgBox(Str(qd.LastUpdated))

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

Dim db As DATABASE, qd As QueryDef, mySQL As String

Set db = DBEngine.Workspaces(0).Databases(0)

Set qd = db.CreateQueryDef("DLquery", _

"CREATE TABLE _ fromquery (mama TEXT(20), _

slava LONG)")

qd.Execute

    Последним способом, который позволяет нам выполнять запросы в Access, являются макрокоманда RunSQL или метод RunSQL объекта DoCmd. В табл. 7.4 представлены все виды запросов, которые мы можем запустить с помощью метода RunSQL и ее макроэквивалента RunSQL. В дальнейшем мы будем говорить только о методе RunSQL.

DoCmd.RunSQL инструкцияSQL

    Инструкция SQL - это строковое выражение, которое содержит правильное SQL выражение. При этом вы можете обращаться к другой базе данных. Максимальная длина строки - 32768 символов.
    В следующем примере требуется наличие формы с названием "Моя форма", которая имеет следующие объекты:

• Несвязанное текстовое поле txtField1.
• Несвязанное текстовое поле txtField2.
• Командная кнопка cmbInsert3.

    В методе для события Click запишем следующий код, предполагая, что данные, которые мы занесем в поля txtField1 и txtField2, должны попасть в новую запись таблицы "Моя Таблица":

Sub cmbInsert3_Click()

     Dim SQLstr As String, SQLstr1 As String

     SQLstr = "INSERT INTO [Моя Таблица] (Фамилия, Имя) "

     SQLstr1 = "VALUES ([txtField1],[txtField2])"

     DoCmd.RunSQL SQLstr & SQLstr1

End Sub

    Как вы видите, нам совершенно не нужно иметь отдельный запрос добавления, достаточно написать четыре строчки кода. Теперь пользователь будет добавлять новые записи там, где это выглядит вполне логично, а не экспериментируя с запросами на вкладке Запросы в контейнере базы данных. Данные строчки являются не более, чем примером, никто не пытается призывать вас отказаться от обычного ввода данных посредством использования связанных полей в форме и соответственно от всех средств, которые предоставляет это мощное средство работы с данными.
    В этой главе мы пытались показать, насколько удобно использовать SQL - структурный язык запросов, который на текущий момент является самым распространенным средством работы с базами данных. Бесспорно и в Visual FoxPro и в Access вы можете при желании обойтись без SQL, но будет ли это разумно? Чем больше вы используете SQL, тем легче вам переходить с одной СУБД на другую. А многие средства быстрой разработки приложений, такие как Delphi или Visual Basic, просто немыслимо использовать без SQL. В этой главе мы не стали рассматривать использование SQL в Visual Basic, но можете считать, что вы уже знаете достаточно, для того чтобы работать с этим средством разработки приложений. Visual Basic использует DAO для работы с базами данных. Следовательно, вы можете использовать объект QueryDef для работы. Можете не откладывать на завтра, а начинать прямо сейчас. Visual Basic, начиная с версии 4.0, имеет еще более гибкое и, в некотором плане, работающее быстрее с данными средство - RDO, но об этом речь впереди.
    В следующей главе мы расширим набор SQL команд, а также рассмотрим вопросы работы с внешними данными.

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

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

    Как правило, данные необходимо использовать не на одном компьютере. Обычно с ними работает группа пользователей. Способ обмена данными посредством дискеты, безусловно, значительно облегчает работу программиста, однако такой способ организации выглядит в настоящее время скорее вызовом, чем правилом. Тем более, что сама работа в сети экономит массу средств и самое главное - время, которое, как вам уже известно, все больше становится эквивалентом денег.
    Visual FoxPro обеспечивает работу с данными с помощью монопольного или раздельного доступа к данным, опций блокировки, буферизации таблиц и записей, а также поддержки транзакций. Помимо этого Visual FoxPro автоматизирует многоразовую установку среды окружения посредством сессий данных.
    В многопользовательских системах необходима уверенность, что только один пользователь может заносить данные в файл или запись в текущий момент. Один из способов добиться этого - открыть таблицу с монопольным доступом. Ни один пользователь или приложение не смогут после этого ни читать данные из этого файла, ни писать в него. Приложение выведет сообщение об ошибке, если какая-нибудь таблица, необходимая для монопольного доступа, уже открыта.
    После выполнения команды SET EXCLUSIVE ON все таблицы будут открываться в монопольном режиме. Если необходимо проверить текущую установку, то используйте функцию SET() с аргументом EXCLUSIVE следующим образом:

? SET("EXCLUSIVE")

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

USE myfile EXCLUSIVE

    В дополнение к монопольному или многопользовательскому режиму доступа к файлам вы можете управлять доступом к таблицам с помощью блокировок как файлов, так и записей. Блокировки могут обеспечить как долгосрочный, так и краткосрочный контроль над данными. Вдобавок, блокировки предотвратят одновременную модификацию двумя или более пользователями одной и той же записи или таблицы. Существуют два типа блокировок: автоматические и ручные.
    Если выполняемая команда требует блокировки таблицы или записи и запись или таблица еще не заблокированы, Visual FoxPro автоматически пытается совершить блокировку, выполняет команду и снимает блокировку. Команды могут блокировать запись, целую таблицу или заголовок таблицы. Когда заблокирована запись, то другие пользователи могут добавлять, удалять и изменять другие записи, кроме заблокированной. Блокировка таблицы предотвращает любые изменения другими пользователями в таблице. Блокировка заголовка занимает промежуточное положение, другие пользователи могут изменять записи, но не могут удалять и добавлять записи.
    В табл. 7.5 приводятся команды, которые осуществляют автоматическую блокировку, и указано, какой вид блокировки они выполняют.

    Иногда бывает необходимо применить ручную блокировку. Visual FoxPro блокирует текущую таблицу с помощью функции FLOCK(). В отличие от команды SET EXCLUSIVE ON, которая предотвращает любой доступ к таблице со стороны других пользователей, FLOCK() блокирует таблицу, оставляя ее доступной для чтения. Остальные пользователи могут открывать таблицу и просматривать записи, даже не задумываясь о том, что она заблокирована. FLOCK() блокирует текущую таблицу и возвращает .T., если блокировка прошла успешно.
    Если в качестве аргумента функции вы укажете псевдоним или номер рабочей области, то у вас появится возможность заблокировать таблицу в другой, не текущей рабочей области. Например: FLOCK('1') или FLOCK('Account'). Эта особенность очень полезна для блокировки связанных таблиц. RLOCK() и LOCK() блокируют текущую запись и возвращают .T., если блокировка завершилась успешно.
    Автоматические блокировки удерживаются внутри транзакции до тех пор, пока не произойдут запись или откат на самом верхнем уровне вложения. Ручные блокировки остаются в действии и после завершения транзакции. Единственный способ освободить запись - это снять блокировку вручную. В следующем примере блокируются первые четыре записи:

OPEN DATABASE "auto_store"

SET REPROCESS TO 3 AUTOMATIC

STORE '1,2,3,4' TO cRecList

cOldExcl=SET("EXCLUSIVE")

SET EXCLUSIVE OFF

USE model

?LOCK(cReclist,'model')

UNLOCK IN model

SET EXCLUSIVE &cRecList

    Буферизация защищает данные во время изменений. Буфер автоматически тестирует, блокирует и освобождает записи и таблицы. Существуют два типа буферизации: записи и таблицы, которые защищают такие операции, как модификация данных и их поддержка на уровне одной или нескольких записей.
    При использовании буферизации Visual FoxPro копирует запись в память или на диск. Первоначальная запись все еще остается доступной для других пользователей. Когда указатель записи перемещается или совершается попытка ее модификации программным путем, Visual FoxPro пытается заблокировать запись, проверяет, что другой пользователь не сделал никаких изменений, и затем записывает изменения на диск. Хорошо при этом иметь задействованный обработчик ошибок для разрешения конфликтов, которые могут случиться при попытке записать изменения в таблицу. Буферизация записи отличается от буферизации таблицы тем, что при первом типе изменения записываются в таблицу либо при перемещении указателя, либо при использовании функции TABLEUPDATE(). При буферизации таблицы изменения записываются только после использования функции TABLEUPDATE(). Буферизация устанавливается с помощью функции CURSORSETPROP(). После установки буферизация она остается в действии до ее отключения с помощью той же функции CURSORSETPROP(), либо до закрытия таблицы.
    Режимы блокировки, используемые с буферизацией, определяют, когда записи блокируются и как блокировка с них снимается. Существуют два режима блокировки: пессимистический и оптимистический.
    Пессимистическая блокировка препятствует доступу других пользователей в многопользовательской среде к записи или таблице во время их редактирования. Пессимистический режим блокировки наиболее надежный для изменения индивидуальных записей, но он может замедлить пользовательские операции.
    Оптимистическая блокировка - более производительный способ редактирования записей, так как блокировка устанавливается только на момент внесения изменений на диск, что значительно уменьшает время, в течение которого один пользователь монополизирует систему в многопользовательском режиме. Когда вы используете буферизацию для внешних таблиц, Visual FoxPro устанавливает оптимистическую блокировку.
    Для установки пессимистической блокировки записи используйте функцию CURSORSETPROP() и следующие параметры:

= CURSORSETPROP("Buffering",2)

    Visual FoxPro пытается заблокировать запись, на которой установлен указатель. Если блокировка завершилась успешно, Visual FoxPro помещает запись в буфер и разрешает редактирование. Когда вы перемещаете указатель записи или выполняете функцию TABLEUPDATE(), Visual FoxPro записывает данные из буфера в таблицу.
    Для установки оптимистической буферизации записи используйте функцию CURSORSETPROP() и следующие параметры:

= CURSORSETPROP("Buffering",3)

    Когда вы перемещаете указатель записи или выполняете функцию TABLEUPDATE(), Visual FoxPro пытается блокировать запись. Если блокировка успешно завершена, Visual FoxPro сравнивает текущее значение записи на диске со значением перед началом буферизации. Если эти значения одинаковые, изменения записываются в таблицу, если же значения разные, то Visual FoxPro генерирует ошибку.
    Если вы хотите установить пессимистическую блокировку нескольких записей, то используйте функцию CURSORSETPROP() следующим образом:

= CURSORSETPROP("Buffering",4)

    В этом случае Visual FoxPro пытается блокировать запись, на которой находится указатель. При успешном завершении блокировки Visual FoxPro помещает запись в буфер и разрешает редактирование. Таким образом вы можете заблокировать несколько записей и все их, естественно, поместить в буфер. После того, как вы используете функцию TABLEUPDATE(), все данные из буфера будут переписаны на диск.
    Вас может смутить то, что буферизация как правило в документации и литературе упоминается как "буферизации таблицы", а блокируется не таблица, а несколько редактируемых записей. Для проверки вышесказанного используйте возможность Visual FoxPro запускать одну и ту же форму несколько раз в разных сессиях данных. При этом каждую сессию данных вы можете рассматривать как отдельного сетевого пользователя.
    Первым делом создайте форму, поместите в нее объект Grid. Теперь для формы установите значение свойства BufferMode равным 1 (pessimistic), а значение свойства DataSession равным 2 (private data session), как это показано на рис. 7.17.

Рис. 7.17.

    После сохранения формы запустите ее из командной строки вначале с помощью следующей команды:

DO FORM justmadeformforbufferingvalidate NAME f1

    А затем запустите эту же форму, но уже с другим именем

DO FORM justmadeformforbufferingvalidate NAME f2

    В диалоговом окне View Window с помощью списка CurrentSession можете проверить, что у вас на данный момент загружено несколько сессий данных, а точнее, одна плюс количество экземпляров нашей формы. Если вы таким же способом загрузите еще одну форму, то количество сессий данных, как вы уже догадались, увеличится на единицу. В окне View Window, переключившись в одну из сессий данных, связанных с одним из экземпляров нашей формы, перейдите в рабочую область, в которой открыта таблица, данные из которой отображаются с помощью объекта Grid. Нажав на кнопку Properties, вы можете увидеть, что буферизация для данной таблицы действительно пессимистическая и множественная (рис. 7.18).

Рис. 7.18.

    Теперь в первом экземпляре формы отредактируйте, например, первую и вторую запись, а во втором - третью и четвертую. Как видите, таблицы не заблокированы, но в то же время при попытке отредактировать первую или вторую запись во втором экземпляре вы получите сообщение о том, что запись в данный момент редактируется другим пользователем. И так будет продолжаться до тех пор, пока вы не примените функцию TABLEUPDATE() в сессии данных, связанной с первым экземпляром формы, и в рабочей области, где открыта ваша таблица.
    Для установки оптимистической блокировки нескольких записей используйте следующее выражение:

= CURSORSETPROP("Buffering",5)

    В этом случае Visual FoxPro записывает данные в буфер и разрешает редактирование до тех пор, пока вы не выполните функцию TABLEUPDATE(). После этого Visual FoxPro совершает следующие действия над каждой записью в буфере:

1. Пытается блокировать каждую отредактированную запись.
2. После успешной блокировки сравнивает текущее значение на диске со значением до начала буферизации.
3. Записывает данные на диск, если сравнение прошло успешно.
4. Генерирует ошибку, если данные различаются.

    Для доступа к данным в буфере можно использовать функцию RECNO(). Номер записи из таблицы используется для обращения к ее аналогу в буфере. Если записи добавляются, то при установленной буферизации они вначале попадают в буфер, и номер вновь добавленной записи отрицательный. Отрицательные числа последовательно нарастают по абсолютному значению по мере добавления записей. В табл. 7.6 представлена ситуация, которая может сложиться при добавлении двух записей.

    Для того чтобы удалить запись, которая была добавлена, но не записана на диск, вам следует использовать функцию TABLEREVERT(), при этом помните, что ее использование с аргументом .T. очистит буфер полностью, а с аргументом .F. - только от текущей записи. Если вы не выполните функцию TABLEREVERT() раньше, чем функцию TABLEUPDATE(), то все записи, даже помеченные на удаление, запишутся на диск и при этом будут помечены на удаление.
    Мы уже не раз упоминали и использовали в примерах функции TABLEUPDATE() и TABLEREVERT(). Эти функции можно использовать при установлении буферизации. В противном случае появится системное сообщение о том, что без буферизации их использовать нельзя. Посмотрим более подробно на синтаксис этих функций:

TABLEUPDATE([lAllRows] [,lForce]] [, cTableAlias

| nWorkArea])

    Первый аргумент lAllRows принимает значения .T. или .F.. Если значение аргумента равно .T. и установлена табличная буферизация, то на диск запишутся изменения во всех отредактированных записях, в противном случае запишутся только изменения из текущей записи.
    Второй аргумент lForce, также принимающий логическое значение, определяет, как относиться к изменениям других пользователей. Если вы вызываете функцию TABLEUPDATE() с аргументом lForce, равным .F., Visual FoxPro сгенерирует ошибку, как только найдет запись, в которой были сделаны изменения другим пользователем. Вам остается только решить, каким образом поступить с этой записью. В случае, если вы используете функцию TABLEUPDATE() со вторым аргументом, равным .T., то все изменения, сделанные другим пользователем или пользователями, будут переписаны.
    Если вы работаете с таблицей в текущей рабочей области, то третий аргумент cTableAlias или nWorkArea можете не указывать. В противном случае надо указать либо псевдоним, либо номер рабочей области таблицы, которую вы редактируете.
    Функция TABLEREVERT() выполняет обратное по отношению к TABLEUPDATE() действие - она очищает буфер без записи изменений на диск.

TABLEREVERT([lAllRows] [, cTableAlias | nWorkArea])

    При этом если первый аргумент lAllRows равен .T., то очищается весь буфер, если же он равен .F., то буфер очищается только от изменений для текущей таблицы.
    Следующие четыре функции помогают находить выход из ситуаций, в которые может попасть приложение, когда велика вероятность одновременного редактирования одних и тех же записей разными пользователями.

    Это функции OLDVAL(), CURVAL(), GETNEXTMODIFIED(), GETFLDSTATE(). Функция OLDVAL() имеет следующий синтаксис:

OLDVAL(cExpression [, cTableAlias | nWorkArea])

    Функция OLDVAL() возвращает первоначальное значение поля таблицы для текущей записи при установленной буферизации. При этом надо упомянуть, что если таблица в базе данных или курсор имеют правила проверки ввода, то устанавливать буферизацию не обязательно.
    Если указатель записи переместится на другую запись, когда установлена буферизация записи, или будет выполнена функция TABLEUPDATE() для записи изменений на диск, или будут произведены какие-либо другие действия, которые ведут к модификации данных, первоначальное значение больше доступно не будет.
    Тип значения, которое вернет функция OLDVAL(), соответствует типу значения аргумента cExpression. Например:

OPEN DATABASE auto_store

USE account

=CURSORSETPROP("Buffering",5)

GO 3

REPLACE account WITH 203

?OLDVAL("account")

*** Будет возвращено значение 103

=TABLEUPDATE(.t.)

?OLDVAL("account")

*** Будет возвращено значение 203

CURVAL(cExpression [, cTableAlias | nWorkArea])

    Функция CURVAL() возвращает значение поля прямо с диска для таблицы или внешнего источника данных.
    Значения полей, возвращаемые функциями CURVAL() и OLDVAL(), можно сравнивать, для того чтобы выяснить, изменял ли другой пользователь в сети значения поля, пока проводилось редактирование данного поля. Естественно, что CURVAL() и OLDVAL() возвращают разные значения только при использовании оптимистической буферизации записи или таблицы.
    Функция CURVAL() возвращает значение для текущего поля, и тип возвращаемого значения определяется типом выражения cExpression.
    В качестве примера слегка увеличим количество объектов в форме JUSTMADEFORMFORBUFFERINGVALIDATE. Добавьте два объекта TextBox, не связанных ни с каким полем, и две командные кнопки. Для события AfterRowColChange объекта Grid1 напишите следующий код:

LPARAMETERS nColIndex

THisForm.Text1.Value =; {dt>OLDVAL(ThisForm.Grid1.Columns(nColIndex).ControlSource)

THisForm.Text2.Value =; {dt>CURVAL(ThisForm.Grid1.Columns(nColIndex).ControlSource)

=TABLEUPDATE(.F.)

ThisForm.Grid1.SetFocus

    Вторая кнопка будет отменять изменения, то есть уничтожать содержимое буфера для текущей записи в текущем образце формы:

=TABLEREVERT(.F.)

ThisForm.Grid1.SetFocus

    Теперь можете запустить эту форму несколько раз, проследив при этом, чтобы ее свойство BufferMode было равно Optimistic, и экспериментировать со значениями в первом и втором текстовом полях. Скорее всего, вы еще раз убедились, насколько хорошо иметь возможность создавать многопользовательскую среду на одном компьютере.
    На рис. 7.19 представлена ситуация, когда первоначальное значение поля RRR для третьей записи Greece в одном экземпляре формы было изменено на Israel, а в другом на Cyprus.

Рис. 7.19.

    Так как значение Israel было записано на диск раньше, то теперь в самой правой форме мы имеем разные значения, возвращаемые функциями OLDVAL() и CURVAL().
    Функция GETFLDSTATE() возвращает числовое значение, указывающее, было ли отредактировано значение поля в таблице или курсоре, или была ли запись добавлена, или был ли изменен статус удаления текущий записи. Если вы не указываете псевдоним или номер рабочей области, то функция GETFLDSTATE() возвращает значение для поля в текущей таблице или курсоре. Эта функция имеет следующий синтаксис:

GETFLDSTATE(cFieldName | nFieldNumber [, cTableAlias | nWorkArea])

    Следует отметить, что GETFLDSTATE() только определяет, изменялся ли статус удаления для записи. Например, если вы пометите запись для удаления, а затем выполните команду RECALL, то функция GETFLDSTATE() укажет, что статус деления изменился, даже если вы вернули его в перовоначальное положение. Поэтому мы рекомендуем использовать функцию DELETED() для определения статуса удаления.
    Функция GETFLDSTATE() может употребляться и при отсутствии буферизации. Значения, которые возвращает эта функция, приведены в табл. 7.7.

    Функция GETNEXTMODIFIED() возвращает номер следующей измененной записи в буферизованой таблице или курсоре:

GETNEXTMODIFIED(nRecordNumber [, cTableAlias | nWorkArea])

    Параметр nRecordNumber указывает номер записи, начиная с которой необходимо искать запись, подвергнутую изменениям.
    Функция GETNEXTMODIFIED() возвращает 0, если нет модифицированных записей после записи, которую вы указали. Запись рассматривается как измененная, если содержимое хоть одного из полей было изменено (даже если было возвращено первоначальное значение) или был изменен статус удаления.
    Если какой-нибудь пользователь в сети сделает изменения в буферизованной таблице, любые изменения, которые вы попытаетесь записать на диск с помощью функции TABLEUPDATE(), приведут к конфликтной ситуации, кроме, конечно, случая, когда вы используете функцию TABLEUPDATE() с двумя аргументами, равными .T. (=TABLEUPDATE(.T.)). Можно разрешить конфликтную ситуацию с помощью функций OLDVAL() и CURVAL(). Функция CURVAL() возвратит значение на диске в текущий момент, а OLDVAL() - значение поля записи в момент, когда началась буферизация.
    В качестве примера внесем еще несколько изменений в форму JUSTMADEFORMFORBUFFERVALIDATING. Добавьте кнопку, к примеру, с заголовком "Все-таки изменить" и сделайте ее изначально недоступной. Код для события Click кнопки "Сохранить" перепишите следующим образом:

GO GETNEXTMODIFIED(0)

k=TABLEUPDATE(.F.)

IF k = .F.

     This.Parent.Command3.Enabled=.T.

ENDIF

ThisForm.Grid1.SetFocus

    Для кода события Click кнопки "Все-таки изменить" напишите следующий код:

=TABLEUPDATE(.F.,.T.)

This.Enabled=.F.

ThisForm.Grid1.SetFocus

    Для события Click кнопки "Отменить" внесите следующие несущественные изменения:

=TABLEREVERT(.F.)

ThisForm.Command3.Enabled=.F.

ThisForm.Grid1.SetFocus

    В итоге у вас получится форма, которая будет работать следующим образом. Когда вы внесете изменения в одни и те же записи и в первом и во втором образце формы, то после нажатия кнопки "Сохранить" вы попадете на первую измененную запись. Если никаких проблем не возникнет, то есть значение CURVAL() и OLDVAL() совпадет, то значение будет сброшено из буфера на диск. В случае возникновения проблем, если переменная k примет значение .F., то станет доступна кнопка "Все-таки изменить" и при этом в текстовых полях Text1 и Text2 вы увидите и значения, возвращаемые функциями OLDVAL() и CURVAL(). То есть у вас есть вся информация для принятия решения. При особом желании вы можете модифицировать вашу форму так, что будет возможность при конфликтных ситуациях восстанавливать значение, которое имело поле до начала буферизации, то есть то значение, которое возвратит функция OLDVAL().

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

    Транзакции обеспечивают наибольшую защиту существующим данным. Транзакции могут использоваться отдельно, либо вместе с буферизацией записи или таблицы. Только таблицы, которые находятся в базе данных, могут воспользоваться транзакциями.
    Visual FoxPro предоставляет три команды, которые обеспечивают контроль за транзакциями:

• BEGIN TRANSACTION - предназначена для инициализации транзакции.
• ROLLBACK - выполняет откат всех изменений, сделанных после последней команды BEGIN TRANSACTION.
• END TRANSACTION - блокирует записи, записывает на диск все изменения, сделанные после ближайшей команды BEGIN TRANSACTION, затем снимает блокировку с записей.

    Изменения в таблицах, в индексных файлах CDX и в полях примечаний таблиц, которые принадлежат базам данных, могут использовать команды транзакций. Транзакции могут работать только с полями таблиц или представлений, но не с переменными памяти и другими объектами.
    Изменения выполняются в конце транзакции. Транзакция кэширует изменения на диске или в памяти. Когда транзакция заканчивается, изменения записываются на диск. Если изменения не могут быть записаны на диск, вся транзакция целиком откатывается и ни одно изменение не завершается. Транзакции обеспечивают встроенную систему, которая защищает базу данных от разрушения с помощью отмены всех изменений, в случае если по каким-то причинам изменения не могут быть записаны на диск. Транзакции откатываются путем возвращения всех измененных записей и индексов в первоначальное состояние.
    Для большей надежности защиты данных транзакции следует использовать вместе с буферизацией для предотвращения потери данных.
    Транзакции могут иметь пять уровней вложенности. Если вы попытаетесь добавить шестой уровень, то будет сгенерирована ошибка.
    Когда вы изменяете записи в базе данных, которая является частью транзакции, другие пользователи в сети не могут иметь доступа (чтения и запись) к этим записям, пока не завершится транзакция.
    Если другие пользователи в сети пытаются получить доступ к записям, которые вы модифицируете, им придется ждать завершения транзакции. Они будут получать сообщение "Record not available<193>Please Wait" ("Запись не доступна. Подождите, пожалуйста"), пока записи не станут доступны. Поэтому необходимо делать транзакции как можно меньше по длине или проводить транзакции в то время, когда другие пользователи не нуждаются в доступе к данным.
    Команда END TRANSACTION сохраняет все изменения, сделанные в таблицах, индексных файлах CDX и полях примечаний, и заканчивает транзакцию. Все изменения, сделанные в базе данных между предыдущей командой BEGIN TRANSACTION и END TRANSACTION, завершаются. Если транзакция является транзакцией первого уровня или единственной транзакцией (то есть транзакцией без вложенности), то изменения записываются на диск.
    Если транзакция вложенная, то END TRANSACTION переводит все кэшированные изменения на следующий уровень. Вложенные транзакции обладают потенциальной возможностью переписать изменения, сделанные в транзакции на более высоком уровне.
    В случае если в это время завершается еще одна транзакция, команда END TRANSACTION сгенерирует ошибку. Имеет смысл вставить в цикл команду END TRANSACTION, которая будет работать до тех пор, пока транзакция не сможет завершиться.
    Для того чтобы сделать откат транзакции, которая началась с помощью команды BEGIN TRANSACTION, используйте команду ROLLBACK. Эта команда восстановит первоначальное состояние таблиц, индексов и полей примечаний.
    Когда вам необходимо выяснить, на каком уровне вложенности вы находитесь в процессе транзакции, используйте функцию TXNLEVEL(). Ниже приведен простейший пример ее использования:

OPEN DATA auto_store

USE Model

BEGIN TRANSACTION

??TXNLEVEL

**** Будет выведено значение 1, которое равняется

***** текущему уровню транзакции

     BEGIN TRANSACTION

?? TXNLEVEL

     **** Будет выведено значение 2, которое равняется

     ***** текущему уровню транзакции

     END TRANSACTION

END TRANSACTION

• Транзакции должны объявляться с помощью команды BEGIN TRANSACTION. Если будут выполняться команды END TRANSACTION или ROLLBACK без соответствующей команды BEGIN TRANSACTION, то будет сгенерирована ошибка.
• Транзакции действуют, пока не будут выполнены команды END TRANSACTION или ROLLBACK. Транзакции могут проходить через несколько процедур или функций. Если приложение заканчивается без команды END TRANSACTION, то выполняется команда ROLLBACK.
• Транзакции используют данные, кэшированные в буфере транзакции, а не данные на диске, для того чтобы использовать самые новые данные.
• Транзакции не могут переписать существующий индексный файл с помощью команды INDEX.
• Транзакции могут использоваться только с таблицами, принадлежащими базам данных.

    Если вы включили ручную блокировку таблицы или файла во время транзакции с помощью функций FLOCK() и RLOCK(), то необходимо обязательно снять блокировку. Команда END TRANSACTION не снимет блокировку.
    Во вложенных транзакциях команды ROLLBACK и END TRANSACTION работают с изменениями, которые произошли после последней команды BEGIN TRANSACTION.
    Изменения внутри вложенных транзакций не запишутся на диск, пока не завершится самый верхний уровень, то есть не будет выполнена самая последняя команда END TRANSACTION.
    Если транзакции выполняются над одними и теми же данными, то преимущество имеет то изменение, которое было выполнено последним, независимо от того, на каком уровне оно находится. Например:

USE Account

BEGIN TRANSACTION

BEGIN TRANSACTION

REPLACE count WITH 103 FOR count=203

     END TRANSACTION

     REPLACE count WITH 203 FOR count 103

END TRANSACTION

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

USE Account

BEGIN TRANSACTION

REPLACE count WITH 203 FOR count 103

BEGIN TRANSACTION

REPLACE count WITH 103 FOR count=203

END TRANSACTION

END TRANSACTION

    Теперь значение поля останется тем же, что и было - 103.

    Если локальная станция имеет достаточно места на жестком диске или достаточно RAM, то вы можете улучшить производительность, разместив временные файлы на локальном диске или на RAM диске. Перенаправление этих файлов на локальный диск или диск RAM увеличивает производительность за счет уменьшения обращения к сетевому диску. Вы можете указать альтернативное местонахождение для этих файлов, включив выражения EDITWORK, SORTWORK, PROGWORK и TMPFILES в ваш файл CONFIG.FPW.
    Если есть возможность отсортировать данные, то имеет смысл это сделать, так как работа с таблицами происходит быстрее, если у вас не включены индексы. То есть используйте поиск с помощью команды, а затем отключайте порядок индекса.
    Если есть возможность работать с какими-то файлами монопольно, то используйте эту возможность, так как при монопольном режиме доступ к таблицам осуществляется быстрее.
    Чтобы уменьшить вероятность попытки одновременного доступа к записи или таблице, сокращайте время блокировок, что можно осуществить, блокируя записи только при сохранении данных на диск, а не во время их редактирования. Оптимистическая буферизация обеспечивает вам кратчайшее время блокировки.

    Средства настройки оболочки Access позволяют установить режимы по умолчанию для открытия баз данных и таблиц. Для этого используется команда Параметры в меню Сервис. После того как появится диалоговое окно, показанное на рис. 7.20, необходимо перейти на вкладку Другие. После этого, используя набор кнопок "Блокировка по умолчанию", вы можете выбрать необходимый тип блокировки.

Рис. 7.20.

    При выборе кнопки "Отсутствует" вы сможете установить только нежесткую блокировку записи. С помощью такой блокировки все редактируемые записи доступны для других пользователей, но при этом, когда вы отредактируете запись и попытаетесь сохранить изменения, то столкнетесь с одним из двух вариантов. Первый, самый простой: кроме вас запись никто не редактировал и сделанные изменения заносятся на диск. Второй: после того как вы начали редактировать данные, кто-то успел изменить их раньше вас и записать изменения на диск. В последнем случае появится окно сообщения, в котором будет предложено три варианта выхода из сложившейся ситуации: игнорировать чужие изменения и записать на диск свои, скопировать измененную запись в буфер обмена, сохранить чужие изменения и отменить свои. Этот режим иногда называется "оптимистической" блокировкой.
    Если вы выберете блокировку изменяемой записи, то для других пользователей будет заблокирована запись, которую вы редактируете, и соседние с ней записи, так как Access устанавливает блокировку для страницы. Страницой для таблиц Access является набор записей размером в 2 килобайта. Эту блокировку иногда называют "пессимистической".
    При выборе блокировки всех записей остальные пользователи не смогут открыть таблицу.
    При этом следует обратить внимание на тот факт, что если вы работаете с данными с помощью формы, то можете установить для нее блокировки с помощью свойства RecordLocks, которые могут отличаться от установок по умолчанию для текущего сеанса работы Access. Эти типы блокировок строго соответствуют тем видам, которые вы устанавливаете с помощью диалогового окна Параметры.
    Свойство RecordLocks помимо форм присутствует еще у запросов и отчетов. При этом для отчетов нет возможности установить блокировку изменяемой записи, скорее всего потому, что отчет их изменять не может.
    В диалоговом окне Параметры можно установить режим по умолчанию для открытия баз данных. Но учтите, что режим доступа к базам данных можно выбирать при их открытии с помощью диалога открытия. Если базы данных открываются монопольно, то остальные пользователи не смогут открыть их, поэтому данный режим вряд ли подходит для сетевого использования.
    Помимо визуальных средств установки различных режимов блокировки, вы можете использовать объект Application для их изменения.
    В следующем примере с помощью метода SetOption объекта Application устанавливается пессимистическая блокировка (блокировка страницы), а с помощью метода GetOption и функции MSGBOX() выводится номер текущей блокировки.

Public Sub mygetoption()

     Dim mystr As String

     Application.SetOption "Блокировка по умолчанию", 2

     mystr = Application.GetOption("Блокировка по_ умолчанию")

     MsgBox (mystr)

End Sub

    Для форм, отчетов и запросов вы обращаетесь к свойству RecordLocks, но учтите, что для уже открытых экземпляров объектов на различных пользовательских машинах свойство не изменится. То есть, изменив свойство некоего объекта, вам попутно надо убедить пользователя закрыть и снова открыть объект, для того чтобы он смог воспользоваться всеми преимуществами нового значения. Пример изменения свойства Recordlocks для запроса:

Public Sub myqueryproperties()

Dim db As DATABASE, qd As QueryDef

Set db = DBEngine.Workspaces(0).Databases(0)

db.QueryDefs("Моя таблица query").Properties("recordLocks") = 2

End Sub

Public Sub ActiveFormRecordLocksChange

     Screen.ActiveForm.RecordLocks = 2

End Sub

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

1. Создайте функцию, которая будет содержать одну строчку: ActiveFormRecordLoc1ksChange, то есть вызов процедуры, которая у вас может называться по-другому.
2. Создайте макрос, выполняющий одно действие RunCode, аргументом которого будет вызов нашей функции, которая, к примеру, может называться fActiveFormLocksChange.
3. Создайте пользовательскую панель инструментов, в которую необходимо перетащить графическое изображение макроса из вкладки Макросы.

    Выполнив данную последовательность действий, вы получите пользовательскую панель инструментов с одной кнопкой, которая в режиме Конструктора будет устанавливать для вашей формы свойство RecordLocks равным значению 2, что соответствует блокировке изменяемой записи. Проявив фантазию, вы можете дополнить вашу панель инструментов более изощренными инструментами, но основа технологии их создания описана выше.
    Очевидно, что то же самое вы можете проделать и для отчетов.
    Для того чтобы вы были уверены, что все операции, которые вы хотите провести, были выполнены, используйте методы BeginTrans, CommitTrans и RollBack объекта Workspace.
    Метод BeginTrans начинает транзакцию. Под транзакцией подразумевается серия изменений, которые проводятся над данными и структурой базы данных. Если по какой-либо причине операции, входящие в текущую транзакцию, не могут быть завершены, то система возвращается в исходное состояние. При этом помните, что на рабочей станции должно быть достаточно места на диске, так как при выполнении транзакции вся информация об операциях в нее входящих заносится на диск.
    Транзакции должны завершаться с помощью обращения к методу CommitTrans. Транзакции могут быть вложенными, не забывайте, что для того, чтобы завершить транзакцию более высокого уровня, вначале необходимо завершить вложенные транзакции. Если по каким-либо причинам приложение не сможет обратиться к методу CommitTrans, то система вернется в первоначальное состояние.
    Количество вложенных транзакций в Access, так же как и в Visual FoxPro, не может превышать пяти. При этом обратите внимание на следующий факт. Метод CommitTrans для текущего объекта Workspaces делает все изменения необратимыми. В то же время, если транзакция вложенная, то откат транзакции на более высоком уровне приведет систему в первоначальное состояние.
    В нижеприведенном примере применяется транзакция для перехода на вторую запись и изменения значения для поля Фамилия. Перед завершением транзакции верхнего уровня предлагается принять решение: заносить изменения на диск или нет.

Sub ForceTrans()

     Dim db As DATABASE, wks As Workspace, rst As _ Recordset

     Dim otvet As Integer

     Set wks = DBEngine.Workspaces(0)

     Set db = wks.Databases(0)

     Set rst = db.OpenRecordset("Моя таблица", _ dbOpenDynaset)

     wks.BeginTrans

     wks.BeginTrans

     rst.MoveLast

     rst.AbsolutePosition = 2

     rst.Edit

     rst.ФАМИЛИЯ = "Макашарипов"

     rst.UPDATE

     wks.CommitTrans

     otvet = MsgBox("Изменить", vbYesNo + _ vbDefaultButton1, "Ваше решение")

     If otvet = vbYes Then

     wks.CommitTrans

     Else

wks.Rollback

     End If

End Sub

    Кроме вложенных транзакций в Access можно использовать параллельные транзакции. Эти транзакции действуют независимо друг от друга. Но при этом вам необходимо создать еще один объект типа Workspace. Соответственно каждая транзакция завершается независимо друг от друга.
    Следующий пример будет работать, только если установлен режим блокировки "Отсутствует". В данном примере обратите внимание на две последние строчки кода. Даже если вы поменяете эти строчки местами, ничего не изменится, то есть вторая транзакция не работает с данными, реально хранящимися на диске.

Public Sub multipletrans()

     Dim db As DATABASE, wks As Workspace, rst As _ Recordset

     Dim db1 As DATABASE, wks1 As Workspace, rst1 As _ Recordset

     Set wks = DBEngine.Workspaces(0)

     Set db = wks.Databases(0)

     Set rst = db.OpenRecordset("Моя таблица", _ dbOpenDynaset)

     Set wks1 = DBEngine.Workspaces(0)

     Set db1 = wks1.Databases(0)

     Set rst1 = db1.OpenRecordset("Моя таблица", _ dbOpenDynaset)

     wks.BeginTrans

     rst.FindFirst "[Фамилия]='Клинтон'"

     rst.Edit

     rst.ФАМИЛИЯ = "Доул"

     rst.UPDATE

     wks1.BeginTrans

     rst1.FindFirst "[Фамилия]='Доул'"

     rst1.Edit

     rst1.ФАМИЛИЯ = "Клинтон"

     rst1.UPDATE

     wks.CommitTrans

     wks1.CommitTrans

End Sub

    Транзакции являются глобальными в рамках объекта Workspace. Поэтому ваши транзакции могут охватывать несколько баз данных и, соответственно, все множество объектов, которые в них содержатся.
    Объекты типа Database и Recordset имеют свойство Transactions, которое может принимать значения True или False. От их значений зависит, сможете ли вы использовать методы BeginTrans, CommitTrans и Rollback для работы с этими объектами. Иногда имеет смысл проверять это свойство перед использованием вышеприведенных методов, особенно при работе с данными из присоединенных таблиц.