1. Введення, Бази даних, Delphi, статті

Даний документ робить акцент на другому методі доступу до баз даних, на основі запитів SQL (pass-through). Автори не прагнули створити курси по вивчення синтаксису мови SQL і його застосування, вони ставили перед собою мету дати кілька прикладів використання компонентів TQuery і TStoredProc. Але щоб зробити це, необхідно розуміти концепцію SQL і знати як працюють selects, inserts, updates, views, joins і збережені процедури (stored procedures). Документ також побіжно торкається питань управління транзакціями і з’єднання з базою даних, але не акцентує на цьому увагу. Отже, приступаючи до теми, створіть простий запит типу SELECT і відобразіть результати.

2. Компонент TQuery


Якщо у ваших додатках ви збираєтеся використовувати SQL, то вам неодмінно доведеться познайомитися з компонентом TQuery. Компоненти TQuery і TTable успадковуються від TDataset. TDataset забезпечує необхідну функціональність для отримання доступу до баз даних. Як такі, компоненти TQuery і TTable мають багато спільних ознак. Для підготовки даних для показу у візуальних компонентах використовується все той же TDatasource. Також, для визначення до якого сервера і базі даних необхідно отримати доступ, необхідно задати ім’я псевдоніма. Це повинно виконуватися установкою властивості aliasName об’єкта TQuery.

Властивість SQL

Все ж TQuery має деяку унікальну функціональність. Наприклад, у TQuery є властивість з ім’ям SQL. Властивість SQL використовується для зберігання SQL-запиту. Нижче наведені основні кроки для створення запиту, де все службовці мають зарплату понад $ 50,000.


  1. Створіть об’єкт TQuery


  2. Задайте псевдонім властивості DatabaseName. (Даний приклад використовує псевдонім IBLOCAL, пов’язаний з демонстраційною базою даних employee.gdb).


  3. Виберіть властивість SQL і клацніть на кнопці з текстом – “…” (Три точки, Інспектор Об’єктів – В.О.). Повинен з’явитися діалог редактора списку рядків (String List Editor).


  4. Введіть: Select * from EMPLOYEE where SALARY> 50000. Натисніть OK.


  5. Виберіть в Інспекторові Об’єктів властивість Active і встановіть його в TRUE.


  6. Додайте на формі об’єкт TDatasource.


  7. Встановіть властивість Dataset у TDatasource в Query1.


  8. Додайте на формі TDBGrid.


  9. Встановіть його властивість Datasource в Datasource1.
Властивість SQL має тип TStrings. Об’єкт TStrings являє собою список рядків, і чимось схожий на масив. Тип даних TStrings має в своєму арсеналі команди додавання рядків, їх завантаження з текстового файлу і обміну даними з іншим об’єктом TStrings. Інший компонент, який використовує TStrings – TMemo. У демонстраційному проекті ENTRSQL.DPR (по ідеї, він повинен знаходиться на окремій дискеті, але до “Радам по Delphi” вона не додається – В.О.), Користувач повинен ввести SQL-запит і натиснути кнопку “Do It” (“зробити це”). Результати запиту відображаються в табличній сітці. У лістингу 1 повністю приведений код обробника кнопки “Do It”.

Лістинг 1






procedure TForm1.BitBtn1Click(Sender: TObject);
begin
Query1.close; {Деактивуємо запит в якості одного із заходів обережності}
Query1.SQL.Clear; {Стираємо будь-який попередній запит}
If Memo1.Lines[0] <> “” {Перевіряємо на предмет порожнього введення} then
Query1.SQL.Add(Memo1.Text) {Призначаємо властивості SQL текст Memo}
else
begin messageDlg (“Не був введений SQL-запит”, mtError, [mbOK], 0);
exit;
end;
try {Перехоплювач помилок}
Query1.Open; {Виконуємо запит і відкриваємо набір даних}
except {Секція обробки помилок}
On e : EDatabaseError do {E – новий дескриптор помилки}
messageDlg(e.message,
mtError,
[mbOK],0); {Показуємо властивість message об’єкта e}
end; {Закінчення обробки помилки}
end;

