Базы данных на Delphi 7

 …

3. ПОИСК ДАННЫХ В БД

У компонента «Table» имеются специальные команды для поиска нужной записи, удовлетворяющей заданному условию. Наиболее удобные функции поиска – «Locate» и «Lookup». Если поиск для функции «Locate» будет произведен успешно, то найденная запись в БД станет текущей, иначе текущая запись не изменится и ошибка не возникнет. Функция «Lookup» не меняет номера текущей записи при поиске, но если информация не будет найдена, то возникнет ошибка. Функция «Lookup» обычно используется для считывания значения нужного поля после поиска нужной записи, а функция «Locate» для установки новой текущей позиции в БД.

Пример точного поиска (а не фрагмента) с учетом регистра букв:

if Table1.Locate( 'famil', 'Petrov', [] ) then s:=Table1 ['name'] else s:='???';

try s:=Table1.Lookup( 'famil', 'Petrov', ‘name’ ); except s:=’???’; end;

Пример точного поиска (а не фрагмента) без учета регистра букв:

if Table1.Locate( 'famil', 'petrov', [loCaseInsensitive] ) then s:= Table1 ['name'] else s:='???';

Пример поиска по фрагменту без учета регистра букв:

Table1.Locate( 'famil', 'P', [loPartialKey, loCaseInsensitive] );

Пример поиска по фрагменту с учетом регистра букв:

if Table1.Locate( 'Company; Phone', VarArrayOf ( ['Micro', '408-'] ), [loPartialKey]) then

s:=Table1['code'] else s:='???';

Пример точного поиска с учетом регистра букв:

try s:=Table1.Lookup ( 'Company; Phone', VarArrayOf ( ['Microsoft', '408-431-1000'] ), 'code' );

except s:=’???’; end;

ЛЕКЦИЯ № 6

1. РЕЛЯЦИОННЫЙ ДОСТУП К БАЗЕ ДАННЫХ

Компоненты BDE, рассмотренные раннее, позволяют получить доступ к базам данных по технологии, разработанной фирмой Borland под названием Borland Database Engine. Эта технология устарела и поставляется только для совместимости со старыми версиями. Не смотря на это, она хорошо работает со старыми типами баз данных, такими как Paradox и dBase.

На смену BDE технологии пришла DBExpress – это новая технология доступа к данным фирмы Borland. Она отличается большей гибкостью и хорошо подходит для программирования клиент – серверных приложений, использующих базы данных. Компоненты с одноимённой закладки хорошо использовать с базами данных, построенных по серверной технологии, например, Oracle, DB2 или MySQL.

ADO (Active Data Objects) – технология доступа к данным, разработанная корпорацией Microsoft. Очень хорошая технология, но ее рекомендуется использовать только с базами данных Microsoft, а именно MS Access или MS SQL Server. Её так же можно использовать, если имеется специфичный сервер баз данных, который может работать только через ODBC.

ODBC – это набор драйверов в операционной системе Windows для доступа к базам данных. Через программный интерфейс ODBC приложения могут получать доступ к информации из СУБД, основанных на языке SQL. Для просмотра и конфигурирования ODBC драйверов необходимо вызвать из меню Windows программу администрирования «Источники данных (ODBC)».

Технология ADO использует драйверы Windows ODBC для доступа к БД, поэтому драйвера DBE не требуются. Для работы с базами MS Access лучше всего использовать ODBC-драйвер «Microsoft Jet 4.0 OLE DB Provider.

Рис.1. Схема доступа к данным через ADO

Компонент “Query” является более мощным аналогом компонента «Table» и имеет практически те же свойства, события и методы, что и компонент «Table». Компонент “Query”, в отличие от компоненты «Table», использует для открытия таблицы данных не имя файла и каталога с таблицей, а так называемый SQL-текст с запросом. В тексте SQL-запроса с помощью специальных команд указывается, какой файл нужно открыть, по какому полю его отсортировать, какие поля и записи должны быть видимыми (условия отбора), а какие нет и др.

Компонент “Query” позволяет:

Открывать, просматривать данные и производить поиск в таблицах БД, подобно компоненту «Table»

