Skip to main content
R
Ru_0114-Alexandrite
14-Alexandrite
April 28, 2023
Solved

cancelling statement due to user request getting an error in thingworx

  • April 28, 2023
  • 1 reply
  • 4302 views

Hi,

i am trying to execute postgres database store procedure in thingworx but unable to get the data, it is showing an error in database logs like "cancelling statement due to user request", but if i am trying to execute the same store procedure in postgres it is giving me the data. i checked some thingworx articles and there they are saying to refresh the database connection or increase the database thing time limit, i tried these two approaches also but not getting the data. Please give me the solution on this as soon as possible.

 

Best answer by nmutter

I did use the SQLThing but still got the cancelling due to user request error. Support helped me and informed that there is a timeout I can set myself on my service/query (default 60 seconds): Article reference https://www.ptc.com/en/support/article/CS345780

1 reply

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
April 28, 2023

What exactly is the average execution time of that stored procedure?

R
Ru_0114-AlexandriteAuthor
14-Alexandrite
April 28, 2023

Hi @VladimirRosu_116627 ,

 

the avg time to execute the store procedure is 30 sec to 60 sec.

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
April 28, 2023

1. First, I presume you modified the database Thing timeout already right? What value did you use there?

2. Second, the Database Thing removes by default any existing connections that spend more than 60 seconds without receiving any data. This means any query that spends >= 60 seconds on the SQL server side, without sending data to ThingWorx. If you are curious how it's doing that, it is by using the standard JDBC parameter named "removeAbandoned".This parameter is set to "true", and also its value is 60 seconds, so if you set a big timeout, but the connection does not receive data because the SQL server spends time to process data internally, this will kick in. Typically, such a big time to process data is seen as way too high, and could overload the server fast, that's why this parameter is set to true.

3. However, in 9.3.3 we introduced a new way to interact with SQL databases, that is using the SQLThing etc (not DatabaseThing). Info here.

Most important for you, this new interaction point does not enable the flag above, effectively removing that limitation. That being said, with great power comes great responsibility, so I would really optimize first that stored procedure, just in case it can be called too many times.

 

I would suggest overall to switch to the new 9.3.3 SQL Thing way of interacting with the DB - if this is really the core issue you experience.

 

Please let us know the results of your test

Terms & ConditionsCookie settingsAccessibility statement