C/FRONT select and How to use SetFilter

Business Central/NAV Technical Forum
1

Hello guys,

I want to implement something like this sql wher clause.

SELECT * FROM WHERE like ‘%VALUE’

Is it possible to use SetFilter in CFRONT to filter all records, which match like ‘%VALUE’ or like ‘VALUE%’

Greeting

2

Hi Eldorado,

You should be able to write (if VALUE can be expressed as a literal):

DBL_SetFilter(hTable, fieldNo, (DBL_U8*)"*VALUE", NULL);

or, if VALUE is a variable, which is more common:

DBL_U8* myValue = “VALUE”;

DBL_SetFilter(hTable, fieldNo, (DBL_U8*)"*%1", myValue, NULL);

If you want a case and accent insensitive match, use the @ operator in the string: “@*%1”

Note: If you are running against SQL Server this type of query is not able to utilize any index on the field, so you will get a clustered index scan, which will be slow for large tables.

Dean.

3

Hello Dean,

Thanks for your fast reply. Is it the same sitnax in CFRONT .NET Wrapper (i’m using it).

4

Is it possible to use CFRONT.dll for Navision 5. to connect on Navision 4

5

CFront.Net does not allow the argument passing version of the method, only the fixed string literal version, so if you have variables you would like to substitute you will need to format the string yourself using Format() or StringBuilder methods.

To answer your second question, you cannot mix the executable versions because a later version will always need to upgrade your database. So you would no longer have a Navision 4 database.

6

I found the solution. In .NET Wrapper we must use this pattern “*VALUE”. Thank you for support.

7

10x for provided help. And more question. Is it possible to make negative in CFRONF (.NET Wrapper)

SELECT * FROM TABLE WHERE NOT (COLUMN1 like ‘a%’ AND COLUMN2=‘123’)

8

No, because the c/side filter syntax does not allow a NOT covering an expression in this way, and to break that down to apply the NOT for each argument would requre an OR across columns, which is not possible.