Властивість Params

Цього повинно бути достатньо для користувача, який знає SQL. Тим не менш, більшість користувачів не знає цієї мови. Отже, ваша робота як розробника полягає в наданні інтерфейсу і створення SQL-запиту. В Delphi, для створення SQL-запиту на льоту можна використовувати динамічні запити. Динамічні запити допускають використання параметрів. Для визначення параметра в запиті використовується двокрапка (:), за яким слід ім’я параметра. Нижче наведено приклад SQL-запиту з використанням динамічного параметри:

          select * from EMPLOYEE
where DEPT_NO = :Dept_no
Якщо вам потрібно протестувати, або встановити для параметра значення за умовчанням, виберіть властивість Params об’єкта Query1. Клацніть на кнопці “…”. Повинен з’явитися діалог налаштування параметрів. Виберіть параметр Dept_no. Потім у спадному списку типів даних виберіть Integer. Для того, щоб задати значення за замовчуванням, введіть потрібне значення в поле редагування “Value”.

Для зміни SQL-запиту під час виконання програми, параметри необхідно пов’язати (bind). Параметри можуть змінюватися, запит виконуватись повторно, а дані оновлюватися. Для безпосереднього редагування значення параметра використовується властивість Params або метод ParamByName. Властивість Params представляє з себе масив TParams. Тому для отримання доступу до параметру, необхідно вказати його індекс. Для прикладу,






Query1.params[0].asInteger := 900;

Властивість asInteger читає дані як тип Integer (назва говорить сама за себе). Це не обов’язково має вказувати але те, що поле має тип Integer. Наприклад, якщо тип поля VARCHAR (10), Delphi здійснить перетворення даних. Так, наведений вище приклад міг би бути записаний таким чином:






Query1.params[0].asString := “900”;

або так:






Query1.params[0].asString := edit1.text;

Якщо замість номера індексу ви хотіли б використовувати ім’я параметра, то скористайтеся методом ParamByName. Даний метод повертає об’єкт TParam з заданим ім’ям. Наприклад:






Query1.ParamByName(“DEPT_NO”).asInteger := 900;

У лістингу 2 приведений повний код прикладу.

Лістинг 2






procedure TForm1.BitBtn1Click(Sender: TObject);
begin
Query1.close; {Деактивуємо запит в якості одного із заходів обережності}
if not Query1.prepared
then
Query1.prepare; {Переконаємося що запит підготовлений}
{Беремо значення, введене користувачем і замінюємо їм параметр.}
if edit1.text <> “” {Перевіряємо на предмет порожнього введення}
then
Query1.ParamByName(“DEPT_NO”).AsString := edit1.text
else
Begin
Query1.ParamByName(“DEPT_NO”).AsInteger := 0;
edit1.text := “0”;
end;
try {Перехоплювач помилок}
Query1.Open; {Виконуємо запит і відкриваємо набір даних}
except {Секція обробки помилок}
On e : EDatabaseError do {E – новий дескриптор помилки} messageDlg(e.message,
mtError,
[mbOK],0); {Показуємо властивість message об’єкта e}
end; {Закінчення обробки помилки}
end;

Зверніть увагу на процедуру, першим ділом що готують запит. При виклику методу prepare, Delphi посилає SQL запит на віддалений сервер. Сервер виконує граматичний розбір і оптимізацію запиту. Перевага такої підготовки запиту полягає в його попередньому розборі і оптимізації. Альтернативою тут може служити підготовка сервером запиту при кожному його виконання. Як тільки запит підготовлений, підставляються необхідні нові параметри, і запит виконується.

Джерело даних

У попередньому прикладі користувач міг ввести номер відділу, і після виконання запиту відображався список співробітників цього відділу. А як щодо використання таблиці DEPARTMENT, що дозволяє користувачеві легко переміщатися між користувачами і відділами?

