PlayFramework 2 + Ebean - raw Sql Update query - makes no effect on db

Question

I have a play framework 2.0.4 application that wants to modify rows in db.

I need to update 'few' messages in db to status "opened" (read messages) I did it like below

    String sql = " UPDATE message  SET opened = true, opened_date = now() "
            +" WHERE id_profile_to = :id1 AND id_profile_from = :id2 AND opened IS NOT true";
    SqlUpdate update = Ebean.createSqlUpdate(sql);
    update.setParameter("id1", myProfileId);
    update.setParameter("id2", conversationProfileId);        
    int modifiedCount = update.execute();

I have modified the postgresql to log all the queries.

modifiedCount is the actual number of modified rows - but the query is in transaction. After the query is done in the db there is ROLLBACK - so the UPDATE is not made. I have tried to change db to H2 - with the same result.

This is the query from postgres audit log

2012-12-18 00:21:17 CET :  S_1: BEGIN
2012-12-18 00:21:17 CET :  <unnamed>:  UPDATE message  SET opened = true, opened_date = now()  WHERE id_profile_to = $1 AND id_profile_from = $2 AND opened IS NOT true
2012-12-18 00:21:17 CET : parameters: $1 = '1', $2 = '2'
2012-12-18 00:21:17 CET :   S_2: ROLLBACK

..........

Play Framework documentation and Ebean docs - states that there is no transaction /if not declared or transient if needed per query/.

So... I have made the trick

    Ebean.beginTransaction();
    int modifiedCount = update.execute();
    Ebean.commitTransaction();
    Ebean.endTransaction();
    Logger.info("update mod = " + modifiedCount);

But this makes no difference - the same behavior ...

Ebean.execute(update);

Again - the same ..

Next step i did - I annontated the method with

@Transactional(type=TxType.NEVER)

and

@Transactional(type=TxType.MANDATORY)

None of them made a difference.

I am so frustrated with Ebean :( Anybody can help, please ?

BTW. I set

    Ebean.getServer(null).getAdminLogging().setDebugGeneratedSql(true);
    Ebean.getServer(null).getAdminLogging().setDebugLazyLoad(true);
    Ebean.getServer(null).getAdminLogging().setLogLevel(LogLevel.SQL);

to see in Play console the query - other queries are logged - this update - not

Answer 1

just remove the initial space...Yes..I couldn't believe it either... change from " UPDATE... to "UPDATE...

And thats all...

Answer 2

i think you have to use raw sql instead of createSqlUpdate statement.