Changing the SQL for a single field

Classic Servoy
1

I’m developing a solution in Servoy 3.5, and would like to customize one aspect of a “follow-me” search: For particular fields, I would like to override the standard SQL generation and create my own SQL, while leaving the rest of the fields the default.

The particular case is that I am using the full-text search facility of PostgreSQL for certain text area fields.

Is this possible? Thanks in advance!

2

Have a look at the loadRecords(query) function.

3

Thanks!

That certainly would work, but it appears that I would have to manually recreate the functionality that Servoy already has in translating the user input in the find form into SQL.

A more detailed example…

In three fields on the form (let’s say two NUMBERs and a TEXT_AREA), the user types:

Field1: “<1”
Field2: “<10”
Field3: “X Y Z”

Servoy would normally do the work of translating those into the appropriate SQL query, something like:

SELECT table.primary_key FROM table WHERE (table.column1 < 1) AND (table.column2 < 10) AND (table.column3 = “X Y Z”);

What I’d like to do is override this process, but just for Field3, so I can produce a custom SQL fragment for just that portion of the query.

Of course, I can always just recreate the Servoy functionality, but that seems too much like real work. :)

4

You could create a special Search Form and substitute Field3 with a global field then using the onSearch event you could parse the data inserted in the global field and do whatever you like.
Have a look at the onFind and onSearch events, IMO that is the way to go.

5

Xof:
That certainly would work, but it appears that I would have to manually recreate the functionality that Servoy already has in translating the user input in the find form into SQL.

…

Of course, I can always just recreate the Servoy functionality, but that seems too much like real work. :)

Also check out:

databaseManager.getSQL(foundset)

databaseManager.getSQLParameters(foundset)