Создавать виртуальные обобщенные (сцепленные) таблицы из нескольких таблиц данных во время работы программы

Выбирать и находить записи по сложным критериям отбора

Сортировать данные по любым полям без наличия индексных файлов

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

Текст SQL-запроса имеет следующий синтаксис:

SELECT [DISTINCT] * или <список_полей>

FROM <список_таблиц>

[WHERE <условие_отбора_записей>]

[ORDER BY <список_полей_для_сортировки>]

[GROUP BY <список_полей_для _ группировки>]

[HAVING <условие_группирования>]

[UNION <вложенный_оператор_SELECT>]

При написании команд в тексте SQL-запроса необходимо следовать той последовательности, которая указаны выше. Регистр символов в тексте запроса не важен. Рассмотрим использование компоненты “Query” и команд SQL-запроса на конкретных примерах.

2. СОЗДАНИЕ ПРОСТЕЙШЕЙ ПРОГРАММЫ ДЛЯ ДОСТУПА К БАЗАМ ДАННЫХ ЧЕРЕЗ ADO

Рис.2. Схема простейшей программы ADO

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

ADOConnection1 – компонент (из вкладки «ADO») связи с драйвером ODBC и базой данных. Под базой данных понимается настоящая база данных типа MS Access, в которой находится одна или несколько таблиц данных. База данных MS Access сохраняется на диске в виде одного файла с расширением «mdb»;

ADOTable1 – компонент связи с таблицей данных из базы данных. Данный компонент является аналогом компонента Table из BDE;

DBGrid1 – сетка для отображения и работы с таблицей данных;

DBNavigator1 – навигатор по таблице БД;

DataSource1 – компонент-посредник между компонентами отображения таблицы БД и компонентом ADOTable1.

Рис.3. Простейшая программа с ADO-компонентами

procedure TForm1.FormCreate(Sender: TObject);

{ Инициализация программы }

begin

  ADOConnection1.ConnectionString:='Data Source=db/Database.mdb'; // локальный каталог БД

  ADOConnection1.Provider:='Microsoft.Jet.OLEDB.4.0'; // Имя драйвера доступа к БД

  ADOConnection1.LoginPrompt:=false; // Отключить запрос имени и пароля доступа к БД

  ADOConnection1.Connected:=true; // Подключаемся к БД

  ADOTable1.TableName:='Справочник'; // Указываем таблицу БД

  ADOTable1.Connection:=ADOConnection1; // Указываем компонент связи с БД

  DataSource1.DataSet:=ADOTable1; // Связываем компоненты DataSource1 и ADOTable1

  DBGrid1.DataSource:=DataSource1; // Связываем компоненты DBGrid1 и DataSource1

  DBNavigator1.DataSource:=DataSource1; // Связываем компоненты DBNavigator1 и DataSource1

  ADOTable1.Open; // Открываем таблицу

end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);

{ Закрытие программы }

begin

  ADOConnection1.Connected:=false; // Отключаемся от БД

end;

procedure TForm1.Button1Click(Sender: TObject);

{ Обновление таблицы }

begin

    ADOTable1.Requery; // Обновляем данные из таблицы БД

end;

3. ОТКРЫТИЕ ТАБЛИЦЫ ДАННЫХ С ПОМОЩЬЮ КОМПОНЕНТЫ «TQUERY»

Рассмотрим процедуру, открывающую таблицу "c:\db\u2_fakul.dbf". Доступными будут все поля и все записи:

procedure TForm1.Button1Click(Sender: TObject);

begin

// Привязываем компонент Datasource1 к компоненту DBGrid1

  DBGrid1.DataSource:=Datasource1;

// Привязываем компонент Query1 к компоненту Datasource1

  DataSource1.DataSet:=Query1;

// Указываем каталог таблиц БД

  DataSource1.DataBaseName:=’ c:\student\db’;

  with query1 do begin

    Close; // Закрываем (на всякий случай) данные в компоненте Query1

    SQL.Clear; // Очищаем текст SQL-запроса

