Запросы с параметрами

[Brainenjii]

Решил попробовать освоить это тёмное искусство, пока местами не очень успешно
Пользуюсь UIB, пишу -

Код: Выделить всё

  Result := FALSE;
  Transaction := LocalReadTransaction;
  SQL.Text := 'SELECT COUNT(ID) FROM REFERENCES_INDEX WHERE ID = :ID';
  Params.Clear;
  Try
    Params.ByNameAsString['ID'] := IntToStr(Value);
    Open;
  Except On E: Exception Do
    Begin
      Log(SQL.Text);
      Log(STR_ERROR_READ_REASON + E.Message);
      Exit;
    End;
  End;
  Result := TRUE;


В логе выходит:

03.03.2010 13:50:12: SELECT COUNT(ID) FROM REFERENCES_INDEX WHERE ID = :ID
03.03.2010 13:50:12: Не удалось прочесть данные. Причина: Parameter "ID" not found.
03.03.2010 13:50:12: Query not open.

В чём ошибка?

[Vadim]

Brainenjii
Это потому, что свойство Params у Вас непонятно к чему относится. Вы как-нибудь этот вопрос осветите...

[Brainenjii]

Это я пишу наследника к TUIBQuery ^_^ Собственно, уже разобрался... Проблема была загадочной - после смены строчки

Код: Выделить всё

SQL.Text := 'SELECT COUNT(ID) FROM REFERENCES_INDEX WHERE ID = :ID';


на

Код: Выделить всё

SQL.Clear;
SQL.Add('SELECT COUNT(ID) FROM REFERENCES_INDEX WHERE ID = :ID');


всё заработало. Я удивлён ^_^

[v-t-l]

Код: Выделить всё

SQL.Text := 'SELECT COUNT(ID) FROM REFERENCES_INDEX WHERE ID = :ID';
  Params.Clear; // - а, может быть, здесь все дело?

По идее, после изменения свойства SQL компонент должен сам создать список параметров. А вы его очищаете.

[Brainenjii]

А и точно ^_^ Видимо пока искал проблему,

Код: Выделить всё

  SQL.Clear;
  SQL.Add(aSQL.SQL);


переползло под

Код: Выделить всё

  Params.Clear;


Теперь не удивлён ^_^ Спасибо ^_^

[dunin]

Хм... странный запрос. Он вроде при любых условиях должен возвращать "1"? Или что-то недопонимаю?

Добавлено спустя 19 секунд:
Смысл запроса?

[Brainenjii]

Это так, для примера ^_^ На самом деле всё немножко по-другому

Код: Выделить всё

...
BQueryClass = Class(TUIBQuery)
  Private
    Updates: TList;
    Procedure Free;
  Public
    Function Get(Const aSQL: BSQLClass): Boolean;
    Function Get(Const aString: String): Boolean;
    Procedure Post(Const aSQL: BSQLClass);
    Procedure Post(Const aString: String);
    Function Go: Boolean;
    Constructor Build;
    Destructor Burn;
End;

Var
  LocalDB: TUIBDatabase;
  ReadTransaction, WriteTransaction: TUIBTransaction;

Implementation

Constructor BQueryClass.Build;
Begin
  Inherited Create(nil);
  Updates := TList.Create;
  FetchBlobs := TRUE;
End;

Destructor BQueryClass.Burn;
Var
  i: Integer;
Begin
  For i := 0 To Updates.Count - 1 Do
    BSQLClass(Updates[i]).Burn;
  Updates.Free;
  Inherited Destroy;
End;

Function BQueryClass.Get(Const aSQL: BSQLClass): Boolean;
Var
  i: Integer;
