I'm a bit frustrated with how Jet is behaving with the server (MySQL).
Apparently it loves to issues commands like those:
I want to use transactions, but with Jet's behavior, it's useless because Jet insists on committing every update it does.
Disabling transactions in the ODBC driver didn't stop it. (This is why you see "CALL [Transaction method] in the query- I figured that if I made a stored procedure, disabling transactions, Jet would STFU and let me decide when to commit or rollback the transaction, but it blithely ignores the setting...)
There's no "Safe Transactions" value anywhere, and I don't know where I'd look to alter Jet's behavior, already having looked at regedit and reading the whitepaper on JET and ODBC.
I also seems can't to use ODBCDirect workspace for bound forms, and even if I set the recordset to a ODBCDirect workspace, I lose all Jet's intelligence in selecting only needed records (in which it does a wonderful job, I'd think). I also can see that if I create a ODBCDirect workspace, I would be creating one more (unwanted) connection to the server, which indicates to me that Jet/Default workspace will connect to the server anyway, and because the default workspace isn't an ODBCDirect, I can't modify/view connection properties.
Can I please have my cake and eat it? Anybody knows whether the behavior can be altered?
Apparently it loves to issues commands like those:
Code:
48 Query SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
48 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `city` WHERE ( 'ABW' = `CountryCode` ) limit 10
071114 13:44:25 48 Query SET AUTOCOMMIT=0
48 Query COMMIT
48 Query SET AUTOCOMMIT=1
48 Query CALL BEGINTRAN
48 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE `Code` = 'ABW'
071114 13:44:27 48 Query SET AUTOCOMMIT=0
48 Query UPDATE `country` SET `Name`='Aruba' WHERE `Code` = 'ABW' AND `Name` = 'Arubz' AND `Continent` = 'North America' AND `Region` = 'Caribbean' AND `SurfaceArea` = 1.93000000000000000e+002 AND `IndepYear` IS NULL AND `Population` = 103000 AND `LifeExpectancy` = 7.84000015258789060e+001 AND `GNP` = 8.28000000000000000e+002 AND `GNPOld` = 7.93000000000000000e+002 AND `LocalName` = 'Aruba' AND `GovernmentForm` = 'Nonmetropolitan Territory of The Netherlands' AND `HeadOfState` = 'Beatrix' AND `Capital` = 129 AND `Code2` = 'AW'
48 Query COMMIT
48 Query SET AUTOCOMMIT=1
071114 13:44:28 48 Query SET AUTOCOMMIT=0
48 Query COMMIT
48 Query SET AUTOCOMMIT=1
48 Query CALL ROLLBACKTRAN
I want to use transactions, but with Jet's behavior, it's useless because Jet insists on committing every update it does.
Disabling transactions in the ODBC driver didn't stop it. (This is why you see "CALL [Transaction method] in the query- I figured that if I made a stored procedure, disabling transactions, Jet would STFU and let me decide when to commit or rollback the transaction, but it blithely ignores the setting...)
There's no "Safe Transactions" value anywhere, and I don't know where I'd look to alter Jet's behavior, already having looked at regedit and reading the whitepaper on JET and ODBC.
I also seems can't to use ODBCDirect workspace for bound forms, and even if I set the recordset to a ODBCDirect workspace, I lose all Jet's intelligence in selecting only needed records (in which it does a wonderful job, I'd think). I also can see that if I create a ODBCDirect workspace, I would be creating one more (unwanted) connection to the server, which indicates to me that Jet/Default workspace will connect to the server anyway, and because the default workspace isn't an ODBCDirect, I can't modify/view connection properties.
Can I please have my cake and eat it? Anybody knows whether the behavior can be altered?