Примітка: Наступний приклад використовує TTable з ім’ям Table1. Для Table1 ім’я бази даних IBLOCAL, ім’я таблиці – DEPARTMENT. DataSource2 TDatasource пов’язаний з Table1. Таблиця також активна і відображає записи в TDBGrid.

Спосіб підключення TQuery до TTable – через TDatasource. Є два основних способи зробити це. По-перше, розмістити код в обробнику події TDatasource OnDataChange. Наприклад, лістинг 3 демонструє цю техніку.

Лістинг 3 – Використання події OnDataChange для перегляду дочірніх записів






procedure TForm1.DataSource2DataChange(Sender: TObject; Field: TField);
begin
Query1.Close;
if not Query1.prepared
then
Query1.prepare;
Query1.ParamByName(“Dept_no”).asInteger := Table1Dept_No.asInteger;
try
Query1.Open;
except
On e : EDatabaseError do
messageDlg(e.message, mtError, [mbOK], 0);
end;
end;

Техніка з використанням OnDataChange дуже гнучка, але є ще легше спосіб підключення Query до таблиці. Компонент TQuery має властивість Datasource. Визначаючи TDatasource для властивості Datasource, об’єкт TQuery порівнює імена параметрів в SQL-запиті з іменами полів у TDatasource. У разі загальних імен, такі параметри заповнюються автоматично. Це дозволяє розробнику уникнути написання коду, наведеного в лістингу 3 (*** приведений вище ***).

Фактично, техніка використання Datasource не вимагає ніякого додаткового кодування. Для підключення запиту до таблиці DEPT_NO виконайте дії, наведені в лістингу 4.

Лістинг 4 – Зв’язування TQuery c TTable через властивість Datasource

Виберіть у Query1 властивість SQL і введіть:

             select * from EMPLOYEE
where DEPT_NO = :dept_no
Виберіть властивість Datasource і призначте джерело даних, пов’язаний з Table1 (Datasource2 в нашому прикладі)

Виберіть властивість Active і встановіть його в True

Це все, якщо ви хочете створити такий тип відносин. Тим не менш, існують деякі обмеження на параметризрвані запити. Параметри обмежені значеннями. Приміром, ви не можете використовувати параметр з ім’ям Column або Table. Для створення запиту, динамічно змінюваного ім’я таблиці, ви могли б використовувати техніку конкатенації рядка. Інша техніка полягає в використання команди Format.

Команда Format

Команда Format замінює параметри форматування (% s,% d,% n і пр.) переданими значеннями. Наприклад,






Format(“Select * from %s”, [“EMPLOYEE”])

Результатом вищенаведеної команди буде “Select * from EMPLOYEE”. Функція буквально робить заміну параметрів форматування значеннями масиву. При використанні декількох параметрів форматування, заміна відбувається зліва направо. Наприклад,






tblName := “EMPLOYEE”;
fldName := “EMP_ID”;
fldValue := 3;
Format(“Select * from %s where %s=%d”, [tblName, fldName, fldValue])

Результатом команди форматування буде “Select * from EMPLOYEE where EMP_ID = 3 “. Така функціональність забезпечує надзвичайну гнучкість при динамічному виконанні запиту. Приклад, наведений нижче в лістингу 5, дозволяє вивести в результатах поле salary. Для поля salary користувач може задавати критерії.

Лістинг 5 – Використання команди Format для створення SQL-запиту






