Where and when do i correctly add a macro in my DAService

Data Abstract
1

i defined a macro that will help create the sql being constructed in my DAService

i found some old post that said to add the function in the DataAbstractServiceAfterAcquireConnection event, but it seems that that one is being called multiple times (so it seems in debugging) and the Registerproc is adding the same over and over again…(Tlist.add)

so is this the correct approach (

DataAbstractServiceAfterAcquireConnection(
aSender: TObject; const aConnectionName: string;
const aAcquiredConnection: IDAConnection);
begin
var mac:IDAHasMacroProcessor;
if Supports(aAcquiredConnection, IDAHasMacroProcessor, mac) then
mac.GetMacroProcessor.RegisterProc(‘CheckBitwise’, CheckBitwise, 2);

2

and as a followup, although the correct sql gets constructed at first sight, i want to be able to check a bit of an integer value, but the macro converts the bit value into a string, so it compares an int with a string…

what am i missing here?

3

forget this last remark, there was bad data which led me to the wrong conclusion

4

Hi,

OnAfterAcquireConnection is fired only so it is suitable place.

Note: You are using TROPooledClassFactory so you are using services from pool. also connection may have already registered method in macro processor .

workaround:

  • register variable and check for if it, like
var mac:IDAHasMacroProcessor;
if Supports(aAcquiredConnection, IDAHasMacroProcessor, mac) then begin
  if mac.GetMacroProcessor.IndexOfName('mytest') = -1 then begin
    mac.GetMacroProcessor.AddVariable('mytest');
    mac.GetMacroProcessor.RegisterProc(‘CheckBitwise’, CheckBitwise, 2);
  end;
end;
6

so here is the problem, since the macro accepts a string array parameters

i don’t see how i can use this to produce an integer & compare in sql

so i need a (field & 1024 = 1024) macro

it seems that DA is trying to do it with varchar params which do not give the desired result

so i must be doing something wrong or can’t it be done this way?

7

Hi,

can you show how your CheckBitwise was implemented?

it can looks like

function YYY.CheckBitwise(Sender: TObject; const Parameters: array of string): string;
begin
  Result := Format('(%s & %1:s = %1:s)',[Parameters[0], Parameters[1]]);
end;
8

tried various formats but now its:

var aField:=Parameters[0];
var aIntegerMask:=Parameters[1];
Result := Format('((%s & %s) = %s)', [aField, aIntegerMask, aIntegerMask]);

also tried

var aField:=Parameters[0];
var aIntegerMask:=Parameters[1].asinteger;
Result := Format('((%s & %d) = %d)', [aField, aIntegerMask, aIntegerMask]);

but since the field is an int, the sql AND needs to be an int…

so instead of DA generating

@P7 nvarchar(1),@P8 nvarchar(1)

for the ((GSI_INTBits & @P7) = @P8)

it should be @P7 int,@P8 int

9

Hi,

what you pass to macro inside dynamic where?
You can pass integer value here

this code:

procedure TForm19.Button1Click(Sender: TObject);
var
  d: TDASQL92QueryBuilder;
begin

  d:= TDASQL92QueryBuilder.Create;
  d.MainTable.MasterTable := 'table1';

  d.Where.Expression := TDAMacroExpression.Create('test',
                             [TDAConstantExpression.Create(1, datInteger),
                              TDAConstantExpression.Create(2, datInteger)]);
  memo1.Lines.Add(d.GenerateSelectSQL);
  d.Free;
end;

generates

SELECT
   *
FROM
   table1
WHERE
   {test(1, 2)}

so macro should work as expected

10

i use TDAWhereBuilder.NewConstant(const aValue: Variant): TDAWhereExpression;

but i’ll pass an int and see what happens

11

ok that works, got it wrong on the client side, no more @ params left, just plain sql now for select

((GSI_IntBits & 1) = 1))

formatted as %d

12

it will work also with

function YYY.CheckBitwise(Sender: TObject; const Parameters: array of string): string;
begin
  Result := Format('(%s & %1:s = %1:s)',[Parameters[0], Parameters[1]]);
end;

because Parameters are just string values ['GSI_IntBits','1'] and result is also string

13

i know but since it is a value that needs to be checked, i’m checking with trytoint to see if it’s a valid integer

that said, it’s probably better to check that client side, i’ll make a wrapper for it clientside