// Занесем в компонент query1 текст SQL-запроса

    SQL.Add('select *'); // даем команду выбрать все поля

    SQL.Add('from "u2_fakul.db"'); // указываем имя таблицы БД

    Open; // Открываем таблицу данных "c:\db\u2_fakul.db"

  end;

end;

Рассмотрим фрагмент процедуры, открывающий таблицу "c:\db\u2_fakul.dbf", причем записи будут доступные все, а поля только с именами «name» и «kod_fakul». Все записи отсортируются по полю «name» в возрастающем порядке.

with query1 do begin

    Close; SQL.Clear;

    SQL.Add('select name, kod_fakul');

    SQL.Add('from "c:\db\u2_fakul.dbf"');

    SQL.Add('order by name');

    Open;

  end;

Рассмотрим фрагмент процедуры, открывающий таблицу "c:\db\u2_fakul.dbf", причем записи будут доступные все, а поля только с именами «name» и «kod_fakul». Все записи отсортируются по полю «kod_fakul» в возрастающем порядке и по полю «name» в убывающем порядке. В тексте SQL-запроса используются две строковые переменные s1 и s2.

var s1,s2: string;

begin

with query1 do begin

    s1:='name, kod_fakul'; s2:='c:\db\u2_fakul.dbf';

    Close; SQL.Clear;

    SQL.Add('select '+s1); SQL.Add('from "'+s2+'"');

    SQL.Add('order by kod_fakul, name DESC');

    Open;

  end;

end;

После открытия данных командой «Open» текст SQL-запроса не имеет значения, и его изменение не изменит отображаемых компонентом данных. Для активизации нового текста SQL-запроса необходимо закрыть данные компонента “Query” командой «Close», после чего задать новый текст запроса и открыть данные командой «Open».

Программирование с использованием SQL-запросов является наиболее передовой технологией работы с базами данных. Данная технология называется реляционным способом доступа к базе данных. Непосредственный доступ к базе данных через компонент «Table» называется навигационным способом. Навигационный способ является устаревшим, т.к. он рассчитан на использование локальных базам данных без сетевой поддержки.

При работе с распределенными в сети (коллективными) базами данных используется только реляционный способ, т.к. только он позволяет предотвращать сетевые коллизии при одновременном доступе нескольких пользователей к базе данных, и существенно разгружает сетевой трафик. Поэтому рекомендуется использовать компонент “Query” вместо компоненты «Table», хотя нужно отметить, что навигационный способ обычно работает быстрей, чем реляционный.

Использование реляционного способа доступа к БД через компоненту “Query” позволяет не только легко производить сложнейшую обработку нескольких взаимосвязанных таблиц данных, но и производить модификацию данных (вставлять новые записи, производить групповое удаление данных, делать каскадную замену информации и др.).

Пример отбора записей по значениям символьного поля:

  SELECT Name FROM Pers WHERE Post=’Менеджер’

     // А лучше так:

  SELECT Name FROM Pers WHERE UPPER(TRIM(Post))=’МЕНЕДЖЕР’

Использование псевдонимов:

  SELECT P.Name FROM “People.db” As P

Пример сцепления двух таблиц:

SELECT p.fio,p.tel

FROM “tel.db” as t, “people.db” as p

WHERE t.code_p =p.code_p;

ЛЕКЦИЯ № 7

1. ОСНОВНЫЕ СОБЫТИЯ КОМПОНЕНТ ДОСТУПА К ТАБЛИЦАМ ДАННЫХ

Компонент “Table” имеет множество событий, которые возникают при любой работе с базой данных, будь то открытие БД, закрытие, переход на другую запись, удаление записи и т.д. Данные события могут возникнуть в одинаковой степени при работе с любым визуальным компонентом, связанным с базой данных. Основные события компонента следующие:

