[SOLVED] TMySQL55Connection (AutoCommit & Refresh) now is different in Laz1.2.2

[LacaK]

Close & open or refresh does not show the changes in Laz1.2.2. But with "SQLtransaction1.commitretaining" then it shows the changes.

As mentioned. It depends on level of "transaction isolation"
In some isolation mode for transaction A are not visible changes made by other transactions (it does not depend if they was commited or not by other transactions) unless transaction A is finished.

[taazz]

let me ask those that know how do you set the transaction isolation for mysql sqlparams should be the holder but what it holds? Any documentation or source code that we can look and find out all the supported parameters and their values?

[eara]

if you find one them mail me...
till then use your own commands which are safer (at least you know what you are doing)...

[JZS]

i think i found a solution, based on http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
in your connection (assuming you name it "con") on AfterConnect event handler put ALSO this 2 lines

Code: [Select]

con.ExecuteDirect('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED'); // or UNCOMMITED, or whatever you like
con.ExecuteDirect('COMMIT'); // << this is necessary


It works.

So the short answer is:
In Laz1.2.2 it depends on level of "transaction isolation".
Thank you LacaK

I think adding a warning would help those who developed in versions prior 1.2.2, who will hit troubles if they were not aware of that, when migrating projects to 1.2.2

Thanks to you all, Taazz, eara, and LacaK.

I noticed another thing in Laz1.2.2, previously I was not able to use DBGrid Column property "DisplayFormat" to format float fields using "#,##0.00" now it works flawlessly.

[JZS]

Since support for transactions was added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute?
For instance, it's more convenient also to, enable specifying params for such options from the TMySQLxxConnection itself?

[LacaK]

Since support for transactions was added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute?

AFAIK it is not possible ATM

[eara]

Since support for transactions was http://forum.lazarus.freepascal.org/Smileys/ExcellentSmileys1/angry2.gif added in Laz1.2.2, is it possible to set the ISOLATION LEVEL without execute?
For instance, it's more convenient also to, enable specifying params for such options from the TMySQLxxConnection itself?

take a look on this again http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html. This is just a default behaviour of MySQL, other db's beahave differently. Mayby they will do it in future, mayby not.
There are also other things that we need them also from db and can't get them (or i don't know how, e.g. how can you create STORED PROC from lazarus side and call the  SET TERM...)
but, without execute?    what is wrong with execute? i love the "execute" (    )
You send to server a command, then is parsed and executed, and best of all YOU know what you send!
What did you prefer, an implemantation (some time with its own bugs) version in settings (that has to change with every db connector version) of a server settings that does the same (under some conditions, but not under others), with insuffiecient documentation (due to time pressure) that is not logged etc etc,
OR such a clean approach?
In either cases you must send something to server,
OR yes there is also another option:

Go and "hijack" SQL server room and persuate the db admins to run the SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED, or what ever you like.

(Just kidding man)
Anyway there are cases that you may need more than one isolation level for you app and you will execute some commands like this.
I don't like to hide everything behind OO approaches that get the freedom of choice from you!
And think it again, if it wasn't this documentation (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html) then we both we would sit and cry here expecting from someone else to solve our problem...

[JZS]

Partially agree with you eara, nothing wrong with Execute.
In fact, I love the "Execute" too   
But I like execute at other places rather than this one

If you have more than one connection, you have to set the session beforehand of each connection, otherwise you will end up setting the Global. But with params you got nothing to worry about, you set and connect instead of set-connect-execute.

Here:
http://wiki.lazarus.freepascal.org/User_Changes_2.6.4#TODBCConnection_.28odbcconn.29_No_longer_autocommit
Look at it from different angle, TODBCConnection can specify AutoCommit as Param, why not having more options included as params for TMySQLxxConnection, if in general, is possible.

I am not complaining here, I am more than happy with things Lazarus offers and is capable of handling.
Was just checking the possibility.

Thank you Lacak and eara. You are great help.

[eara]

If you have more than one connection, you have to set the session beforehand of each connection, otherwise you will end up setting the Global. But with params you got nothing to worry about, you set and connect instead of set-connect-execute.

I don't get it what do you mean... i am stucked with my case, and propably i am blind to other perspectives. Anyway there is no magic, in both cases there should be a direct execute for setting this or that.  And in both cases you can set it before open, but the actually set will hapen after connect (otherwise MySQL should have mystirious prediction abilities, that leads to the question "is MySQL an entity with telepathy?" etc etc).

here

http://wiki.lazarus.freepascal.org/User_Changes_2.6.4#TODBCConnection_.28odbcconn.29_No_longer_autocommit

i see what i didn't knew before your post (thanks again for the post) that they didn't support transactions for MySQL and now they support (but they didn't cleared this out before. I just found a TSQLtransaction component and thought that i had transactions with it, but.... what the heck, now we have them and we use them).

If you wish to avoid the execute then you can encapsulate it in a new TMyConection class and adapt it to your project (but who's got time for this now)

[JZS]

should be a direct execute for setting this or that.  And in both cases you can set it before open, but the actually set will hapen after connect

I know, it must happen after connect  , I only wanted to have TMySQLxxConnection ready to be setup before connect and once connected you got it ready to do what you set it up to do (no mysterious prediction no telepathy).

Now you make me sound picky.

It was all about ON-OFF options rather than using events.
I confirm I got no issue with what can be done so far, with or without Execute.
And I still confirm I like execute 

(thanks again for the post)

Thanks for you (and your beloved Execute) for the solution 

but who's got time for this now

Lazarus team has 
They have nothing to do, except making Lazarus better and better 

I am happy with what Lazarus Team has provided for us, and what Lazarus can do so far, so no issues no complaints.

[eara]

Sorry, no intention to do this...

[JZS]

FINALLY something that can be an alternative:

Code: [Select]

uses mysql50;
...
Conn.Connected := True;
mysql_autocommit(Conn.Handle, 1); // 1= True, 0= False
mysql_commit(Conn.Handle);
Conn is TMySQL55Connection

[eara]

mmm... calling underlying lib directly... This is also an approach, but it the negative i see with this approach is that it binds your app to the specific lib version (something that can be hidden in case you are using TSQLConnector). Anyway is also a solution if it does what you want to do.

I still insist on my approach... (that means not that is the best approach, i don't want to blame me afterwards, but since we are talking about it...)

If you are using TSQLConnector (instead of TMySQLXXConnection)
you can wrap your calls (as i did) in AfterConnect Event handler.

In case you don't use a single main connection component, but you create connections on  the fly you can still do this with something like the following

mySQLConnector.AfterConnect:=@myEventHandlerMethod

where myEventHandlerMethod is just a member in one of your classes (e.g. main_form's that usually lives as long as your app) which has a suitable signature to be used as event handler for the AfterConnect which means that you have declare like this:

TfrmMain.myEventHandlerMethod(Sender: TObject);
begin .... end;

And what this appi does? Looks like it just set autocommit (for session i supose) to true and then commits the current transaction (in order to apply the setting for the new transactions).
But then i think that you can't do rollback!
Is this what you need?