Form Data Model & MySQL queries

[lollo901]

Hi Community,

We have a MySQL DB connected via Apache Sling Connection Pooled DataSource.

It's working fine for getting, inserting and updating data through the forms.

We need now to implement a search on a column in a table in the db from a form, but I found out that the default data model get service cannot be modified with a custom SQL statement (for example "SELECT * FROM Table WHERE Column LIKE (?)") in the form data model editor.

Is there a way to create a new service with a custom SQL statement? Or is there a way to use custom SQL statements with form data model? 

thanks in advance

[Mayank_Gandhi]

@lollo901 you should pass the search string as a param from the FDM and it will return the record or empty based on the availability of the record. 

[lollo901]

I write the exact statement that I would like to use in order to retrieve my data

SELECT *
FROM users
WHERE firstname LIKE '%namepart%'

How can i pass the param '%namepart%' in order to retrieve all records that contains my search?

I try to explain better

If I have a firstname record that is "John Fitzgerald", if the user just type "John", the service should retrieve all the entries that have John in the firstname column. At the moment the service seems to be just WHERE firstname LIKE "John", but i need to perform WHERE firstname LIKE "%John%"

How do I pass a param with "%"? Is it possible?

[Mayank_Gandhi]

@lollo901 in the fdm you need to select param in the service. Let me send you a screenshot in sometime

[lollo901]

@Mayank_Gandhi  If I try the service, the result is a void array

If I type the whole name 

[Mayank_Gandhi · Accepted solution]

@lollo901 it doesn't work on a pattern basis or partial string, the whole string/value needs to match the DB value for the data to be returned

[lollo901]

Ok, so I cannot customize the SQL statement in FDM. Thank you

[Vijay_Katoch]

You can pass the argument in read service to query the required data from DB.