Can Base Execute a MySQL Transaction

[SteveR]

I am using MySQL as a backend. Base is being used as the front end.
I also have PHPmyAdmin installed, which works directly with MySQL. With PHPmyAdmin, the following syntax below works.

---

START TRANSACTION;

INSERT INTO ServicePlans ( PlanID , PlanName , PlanPrice , DiscountedPrice )
VALUES (“M1005”, “Messaging Package”, 19.99, 16.99 );

INSERT INTO Orders ( CustomerID , PlanID , OrderDate )
VALUES ( 19, “M1005”, “1015-01-01” );

COMMIT;

---

Can the syntax above be “translated” into a form that Base can use?

It appears not, as certain text in the Base SQL tab (“tools” → “sql”) appears green, which seems to imply that Base does accept the text. Specifically the text “Start Transaction” and “Commit” appear green which seems to imply that Base is informing you of an incorrect syntax before you even get to execute the code.

[doug]

base queries make extensive, mandatory use of the back quote (`) or alternatively the double quote ("). I would guess your query will work if the field names are bracketed in back quotes and the text strings are converted to single quotes.

[SteveR]

I experimented with various combinations of quotes, nothing worked. These queries do work when executed individually.

I still suspect that the worrds “Start Transaction” and “Commit” are not recognized as valid SQL statements. Valid SQL commands appear blue, these words apprear green.

[doug]

If I understand correctly, the use of START TRANSACTION and COMMIT as a single unit could be a performance optimization, particularly if invoked programmativally. Omitted, MySQL ordinarily would revert to autocommit mode. The INSERT INTO transactions would autocommit individually with a normal MySQL implementation. Because you are executing as block from GUI, seems like START... and COMMIT could be omitted successfully.

[SteveR]

Good suggestion, but removing “Start Transaction” and “Commit” did not work. Again, the two insert statements did work successfully when executed individually.

[SteveR]

Thanks. Do you wish to make your response an “official” answer? Technically, there is no published answer at this point. I can then “accept” it.

[doug]

Yes, I confirm those limitations with MySQL backend on both JDBC and native connectors. Oddly, the query succeeds on HSQLDB internal database. This appears to be limitation in LO.