Begin
  Result := FALSE;
  Transaction := ReadTransaction;
  Params.Clear;
  SQL.Clear;
  SQL.Add(aSQL.SQL);
  For i := 0 To aSQL.Params.Count - 1 Do
    Params.ByNameAsString[BSQLParamClass(aSQL.Params[i]).Iterator] :=
      BSQLParamClass(aSQL.Params[i]).Value;
  Try
    If aSQL.IsProcedure Then
      Execute
    Else
      Open;
  Except On E: Exception Do
    Begin
      Log(SQL.Text);
      Log(E.Message);
      Exit;
    End;
  End;
  Result := TRUE;
End;

Function BQueryClass.Get(Const aString: String): Boolean;
Begin
  With BSQLClass.Build(aString) Do
    Begin
      Result := Get(aSQL);
      Burn;
    End;
End;

Procedure BQueryClass.Post(Const aSQL: BSQLClass);
Var
  NewSQL: BSQLClass;
  i: Integer;
Begin
  NewSQL := BSQLClass.Build(aSQL.SQL);
  For i := 0 To aSQL.Params.Count - 1 Do
    NewSQL.AddParam(BSQLParamClass(aSQL.Params[i]).Iterator,
      BSQLParamClass(aSQL.Params[i]).Value);
  Updates.Add(aSQL);
End;

Procedure BQueryClass.Post(Const aString: String);
Var
  aSQL: BSQLClass;
Begin
  With BSQLClass.Build(aString) Do
    Begin
      Post(aSQL);
      Burn;
    End;
End;

Function BLocalQueryClass.Go: Boolean;
Var
  i, j: Integer;
Begin
  Result := FALSE;
  Transaction := WriteTransaction;
  Transaction.StartTransaction;
  For i := 0 To Updates.Count - 1 Do
    Begin
      Params.Clear;
      SQL.Text := BSQLClass(Updates[i]).SQL;
      For j := 0 To BSQLClass(Updates[i]).Params.Count - 1 Do
        Params.ByNameAsString[
          BSQLParamClass(BSQLClass(Updates[i]).Params[j]).Iterator] :=
          BSQLParamClass(BSQLClass(Updates[i]).Params[j]).Value;
      Try
        BSQLClass(Updates[i]).Burn;
        If aSQL.IsProcedure Then Execute
        Else ExecSQL;
      Except On E: Exception Do
        Begin
          For j := i + 1 To Updates.Count - 1 Do
            BSQLClass(Updates[j]).Burn;
          Log(SQL.Text);
          Log(E.Message);
          Transaction.Rollback;
          Updates.Clear;
          Exit;
        End;
      End;
    End;
  Transaction.Commit;
  Updates.Clear;
  Result := TRUE;
End;


и BSQLClass -

Код: Выделить всё

Unit BSQLUnit;

{$mode objfpc}{$H+}

Interface

Uses
  Classes, SysUtils;

Type

{ BSQLParam }

BSQLParamClass = Class
  Private
    bIterator: String;
    bValue: String;
  Public
    Property Value: String Read bValue;
    Property Iterator: String Read bIterator;
    Constructor Build(Const aIterator, aValue: String);
    Destructor Burn;
End;

Type

{ BSQLClass }

BSQLClass = Class
  Private
    bIsProcedure: Boolean;
    bParams: TList;
    bSQL: String;
  Public
    Property SQL: String Read bSQL;
    Property Params: TList Read bParams;
    Property IsProcedure: Boolean Read bIsProcedure;
    Procedure AddParam(Const aIterator, aValue: String);
    Procedure AddParam(Const aIterator: String; Const aValue: Integer);
    Procedure AddParam(Const aIterator: String; Const aValue: TDateTime);
    Constructor Build(Const aString: String; Const ForProcedure: Boolean = FALSE);
    Destructor Burn;
End;

Implementation

{ BSQLParamClass }

Constructor BSQLParamClass.Build(Const aIterator, aValue: String);
Begin
  Inherited Create;
  bIterator := aIterator;
  bValue := aValue;
End;

Destructor BSQLParamClass.Burn;
Begin
  Inherited Destroy;
End;

{ BSQLClass }

