Hi all,

I have a question with the SQL widget in OutSystems 10.

Set Up: I have an extension set up to view tables from our DB2 database (this is a direct connection to our system test data in our internal TMS application). I am using the SQL widget in OutSystems to return values from these tables.

Problem: I have created an SQL SELECT statement to return a list of Bookings. I have tested the SQL and it is working in test and front end. However, if a user is changing any booking in our TMS, the SQL will not execute in OutSystems. Below is the error message in Service Center. From my understanding, the query cannot execute because the table is being 'locked' by a user.


What Have I Tried: I have tried adding multiple SET statements in the SQL to ignore any locked/committed files. However, most of the time the syntax is not being recognized in OutSystems.

  • WITH (NO LOCK)
  • SET OPTION COMMIT = UR
  • SET ISOLATION LEVEL UNCOMMITTED
  • WITH UR

Potential Bug in OutSystems: For all of the above, the SQL failed to execute in the application. However, when adding WITH UR at the end of the statement and testing this in the SQL widget, I received no error messages and results were returned. After publishing this, the query failed. Not sure why the query would be successful in the test but not in the application?!


Does anyone have any ideas of what I need to add to my SQL to ensure for read-only? Any suggestions are much appreciated.

Thanks,

Asha

Hi Asha,


As you are saying in problem section "the query cannot execute because the table is being 'locked' by a user."

I have also faced this issue.

this issue occured when two or more user are prerformed some action on same table . 

Like one user trying to insert data in this table and antoher user is fatching data from same table within same time and insert query taking time so table is locked in this process.

try to kill session of DB.


Kind regard

Rahul

Hi Asha, 

I don't think killing any sessions solves your problem, you want to be able to read a table despite it being locked for insert or update. 

As for the SQL widget, there's a lot of things unstable about it. Sometimes queries produce errors when testing but not after publishing, and apparently also vice versa. Do you have an error log from using the WITH UR statement in Service Center?

Monique de Vos wrote:

Hi Asha, 

I don't think killing any sessions solves your problem, you want to be able to read a table despite it being locked for insert or update. 

As for the SQL widget, there's a lot of things unstable about it. Sometimes queries produce errors when testing but not after publishing, and apparently also vice versa. Do you have an error log from using the WITH UR statement in Service Center?

Hi Monique,

You are correct!! I should be able to read the table regardless if a user is locking the table or not.

That's so annoying! I hope OutSystems are looking to improve this! Sure, below is the error message when using WITH UR.



Yes, I hope it's going to be improved too, it makes debugging pretty messy!

Something I'm not sure if you added to the query already is FOR READ ONLY (which makes it: FOR READ ONLY WITH UR). If that doesn't work I'd check the following things: 

- If all variables (if any) have the Expand Inline option set to Yes (and if that makes a difference);

- If you can run the same query directly in your database (checking for syntax errors);

- If IBM iAccess is installed in your server (DB2), and if not, if the extension only supports a set of possible queries;

- If any of the queries you tested previously in the SQL widget (but where the syntax was not recognized by Service Studio?) run succesfully when published.