Ask Your Question
0

Can Base Execute a MySQL Transaction

asked 2015-03-07 21:29:54 +0200

Steve R. gravatar image

updated 2015-03-07 21:33:41 +0200

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.

edit retag flag offensive close merge delete

Comments

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.

doug gravatar imagedoug ( 2015-03-08 19:15:29 +0200 )edit

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.

Steve R. gravatar imageSteve R. ( 2015-03-09 19:22:40 +0200 )edit

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.

doug gravatar imagedoug ( 2015-03-11 02:51:26 +0200 )edit

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

Steve R. gravatar imageSteve R. ( 2015-03-11 04:54:23 +0200 )edit

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.

Steve R. gravatar imageSteve R. ( 2015-03-12 16:59:13 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-12 04:31:36 +0200

doug gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-07 21:29:54 +0200

Seen: 313 times

Last updated: Mar 07 '15