Hi,
I would like to ask how to test an SQL query in ODC Studio. I'm in the development environment. I have seen that it can be done within the editor itself in the “Test Inputs” tab.
When I click on the “Test SQL” button, I get the following error:
If I run this SQL query in my normal application flow, it does not return any errors and it works perfectly. I don't understand where the problem could be (the syntax is correct). Is there any way to fix this error, and is there an alternative to what the SQL query returns?
I understand that debugging would probably help, but I would like to test the query before debugging.
Thank you!
Hi @Amin El Meziani ,
You need to pass the test value of @filterParking parameter while doing the SQL test, otherwise you will have this error. When you run the application you are passing the value that's why you are not receiving any error.
ref: https://learn.outsystems.com/training/journeys/web-developer-662/using-custom-sql-queries-exercise/odc/622
regards,
Manish Jawla
You have this SQL in ODC Studio:
SELECT {PARKING}.*
FROM {PARKING}
WHERE {PARKING}.[NOM] IN (@FilterParkings)
Why it fails in “Test SQL”
@FilterParkings is a parameter.
In ODC, parameters are replaced by the Test Inputs you provide.
If you don’t enter anything in Test Inputs, ODC generates:
WHERE {PARKING}.[NOM] IN ()
That’s invalid SQL, hence the error: syntax error at or near ")".
Please pass property value during test. It will work
@Amin El Mezianiis that filter an inline parameter right? It's not clear it is a number or a string but here is my advice:pass in a parameter called ShouldApplyFilter (boolean) and do it like this
Then on the SQL flow item assing it to the FilterList.IsEmpty
WHERE @ShouldApplyFilter = 0 OR {PARKING}.[NOM] IN (@FilterParkings)
For the final part:If the list is a Long integer list -> Use the BuildSafe_InClauseIntegerListIf this list is text based -> Use the BuildSafe_InClauseTextList
and then (mark the input parameter for the filter as text and as inline parameter) pass what comes out of the action you picked as input to that filter parameterand if you want to test it in service studio the test value should be surrounded with ''
" 'Item1', 'Item2', 'Item3' " for textOR
'0,1,2,3,4,5' for Integers
When testing your SQL, make sure to assign a value to the @filterParking parameter. If it’s left empty, the test will fail with an error. In the actual application, this issue doesn’t occur because the parameter value is automatically supplied at runtime.
Reference: https://learn.outsystems.com/training/journeys/web-developer-662/using-custom-sql-queries-exercise/odc/622
Regards, Deepak Raj M
No need to repeat (almost verbatim) already given answers.