Имя событияСобытие возникаетAfterOpen / BeforeOpenПосле / перед открытием базы данных (например, для метода Open)AfterClose / BeforeCloseПосле / перед закрытием базы данных (например, для метода Close)AfterScroll / BeforeScrollПосле / перед переходом на другую запись в БД (например, для метода Next)AfterEdit / BeforeEditПосле / перед началом редактирования текущей записи (например, для метода Edit)OnEditErrorВ случаи возникновения ошибки при попытке начать редактирование текущей записи (например, для метода Edit)AfterInsert / BeforeInsert / OnNewRecordПосле / перед / в процессе вставки или добавления в БД новой записи (например, для метода Insert)AfterPost / BeforePostПосле / перед сохранением сделанных изменений в текущей записи (например, для метода Post)OnPostErrorВ случаи возникновения ошибки при сохранении сделанных изменений в текущей записи (например, для метода Post)AfterDelete / BeforeDeleteПосле / перед удалением текущей записи (например, для метода Delete)OnDeleteErrorВ случаи возникновения ошибки при удалении текущей записи (например, для метода Delete)AfterCancel / BeforeCancelПосле / перед отменой сделанных изменений в текущей записи (например, для метода Cancel)

Контроль ввода данных перед сохранением записи:

procedure Table_FakulBeforePost(DataSet: TDataSet);

var s: string;

begin

  try s:=table1['name']; except s:=''; end;

  if s='' then begin

       MessageDlg('Укажите имя!', mtInformation, [mbOK],0); abort;

   end;

end;

ЛЕКЦИЯ № 8

1. ПРИМЕРЫ МЕТОДОВ РАБОТЫ С ДАННЫМИ БД

1. Использование закладок:

procedure TForm1.Button1Click(Sender: TObject);

var SaveP: TBookmark;

begin

try

  SaveP := Table1.GetBookmark;

…………………

  Table1.GotoBookmark(SaveP);

  Table1.FreeBookmark(SaveP);

except end;

end;

2. Показ количества записей в БД:

procedure TForm1.Timer1Timer(Sender: TObject);

begin

  try

    StaticText1.caption:='Всего доступно записей: '+inttostr(Table1.recordcount);

  except; end;

  Application.ProcessMessages;

end;

3. Изменение значений поля через SQL-запрос:

   UPDATE Pers.db

       SET Salary = Salary + 50

        WHERE Salary < 500 // Если сотрудник имеет оклад менее 500 руб., то его оклад увелич. на 50 руб.

………

   UPDATE Store.db

         SET S_Price = S_Price * 1.1 // Цена всех товаров увеличивается на 10 %

4. Добавление записи через SQL-запрос:

    INSERT INTO Store.db

          (Name, Price) VALUES (“Морковь”, 4.7)

5. Добавление нескольких записей через сложный SQL-запрос:

    INSERT INTO CardsArchives (Code, Move, Date)

          SELECT C_Code, C_Move, C_Date

              FROM Cards WHERE C_Date BETWEEN 1.1.98 AND 31.12.98

6. Удаление записей через SQL-запрос:

   DELETE FROM Store.db

       WHERE S_Quantify = 0

7. Использование параметров в SQL-запросах (свойство «Params» типа «TParams»):

  Query1.ParamByName(‘pCena’).AsFloat:= StrToFloat(Edit1.Text);

или

 Query1.Params[0].AsFloat:= StrToFloat(Edit1.Text);

Пример с параметром:

   SELECT Name, Post, Cena

        FROM Pers.db

        WHERE Cena >= :pCena

2. ИСПОЛЬЗОВАНИЕ SQL-ФУНКЦИЙ ДЛЯ РАБОТЫ С БД

1. Сроковые функции SQL:

UPPER(Str)

LOWER(Str)

TRIM(Str)

SUBSTRING(Str FROM n1 TO n2)

CAST( AS ) – приведение выражения Expression к типу Type

|| – конкатенация строк

Например:

    SQL.Add('Select cast(ff.nomer as numeric) as nomer, kk.famil,');

    SQL.Add('cast((SUBSTRING(kk.ima FROM 1 FOR 1)||');

    SQL.Add('SUBSTRING(kk.otch FROM 1 FOR 1)) as character(2)) as io');

2. Создание таблицы:

CREATE TABLE NewTable.db

    (Number INTEGER,

      Name CHAR(20),

      Birthday DATE);

3. Удаление таблицы БД:

DROP TABLE NewTable.db

4. Изменение структуры таблицы:

ALTER TABLE Pers.db

    ADD Section SMALLINT, //добавление поля

    ADD Note CHAR(30),

    DROP Post // удаление поля

