Запросы и параметры, или как избавится от многих проблем….

Материал из DRKB

Запросы и параметры, или как избавится от многих проблем….[править | править код]

Где я? О чём вы?[править | править код]

Компонент TQuery имеет свойство Params: TParams, а компонент TADOQuery имеет свойство Parameters:TParameters. Эти объекты позволяют в уже готовом запросе подставить какие-либо значения. Делается это таким незамысловатым способом:

ADOQuery1.Active := False;
ADOQuery1.SQL.Text := 'SELECT * FROM MyTable WHERE MyField = :prm';
ADOQuery1.Parameters.ParseSQL(ADOQuery1.SQL.Text, True);
ADOQuery1.Parameters.ParamByName('prm').Value := 'чего-то там';
ADOQuery1.Active := True;


Для BDE код будет собственно таким:

Query1.Active := False;
Query1.SQL.Text := 'SELECT * FROM MyTable WHERE MyField = :prm';
Query1.Params.ParseSQL(Query1.SQL.Text, True);
Query1.Params.ParamByName('prm').Value := 'чего-то там';
Query1.Active := True;


А зачем это нужно?[править | править код]

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

...подготавливаем запрос один раз...

ADOQuery1.Active := False;
ADOQuery1.SQL.Text := 'SELECT * FROM MyTable WHERE MyField = :prm';
ADOQuery1.Parameters.ParseSQL(ADOQuery1.SQL.Text, True);


... всё запрос готов ...

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Active := False;
  ADOQuery1.Parameters.ParamByName('prm').Value := Edit1.Text;
  ADOQuery1.Active := True;
end;


2) При передаче даты и времени. Тут прикол вот в чём. Допустим стоит у вас SQL Server в США и использует формат времени и даты как "01/20/04 1PM", а клиент у вас работает один в Канаде где формат даты "20/01/04 01:00 PM", а другой в России где формат даты "20/01/04 13:00". При отсылке запроса с датой в виде строки надо обязательно согласовывать форматы даты времени клиента и сервера, а при передаче параметров не надо! Дело в том что преобразование параметра происходит на сервере, и сервер его сам преобразует в тот формат который ему нужен!

ADOQuery1.Active := False;
ADOQuery1.SQL.Text := 'SELECT * FROM MyTable WHERE LastDate > :prm';
ADOQuery1.Parameters.ParseSQL(ADOQuery1.SQL.Text, True);
ADOQuery1.Parameters.ParamByName('prm').Value := Now;
ADOQuery1.Active := True;


3) При необходимости использовать в запросе двоичные данные или Memo поля использование параметров - это единственный путь

ADOQuery1.Active := False;
ADOQuery1.SQL.Text := 'INSERT INTO MyTable (id, MyPicture)';
ADOQuery1.SQL.Add('VALUES (:Id, :pic)');
ADOQuery1.Parameters.ParseSQL(ADOQuery1.SQL.Text, True);
ADOQuery1.Parameters.ParamByName('id').Value := Now;
ADOQuery1.Parameters.ParamByName('pic').LoadFromFile('c:\mypic.bmp', ftGraphic);
ADOQuery1.ExecSQL;


Author: Автор: Vit (www.delphist.com, www.drkb.ru, www.unihighlighter.com, www.nevzorov.org)
ID: 02414



Примечание от Anatoly Podgoretsky

Для рантайм можно указать про добавление параметров, определение их типов, поскольку у людей часто возникают проблемы с типами параметров, с присвоением значений через As Функции и через Value.

with ADOQuery1.Parameters.AddParameter do
begin
  Name := 'prm';
  DataType := ftDateTime;
  Direction := pdInput;
  Value := Now;
end;


Есть и другие полезные свойства, например Attributes

строка ADOQuery1.Parameters.ParamByName('prm').Value := Now;

не нужна, но может использоваться.

Что здесь сделано?

  1. Создан явный параметр
  2. ему дано имя
  3. явно указан тип
  4. явно указано направление, что этот параметр входной
  5. и присвоено ему значение, эквивалент ParamByName

что это дает:

Это снимает все разночтения, которые могут возникнуть при автоматическом разборе выражения с типом и присвоением ему значения.


ID: 02415



Примечание от Петровича

Внесу и свою лепту

К параметрам запроса можно обращаться и по индексу. Например так:

with ADOQuery1 do
begin
  SQL.Text := 'UPDATE Customers SET Addr1 = :p WHERE CustNo = :c';
  Parameters[0].Value := 'г.Арбатов';
  Parameters[1].Value := 753;
  ExecSQL;
end;


Это полностью эквивалентно:

