Hello Everybody,
I have an apps that is supposed to do a custom Query from user input, so the input for SQL is purely all from input parameter. However, I couldn't access the table using test input, but I was able to access it using SQL.
For more detailed explanation, please refer to these pictures below:
Pic1. Error SQL Query
Pic2. Error Test Inputs
Pic3. Error Notification
Pic4. Error Executed SQL
Pic5. Successful SQL Query
Pic6. Successful Executed SQL
As shown above, the error occurred when I tried to pass the SQL Query using input parameter (which also named @Query), and the notification said 'Invalid Object Name'.
However, when I tried to run the SQL Query directly into SQL box (picture5), the SQL run successfully and I notice the differences between running it through 'Test Inputs' Parameter and directly into SQL box. The differences are on the 'Test Inputs' Parameter, the column name is surrounded by '[]' while on the SQL box, the column name is surrounded by '""' (pic4 and pic6). However, since the query is input from user, it is impossible to set the SQL Query directly into SQL Box, and the only way I could think of is passing it's value from Input Parameter.
I also notice that this problem only occurred because I was using exposed entity (which is installed as an extension) from external source (Oracle Server), because when I run the tests using data from Outsystems entity, I get results for both SQL box and Test Input Parameter.
Can anybody help me to solve this problem?
P.S.: the table_name and column_name on this SQL is correct. I censored it to avoid any trouble and you could safely assume that any table and column name here are correct
Hi SamuelHave you set the @Query parameter ExpandInline property to Yes?Normal parameters are translated into actual SQL parameters. But when Expand Inline is set to Yes, the content of the parameter will not be treated as a SQL parameter, but rather, it will become a part of the SQL query as a literal, which will then be combined and sent to the database as a converted SQL statement.
Please be aware that this increases the risk of security issues related to SQL injection.
Hello edubaixo and Damian,
Sorry for late reply, but I've set the expand inline parameter to yes, even before this problem occured
I tried this myself but I got a result with using the users table. See my attached module.
@Damian Fonville, yes,
that's not where the problem is, he also gets correct result for Outsystems tables, the trouble is with integrations with external database.
@Samuel Susetia Davilantinov Nugraha , do you have a problem only in testing from service studio, or also when using the published application ?
I'm sorry, I miss understood the question, I don't have experience with external tables so my help would be useless
Hello Dorine,
That's right, the problem only occured when using integration with external database.
My appa is on development stage, however, and hasn't reached publication (sorry if i missunderstood your meaning)
i mean, when you publish the application into your dev environment and test the same sql widget in the running application, does it work then. In that case, it is probably just a limitation of ability to test from developer studio.