Procedure BSQLClass.AddParam(Const aIterator, aValue: String);
Begin
  Params.Add(BSQLParamClass.Build(aIterator, aValue));
End;

Procedure BSQLClass.AddParam(Const aIterator: String; Const aValue: Integer);
Begin
  Params.Add(BSQLParamClass.Build(aIterator, IntToStr(aValue)));
End;

Procedure BSQLClass.AddParam(Const aIterator: String; Const aValue: TDateTime);
Begin
  Params.Add(BSQLParamClass.Build(aIterator,
    FormatDateTime('dd.mm.yy', aValue)));
End;

Constructor BSQLClass.Build(Const aString: String; Const ForProcedure: Boolean);
Begin
  Inherited Create;
  bParams := TList.Create;
  bSQL := aString;
  bIsProcedure := ForProcedure;
End;

Destructor BSQLClass.Burn;
Var
  i: Integer;
Begin
  For i := 0 To Params.Count - 1 Do
    BSQLParamClass(Params[i]).Burn;
  Params.Free;
End;

End.


LocalDB и транзакции определить или в Initialization или ещё где-нибудь ^_^ И потом в коде писать что-то вроде

Код: Выделить всё

Procedure Test;
Var
  aQuery: BQuery;
  aSQL: BSQLClass;
Begin
  aQuery := BQuery.Build;
  aQuery.Get('SELECT * FROM MYTABLE');
  While Not(aQuery.EOF) Do
    Begin
      aQuery.Next;
    End;
  aSQL := BSQLClass.Build('SELECT * FROM MYTABLE WHERE ID = :ID');
  aSQL.AddParam('ID', Random(10));
  aQuery.Get(aSQL);
  aSQL.Burn;
  If Not(aQuery.EOF) Do
    Begin
     
    End;
  aQuery.Burn;
End;


