Savepoint in mysql

[bdeg7]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SAVEPOINT test123' at line 1

That's the error I'm getting, when using simple Savepoint syntax, which works in PHP, and navicat, and mysql workbench.

ColdFusion:  v9

MySQL: v5.5 Community

Even installed (& tested) MySQL Connector:  v5.38 (latest)

It refuses to use that syntax via coldfusion, and it's a response that is processed from mysql server I thought?
Stacktrace: base.cfc -> com.mysql.jdbc.StatementImpl.execute -> com.mysql.jdbc.MysqlIO.sendCommand -> com.mysql.jdbc.MysqlIO.checkErrorPacket -> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorExceptio
So why would this work everywhere except coldfusion (via or not via jdbc even)?

Yes I'm aware that coldfusion has a transaction{} system, however that is block-based (single script run), and I need a session based rollback/commit system (keep connection open, single per user session).

I am at the point that I am about to have to write a custom Java solution and classload it.

So I'd definitely appreciate any tips or thoughts on this!!

Happy holidays all.

[bdeg7 · Accepted solution]

FYI , I found my answer. The keyword I needed and didn't have was "Hibernate".

http://www.compoundtheory.com/coldfusion-9-orm-and-transactions-it-does-not-mean-what-you-think-it-m...

So it is possible to do what I need without hacking coldfusion.

Good day to you.