procedure TForm1.BitBtn1Click(Sender: TObject);
var
sqlString : string; {Тут зберігається SQL-запит}
fmtStr1,
fmtStr2 : string; {Тут зберігається рядок, передана для форматування}
begin
{Створення каркаса запиту}
sqlString := “Select EMP_NO %s from employee where SALARY %s”;
if showSalaryChkBox.checked {Якщо checkbox Salary відзначений}
then
fmtStr1 := “, SALARY”
else
fmtStr1 := “”;
if salaryEdit.text <> “” {Якщо поле редагування Salary не порожнє}
then
fmtStr2 := salaryEdit.text
else
fmtStr2 := “>0”;
Query1.Close; {Деактивуємо запит в якості одного із заходів обережності}
Query1.SQL.Clear; {Стираємо будь-який попередній запит}
Query1.SQL.Add(Format(sqlString,[fmtStr1, fmtStr2]));{Додаємо}
{Форматований рядок до властивості SQL}
try {Перехоплювач помилок}
Query1.Open; {Виконуємо запит і відкриваємо набір даних}
except {Секція обробки помилок}
On e : EDatabaseError do {E – новий дескриптор помилки}
messageDlg(e.message, mtError,[mbOK],0);
{Показуємо властивість message об’єкта e}
end; {Закінчення обробки помилки}
end;

У цьому прикладі ми використовуємо методи Clear і Add властивості SQL. Оскільки “Підготовлений” запит використовує ресурси сервера, і немає ніякої гарантії що новий запит буде використовувати ті ж таблиці і стовпчики, Delphi, при кожному зміні властивості SQL, здійснює операцію, зворотну “підготовці” (Unprepare). Якщо TQuery не був підготовлений (тобто властивість Prepared встановлено в False), Delphi автоматично готує його при кожному виконанні. Тому в нашому випадку, навіть якби був викликаний метод Prepare, додатку від цього не буде ніякої користі.

Open проти ExecSQL

У попередніх прикладах TQuerie виконували Select-запити. Delphi розглядає результати Select-запиту як набір даних, типу таблиці. Це просто один клас допустимих SQL-запитів. Наприклад, команда Update оновлює вміст запису, але не повертає запису або якого-небудь значення. Якщо ви хочете використовувати запит, не повертає набір даних, використовуйте ExecSQL замість Open. ExecSQL передає запит для виконання на сервер. У загальному випадку, якщо ви очікуєте, що отримаєте від запиту дані, то використовуйте Open. В іншому випадку допускається використання ExecSQL, хоча його використання з Select не буде конструктивним. Лістинг 6 містить код, що пояснює сказане на прикладі.

Лістинг 6






procedure TForm1.BitBtnClick(sender : TObject)
begin
Query1.Close;
Query1.Clear;
Query1.SQL.Add(“Update SALARY from EMPLOYEE ” +
“where SALARY<:salary values (SALARY*(1+:raise)”);
Query1.paramByName(“salary”).asString := edit1.text;
Query1.paramByName(“raise”).asString := edit2.text;
try
Query1.ExecSQL;
except
On e : EDatabaseError do
messageDlg(e.message, mtError, [mbOK], 0);
end;
end;

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

3. Компонент TStoredProc


Процедура є список команд (SQL або певного сервера), що зберігаються і виконуваних на стороні сервера. Збережені процедури не мають концептуальних відмінностей з іншими типами процедур. TStoredProc успадковується від TDataset, тому він має багато спільних характеристик з TTable і TQuery. Особливо помітно схожість з TQuery. Оскільки процедури не вимагають повернення значень, ті ж правила діють і для методів ExecProc і Open. Кожен сервер реалізує роботу процедур з невеликими відмінностями. Наприклад, якщо в Як сервер ви використовуєте Interbase, збережені процедури виконуються у вигляді Select-запитів. Наприклад, щоб подивитися на результати збереженої процедури, ORG_CHART, в демонстраційній базі даних EMPLOYEE, використовуйте наступних SQL-запит:

 Select * from ORG_CHART
При роботі з іншими серверами, наприклад, Sybase, ви можете використовувати компонент TStoredProc. Даний компонент має властивості для імен бази даних і процедури, що зберігається. Якщо процедура вимагає на вході якихось параметрів, використовуйте для їх введення властивість Params.

4. TDatabase


Компонент TDatabase забезпечує функціональність, якої не вистачає TQuery і TStoredProc. Зокрема, TDatabase дозволяє створювати локальні псевдоніми BDE, так що додатком не будуть потрібні псевдоніми, що містяться в конфігураційному файлі BDE. Цим локальним псевдонімом в додатку можуть скористатися всі наявні TTable, TQuery і TStoredProc. TDatabase також дозволяє розробнику налаштовувати процес підключення, пригнічуючи діалог введення імені та пароля користувача, або заповнюючи необхідні параметри. І, нарешті, найголовніше, TDatabase може забезпечувати єдину зв’язок з базою даних, підсумовуючи всі операції з базою даних через один компонент. Це дозволяє елементам управління для роботи з БД мати можливість управління транзакціями.

Транзакцією можна вважати передачу пакета інформації. Класичним прикладом транзакції є передача грошей на рахунок банку. Транзакція має складатися з операції внесення суми на новий рахунок і видалення тієї ж суми з поточного рахунку. Якщо один з цих кроків з якоїсь причини був не виконаний, транзакція також вважається невиконаним. У разі такої помилки, SQL сервер дозволяє виконати команду відкоту (rollback), без внесення змін до бази даних. Управління транзакціями залежить від компонента TDatabase. Оскільки транзакція зазвичай складається з декількох запитів, ви повинні відзначити початок транзакції і її кінець. Для виділення початку транзакції використовуйте TDatabase.BeginTransaction. Як тільки транзакція почне виконуватися, всі виконувані команди до виклику TDatabase.Commit або TDatabase.Rollback переводяться в тимчасовий режим. При виклику Commit всі змінені дані передаються на сервер. При виклику Rollback всі зміни втрачають силу. Нижче в лістингу 7 приведений приклад, де використовується таблиця з ім’ям ACCOUNTS. Показана процедура намагається передати суму з одного рахунку на інший.

Лістинг 7






procedure TForm1.BitBtn1Click(Sender: TObject);
{ПРИМІТКА: Поле BALANCE у ACCOUNTS має тригер, перевіряючий ситуацію, коли віднімається сума перевищує BALANCE. Якщо так, UPDATE буде скасовано}
begin
try
database1.StartTransaction;
query1.SQL.Clear;
{Віднімаємо суму з вибраного рахунку}
query1.SQL.Add(Format(“update ACCOUNTS ” +
“set BALANCE = BALANCE – %s ) ” +
“where ACCT_NUM = %s “,
[edit1.text,
Table1Acct_Num.asString]));
query1.ExecSQL;
query1.SQL.Clear;
{Додаємо суму до вибраному рахунку}
query1.SQL.Add(Format(“update ACCOUNTS ” +
“set BALANCE = BALANCE + %s ” +
“where ACCT_NUM = %s “,
[edit1.text,
Table2Acct_Num.asString]));
query1.ExecSQL;
database1.Commit; {У цьому місці робимо всі зміни}
table1.Refresh;
table2.Refresh;
except
{При виникненні в наведеному коді будь-яких помилок, відкатуємо транзакцію назад}
One : EDatabaseError do
begin
messageDlg(e.message, mtError, [mbOK], 0);
database1.rollback;
exit;
end;
One : Exception do
begin
messageDlg(e.message, mtError, [mbOK], 0);
database1.rollback;
exit;
end;
end;
end;

І останнє, що потрібно врахувати при з’єднанні з базою даних. У наведеному вище прикладі, TDatabase використовувався в якості єдиного каналу для зв’язку з базою даних, тому було можливим виконання тільки однієї транзакції. Щоб виконати це, було визначено ім’я псевдоніма (Aliasname). Псевдонім зберігає в собі інформацію, що стосується з’єднання, таку, як Driver Type (тип драйвера), Server Name (ім’я сервера), User Name (ім’я користувача) і іншу. Дана інформація використовується для створення рядка з’єднання (connect string). Для створення псевдоніма ви можете використовувати утиліту конфігурації BDE, або, як показано в прикладі нижче, заповнювати параметри під час виконання програми.

