Советы по Delphi
Этот SQL не работает для
Delphi 1
Имя SQL-таблицы Этот SQL не работает для RequestLive := True : SELECT * FROM DBO.TABLE1
Этот SQL работает для RequestLive := True : SELECT * FROM "DBO.TABLE1"
Помните о том, что кавычки необходимы для обозначения имени таблицы! Также имейте в виду, что поставляемая документация говорит о том, что для таблиц Oracle кавычки не нужны! Неверно, нужны!!! [000325]
Интерактивные SQL-запросы Как мне передать значение переменной в SQL-запросе? К примеру, в обработчике onClick клавиши вывести все записи с величиной поля большей, чем задал пользователь. Можно ли в Delphi создать что-либо подобное механизму запросов, реализованному в Paradox for Windows?
Решение этой задачи в Delphi подобно созданию и выполнению строки запроса SQL в Paradox.
Pdoxwin код:
|
method pushButton(var eventInfo Event)
var
s string
q query
d database
endvar
d.open( "MYALIAS" ) s = "select * from mytable where somefield=\"" + entryField.value + "\"" q.readFromString( s ) q.executeSQL( d ) endmethod |
| procedure TForm1.Button1Click(Sender: TObject); begin MyQuery.Active := false; MyQuery.SQL.clear; MyQuery.SQL.add('select * from mytable where somefield="' + EntryField.Text + '"'); MyQuery.Active := true; end; |
Одной строкой
Синтах SQL-функции Substring Какой синтакс у SQL-функции SUBSTRING()?
| SUBSTRING('Delphi - это супер!!!' from 1 to 6) |
Delphi 1
SQL и расширенные символы Я использую поле Tmemo.
Это классно работает до тех пор, пока я не использую расширенные символы (с кодом больше 127). При использовании этих символов текст-то я послать могу, но при последующем поиске записи я получаю такое сообщение об ошибке:
"General SQL error : Cannot transliterate character between character sets." (Общая ошибка SQL: не могу сопоставить символ с имеющимися наборами символов).
Я понял, в чем ваша ошибка: вы должны установить встроенный набор символов LIBS в DEFAULT CHARACTER SET ISO8859_1. Когда вы впоследствии создадите таблицу, она будет использовать данный набор символов для создания alfanumeric-данных и Blob-полей. Псевдоним базы данных должен быть установлен на драйвер языка BLLT1FR.
После того, как я сделал это и пересоздал базу данных, все заработало как надо.
Может быть и не стоило публиковать эту информацию, поскольку многие пользователи пользуются локальными драйверами, наборами символов и новой версией SQL, но я посчитал ее ценной, поскольку как раз эта инфорамция в описании и отсутствует. Она присутствует только в LIBS "readme"-файле. [000412]
Delphi 1
SQL в Delphi Delphi поддерживает статический и динамический SQL. В Delphi имеется объект TQuery, который используется для хранения и выполнения SQL-запросов.
Свойство TQuery SQL содержит текст SQL-запроса, выполняемых TQuery.
Данное свойство имеет тип TStrings, означающее, что оно может хранить в списке целую серию строк. Список ведет себя подобно массиву, но в действительности это специальный класс с уникальными возможностями.
Компонент TQuery позволяет выполнять два типа SQL-запросов:
- Статические SQL-запросы
- Динамические SQL-запросы
SELECT * FROM CUSTOMER WHERE CUST_NO = 1234
Динамический SQL-запрос, или, как его еще называют, параметрический запрос, включает в себя параметры для колонок или имени таблицы. Например, следующая строка является динамическим SQL-запросом:
SELECT * FROM CUSTOMER WHERE CUST_NO = :Number
Переменная Number, указанная после двоеточия - параметр, который вы назначаете во время выполнения приложения. Во время выполнения запроса параметр может изменяться.
Delphi-приложения могут использовать SQL для получения доступа к следующим БД:
- Таблицы Paradox или dBASE, использующие локальный SQL. Допустимый синтаксис является подмножеством стандарта ANSI-standard SQL и включает основные SELECT, INSERT, UPDATE, и DELETE запросы. Для получения дополнительной информации о локальном синтаксисе SQL обратитесь к справке Using Local SQL.
- Базы данных Local InterBase Server, включая Local InterBase Server. Допускаются любые запросы InterBase SQL. Для получения дополнительной информации о синтаксисе и ограничениях обратитесь к электронной справке SQL Statement and Function Reference.
- Базы данных на удаленных серверах баз данных (только в версии Delphi Client/server). По-видимому вы установили подходящий SQL Link. В SQL серверах допускаются любые стандартные SQL запросы. Для получения дополнительной информации о синтаксисе и ограничениях обратитесь к электронной справке вашего сервера.
Обработка транзаций в приложениях:
Delphi приложения могут управлять транзакциями следующими способами:
- Неявно, автоматически стартуя и запуская транзакции, когда приложение пытается передать данные (Post data).
- Явно, следующими способами в зависимости от уровня управления, требующемуся вашему приложению:
- Методы TDatabase StartTransaction, Commit, и Rollback.
- Это рекомендуемый метод.
- Passthrough (транзитная пересылка) SQL в компоненте TQuery. Ваше приложение должно использовать специфически-серверные SQL запросы управления транзакциями, и вы должны понять как управляются транзакции вашим сервером.
Одной строкой
SQL outer join
|
Select * From "TEST.DB" Test Left Outer Join "Emp.DB" Emp On Test.Emp_ID = Emp.Emp_ID |
Delphi 1
Local SQL и временная таблица Local SQL не поддерживает вложенные запросы, но после того как я заработал клок седых волос, я нашел в высшей степени простое решение: использование временной таблицы.
Пример:
| with GeneralQuery do begin SQL.Clear; SQL.Add(.... внутренний SQL); SQL.Open; DbiMakePermanent(handle, 'temp.db',true); SQL.Clear; SQL.Add(SELECT ... FROM 'temp.db'....) SQL.Open; end; |
- Dieter Menne [000732]
Delphi 1
Проблемы StoredProc у SQL server Обнаруженная мною проблема заключается в ненормальной работе BDE с TStoredProc, когда хранимая процедура SQL получает на входе параметр типа String. Как я понял, BDE/SQL Links перед вызовом хранимой процедуры заносит в строку управляющие символы. Чтобы обойти эту проблему, Borland предлагает использовать TQuery. Конечно, ничего не стоит перевести TStoredProcs в TQuerys (с сохранением полного набора характеристик и без потери скорости), но мне стала интересна причина такого поведения компонента, и я решил покопаться в TStoredProc насколько это было мне возможно и интересно, для чего я добавил дополнительный параметр к хранимой процедуре, позволяющий указывать длину передаваемой процедуре строки. Затем, уже в процедуре, если реальная длина строки оказывалась больше, с помощью дополнительно передаваемого параметра бралась, и в дальнейшем использовалась только левая часть строки, а остальные управляющие символы игнорировались.
Вот пример:
Приведенная ниже процедура SQL Server возвращает 1 если таблица существует, и 2 в противном случае. CREATE PROCEDURE up_TableExists ( @TableName varchar(50), @TableNameLen int = null) AS declare @CleanTblName varchar(50) if @TableNameLen is not null select @CleanTblName = SubString(@TableName,1,@TableNameLen) else select @CleanTblName = @TableName if EXISTS (SELECT name FROM sysobjects WHERE name = @CleanTblName) RETURN 1 else RETURN 2 Поехали...
В Delphi, прежде чем вызвать ExecProc, установите параметр длины строки... вот пример вызова хранимой процедуры в Delphi...
| var sTableName: String; rc: Boolean; ... sTableName := 'MyTable'; With StoredProc1 do begin . . . ParamByName('@TableName').AsString := sTableName; { обход проблемы: передаем длину строки SQL Server для обработки хранимой процедурой } ParamByName('@TableNameLen').AsInteger := Length(sTableName); Prepare; ExecProc; rc := ParamByName('Result').AsInteger = 1; {rc True если result = 1} if rc then .... end; |
Delphi 1
SQL: вложенные пробелы Выполнение SQL с пробелами и специальными символами в имени поля/колонки
Выполнение SQL-запросов в Delphi-компоненте TQuery (или специального средства SQL-запроса в Database Desktop, Visual dBASE или Paradox for Windows) требует специального синтаксиса для любых колонок, содержащих пробелы или специальные символы.
Пользуясь таблицей Biolife.DB из демо-данных Delphi, проиллюстрируем использование любых специфических требований синтаксиса. Запрос SQL Select мог бы быть сформирован следующим образом: SELECT Species No, Category, Common_Name, Species Name, Length (cm), Length_In, Notes, Graphic FROM BIOLIFE В нормальной ситуации пробелы в номерах и именах колонок, длина в сантиметрах, круглые скобки и другие символы могут стать причиной синтаксической ошибки.
Для коррекции синтаксиса в вышеприведенном SQL-выражении необходимо внести два изменения. Во-первых, любые колонки, содержищие пробелы или специальные символы должны быть заключены в двойные или одинарные (апострофы) кавычки. Во-вторых, ссылке на имя колонки должна предшествовать ссылка на саму таблицу и точка после нее. Второе требование особенно важно, поскольку заключенная в кавычки строка интерпретируется не как строковое выражение, а как значение колонки. Ниже приведено правильно отформатированное выражение: SELECT BIOLIFE."Species No", BIOLIFE."Category", BIOLIFE."Common_Name", BIOLIFE."Species Name", BIOLIFE."Length (cm)", BIOLIFE."Length_In", BIOLIFE."Notes", BIOLIFE."Graphic" FROM "BIOLIFE.DB" BIOLIFE В приведенном выше примере таблицный псевдоним BIOLIFE используется в качестве ссылки на саму таблицу и располагается перед именем колонки . Данная ссылка может принимать форму имени псевдонима, реального имени таблицы, или ссылаться на имя файла при использовании таблиц dBASE или Paradox. Следующее SQL-выражение должно также хорошо работать.
Примечание: Данное SQL-выражение может использоваться при условии, что необходимый псевдоним уже открыт. В случае TQuery это означает, что псевдоним определен в свойстве DatabaseName. SELECT BIOLIFE."Species No", BIOLIFE.Category, BIOLIFE.Common_Name, BIOLIFE."Species Name", BIOLIFE."Length (cm)", BIOLIFE.Length_In, BIOLIFE.Notes, BIOLIFE.Graphic FROM BIOLIFE Если псевдоним недоступен, то для таблицы должен быть определен путь целиком, например, так: SELECT "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species No", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Category", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Common_Name", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species Name", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length (cm)", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length_In", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Notes", "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Graphic" FROM "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB" Наконец, есть два средства автоматического форматирования специального синтаксиса. Первым является Visual Query Builder, включаемой в версию Client/Server Delphi. Visual Query Builder выполняет такое форматирование автоматически, по мере создания запроса. Другое средство - Database Desktop Show SQL, доступный при создании и редактировании запроса QBE-типа. После выбора пункта меню Query|Show SQL, отображаемый SQL-текст может быть вырезан и вставлен где необходимо.
[000841]
Параметризованные запросы Создание и использование параметризованных запросов (Parameterized Queries)
Как мне передать переменную в запрос?
Сначала вы должны создать запрос, использующий переменную. Select Test."FName", Test."Salary Of Employee" From Test Where Test."Salary of Employee" > :val Примечание: Если вы просто пишете имя поля как "Salary of Employee", вы получите ошибку "Capability Not Supported". Это должно быть просто Test."Salary of Employee".
В нашем случае имя переменной "val", но это может быть любое другое (естественно). Затем вы переходите к свойству TQuery's params и устанавливаете параметр "val" в зависимости от требуемого типа. В нашем примере мы используем тип integer.
Затем вы должны создать код, устанавливающий значение параметра. Для задания значения мы будем использовать компонент TEdit.
| procedure TForm1.Button1Click(Sender: TObject); begin with Query1 do begin Close; ParamByName('val').AsInteger := StrToInt(Edit1.Text); Open; end; end; |
Если в своем запросе вы хотите использовать ключевое слово LIKE, то вы можете сделать это так:
Примечание: Следующий код использует таблицу пользователя, расположенную в каталоге \delphi\demos\data. При этом также возможно использование псевдонима DBDEMOS.
Код SQL для свойства TQuery.SQL: SELECT * FROM CUSTOMER WHERE Company LIKE :CompanyName Код Delphi:
| procedure TForm1.Button1Click(Sender: TObject); begin with Query1 do begin Close; ParamByName('CompanyName').AsString := Edit1.Text + '%'; Open; end; end; |
Вот демонстрация такого способа:
| ParamByName('CompanyName').AsString := Edit1.Text + '%'; |
| Params[0].AsString := Edit1.Text + '%'; |
[000843]
Delphi 2
Не работающий SQL OR Я заполнил таблицу 10 записями и сделал SQL с "OR" (в свойстве "select * from", где acreage=5.5 или acreage=6). Это работает. Затем я возвратился и поместил вторичный индекс в поле acreage - и это НЕ СРАБОТАЛО.
Наличие вторичного индекса не позволяет нормально отработать запросу SQL.
- Chris Hall/Randall Nelson [000982]
Функции дат в SQL Тема: Функции дат в SQL
Кто-нибудь знает как "вытащить" месяц или год из datetime-поля с помощью SQL? Я знаю, что QBE этого не может. SQL в состоянии это сделать?
Как насчет функции EXTRACT? SELECT SALEDATE, EXTRACT(DAY FROM SALEDATE) AS DD, EXTRACT(MONTH FROM SALEDATE) AS MM, EXTRACT(YEAR FROM SALEDATE) AS YY FROM ORDERS - Steve Koterski [001009]
Зарезервированные слова Local SQL Ниже приведен список в алфавитном порядке слов, зарезервированных Local SQL в Borland Database Engine. Имейте в виду, что данный совет публикуется "как есть".
ACTIVE, ADD, ALL, AFTER, ALTER, AND, ANY, AS, ASC, ASCENDING, AT, AUTO, AUTOINC, AVG
BASE_NAME, BEFORE, BEGIN, BETWEEN, BLOB, BOOLEAN, BOTH, BY, BYTES
CACHE, CAST, CHAR, CHARACTER, CHECK, CHECK_POINT_LENGTH, COLLATE, COLUMN, COMMIT, COMMITTED, COMPUTED, CONDITIONAL, CONSTRAINT, CONTAINING, COUNT, CREATE, CSTRING, CURRENT, CURSOR
DATABASE, DATE, DAY, DEBUG, DEC, DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DESCENDING, DISTINCT, DO, DOMAIN, DOUBLE, DROP
ELSE, END, ENTRY_POINT, ESCAPE, EXCEPTION, EXECUTE, EXISTS, EXIT, EXTERNAL, EXTRACT
FILE, FILTER, FLOAT, FOR, FOREIGN, FROM, FULL, FUNCTION
GDSCODE, GENERATOR, GEN_ID, GRANT, GROUP, GROUP_COMMIT_WAIT_TIME
HAVING, HOUR
IF, IN, INT, INACTIVE, INDEX, INNER, INPUT_TYPE, INSERT, INTEGER, INTO, IS, ISOLATION
JOIN
KEY
LONG, LENGTH, LOGFILE, LOWER, LEADING, LEFT, LEVEL, LIKE, LOG_BUFFER_SIZE
MANUAL, MAX, MAXIMUM_SEGMENT, MERGE, MESSAGE, MIN, MINUTE, MODULE_NAME, MONEY, MONTH
NAMES, NATIONAL, NATURAL, NCHAR, NO, NOT, NULL, NUM_LOG_BUFFERS, NUMERIC
OF, ON, ONLY, OPTION, OR, ORDER, OUTER, OUTPUT_TYPE, OVERFLOW
PAGE_SIZE, PAGE, PAGES, PARAMETER, PASSWORD, PLAN, POSITION, POST_EVENT, PRECISION, PROCEDURE, PROTECTED, PRIMARY, PRIVILEGES
RAW_PARTITIONS, RDB$DB_KEY, READ, REAL, RECORD_VERSION, REFERENCES, RESERV, RESERVING, RETAIN, RETURNING_VALUES, RETURNS, REVOKE, RIGHT, ROLLBACK
SECOND, SEGMENT, SELECT, SET, SHARED, SHADOW, SCHEMA, SINGULAR, SIZE, SMALLINT, SNAPSHOT, SOME, SORT, SQLCODE, STABILITY, STARTING, STARTS, STATISTICS, SUB_TYPE, SUBSTRING, SUM, SUSPEND
TABLE, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TRANSACTION, TRIGGER, TRIM
UNCOMMITTED, UNION, UNIQUE, UPDATE, UPPER, USER
VALUE, VALUES, VARCHAR, VARIABLE, VARYING, VIEW
WAIT, WHEN, WHERE, WHILE, WITH, WORK, WRITE
YEAR
Операторы:
||, -, *, /, <>, <, >, ,(запятая), =, <=, >=, ~=, !=, ^=, (, )
[001022]
Сиротские Master-записи Как с помощью SQL найти записи таблицы, которых нет в другой таблице?
Вот пример:
| with PeopleHiddenForm.PersonQuery.SQL do begin Add('Select P.Last, P.First, P.Middle, P."Suffix", P.KeyNo, COUNT(PersMemL.PersonKeyNo)'); Add('From Person P Left Outer Join '); Add(' PersMemL PersMemL'); Add('On ((P.KeyNo = PersMemL.PersonKeyNo))'); Add('Group By P.Last, P.First, P.Middle, P.Suffix, P.KeyNo'); Add('Having ((Count(PersmemL.PersonKeyNo) = 0))'); |
На практике этот способ оказывается очень эффективным, по крайней мере, с локальным SQL в таблицах Paradox.
- David G. Wachtel [001048]
Delphi 1
Назначение SQL-счетчика переменной
|
query.Close; query.SQL.Clear; query.SQL.Add('select count(*) from table where field = :XXX'); Query.ParamByName('XXX').AsString := value; query.Open; while Query.Eof <> True do begin SqlCount := Query.Fields[0].AsInteger; Query.Next end; |
Объявление
| property RecordCount: Longint; |
Времени исполнения и только для чтения. Свойство RecordCount определяет количество записей в наборе данных. Количество возвращаемых записей может зависеть от сервера и не зависит от границ задаваемого диапазона. [001216]
Как удобнее работать с динамически формируемыми запросами? Nomadic советует:
В процессе работы с БД иногда необходимо выполнить какие-то мелкие запросы. Держать для этого где-то временную Query меня лично ломает, посему ловите творение (под Delphi) - модуль для создания временных TQuery и работы с ними.
примеры использования:
|
var S:string; ... S := FastLookUp( format( 'select A.F1 from A,B where A.F4=B.F4 and B.F9=%d', [1] ) ); with GiveMeResultSet( 'select*from A where F1="777"' ) do try .... finally Free; {не забудьте!} end; ..... if NOT ExecuteSQL( 'delete from A' ) then ShowMessage( 'Something Wrong' ); ..... |
|
{ Temporary Queries Creatin' and handlin' (c) 1997-98 by Volok Alexander (D1/D2) creation date: 30.10.1997 last update : 17.06.1998 } unit TmpQuery; interface uses DBTables; const InternalDBname = 'MAIN'; {Изменять по вкусу - TDataBase.DataBaseName} type TSQLScript = {$IFDEF WIN32} string {$ELSE} PChar {$ENDIF}; {Создаст куери с текстом запроса, но не откроет его} function CreateTempQuery(SQLscript: TSQLscript): TQuery; {Создаст куери и откроет запрос - не забудьте прибить} function GiveMeResultSET(SQLscript: TSQLscript): TQuery; {Проверит непустоту выборки, заданной ...} function CheckExistence(SQLscript: TSQLscript): boolean; {Вытащит аж одно значение(лукап) из выборки, заданной ...} function FastLookUP(SQLscript: TSQLscript): string; {Выполнит запрос и сообщит результат} function ExecuteSQL(SQLscript: TSQLscript): boolean; implementation uses Forms; function CreateTempQuery(SQLscript: TSQLscript): TQuery; begin Result:= TQuery.Create(Application); with Result do begin DatabaseName := InternalDBname; {$IFDEF WIN32} SQL.Text := SQLscript; {$ELSE} SQL.SetText(SQLscript); {$ENDIF} end; end; function ExecuteSQL(SQLscript: TSQLscript): boolean; begin with CreateTempQuery(SQLscript) do begin try ExecSQL; Result := True; except Result := False; end; Free; end; end; function CheckExistence(SQLscript: TSQLscript): boolean; begin with GiveMeResultSET(SQLscript) do begin Result := NOT EOF; Free; end; end; function GiveMeResultSET(SQLscript: TSQLscript): TQuery; begin Result := CreateTempQuery(SQLscript); with Result do try Open; except Free; Result:= NIL; end; end; function FastLookUP(SQLscript: TSQLscript): string; begin with GiveMeResultSET(SQLscript) do begin try Result:= Fields[0].AsString; except Result:= ''; end; Free; end; end; end. |
Delphi 1
Поиск записи в SQL DataSet В случае изменения содержимого полей редактирования сделайте следующее:
|
Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT * FROM <таблица> WHERE <поле> Like ''' + SpeedEdit.Text + '*'''); Query1.Open; |
При попытке выполнения такого оператора SQL - 'DELETE from T39 T39C0 WHERE T39C0.F1LHT35=253291661' SQL-сервер ругается на недопустимый синтаксис. В чем я неправ? Nomadic отвечает:
В данном случае, видимо, T39C0 расценивается как псевдоним. Hо стандартом SQL-92 такое запрещено в DELETE.
Цитата собственно из этого стандарта (сборник из delete и names and identifiers, определение identifier пропущено, просто набор <simple latin letter> | <digit>, начинается с буквы): Format <delete statement: positioned>::= delete from <table name> where current of <cursor name> <table name> ::= <qualified name> | <qualified local name> <qualified name> ::= [<shema name><period>] [<qualified identifier>] <qualified identifier> ::=<identifier> <shema name>::=[<catalog name><period>]<unqualified shema name> <unqualified shema name>::=<identifier> <catalog name>::=<identifier> <qualified local name>::= MODULE <period><local table name> <local table name>::=<qualified identifier> Стандартом запрещено вот такое select test.a, p_test.a from test p_test; вот это не по стандарту, хотя Microsoft SQL Server такое ест. [001286]
Delphi 1
Поиск с помощью SQL Предположим:
- если вашей таблицы определены следующие поля... last_name char (n), first_name char (n) то... select last_name+', '+first_name from person where first_name='john'
- если вашей таблицы определены следующие поля... person_name char (n) (например, Lennon, John) то... select person_name from person where person_name like '%John' <--- 'John' должен быть в конце строки, еще используйте '%John%'
Как получить результирующим полем разницу между хранимой датой и текущей датой? Nomadic отвечает:
SELECT CAST((поле_с_датой -"NOW") AS INTEGER) FROM MyBase Получишь результат в днях. [001351]
Delphi 1
SQL и поле даты Есть множество способов сделать это:
- Если дата константа, используйте: WHERE Date = #31/11/95# В зависимости от "настроек вашей страны", это могло бы быть и #11/31/95#. Попробуйте оба: один из них работает.
SQL-запросы в Delphi Примечание: Данный документ представляет собой коллективный труд нескольких авторов, которые индивидуально несут ответственность за качество предоставленной здесь информации. Borland не предоставлял, и не может предоставить никакой гарантии относительно содержимого данного документа.
Введение
Компонент TQuery
Свойство SQL
Свойство Params
Источник данных
Команда Format
Open против ExecSQL
Компонент TStoredProc
TDatabase
Выводы
1. Введение
Компоненты Delphi для работы с базами данных были созданы в расчете на работу с SQL и архитектурой клиент/сервер. При работе с ними вы можете воспользоваться характеристиками расширенной поддержки удаленных серверов. Delphi осуществляет эту поддержку двумя способами. Во-первых, непосредственные команды из Delphi позволяют разработчику управлять таблицами, устанавливать пределы, удалять, вставлять и редактировать существующие записи. Второй способ заключается в использовании запросов на языке SQL, где строка запроса передается на сервер для ее разбора, оптимизации, выполнения и передачи обратно результатов.
Данный документ делает акцент на втором методе доступа к базам данных, на основе запросов 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.
- Создайте объект TQuery
Листинг 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; |
Этого должно быть достаточно для пользователя, знающего 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; |
| Query1.params[0].asString := '900'; |
| Query1.params[0].asString := edit1.text; |
| Query1.ParamByName('DEPT_NO').asInteger := 900; |
Листинг 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; |
Источник данных
В предыдущем примере пользователь мог ввести номер отдела, и после выполнения запроса отображался список сотрудников этого отдела. А как насчет использования таблицы 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; |
Фактически, техника использования 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']) |
| tblName := 'EMPLOYEE'; fldName := 'EMP_ID'; fldValue := 3; Format('Select * from %s where %s=%d', [tblName, fldName, fldValue]) |
Листинг 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; |
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; |
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 имеет свойство 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:\IBLOCAL\EXAMPLES\EMPLOYEE.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; |
Другой темой является использование 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 в ваших приложениях. [001356]
Delphi 1
SQL: - сортировка вычисляемого поля Иногда схема данных требует, чтобы набор данных имел вычисляемый результат. В приложениях Delphi в случае использования SQL это возможно, но эта технология немного разнится в зависимости от используемого типа данных.
Для локального SQL, включая таблицы Paradox и dBASE, вычисляемому полю дают имя с использованием ключевого слова AS. При этом допускается ссылаться на такое поле для задания порядка сортировки с помощью ключевой фразы ORDER BY в SQL-запросе. Например, используя демонстрационную таблицу ITEMS.DB: SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL FROM "ITEMS.DB" I ORDER BY TOTAL В данном примере вычисляемому полю было присвоено имя TOTAL (временно, только для ссылки), после чего оно стало доступным в SQL-запросе для выражения ORDER BY.
Вышеуказанный метод не поддерживается в InterBase. Тем не менее, сортировать вычисляемые поля в таблицах InterBase (IB) или сервере Local InterBase Server все же возможно. Вместо использования имени вычисляемого поля, в выражении ORDER BY используется порядковое число, представляющее собой позицию вычисляемого поля в списке полей таблицы. Например, используя демонстрационную таблицу EMPLOYEE (расположенную в базе данных EMPLOYEE.GDB): SELECT EMP_NO, SALARY, (SALARY / 12) AS MONTHLY FROM EMPLOYEE ORDER BY 3 DESCENDING В то время, как таблицы IB и LIBS используют второй метод, и не могут воспользоваться первым, оба метода доступны при работе с локальным SQL. К примеру, используя SQL-запрос для таблицы Paradox, и приспосабливая его для работы с относительной позицией вычисляемого поля, а не его именем: SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL FROM "ITEMS.DB" I ORDER BY 3 [001358]
SQL: - суммирование вычисляемого поля Бывают случаи, когда в приложении Delphi, которое для получения доступа к данным использует SQL, необходимо узнать сумму вычисленных данных. Другими словами, необходимо с помощью SQL создать вычисляемое поле и применить к нему функцию SUM.
При выполнении такой операции с SQL-таблицами (например, Local InterBase Server), все достаточно тривиально, и сумма вычисляется простым использованием функции SUM с указанием поля. Например, используя демонстрационную таблицу EMPLOYEE (из базы данных EMPLOYEE.GDB): SELECT SUM(SALARY / 12) FROM EMPLOYEE Та же самая методика применима в случае возвращаемого набора данных, в котором значения группируются в другом столбце с помощью утверждения GROUP BY: SELECT EMP_NO, SUM(SALARY / 12) FROM EMPLOYEE GROUP BY EMP_NO ORDER BY EMP_NO Пока SQL базы данных поддерживают суммирование вычисляемых полей, локальный SQL этого делать не будет. Для получения результатов нужны другие методы, например копирование результатов запроса с вычисляемым полем во временную таблицу (как и в случае компонента TBatchMove), и использование компонента TQuery для вычисления суммы данных во временной таблице. [001360]
SQL: - использование функции SUBSTRING SQL-функция SUBSTRING может использоваться в приложениях Delphi, работающих с запросами к локальной SQL, но она не поддерживается при работе с таблицами InterBase (IB) и Local InterBase Server (LIBS). Ниже приведен синтаксис функции SUBSTRING, примеры ее использования в запросах к local SQL, и альтернатива для возвращения тех же результатов для таблиц IB/LIBS.
Синтаксис функции SUBSTRING: SUBSTRING(<column> FROM <start> [, FOR <length>]) Где:
<column> - имя колонки таблицы, из которой должна быть получена подстрока (substring).
<start> место в значении колонки, начиная с которого извлекается подстрока.
<length> длина извлекаемой подстроки.
Функция SUBSTRING в примере ниже возвратит второй, третий и четвертый символы из колонки с именем COMPANY: SUBSTRING(COMPANY FROM 2 FOR 3) Функция SUBSTRING может быть использована и для списка полей в SELECT-запросе, где ключевое слово WHERE допускает сравнение значения с определенным набором колонок. Функция SUBSTRING может использоваться только с колонками типа String (на языке SQL тип CHAR). Вот пример функции SUBSTRING, использующей список колонок в SELECT-запросе (используем демонстрационную таблицу Paradox CUSTOMER.DB): SELECT (SUBSTRING(C."COMPANY" FROM 1 FOR 3)) AS SS FROM "CUSTOMER.DB" C Данный SQL-запрос извлекает первые три символа из колонки COMPANY, возвращаемой как вычисляемая колонка с именем SS. Вот пример функции SUBSTRING, использованной в SQL-запросе с ключевым словом WHERE (используем ту же самую таблицу): SELECT C."COMPANY" FROM "CUSTOMER.DB" C WHERE SUBSTRING(C."COMPANY" FROM 2 FOR 2) = "an" Данный запрос возвратит все строки таблицы, где второй и третий символы в колонке COMPANY равны "ar".
Так как функция SUBSTRING не поддерживается в базах данных IB и LIBS, операции с подстроками со списком колонок в запросе невозможны (исключение: IB может работать с подстроками через функции, определяемые пользователем, User-Defined Functions). Но с помощью оператора LIKE и сопутствующих символьных маркеров подстановки возможно работать с подстрокой и в случае WHERE. Вот пример на основе таблицы EMPLOYEE (в базе данных EMPLOYEE.GDB): SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE WHERE LAST_NAME LIKE "_an%" Данный SQL-запрос возвратит все строки таблицы, где второй и третий символы в колонке LAST_NAME равны "an", см. предыдущий пример на основе таблицы Paradox. Базам данных IB и LIBS для выполнения сравнения подстроки в операторе запроса WHERE данный метод необходим (и невозможно воспользоваться функцией SUBSTRING), таблицы же Paradox и dBASE (например, local SQL) могут воспользоваться любым методом. [001362]