5. Создание и удаление индекса:

CREATE INDEX indName ON Personnel.db (Name)

6. Удаление индекса:

DROP INDEX “personnel.db”.indName

7. Задание вычисляемого поля:

  SELECT “– ” || Name, Salary, Salary*1.1

       FROM Personnel;

  Выводятся старые значения окладов сотрудников и новые, увеличенные на 10%. К каждой фамилии с помощью операции конкатенации добавляется символ “-”.

8. Отбор записей с уникальными значениями поля:

SELECT DISTINCT Post FROM Personnel

9. Проверка частичного совпадения значения поля:

SELECT Name

     FROM Perssonel

     WHERE Name LIKE “Ав”

10. Проверка частичного совпадения с помощью шаблона:

 SELECT Name

      FROM Goods

      WHERE Name LIKE “%” || “п_р” || “%”

% – замещение любого кол-ва символов, в том числе и нулевого

  _ – замещение одного символа

11. Проверка нулевых значений поля

SELECT *

      FROM Store

      WHERE S_Price IS NULL

12. Проверка вхождения записи в список:

SELECT Name, Salary

      FROM Perssonel

      WHERE Post IN (“Менеджер”,”Ст. менеджер”)

13. Проверка вхождения записей в диапазон:

SELECT *

      FROM Cards

      WHERE C_Date BETWEEN “13.4.99” AND “15.4.99”

14. Группирование записей. При группировании записей автоматически исключается повтор значений группируемых полей:

SELECT C_Date, COUNT (C_Date)

       FROM Cards

       GROUP BY C_Date

       HAVING COUNT(C_Date)>50

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

 AVG() – среднее значение

 MAX()

 MIN()

 SUM()

 COUNT() – кол-во значений

 COUNT(*) кол-во ненулевых значений

15. Сортировка по двум полям:

   SELECT Name, Post, Salary

       FROM Pers.db

       ORDER BY Post, Salary DESC

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

   FROM <Таб-ца1> [<Вид соединения>] JOIN <Таб-ца2> ON <Условие отбора>

Вид соединения, какая из 2 таблиц будет главной:

   LEFT – слева

   RIGHT – справа

{ Left join – таблицы объединяются, левая таблица – основная }

{ Пример ниже выводит номера людей и тех, у кого нет телефона }

SELECT distinct p.Fam, p.Name, pNumber

FROM “c:\mydb\people.dbf” as p LEFT JOIN “c:\mydb\tel.dbf” as t

ON (p.ID_People=t.ID_People)

{ Right join – таблицы объединяются, правая таблица – основная }

{ Если в предыдущем примере заменить LEFT JOIN на RIGHT JOIN, то программа выведет все номера телефонов из базы «Tel», и соответствующую телефону фамилию, если она имеется }

{ Full join – таблицы объединяются с дополнениями по каждой таблице }

{ Если в предыдущем примере заменить LEFT JOIN на FULL JOIN, то программа выведет все номера телефонов и все фамилии, причем какая-либо фамилия может быть без телефона, и какой-нибудь телефон – без фамилии }

ЛЕКЦИЯ № 9

1. МЕХАНИЗМ ТРАНЗАКЦИЙ ПРИ ОБРАБОТКЕ БАЗ ДАННЫХ

1. Транзакция – это действия на таблицами баз данных, которые должны быть выполнены полностью, от начала до конца. Если окажется, что действия над таблицами БД в транзакции не могут быть полностью и нормально выполнены, то необходимо их (проделанные действия) полностью отменить и вернуть таблицы данных в исходное состояние (до начала действий транзакции).

2. В Delphi работа с транзакциями обычно организуется через компонент типа «TDatabase». Для транзакции существует команда начала транзакции «StartTransaction», подтверждения транзакции «Commit» и отмены (отката) транзакции «Rollback» (в случае ошибки транзакции).

3. Алгоритм работы с транзакциями для таблиц типа Paradox:

Закрываем все таблицы из каталога транзакций, иначе старт транзакции будет невозможен;

Настраиваем режим транзакции на таблицы типа Paradox;

