SELECT...FOR UPDATE using Anorm

Question

I'm trying to write a SQL SELECT...FOR UPDATE using Anorm in Play so that I can have multiple threads interact with the same database, but it's throwing an issue.

The code is:

db.withConnection { implicit connection: Connection =>
      SQL"""

          start transaction;

          select * from push_messages where vendorId=$vendorId for update;

          UPDATE push_messages set stageOne=$first, stageTwo=$second, stageThree=$third,
          stageFour=$fourth, stageFive=$fifth, highestStage=$highestStage, iOSTotal=$iOSTotal,
          androidTotal=$androidTotal, iOSRunningCount=$iOSRunningCount, androidRunningCount=$androidRunningCount,
          problem=$newProblem, iOSComplete=$iOSCompleted, androidComplete=$newAndroidComplete,
          totalStageThrees=$totalStageThrees, totalStageFours=$totalStageFours, expectedTotals=$expectedTotals,
          startTime=$startTime, date=$date, topics=$topics, androidFailures=$androidFailures, iOSFailures=$iOSFailures where vendorId=$vendorId;

          commit;

        """.execute
    }

But, it doesn't seem to like the use of .execute on the select statement. Is there a good way to break this up to do the select...for update so that I can use either execute() or executeUpdate?

Any and all help would be appreciate. Thanks.

Answer 1

As most JDBC base library, Anorm is using PreparedStatement to safely interact with the DB, so you should not pass it such multi statement string, but only a single statement to each SQL call.

Moreover, about start transaction, you'd better use the JDBC way for it (e.g. using Play DB DB.withTransaction { ... }).