How compare a DB filed BLOB type text using TCriteria Like/ILike ?

BIZ TMS Aurelius
1

Hi, I have an entity with a field BLOB text type and I need search inside the field a string using like/ilike. Basically I need to do this:

Linq.ILike(fieldName, value)

where "fieldName" is a field name of the BLOB text in the .abs database and "value" is the string to be searched inside the BLOB text field.

I did researching SQLFunctions, TProjection, TLinkExpression but I haven't clear how implement or extend this functionality.

Thanks in advance.

2

TMS Aurelius just translates its criteria objects into an SQL statement. Linq.Like and Linq.ILike already exist and will simply create a condition as <fieldname> LIKE <value>.

If your database needs a specific treatment for blob types, then in deed you might need to create some specific SQLFunction for it to work.

3

Ok, the way is SQL functions, the input is from GraphQL and I'm translating the GraphQL to Aurelius, using SQL functions how I must declare SQL generator and how use the SQL function from GraphQL ?

Thanks.

4

I'm not sure how is related to GraphQL or the relevance of it.
But in any case, you can refer to this documentation topic to learn how to use SQL functions:

https://doc.tmssoftware.com/biz/aurelius/guide/queries.html#sqlfunction

5

Hi, I tried TSQLFunctions but is not works, I need convert the TBlob field of entity as TBlob.AsString and compare with %stringValue% using like/ilike. TUtils.ValueToVariant make conversion of TBlob as bytes and I need that cast will be AsString.

Any suggestion will be very appreciated.

Thanks.

6

The one way will be have a callback when TCriteria.List process each record with an option indicating in a variable if the record is included or excluded, for now I filter the records after TCriteria.List return.

7

I'm sorry. I really don't understand what you mean here.

Aurelius generates SQL statement which are executed directly in the database. It's the database that retrieves the filtered records from the executed SQL statement.

Are you asking for filtering the records client-side, after the all records are retrieved unfiltered? If yes, then you just interact with the object list, it's just pure Delphi code, not Aurelius related.

8

The last is that I did, also I tried declare a property as transient (with a getter) in the entity but when it is used in TCriteria the error "property not found" is raised.

9

Yes, because as I already explained, Aurelius executes an SQL statement from the criteria, if the value is not in the database, it can't be queried.