B4J Question IN clause jrdc2 server

[yiankos1]

Hello team,

I have a command in jRDC server:

sql.filterGymsCount = SELECT COUNT(*) FROM gyms g JOIN gymTypes gt ON g.id = gt.id_gym JOIN types t ON gt.id_type = t.id WHERE t.name IN (?) GROUP BY g.id HAVING COUNT(DISTINCT t.name) = ?

At the first question mark (IN clause) user can pass the type of the gym in order to filter them. This is comma seperated values for example:

'Crossfit','HYROX'

At b4a, If I pass a single filter e.g. Crossfit without apostrophe, command working well. If I add apostrophe then does not return any value.

If I pass two filters without apostrophe then does not work.

I can't really understand where the problem stands.

If I test it at DBeaver as a sql script by adding two filters with apostrophe: 'Crossfit','HYROX' everything working fine.

Does anyone have any idea?

 

[aeric]

You will need 2 question marks inside the bracket after the IN.

sql.filterGymsCount = SELECT COUNT(*) FROM gyms g JOIN gymTypes gt ON g.id = gt.id_gym JOIN types t ON gt.id_type = t.id WHERE t.name IN (?, ?) GROUP BY g.id HAVING COUNT(DISTINCT t.name) = ?

 

[yiankos1]

You will need 2 question marks inside the bracket after the IN.

sql.filterGymsCount = SELECT COUNT(*) FROM gyms g JOIN gymTypes gt ON g.id = gt.id_gym JOIN types t ON gt.id_type = t.id WHERE t.name IN (?, ?) GROUP BY g.id HAVING COUNT(DISTINCT t.name) = ?

Thank you for your answer. Number of filters are variable, so questions marks. I send a single string using this:

        Private parameter As String

        For Each v As String In mapFilter.Values
            parameter = parameter & "," & v
        Next
    
        gymsCount(parameter.SubString(1))

and if i use apostrophes that does not work:

        Private parameters As String

        For Each v As String In mapFilter.Values
            parameters = parameters & ",'" & v & "'"
        Next
    
        gymsCount(parameters.SubString(1))

 

[jahswant]

I USE THIS

SELECT * FROM classe WHERE FIND_IN_SET(classe_id,?);

YOU CAN SEND '1,2,3,4,5,6,7,7,9,10' YOU DON' T NEED IT TO BE LIKE (1,2,3,4,5,6,7,7,9,10).

THIS CODE WILL WORK ON MYSQL.

 

[yiankos1]

I USE THIS

SELECT * FROM classe WHERE FIND_IN_SET(classe_id,?);

YOU CAN SEND '1,2,3,4,5,6,7,7,9,10' YOU DON' T NEED IT TO BE LIKE (1,2,3,4,5,6,7,7,9,10).

THIS CODE WILL WORK ON MYSQL.

Indeed, FIND_IN_SET helped a lot. Thank you for your answer.
Here is the code:

SELECT COUNT(*)
FROM (
    SELECT
        g.id
    FROM
        gyms g
    JOIN
        gymTypes gt ON g.id = gt.id_gym
    JOIN
        types t ON gt.id_type = t.id
    WHERE
        FIND_IN_SET(t.name, 'Crossfit,HYROX,Yoga') > 0
    GROUP BY
        g.id
    HAVING
        COUNT(DISTINCT t.name) = 3
) AS subquery