DynamicWhere server side with IDADataset

[claudio.piffer]

Hi,

I have found a strange “problem” when I use, server side, the DynamicWhere.

The problem is this (I use the last 10.0.0.1467 version for Delphi):

in DASchemaModeler I defined a new datatable with AutoSQL statment. In this case when I use the DynamicWhere (only to server side) when run the code the system raise an exception that the param :DAPARAM_1 is not found.

If I set the same datatable with SQL (the DASchemaModeler create automatically the statment with the {WHERE} macro) and I not modified it everything works regularly.

It seems that when working in AutoSQL (and only on the server side) the macro {WHERE} is not recognized.

Is this actually a problem or am I doing something wrong?

[EvgenyK (Evgeny Karpov)]

Hi,

Can you provide simple testcase that reproduces this issue, pls?
you can send it directly to support@ for keeping it privately

[claudio.piffer]

Hi,

I try but the Oracle database that I use is very large. I try to create a test with another type of database

Thank you very much!

[EvgenyK (Evgeny Karpov)]

Hi,

you can share your code that causes this exception.
in some cases it should be enough.

if you launch your project in debug mode, what will be call-stack?

[claudio.piffer]

Hi

I try to explain here:

I use Delphi 10.3.3, DA last version, UniDac last version and Oracle database.

In schema Modeler I defined a new Table with statment type AutoSQL:

In this configuration when I run this code:

var
  LUserDataSet: IDADataset;
  LUserClientIP: string;
begin
  LUserDataSet := LoginSchema.NewDataset(Connection, 'User');
  LUserDataSet.DynamicWhere.Clear;
  with LUserDataSet.DynamicWhere do
    Expression := NewBinaryExpression(NewField('', 'LOGIN_NT'), dboEqual, AUserName, datString);

  LUserDataSet.Open;

raise this exception:

If I change only the statment type in SQL (autogenerate):

the code above work fine.

[EvgenyK (Evgeny Karpov)]

Hi,

@claudio.piffer

LUserDataSet := LoginSchema.NewDataset(Connection, 'User'); 
LUserDataSet.DynamicWhere.Clear; with LUserDataSet.DynamicWhere do 
  Expression := NewBinaryExpression(NewField('', 'LOGIN_NT'), dboEqual, AUserName, datString);

NewDataset method has overload method that accepts DynamicWhere expression as xml string. if you pass DynamicWhere XML to that overload method, will it solve this issue in AutoSQL mode?

[claudio.piffer]

@EvgenyK

namicWhere expression as xml string

Which?

[EvgenyK (Evgeny Karpov)]

Hi,

2nd method, i.e.

// sXml: Unicodestring;
with LUserDataSet.DynamicWhere do begin
  Expression := NewBinaryExpression(NewField('', 'LOGIN_NT'), dboEqual, AUserName, datString);
  sXml := Xml;
  Clear;
end;
LUserDataSet := LoginSchema.NewDataset(Connection, 'User',[],sXml);

[claudio.piffer]

Many thank EvgenyK, with your code works.

I only have a couple of comments:

1. with your code I must to create the dataset twice. One for define the XML “where clause” and one for inject the xml

2. Why this not work with AutoSQL but work if I define the {WHERE} macro? In this mode I can create only one instance of the dataset

Best Regards

[EvgenyK (Evgeny Karpov)]

Hi,

@claudio.piffer

Why this not work with AutoSQL but work if I define the {WHERE} macro? In this mode I can create only one instance of the dataset

this is AlwaysGenerateDynamicWhereStatement parameter, that is has False value by default. if aWhereClause isn’t specified, SQL is generated w/o WHERE {WHERE} line.

workarounds:

• pass non-empty aWhereClause
• pass AlwaysGenerateDynamicWhereStatement parameter with True value in overloaded method.

@claudio.piffer

with your code I must to create the dataset twice. One for define the XML “where clause” and one for inject the xml

you are right. better code:

with Connection.GetWhereBuilder do begin
  Expression := NewBinaryExpression(NewField('', 'LOGIN_NT'), dboEqual, AUserName, datString);
  sXml := Xml;
  Free;
end;

[claudio.piffer]

Many thanks EvgenyK!!