ODAC to MyDAC conversion insert issue

[jdredd]

Using Rio Update 3 and latest MyDAC components as of today.

I am in the process of slowly moving programs from Oracle to MySQL at work.

I finished converting one of my long running programs that have used Devart's ODAC component set.. 10 years old or so...

Went mostly smoothly.. just rename some components from ODAC to MyDAC and tweak a few SQL statements to use the MySQL version of an Oracle call.

Anyways, the only big thing I ran into was inserts.

When I do inserts with the MyDAC, after execsql's ... I then need to do a Commit SQL Statement.

Code: Select all

qInsertSomething.SQL.Text := 'Insert Into Blah......';
qInsertSomething.execsql;
qInsertSomething.SQL.Text := 'Commit;
qInsertSomething.execsql;

With the ODAC components, I didn't need the Commit calls....

I don't use transactions or anything, as almost everything I work with is either just add it to existing tables or truncate it and refill it all with new stuff. Few gb of data nightly of stuff... nothing to crazy.

Hate to be that guy, but am I missing something in the TMyConnection or TMyQuery properties... or just how MySQL likes to work.

I've been using ODAC for 10 years roughly, so been very use to how that works and in all the years, I think I reported 1 bug/issue...

[ViktorV]

MySQL operates in the AUTOCOMMIT mode by default. You can learn about which mode you use by running the command

Code: Select all

SHOW VARIABLES LIKE 'AUTOCOMMIT'.

You can enable or disable the AUTOCOMMIT mode for the current connection by setting the configuration variable to:

Code: Select all

SET AUTOCOMMIT = 0;

or

Code: Select all

SET AUTOCOMMIT = 1;

See our MySQL documentation for more details: dev.mysql.com/doc/refman/8.0/en/commit.html
Therefore, to solve the defind task, you can try using the following code:

Code: Select all

qInsertSomething.Connection.ExecSQL('SET autocommit = 1');
qInsertSomething.SQL.Text := 'Insert Into Blah......';

or

Code: Select all

qInsertSomething.SQL.Text := 'SET AUTOCOMMIT = 1; Insert Into Blah......';

[jdredd]

AutoCommit appears to be enabled?

[ViktorV]

Unfortunately, we cannot reproduce the problem in our environment based on the data you provide.
Please, compose and send us via the contact form (https://devart.com/company/contactform.html) a small sample of stably reproducing the error with test access to your server and send it to us. As soon as we get such an example and the problem will be reproduced in our environment and we will try to give you an answer right away. If the cause of the error is in our code, we will fix it as soon as possible.