with ADOQuery1 do
begin
  SQL.Text := 'UPDATE Customers SET Addr1 = :p WHERE CustNo = :c';
  Parameters.ParamByName('p').Value := 'г.Арбатов';
  Parameters.ParamByName('c').Value := 753;
  ExecSQL;
end;


Кстати, обращаться по именам можно и чуть короче:

with ADOQuery1 do
begin
  SQL.Text := 'UPDATE Customers SET Addr1 = :p WHERE CustNo = :c';
  Parameters.ParamValues['p'] := 'г.Арбатов';
  Parameters.ParamValues['c'] := 753;
  ExecSQL;
end;


Но, вернусь к обращению по индексу.

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

with ADOQuery1 do
begin
  SQL.Text := 'UPDATE Customers SET Addr1 = :p, Addr2 = :p WHERE CustNo = :c';
  Parameters[0].Value := 'г.Арбатов';
  Parameters[1].Value := 753;
  ExecSQL;
end;


то результат выполнения будет отличаться от варианта:

with ADOQuery1 do
begin
  SQL.Text := 'UPDATE Customers SET Addr1 = :p, Addr2 = :p WHERE CustNo = :c';
  Parameters.ParamValues['p'] := 'г.Арбатов';
  Parameters.ParamValues['c'] := 753;
  ExecSQL;
end;


Все дело в том, что разбор SQL-оператора на уровне компонент Delphi весьма прост, и на мой взгляд делается не совсем корректно. А именно, не смотря на то, что во втором варианте SQL-оператора упоминаются лишь два параметра: p и c (параметр p упоминается дважды), Delphi создаст ТРИ параметра в массиве Params. Поэтому, оператор Parameters[1].Value := 753; выполнит присваивание значения не параметру с именем c, а второму экземпляру параметра p. А значение параметра c останется равным NULL. Соответственно, при выполнении ExecSQL ошибки возникнуть не должно, но результат будет неправильным. А вот при обращении по имени, все будет хорошо! Наверное, при передаче параметров запроса на SQL-сервер (или соответствующий движок БД), сами компоненты "вытаскивают" значения параметров тоже по имени. Поэтому, не смотря на то что после:

Parameters.ParamValues['p'] := 'г.Арбатов';
Parameters.ParamValues['c'] := 753;

заполненными будут лишь параметры с индексами 0 и 2, на правильной работе это не скажется, поскольку до параметра с индексом 1 по имени вообще нельзя "достучаться".

Казалось-бы, зачем так много говорить об этой проблеме? Можно ведь вообще не использовать доступ по индексу, как я и советовал ранее. Но, лично я все-таки использую его. Вот например небольшой объектик которым я люблю пользоваться. Может кому и пригодится. Приведу пример для ADO, но подобное существует и для других методов доступа к БД.

type
  TDbAdo = class
  private
    FConnection: TADOConnection;
  public
    constructor Create (AADOConnection: TADOConnection);

    // Возвращает закрытый TADOQuery с заполненными SQL.Text и Parameters.
    // Ответственность за уничтожение возвращенного TADOQuery лежит на вызывающем!
    function CreateQuery(SQLText: String; fParams: array of const; qParams: array of Variant): TADOQuery;

    // Выполняет SQL-оператор, и возвращает RowAffected
    function ExecSql(SQLText: String; fParams: array of const; qParams: array of Variant): Integer;

    // Возвращает значение первого поля первой записи результата SQL-запроса
    function GetField(SQLText: String; fParams: array of const; qParams: array of Variant): Variant;

    // Возвращает открытый TADOQuery с результатом SQL-запроса.
    // Ответственность за уничтожение возвращенного TADOQuery лежит на вызывающем!
    function CreateSelect(SQLText: String; fParams: array of const; qParams: array of Variant): TADOQuery;

    // Возвращает число записей в таблице TableName
    function GetRecCount(const TableName: string): Integer; overload;

    // Возвращает число записей в таблице TableName у которых значение поля FieldName равно Value
    function GetRecCount(const TableName, FieldName: string; Value: Variant): Integer; overload;

    // TADOConnection с которым соединен данный TDbAdo
    property Connection: TADOConnection read FConnection;
  end;


function SQLInfo(SQLText: string; fParams: array of const; qParams: array of Variant): string;

  function VarToSqlConstStr(v: Variant): string;
  begin
    case VarType(v) of
      varEmpty, varNull:
        Result := 'NULL';
      varSmallint, varInteger, varSingle,
      varDouble, varCurrency, varShortInt,
      varByte, varWord, varLongWord, varInt64:
        Result := v;
      varDate, varOLEStr, varStrArg, varString:
      begin
        Result := Trim(v);
        if Result = '' then
          Result := 'NULL'
        else
          Result := QuotedStr(Result);
      end;
      varBoolean:
        Result := 'NY'[Ord(Boolean(v))];
    else
      raise EConvertError.Create('VarToSqlConstStr: значение неизвестного типа ' + IntToStr(Ord(VarType(v))));
    end;
  end;

