Using SELECT .. FOR UPDATE in SQL Query/Command service (postgresql)

Question

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 1.2.3.4.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.
 
OR
 
--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.

odukiya · Accepted Answer

Hello @nmilleson

I think using stored procedure is a better way and you can use that for single thread execution. I couldn't with 100% certainty claim that my approach use single db connection.

Thanks

Om Dukiya

odukiya · Answer

Hello @nmilleson

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:

Define service A for select and service B for insert
Define service C to call service A and service B.
Note that service A/B will be committed to database once executing successfully, which means even if service C fails midway after calling service A/B, a rollback to database will not happen.

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded