Case insensitive operator

Data Abstract
1

Any chance to add operator to TDABinaryOperator to manage case insensitive search, as this:

SELECT descripcion FROM gps.gps_sitios WHERE descripcion ~* 'multi';

  TDABinaryOperator = (dboAnd, dboOr, dboXor, dboLess, dboLessOrEqual, dboGreater,
    dboGreaterOrEqual, dboNotEqual, dboEqual, dboLike, dboIn, dboAddition, dboSubtraction,
    dboMultiply, dboDivide, dboNotIn, dboLikeCaseInsensitive);


  function ProcessBinaryExpression(AExpression: TDABinaryExpression): string;
  const
    TDABinaryOperatorStr: array[TDABinaryOperator] of string =
    ('AND', 'OR', '' {dboXor}, '<', '<=', '>', '>=', '<>', '=', 'LIKE', '' {dboIn}, '+', '-', '*', '/', '' {dboNotIn}, '~*');

or maybe any other ideas you have to support case insensitive search on a field , like expressed here

Best regards.

2

Hi,

I don’t see ~* operator in SQL92 standard

3

Agree. What to do then? If DA only support SQL92 standard that limits the additional features every database engine have. Must be a generic way to overpass that, not implying having to wrote a custom SQL foe every datasets. Is the same case for LIMIT , TOP, etc.

I need to make case insensitive search. How to implement in DA?

Best regards

4

Hi,

in your case, ~* operator exists only in PostgreSQL and doesn’t in other DB. Also other DBs have no similar operator. in case LIMIT, TOP, we have similar constructions in other DBs.

you can implement custom macro that will work as you want

5

Sure, but this operator will be used only in dynamic where. Why don’t allow to use and just raise an exception when not supported? Is preferable at no support at all…

Will work with autosql? Can explain HOW to use that? Thanks.

6

Hi,

  • create own function that accepts 2 values and generates (value1 ~* value2). see example in TDAPostgresMacroProcessor.DatePart.
  • get macro processor for current connection similar to TDataAbstractService.AssignMacroProcesserEvent method
  • register your function with macro processor like TDASQLMacroProcessor.RegisterMacros.

you can put your code that adds a new function into the OnAfterAcquireConnection event of DAService