var
  i: Integer;
begin
  try
    Result := Format(SQLText, fParams) + '|';
    for i := 0 to High(qParams) do
      Result := Result + VarToSqlConstStr(qParams[i]) +',';
    SetLength(Result, Length(Result)-1);
  except
    Result := ExMsg(SQLText);
  end;
end;


constructor TDbAdo.Create(AADOConnection: TADOConnection);
begin
  inherited Create();
  FConnection := AADOConnection;
end;

function TDbAdo.CreateQuery(SQLText: string; fParams: array of const; qParams: array of Variant): TADOQuery;
var
  i: Integer;
begin
  Result := TADOQuery.Create(Self.Connection);
  with Result do
    try
      Connection := Self.Connection;
      SQL.Text := Format(SQLText, fParams);
      if High(qParams)+1 < Parameters.Count then
        raise Exception.CreateFmt('CreateADOQuery: Передано параметров (%d) меньше чем требует SQL-запрос (%d)', [High(qParams)+1, Parameters.Count]);
      for i := 0 to Parameters.Count-1 do
        Parameters[i].Value := qParams[i]; // <- вот доступ к параметрам по индексу
    except
      Result.Free;
      raise;
    end;
end;

function TDbAdo.ExecSql(SQLText: string; fParams: array of const; qParams: array of Variant): Integer;
begin
  try
    with CreateQuery(SQLText, fParams, qParams) do
      try
        Result := ExecSQL;
      finally
        Free
      end;
  except
    ExMsg('ExecSql: Ошибка выполнения SQL-оператора:'^M^J^I + SQLInfo(SQLText, fParams, qParams));
    raise;
  end;
end;

function TDbAdo.CreateSelect(SQLText: string; fParams: array of const; qParams: array of Variant): TADOQuery;
begin
  Result := CreateQuery(SQLText, fParams, qParams);
  try
    Result.Open;
  except
    Result.Free;
    ExMsg('CreateSelect: Ошибка выполнения SQL-оператора:'^M^J^I + SQLInfo(SQLText, fParams, qParams));
    raise;
  end;
end;

function tDbAdo.GetField(SQLText: string; fParams: array of const; qParams: array of Variant): Variant;
begin
  with CreateSelect(SQLText, fParams, qParams) do
    try
      Result := Fields[0].Value;
    finally
      Free;
    end;
end;

function TDbAdo.GetRecCount(const TableName: string): Integer;
begin
  Result := GetField('SELECT Count(1) FROM %s', [TableName], []);
end;

function TDbAdo.GetRecCount(const TableName, FieldName: string; Value: Variant): Integer;
begin
  Result := GetField('SELECT Count(1) FROM %s WHERE %s = :v', [TableName, FieldName], [Value]);
end;


Тут правда я использую некоторые функции из моей библиотеки. Нужный модуль (awString.pas) я уже выкладывал то-ли здесь, то-ли на Vingrade. Ну а кому лень искать, все нужные функции я добавлю в конец поста.

Теперь, как енто пользовать.

Например, кладем на форму (я обычно делаю жто в Data-модуле) ADOConnection1: TADOConnection.

Описываем соединение с поставляемой Borland демо-базой данных:

ADOConnection1.ConnectionString := 'FILE NAME=C:\Program Files\Common Files\System\OLE DB\Data Links\DBDEMOS.udl'


Затем, в OnCreate формы пишем:

ADOConnection1.Open;
MyDB := tDbAdo.Create(ADOConnection1);


Переменную MyDB описываем например как глобал:

var
  MyDB: TDbAdo;


Ну а далее все просто. Например:

procedure TForm3.Button1Click(Sender: TObject);
begin
  ShowMessage('Всего стран: ' + IntToStr(MyDB.GetRecCount('country')));
  ShowMessage('Всего стран в Северной Америке: '+IntToStr(MyDB.GetRecCount('Country', 'Continent', 'North America')));
  ShowMessage('Всего стран в Южной Америке: '+IntToStr(MyDB.GetRecCount('Country', 'Continent', 'South America')));

  ShowMessage('Площадь Канады: ' + IntToStr(
    MyDB.GetField('SELECT Area FROM Country WHERE Name = :p0', [], ['Canada'])
  ));

  with TStringList.Create do
  try
    // получение списка стран Южной Фмерики с численностью населения менее 10 000 000
    with MyDB.CreateSelect('SELECT Name, Population FROM Country'
                           + ' WHERE (Continent = :Continent)'
                           + ' AND (Population < :Population)'
                           , [], ['South America', 10000000]) do
    try
      while not Eof do
      begin
        Add(Fields[0].AsString + ' ' + Fields[1].AsString);
        Next;
      end;
    finally
      Free;
    end;

    ShowMessage('Список стран Южной Америки с численностью населения менее 10 000 000:'^M^J + Text);
  finally
    Free;
  end;
