FilterSQL with bind variables works after the second open

Edmund Pfundstein · Post by **Edmund Pfundstein** » Wed 27 Aug 2008 14:21

Hi,
since 6.50.0.37 21-Aug-08, I have a problem with TORATABLE and FILTERSQL with the use of bind variables. After the first open the table isempty. Following I Close the Table, set the value of the bind variables, open the table again and it works. Therefore I use the following workaround.

Example:
..
torasession.option.useunicode := true
..
..
//BEGIN-WORKAROUND
// Workaround des Fehlers, dass bei FilterSQL mit Bindvariablen der
// 1. Open von TOraTable die Datenmenge leer bleibt.
if TableSchaden.FilterSQL = '' then
begin
TableSchaden.FilterSQL := 'rownum 0 then
begin
TableSchaden.FilterSQL := 'IDKUN = :IDKUN and IDVER = :IDVER' + GetSQLLoginFilterString;
TableSchaden.prepare;
TableSchaden.ParamByName('IDKUN').asinteger := aIdKun;
TableSchaden.ParamByName('IDVER').asinteger := aIdVer;
end
else
begin
TableSchaden.FilterSQL := 'IDKUN = :IDKUN' + GetSQLLoginFilterString;
TableSchaden.prepare;
TableSchaden.ParamByName('IDKUN').asinteger := aIdKun;
end;
tableschaden.open;

I hope you can reproduce this situation.

Thank you
Edmund Pfundstein

Plash · Post by **Plash** » Thu 28 Aug 2008 08:00

We could not reproduce the problem. Please send to odac*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

Edmund Pfundstein · Post by **Edmund Pfundstein** » Mon 15 Sep 2008 15:13

Hi,

here is the example to reproduce the problem:

procedure TForm1.ButTestSQLFilterClick(Sender: TObject);
var
tblDept: TOraTable;
oraSession: TOraSession;
begin
oraSession := TOraSession.Create(self);
try
oraSession.HomeName := 'OraServerHome10201';
oraSession.ConnectString := 'scott/tiger@corlocal';
oraSession.Connect;
tblDept := TOraTable.create(nil);
try
tblDept.TableName := 'dept';
tblDept.FilterSQL := 'deptno = :deptno';
tblDept.Prepare;
tblDept.ParamByName('deptno').asinteger := 10;
tblDept.KeyFields := 'loc';
tblDept.Open;
try
Assert(not tblDept.IsEmpty);
finally
tblDept.Close;
end;
orasession.Disconnect;
finally
if assigned(tblDept) then
FreeAndNil(tblDept);
end;
finally
if assigned(oraSession) then
FreeAndNil(oraSession);
end;
end;

Thank you

Edmund Pfundstein

Plash · Post by **Plash** » Tue 16 Sep 2008 07:11

You should set the KeyFields property before calling the Prepare method because setting the KeyFields property resets the SQL property to an empty string.