Form Data Model & MySQL queries | Community
Skip to main content
L
lollo901
New Participant
November 25, 2022
Solved

Form Data Model & MySQL queries

  • November 25, 2022
  • 2 replies
  • 1528 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Mayank_Gandhi

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

 

If I type the whole name 


@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

2 replies

Vijay_Katoch
Vijay_Katoch
New Participant
November 27, 2022

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

    Mayank_Gandhi
    Mayank_Gandhi
    Employee
    November 25, 2022

    @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. 

      L
      lollo901Author
      New Participant
      November 28, 2022

      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
        Mayank_Gandhi
        Employee
        November 28, 2022

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

          Terms & ConditionsAccessibility statement

          Loading footer...