# TDAWherebuilder using collations
**URL:** https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291
**Category:** Data Abstract
**Tags:** delphi
**Created:** [October 13, 2020, 7:18am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291 "2020-10-13T07:18:35Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 13, 2020, 7:18am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/1 "2020-10-13T07:18:35Z")
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
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 13, 2020, 8:27am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/2 "2020-10-13T08:27:22Z")
Hi,
I think, you should declare specific macro on server-side and use it like
```sql
WHERE {COLLATE(SearchName,Latin1_general_CI_AI)} LIKE '%Dlugol%'
```
or
```sql
WHERE {LATIN_COLLATE(SearchName)} LIKE '%Dlugol%'
```
* * *
your macro should generate
```auto
SearchName COLLATE Latin1_general_CI_AI
```
see `TDAMSSQLMacroProcessor.Length` as a example for such macro and `TDASQLMacroProcessor.RegisterMacros` for registration of macro.
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 13, 2020, 9:02am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/3 "2020-10-13T09:02:58Z")
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?
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 13, 2020, 9:16am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/4 "2020-10-13T09:16:45Z")
Hi,
check the [MacroProcessor](https://docs.dataabstract.com/Technologies/MacroProcessor/) article.
you should register your macro in default macroprocessor that can be received from default connection like
```delphi
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](https://docs.dataabstract.com/API/Delphi/Classes/TDataAbstractService/#OnAfterAcquireConnection) event.
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 13, 2020, 11:20am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/5 "2020-10-13T11:20:26Z")
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?
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 13, 2020, 11:41am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/6 "2020-10-13T11:41:45Z")
Hi,
you can’t use `{Collate(xxx)}` as is in DynamicWhere expression.
use [NewMacro](https://docs.dataabstract.com/API/Delphi/Classes/TDAWhereBuilder/#NewMacro) like
```delphi
Result := wb.NewBinaryExpression(
aDataSource.DataTable.LogicalName,
wb.NewMacro('Collate',[fld_vw_POSearchName,'Latin1_general_CI_AI']),
dboLike,
'%'+ sFilter + '%',
datWideString);
```
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 13, 2020, 12:36pm UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/7 "2020-10-13T12:36:26Z")
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%
< /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
< constant type="WideString" null="0">Latin1_general_CI_AI)
< /macro>
< constant type="WideString" null="0">%brand%
< /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?
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 13, 2020, 2:46pm UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/8 "2020-10-13T14:46:38Z")
Hi,
this one works for me:
```auto
wb.Expression := wb.NewBinaryExpression(
wb.NewMacro('COLLATE_LATIN', [
wb.NewField(ServerDataModule.tbl_Customers.LogicalName, 'CustomerID')]),
dboLike,
'%'+ Edit1.Text + '%',
datWideString);
```
[VCLApplication.zip](https://talk.remobjects.com/uploads/short-url/fanwHMCBBedDtyIZYLf7kcYwAVi.zip) (32.7 KB)
Note: ADO driver cannot process correctly `COLLATE :param1` so I’ve hardcoded it as `COLLATE Latin1_general_CI_AI`.
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 14, 2020, 6:30am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/9 "2020-10-14T06:30:19Z")
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?
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 14, 2020, 8:07am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/10 "2020-10-14T08:07:28Z")
Hi,
can you create a simple testcase that reproduces that error, pls?
you can drop email to support@ for keeping privacy.
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 22, 2020, 9:15am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/11 "2020-10-22T09:15:37Z")
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
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 22, 2020, 9:40am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/12 "2020-10-22T09:40:11Z")
Hi,
yes, it is possible.
you can use `IDADataset.OnBeforeOpen` event and check `Self.SQL` property
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 22, 2020, 10:53am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/13 "2020-10-22T10:53:33Z")
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
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 22, 2020, 10:58am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/14 "2020-10-22T10:58:05Z")
Hi,
it can be something like
```auto
procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject;
const aDataset: IDADataset; const aIncludeSchema: Boolean;
const aMaxRecords: Integer);
begin
if aDataset.LogicalName = myName then
aDataset.OnBeforeOpen := MyBeforeOpen;
end;
```
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 22, 2020, 11:20am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/15 "2020-10-22T11:20:56Z")
Thanks.
I’m sorry but IDADataset does not have a SQL property.
---
### Author:  [@EvgenyK](https://talk.remobjects.com/u/EvgenyK)
#### Post date: [October 22, 2020, 11:51am UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/16 "2020-10-22T11:51:08Z")
Hi,
Sorry, cast it to IDAServerDataset:
```auto
(aDataset as IDAServerDataset).SQL
```
---
### Author:  [@kasteren](https://talk.remobjects.com/u/kasteren)
#### Post date: [October 22, 2020, 12:05pm UTC](https://talk.remobjects.com/t/tdawherebuilder-using-collations/23291/17 "2020-10-22T12:05:27Z")
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.