I have a service in ThingWorx 9.0.1 that is selecting a row for update to avoid concurrent updates. The service works (in that it updates the tables correctly) but it always throws an exception. The code:
begin; select * from opportunity where opportunity_number = 2 for update; update opportunity set description = 'testing 184.108.40.206.5..' where opportunity_number = 2; commit;
I've tried several ways to eliminate the error (changing the result type, changing from query to command, etc..) I always get one of these errors:
--When I'm using the Query handler:
Unable to Invoke Service test on Thing.DB.Forecasting : Execute Query failed: org.postgresql.util.PSQLException: No results were returned by the query.
--When I'm using the Command handler:
Unable to Invoke Service test on Thing.DB.Forecasting : Execute Update failed: org.postgresql.util.PSQLException: A result was returned when none was expected.
Like I said before, the service does work, but I'd like to get rid of that exception if possible.
It is impossible to run select and insert in one SQL Service(Query or Command) with ThingWorx Thats the reason you are facing the issue. You can try the following approach:
Thanks for the reply. One more followup question regarding your last bullet point:
If I 'SELECT FOR UPDATE' in service A, can I be 100% sure that service B will use the same database connection (currently I have a max connection count of 10 in the Thing Configuration)? If not, there's the possibility that I won't be able to successfully run service B (due to the row lock from service A), correct?
Would I be better off perhaps creating a stored procedure?
It is a really nice question , I am not pretty sure about it let me check and i will get back to you .
I believe It's better if you create stored proc on DB side and call it from Thingworx in this case so that it can be a single threaded execution.