По-моему так немного меньше слов при работе с запросами и эти юниты можно таскать с собой из проекта в проект (так и делал раньше, но без BSQLClass и параметризированных запросов соответственно (Updates был TStringList'ом и параметры с ним хранить было проблематично ^_^))
P.S. кстати, если записи с ID = :ID нет, то вернёт 0 ^_^ Проверка на то - есть запись, или нет ^_^

[v-t-l]

Повторяю:

Код: Выделить всё

Params.Clear

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

[dunin]

...
По-моему так немного меньше слов при работе с запросами и эти юниты можно таскать с собой из проекта в проект (так и делал раньше, но без BSQLClass и параметризированных запросов соответственно (Updates был TStringList'ом и параметры с ним хранить было проблематично ^_^))
P.S. кстати, если записи с ID = :ID нет, то вернёт 0 ^_^ Проверка на то - есть запись, или нет ^_^

Ну понятно.
Как-то сложно все. ИМХО. У меня на этот случай есть давно написанный юнит. Тоже таскаю из проекта в проект. Недавно на UIB переписал. Пример уже в какой-то ветке приводил... Вот как-то так:

Код: Выделить всё

uses
  uib,
  Variants,
  SysUtils;

const
  sql001 = 'Неверно заданы параметры в процедуре';

  function RunQuery(Base: TUIBDataBase; Transaction: tUIBTransaction; _SQL:String; ParamsValue:array of variant; DeadAfterRun: boolean = False): tUIBQuery; overload;

  function BoolSQL (Base: TUIBDataBase; Transaction: tUIBTransaction; TableName: string; SelField: string; ValueField: variant; DoCommitRetaining: boolean = False): boolean; overload;
  function BoolSQL (Base: TUIBDataBase; Transaction: tUIBTransaction; TableName: string; IdField: string; SelFields: array of string; ValueFields: array of variant; DoCommitRetaining: boolean = False): boolean; overload;

  function InsSQL   и т.д....

...

function BoolSQL (Base: TUIBDataBase; Transaction: tUIBTransaction; TableName: string; SelField: string; ValueField: variant; DoCommitRetaining: boolean = False): boolean; overload;
  var ibSQL: tUIBQuery;
begin
  ibSQL:= tUIBQuery.Create(Base);
  ibSQL.DataBase:= Base;
  ibSQL.Transaction:= Transaction;
  try
    ibSQL.SQL.Add('select first 1 count('+SelField+') as itis from '+TableName+' where '+SelField+'='+':itis');
    ibSQL.Params.ByNameAsVariant['itis']:= ValueField;
    ibSQL.Open;
    Result:= not (ibSQL.Fields.ByNameAsVariant['itis'] = 0);
  finally
    ibSQL.Close;
    FreeAndNil(ibSQL);
    //if DoCommitRetaining then Base.DefaultTransaction.CommitRetaining;
  end;//finally
end;   

Пользуюсь примерно так:

Код: Выделить всё

if not BoolSQL(UIBDatabase1, UIBtransaction1, 'users', 'name', 'Brainenjii')
then showmessage('Досье на Brainenjii еще не заведено')

[Brainenjii]

dunin: У меня на этот счёт BSQLClass ^_^ Вернее, транзакция и TUIBDatabase определяется BQueryClass(например для одного проекта, который работает с 2 базами данных (локальной и удалённой) я определил 2 класса - BLocalQueryClass и BForeignQueryClass. А для упрощения создания запросов - или создание константы (например SQL_INSERT ITEM = 'INSERT INTO MYTABLE(ID, CAPTION) VALUES (:ID, :CAPTION), а затем

Код: Выделить всё

  aSQL := BSQLClass.Build(SQL_INSERT_ITEM);
  aSQL.AddParam('ID', 1);
  aSQL.AddParam('CAPTION', 'Random' + IntToStr(Random(1000)));
  aQuery.Post(aSQL);
  aQuery.Go;
  aSQL.Burn;


или определить ещё один конструктор для BSQLClass -

Код: Выделить всё

...BSQLClass = Class
  Private
    bIsProcedure: Boolean;
    bParams: TList;
    bSQL: String;
  Public
    Property SQL: String Read bSQL;
    Property Params: TList Read bParams;
    Property IsProcedure: Boolean Read bIsProcedure;
    Procedure AddParam(Const aIterator, aValue: String);
    Procedure AddParam(Const aIterator: String; Const aValue: Integer);
    Procedure AddParam(Const aIterator: String; Const aValue: TDateTime);
    Constructor Build(Const aString: String; Const ForProcedure: Boolean = FALSE);
    Constructor BuildForBoolSQL(Const TableName, FieldName: String; Const aValue: Variant): Boolean;
    Destructor Burn;
End;
...
Constructor BSQLClass.BuildForBoolSQL(Const TableName, FieldName: String; Const aValue: Variant): Boolean;
Begin
  Inherited Create;
  bParams := TList.Create;
  bSQL := 'SELECT FIRST 1 COUNT (' + FieldName + ') AS ITIS FROM ' + TableName  + ' WHERE ' + FieldName + ' = :ITIS';
  AddParam('ITIS', aValue);
End;


и потом, соответственно

Код: Выделить всё

  aQuery.Get(BSQLClass.BuildForBoolSQL('MYTABLE', 'MYFIELD', 1));


v-t-l, Params.Clear - это так, для перестраховки ^_^ У меня один BQueryClass может помнить довольно много запросов, так что, чтобы вдруг чего от предыдущих не осталось ^_^ А BSQLClass.AddParam - это устанавливает значение для параметра. Вернее, даже сохраняет его, чтобы потом установить при исполнении запроса ^_^ Как-то так ^_^

[ViruZ]

Подскажите, как правильно передать в качестве параметра дату (компонент UIBQuery)?
Запрос через ibexpert работает отлично.

Код: Выделить всё

SELECT
xic.Image_Date,
xstc.patient_no, xstc.patient_age,
xpc.AutoPatientID, xpc.EthnicGroup, xpc.PatientID

FROM XImage_Cache xic, XStudy_Cache xstc, XPatient_Cache xpc
WHERE (xpc.AutoPatientID = xic.Patient_No) AND
      (xstc.AutoStudyID = xic.Study_No) AND
      (xic.Image_Date BETWEEN CAST(:StartDate as date) AND cast(:EndDate as date))

UNION

SELECT
xi.Image_Date, xi.Image_Comments,
xp.AutoPatientID, xp.OtherPatientID, xp.EthnicGroup, xp.PatientID

FROM XImage xi, XStudy xst, XPatient xp
WHERE (xp.AutoPatientID = xi.Patient_No) AND
      (xst.AutoStudyID = xi.Study_No) AND
      (xi.Image_Date BETWEEN cast(:StartDate as date) AND Cast(:EndDate as date)) AND
      NOT EXISTS (SELECT AutoImageID
                  FROM XImage_Cache xic
                  WHERE xic.AutoImageID = xi.AutoImageID)


Добавлено спустя 32 минуты 9 секунд:
Скрин ошибки к предыдущему посту

error.GIF

Добавлено спустя 20 минут 10 секунд:
Упростил текст запроса для отбора только необходимых мне данных, передача даты пока остается актуальной.

Код: Выделить всё

SELECT DISTINCT
xpc.AutoPatientID, xpc.EthnicGroup,
xstc.patient_age,
xic.Image_Date

FROM XImage_Cache xic, XStudy_Cache xstc, XPatient_Cache xpc
WHERE (xpc.AutoPatientID = xic.Patient_No) AND
      (xstc.AutoStudyID = xic.Study_No) AND
      (xic.Image_Date BETWEEN CAST(:StartDate as date) AND cast(:EndDate as date))

UNION

SELECT distinct
xp.AutoPatientID, xp.EthnicGroup,
xst.patient_age,
xi.Image_Date

FROM XImage xi, XStudy xst, XPatient xp
WHERE (xp.AutoPatientID = xi.Patient_No) AND
      (xst.AutoStudyID = xi.Study_No) AND
      (xi.Image_Date BETWEEN cast(:StartDate as date) AND Cast(:EndDate as date)) AND
      NOT EXISTS (SELECT AutoImageID
                  FROM XImage_Cache xic
                  WHERE xic.AutoImageID = xi.AutoImageID)
ORDER BY 1

[Brainenjii]

Я дату передаю как строку - FormatDateTime('dd.mm.yy', aDateValue);

[ViruZ]

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

Добавлено спустя 52 минуты 11 секунд:
Еще один вопрос, я просто пока только осваиваю запросы, поэтому попрошу сильно не пинать
Есть запрос вида

Код: Выделить всё

SELECT DISTINCT
xpc.AutoPatientID, xpc.EthnicGroup,
xstc.patient_age,
xic.Image_Date
FROM XImage_Cache xic, XStudy_Cache xstc, XPatient_Cache xpc
WHERE (xpc.AutoPatientID = xic.Patient_No) AND
(xstc.AutoStudyID = xic.Study_No) AND
(xic.Image_Date BETWEEN CAST('01.04.2010' as timestamp) AND cast('02.04.2010' as timestamp))
UNION
SELECT distinct
xp.AutoPatientID, xp.EthnicGroup,
xst.patient_age,
xi.Image_Date
FROM XImage xi, XStudy xst, XPatient xp
WHERE (xp.AutoPatientID = xi.Patient_No) AND
(xst.AutoStudyID = xi.Study_No) AND
(xi.Image_Date BETWEEN cast('01.04.2010' as timestamp) AND Cast('02.04.2010' as timestamp)) AND
NOT EXISTS (SELECT AutoImageID
FROM XImage_Cache xic
WHERE xic.AutoImageID = xi.AutoImageID)
ORDER BY 1


Как мне получить в первой графе только уникальные AutoPatientID?