Указываем каталог транзакции компоненту DataBase;

Открываем нужные таблицы для работы;

Стартуем транзакцию;

Делаем нужную работу с БД (например, каскадное удаление);

Подтверждение транзакции;

Отмена транзакции при проблеме (при каком-либо сбое в пунктах 6 или 7);

Обновляем таблицы данных, задействованных в транзакции.

4. Рассмотрим пример работы с транзакциями:

procedure TForm1.Button1Click(Sender: TObject);

var i: word;

begin

  try

    // Закрываем все таблицы из каталога транзакций

    // иначе старт транзакции будет невозможен

    try Table1.Close; Table2.Close; except end;

    Database1.TransIsolation:=tiDirtyRead; // Настраиваем режим транзакции на таблицы типа Paradox

    Database1.DataBaseName:='C:\Student\Db'; // Указываем каталог транзакции

    Table1.Open; Table2.Open; // Открываем нужные таблицы для работы

    Database1.StartTransaction; // Стартуем транзакцию

    // Делаем работу с БД –

             Table1.Delete; Table1.Delete; Table2.Delete; Table2.Delete; showmessage('!!!');

             abort; // Имитируем проблему

    // Конец работы с БД –

    Database1.Commit; // Подтверждение транзакции

  except

    Database1.Rollback; // Отмена транзакции при проблеме

  end;

    // Обновляем таблицы данных

// Вместо «Table1.Refresh; Table2.Refresh;» лучше так:

  for i:=0 to Database1.DataSetCount-1 do Database1.DataSets[i].Refresh;

end;

2. ТРАНЗАКЦИИ В ТЕХНОЛОГИИ ADO

procedure TForm1.Button1Click(Sender: TObject);

var i: integer;

begin

  ADOConnection1.BeginTrans; // Начало транзакции

  try

    ADOTable1.Delete;

    ADOTable2.Delete; ADOTable2.Delete;

    showmessage('А сейчас будет имитация сбоя и откат транзакции!');

    abort; // Генерация сбоя

    ADOConnection1.CommitTrans; // Подтверждение транзакции

    except

  ADOConnection1.RollbackTrans; // Откат транзакции

  end;

// ADOTable1.Requery; ADOTable2.Requery; // Обновление данных из таблицы

  // Обновление данных из всех таблиц БД (так лучше)

   for i:=0 to ADOConnection1.DataSetCount-1 do

     ADOConnection1.DataSets[i].Requery;

end;

3. КАСКАДНОЕ УДАЛЕНИЕ ДАННЫХ С БД

Каскадное удаление данных – это удаление связанных между собой данных с разных таблиц одновременно.

procedure TForm1.Button6Click(Sender: TObject);

var k: integer;

begin

  ADOTable1.Filtered := False;

  k:=ADOTable2['Kod_firmi'];

  ADOTable2.Delete;

  ADOTable1.Filter := 'Kod_postavschika = '+IntToStr(k);

  ADOTable1.Filtered := True;

  ADOTable1.First;

  while not ADOTable1.Eof do ADOTable1.Delete;

  ADOTable1.Filtered := False;

  DBLookupComboBox3.KeyValue := ADOTable2['Nazv_firmi'];

end;

procedure TForm1.ADOTable2AfterScroll(DataSet: TDataSet);

begin

DBLookupComboBox3.KeyValue := ADOTable2['Nazv_firmi'];

end;

********

procedure TForm1.Button7Click(Sender: TObject);

begin

if Edit5.Text = '' then

  ShowMessage('Сначала нужно ввести телефон директора.')

else

  begin

    ADOConnection1.BeginTrans;

    try

      ADOQuery4.Close;

      ADOQuery4.SQL.Clear;

      ADOQuery4.SQL.Add('delete');

      ADOQuery4.SQL.Add('from Diski_CD');

      ADOQuery4.SQL.Add('where Kod_postavschika in (select Kod_firmi from Firmi_postavschiki where Tel_dir like '''+Edit5.Text+'%'')');

      ADOQuery4.ExecSQL;

      ADOQuery4.Close;

      ADOQuery4.SQL.Clear;