John Radones
Rank: #21090
16Views
6Comments
Advance SQL returns no result after migrating from outsystems 10 to 11
Question
SQL
Oracle
OutSystems 11

I have a problem with advanced sql, we moved our application to outsystems 11 environment and fix the errors.

However, after fixing all the errors I have notice that there is no result from my table after I run the application.

I have checked in debug mode if the advance sql is returning any result  and it doesn't. please see below Screen shot.


However when I tried to test the script it's returning result. please see below screen shot.


There is no errors, I already checked service studio for error logs, tried to create another advance sql, new structure. update the application, checked database connection, extensions. but nothing.

Appreciate if anyone could help.


Note: I'm fetching data from Oracle.

0
0
on 26th Jan
Copy link to comment
Bas de Jong
Rank: #312

Hi, please make sure if the input parameters for the SQL query at runtime are the same as you test it with. If that is the case, i think the best way to proceed is to contact outsystems support. 

0
0
on 26th Jan
Copy link to comment
John Radones
Rank: #21090

Hi,

Thank you for your response. 

I don't have any input parameter in the script

0
0
on 27th Jan
Copy link to comment

Hi John,

In addition to what Bas has mentioned, please ensure that the entity attributes/columns you are comparing with are not requiring the upper/lower case or type casting of the value.

We just recently had an upgrade as well and the advanced sql seems working just fine.

Hope this helps.

Thank you and kind regards,

Chris

0
0
on 27th Jan
Copy link to comment
John Radones
Rank: #21090

Hi Chris,

Thank you for your response.

I have tested the script in SQL developer and it is working fine. Also test the script in service studio and it is returning results.

However in runtime/debug mode the output structure is empty.

I created another extension for oracle, also created new structure with the same script but nothing happen same result. 

The weird thing is, I created another struct with only 2 attributes and call 1 of the tables in the same extension. like this.

SELECT {AP_SUPPLIERS2}.[EMPLOYEE_ID],{AP_SUPPLIERS2}.[VENDOR_ID] from
 {AP_SUPPLIERS2}

And it is working. But when I use the original script and only select only 2 attributes it does not work.

The original script includes join tables which I did not include in my post because I believe that is not the case because it is working in SQL developer tool and Service studio.

So I don't know. I might take a look again with the SQL script.

0
0
on 27th Jan
Copy link to comment
Tom Zhao
Rank: #287

Hi John,

>However when I tried to test the script it's returning result. please see below screen shot. 

Can you test the script on Service Studio directly? If it's not got the result, You can compare executed Sql with what you tested before.

Kind regards

0
0
on 27th Jan
Copy link to comment
John Radones
Rank: #21090

Hi Tom,

Thank you for your response.

I already tested the script in SQL developer. also tested it in Service studio.

The SQL script has results. However in runtime when I'm on dubg mode the output structure is not returning any result.

0
0
on 27th Jan
Copy link to comment