Prepare()ing a parameterized SQL statement in TOraQuery

[siapf_fp]

Hello everybody,

We (my team leader and myself) are currently working on a little database test program, in order to leverage some of the TOraQuery features.

Here is our current setup for this test :

- Delphi 2009 with Updates 3 and 4
- ODAC 6.90.0.55 for Delphi 2009 (registered package)
- Oracle: Oracle8 Enterprise Edition 8.0.5.0.0
- OCI: Version 8.0.5.0.1

The application itself is as simple as possible :

on a Form (TForm) we put a TOraSession component (set up for the demo user SCOTT)
In the Form.create() event we dynamically instantiate TOraQuery and we then feed it
with our SQL statement on the Oracle demo user SCOTT. A DBGrid component is used in order to
show the results of the Query execution and the parameter value is provided by
the user via an edit component.

Here is the code :

Code: Select all

procedure TForm1.FormCreate(Sender: TObject);
begin
  OraSession1.Schema := '';

  FQuery := TOraQuery.Create(self);
  FQuery.Session := OraSession1;
  FQuery.UniDirectional := True;
  with FQuery do
  begin
    SQL.Clear;
    SQL.Add('SELECT ');
    SQL.Add(' ENAME');
    SQL.Add('FROM ');
    SQL.Add(' EMP');
    SQL.Add('WHERE  ');
    SQL.Add(' JOB = :value');
    Prepare;
  end;
end;

Please note the Prepare() statement.
Then, having filled the Edit field, we trigger the Query execution. We do it with a TButton component :

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
begin
  if Trim(Edit1.Text) = '' then
    Exit;
  DBGrid1.DataSource.DataSet := FQuery;
  FQuery.Close;
  FQuery.ParamByName('value').AsAnsiString := AnsiString(ToUpper(Edit1.Text));
    FQuery.Open;
end;

There lies our problem: using Button2Click() more than one time triggers an ORA-01002 exception.
After a little investigation we figured out some facts :

- calling unprepare() then prepare() before filling the parameter value could solve the problem (of course the first prepare is then made useless)
- not closing the query and just refreshing it instead does the job too. Below is that version of the code :

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
begin
  if Trim(Edit1.Text) = '' then
    Exit;
  DBGrid1.DataSource.DataSet := FQuery;
  FQuery.ParamByName('value').AsAnsiString := AnsiString(ToUpper(Edit1.Text));

  if FQuery.active then
    FQuery.Refresh
  else
    FQuery.Open;
end;

We used to use the first version of TForm1.Button2Click() with (ODAC 4.00/Delphi 7) and it did work. Installing ODAC 6.90 demo with Delphi 7 and testing a similar application with it gave us the same behavior as with Delphi 2009. Thus our post here.

We could not find any information about this behavior modfication in the documentation. Could somebody enlighten us about this issue ?

Thank you in advance.

[Challenger]

Thank you for information. We have reproduced the problem. The investigation of the problem is in progress. As soon as we solve the problem we will let you know.