end;


Вот и все. Ах, да. Как обещал, необходимые функции:

var GetLocationInfoStrFunc: function (Addr: Pointer): string = nil;

// Процедура используемая для получения информации об адресе Addr (имя модуля,
// процедуры, и пр.).
// Если определена, то используется функцией GetExText.
function LocationInfoStr(Addr: Pointer): string;
begin
  if Assigned(GetLocationInfoStrFunc) then
    Result := GetLocationInfoStrFunc(Addr)
  else
    Result := '[' + SysUtils.IntToHex(Cardinal(Addr), 8) + ']';
end;

// Возвращает объект последнего исключения или Nil (если вызвана вне except .. end)
function LastExcept: Exception;
var
  c :TClass;
  o :tObject;
  s1,s2 :String;
begin
  o := ExceptObject;
  if  o = nil  then
    Result := Nil
  else if  o is Exception  then
    Result := Exception(o)
  else begin
    // Исключение возникло за пределами нашего EXE-файла, например в Dll
    // или ExceptObject вообще не имеет в предках класса Exception!
    // Возвращать его нельзя, поскольку полноценная работа с ним невозможна.
    // В частности, если это Exception из Dll, то его поле Message, имеющее
    // тип LongString нельзя будет переприсваивать - память под текущую
    // строку выделена не нашим распределителем памяти.
    // Можно было-бы заменить его собственным экземпляром, освободив текущий
    // экземпляр, но нам недоступна переменная System.RaiseListPtr. И кроме
    // того возможны ситуации когда обработка текущего исключения возобновится
    // в Dll, тогда опять возникнет проблемма.
    // Не нахожу ничего лучшего, как возбудить исключение с соответствующим
    // текстом ошибки.
    // Если в предках ExceptObject есть Exception, то включить его Message
    // в текст возбуждаемонго исключения.
    c := o.ClassType;
    while (c <> nil) and (not c.ClassNameIs('Exception')) do
      c := c.ClassParent;

    if c = nil then
    begin  // в иерархии o нет 'Exception' - в принципе это нонсенс
      s1 := 'В предках ExceptObject отсутствует Exception.';
      s2 := '';
    end
    else
    begin  // Есть. Перенесем его Message
      s1 := 'ExceptObject не принадлежащий модулю.';
      s2 := ^M^J^I'  Message = "'+Exception(o).Message+'"';
    end;
    raise EInvalidCast.CreateFmt('Ошибка LastExcept: %s'^M^J^I'  ExceptObject = %s at %s%s'
                                   ,[s1,o.ClassName,LocationInfoStr(ExceptAddr),s2]);
  end;
end;

function GetExMsg(e: Exception): string;
begin
  if e = Nil then
    Result := ''
  else with e do
  begin
    Result := Message;
    //
    //if  ClassName = 'EInOutError'  then
    //  Result := 'Ошибка в/в ('+IntToStr(EInOutError(e).ErrorCode)+')'
    //            +^M^J^I+Result;
  end;
end;

function GetExText(e: Exception; ExAddr: Pointer = nil): string;
var
  s: string;
begin
  if e = nil then
    Result := ''
  else with e do
  begin
    Result := GetExMsg(e);

    s := ClassName;
    if s = 'Exception' then Exit;

    if ExAddr <> nil then
      s := s + ' at ' + LocationInfoStr(ExAddr);

    if e.ClassNameIs('EInOutError') then
      s := s + ' (Ошибка в/в ' + IntToStr(EInOutError(e).ErrorCode) + ')';

    if Result <> '' then
      s := s + ^M^J^I + Result;

    Result := s;
  end;
end;

function ExMsg(e: Exception; const Msg: string = ''): string; overload;
begin
  if e = Nil then
    Result := 'No exception'
  else
  begin
    Result := GetExMsg(e);
    if Msg <> '' then
      Result := Msg + ^M^J^I + Result;
    e.Message := Result;
  end;
end;

function ExMsg(const Msg: string = ''): string; overload;
begin
  Result := ExMsg(LastExcept, Msg);
end;

function ExMsg(const Fmt: string; const Args: array of const): string; overload;
begin
  Result := ExMsg(Format(Fmt, Args));
end;


ID: 02416