TDAWherebuilder using collations

Data Abstract
1

Using RO DA 9.7.115.1441
I’d like to use a different collation in the where conditions with a NewBinaryExpression.
Something like:

WHERE SearchName COLLATE Latin1_general_CI_AI LIKE '%Dlugol%'

How can I do this?

Thanks

Kind regards
Michel

2

Hi,

I think, you should declare specific macro on server-side and use it like

WHERE {COLLATE(SearchName,Latin1_general_CI_AI)}  LIKE '%Dlugol%'

or

WHERE {LATIN_COLLATE(SearchName)}  LIKE '%Dlugol%'

your macro should generate

SearchName COLLATE Latin1_general_CI_AI

see TDAMSSQLMacroProcessor.Length as a example for such macro and TDASQLMacroProcessor.RegisterMacros for registration of macro.

3

Thanks for your quick reply.

If I understand correctly I should implement my own macro in uDAADOInterfaces.pas and register it.

Do you have an example for using these macro’s?

4

Hi,

check the MacroProcessor article.

you should register your macro in default macroprocessor that can be received from default connection like

  if Connection.UseMacroProcessor then
    if Supports(Connection, IDAHasMacroProcessor, lmacro) then begin
      if Assigned(lmacro.GetMacroProcessor)  then begin
         // register macro here
         lmacro.GetMacroProcessor.RegisterProc('COLLATE', MyCollateMethod, 1);
      end;
    end;

it can be done, say, in the OnAfterAcquireConnection event.

5

Okay, I tried this I had my doubts about using a macro with the DAWherebuilder… I implemented and registered my Collate function.

I thought I’d replace then fieldname with the macro:
Result := wb.NewBinaryExpression(aDataSource.DataTable.LogicalName, '{Collate('+fld_vw_POSearchName+', '+'Latin1_general_CI_AI)}', dboLike, '%'+ sFilter + '%', datWideString)

But I get an error:

'{Collate(SearchName, Latin1_general_CI_AI)}' field can't be used inside the where clause

I only need the collation on a specific field and the client decides the search action which does not always include the field SearchName.

How can I accomplish this?

6

Hi,

you can’t use {Collate(xxx)} as is in DynamicWhere expression.

use NewMacro like

Result := wb.NewBinaryExpression(
        aDataSource.DataTable.LogicalName, 
        wb.NewMacro('Collate',[fld_vw_POSearchName,'Latin1_general_CI_AI']), 
        dboLike, 
        '%'+ sFilter + '%', 
        datWideString);
7

A thanks. Didn’t noticed the NewMacro functions.

Getting closer…

NewMacro has two overloads, one with a string (the macro name) and one with a string and a array of TDAWhereExpression.

I tried both:

Result := wb.NewBinaryExpression( wb.NewMacro('Collate(SearchName, Latin1_general_CI_AI)'),
                                  dboLike,
                                  '%'+ sFilter + '%',
                                  datWideString 
                                  )

xml:

< binaryoperation operator="Like">
	< macro name="Collate(SearchName, Latin1_general_CI_AI)"/>
	< constant type="WideString" null="0">%brand%</ constant>
< /binaryoperation>

and

Result := wb.NewBinaryExpression( wb.NewMacro('Collate', [
                                    wb.NewField(aDataSource.DataTable.LogicalName, fld_vw_POSearchName),
                                    wb.NewConstant('Latin1_general_CI_AI)')
                                    ]),
                                  dboLike,
                                  '%'+ sFilter + '%',
                                  datWideString 
                                  )

xml:

< binaryoperation operator="Like">
	< macro name="Collate">
		< field tablename="vw_PO">SearchName</ field>
		< constant type="WideString" null="0">Latin1_general_CI_AI)</ constant>
	< /macro>
	< constant type="WideString" null="0">%brand%</ constant>
< /binaryoperation>

Both give the same error on the server:
A "safecall" error occured, but the source object did not provide the proper error information. Sorry we cannot be more helpful at this time.

What am I doing wrong?

8

Hi,

this one works for me:

    wb.Expression := wb.NewBinaryExpression(
            wb.NewMacro('COLLATE_LATIN', [
            wb.NewField(ServerDataModule.tbl_Customers.LogicalName, 'CustomerID')]),
            dboLike,
            '%'+ Edit1.Text + '%',
            datWideString);

VCLApplication.zip (32.7 KB)

Note: ADO driver cannot process correctly COLLATE :param1 so I’ve hardcoded it as COLLATE Latin1_general_CI_AI.

9

Thanks,

Your example works fine here. I’ve copy-paste your code. But the exact same macro doesn’t work in our application… I get the same where expression but the server keeps giving the safecall error. If I remove the macro (use the old code) and it works fine, of course without the collation.

I see that the macro is registered in the service.
After that I get a msgBINWriteException:

A "safecall" error occured, but the source object did not provide the proper error information. Sorry we cannot be more helpful at this time.

Any ideas where to look for the problem? Are there special properties I have to set for macro’s?

10

Hi,

can you create a simple testcase that reproduces that error, pls?
you can drop email to support@ for keeping privacy.

11

Hello,

I tried to build a testcase but I can’t reproduce the error in a testcase.
I got the macro working in our application but sometimes it gives the safecall error. It has something to do with the acquired connection but I can’t pinpoint it. Running out of time.
I’m going to try to find another solution.

Is it possible to change the generated WHERE condition in the SQL statement on the server?

Thanks

12

Hi,

yes, it is possible.
you can use IDADataset.OnBeforeOpen event and check Self.SQL property

13

I can’t seem to find where the dataset is on the server.

I have a DataAbstract service with the table in the Schema.
The client has a TDAMemDataTable with the dynamic where. There I find an OnBeforeOpen event.

Where should I implement the OnBeforeOpen to change the SQL property?

Thanks

14

Hi,

it can be something like

procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject;
  const aDataset: IDADataset; const aIncludeSchema: Boolean;
  const aMaxRecords: Integer);
begin
  if aDataset.LogicalName = myName then
    aDataset.OnBeforeOpen := MyBeforeOpen;
end;
15

Thanks.

I’m sorry but IDADataset does not have a SQL property.

16

Hi,

Sorry, cast it to IDAServerDataset:

(aDataset as IDAServerDataset).SQL
17

Yes, it works.

When I have time, I going to try to find out why the macro is not always working.

Thanks you very much.