TDatabase має властивість Params, в якому зберігається інформація з’єднання. Кожен рядок Params є окремим параметром. У наведеному нижче прикладі користувач встановлює параметр User Name в поле редагування Edit1, а параметр Password в поле Edit2. У коді лістингу 8 показаний процес підключення до базі даних:

Лістинг 8






procedure TForm1.Button1Click(Sender: TObject);
begin
try
With database1 do
begin
Close;
DriverName := “INTRBASE”;
KeepConnection := TRUE;
LoginPrompt := FALSE;
With database1.Params do
begin
Clear;
Add(“SERVER NAME=C:IBLOCALEXAMPLESEMPLOYEE.GDB”);
Add(“SCHEMA CACHE=8”);
Add(“OPEN MODE=READ/WRITE”);
Add(“SQLPASSTHRU MODE=SHARED NOAUTOCOMMIT”);
Add(“USER NAME=” + edit1.text);
Add(“PASSWORD=” + edit2.text);
end;
Open;
end;
session.getTableNames(database1.databasename, “*”,
TRUE,
TRUE,
ComboBox1.items);
Except
One : EDatabaseError do
begin
messageDlg(e.message, mtError, [mbOK], 0);
end;
end;
end;

Цей приклад показує як можна здійснити підключення до сервера без створення псевдоніма. Ключовими моментами тут є визначення DriverName і заповнення Params інформацією, необхідною для підключення. Вам не потрібно визначати всі параметри, вам необхідно задати тільки ті, які не встановлюються в конфігурації BDE певним вами драйвером бази даних. Введені у властивості Params дані перекривають всі установки конфігурації BDE. Записуючи параметри, Delphi заповнює залишилися параметри значеннями з BDE Config для даного драйвера. Наведений вище приклад також вводить такі поняття, як сесія і метод GetTableNames. Це виходить за рамки обговорюваної теми, досить згадати лише той факт, що змінна session є дескриптором database engine. У прикладі вона додана тільки для “показухи”.

Іншою темою є використання SQLPASSTHRU MODE. Цей параметр бази даних відповідає за те, як Натів-команди бази даних, такі, як TTable.Append або TTable.Insert будуть взаємодіяти з TQuery, підключеної до тій же базі даних. Існують три можливі значення: NOT SHARED, SHARED NOAUTOCOMMIT і SHARED AUTOCOMMIT. NOT SHARED означає, що Натів-команди використовують одне з’єднання з сервером, тоді як запити – інше. З боку сервера це бачиться як робота двох різних користувачів. В будь-який момент часу, поки транзакція активна, Натів-команди не будуть виконуватися (committed) до тих пір, поки транзакція не буде завершена. Якщо був виконаний TQuery, то будь-які зміни, передані в базу даних, проходять окремо від транзакції.

Два інших режиму, SHARED NOAUTOCOMMIT і SHARED AUTOCOMMIT, роблять для Натів-команд і запитів загальним одне з’єднання з сервером. Різниця між двома режимами полягають у передачі виконаної Натів-команди на сервер. При вибраному режимі SHARED AUTOCOMMIT безглуздо створювати транзакцію, використовує Натів-команди для видалення запису і подальшої спробою здійснити відкат (Rollback). Запис повинен бути видалена, а зміни мають бути зроблені (committed) до виклику команди Rollback. Якщо вам потрібно передати Натів-команди в межах транзакції, або включити ці команди в саму транзакцію, переконайтеся в тому, що SQLPASSTHRU MODE встановлений в SHARED NOAUTOCOMMIT або в NOT
SHARED.

5. Висновки


Delphi підтримує безліч характеристик при використанні мови SQL з вашими серверами баз даних. На цій ноті дозвольте попрощатися і побажати частіше використовувати SQL в ваших додатках.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*