user5407287 user5407287 - 3 months ago 9
MySQL Question

SELECT...FOR UPDATE using Anorm

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

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 { ... }).