I'm currently writing a small application which has a VB front-end to communicate with a MySQL database. I am using the latest V4.0.10. All the tables in my database are of type InnoDB (and must be to have the transaction control I require).
The problem I am having is this:
I connect to the database and start a new transaction. I then insert records into table1, and without commiting, try to do a select on the records from table1. The problem is that the new records are not being picked up by my query. I was under the impression that as I was still in the same transaction I would be able to see the recently inserted records.
Notes: if I commit the data after doing the insert to table1 and then run my query, it works fine, but I don' t want to have to commit the data at that point.
By default the transaction isolation level is READ_REPEATABLE. I've even tried changing this to READ_UNCOMMITTED, but it makes no difference.
Previously table1 was of type MyISAM and it all worked well, but it is no longer appropriate to keep the table as type MyISAM.
Has anyone come across a similar issue? Any help would be much appreciated
You might try repeating the same SQL statements on the MySQL client command line and see if you get the same results. If you find that the Select then returns the inserted rows, then the problem is likely that you have two connections happening in VB: one in the part that does the Insert and Commit, and a different connection in the part that does the Select.
I tried what you suggested, and it works as I would have expected, but I'm still confused as I control the database connection, and there is definately only one being used within my VB code.
What is really strange is that I can do a 'select count(*)...' on the table from within my code, and it returns the correct number of rows, so it does see the inserts, but when I try to execute the query:
SELECT IFNULL(SUM(col1), 0) FROM table1
it always returns 0, when it should not.
OK, I know why it doesn't work now, only I'm not sure how to deal with it. Basically, I set up a database connection which I had made private to a module. I execute code in other modules which require this connection (there should only ever be one).
The following is an extract of my code...
Private dbConn As ADODB.Connection
Public Sub openDBConnection()
On Error GoTo err_openDB
Set dbConn = New ADODB.Connection
runSql "Set autocommit = 0"
MsgBox "Error opening Database" & Err.Description
Public Sub startNewTransaction()
Public Sub commitTransaction()
Public Sub rollbackTransaction()
Public Sub closeDBConnection()
On Error GoTo err_closeDB
MsgBox "Error closing Database" & Err.Description
Public Function getDBConn()
getDBConn = dbConn
Public Sub countRows(strSql As String)
Dim rs As New ADODB.Recordset
rs.Open strSql, getDBConn, adOpenStatic, adLockReadOnly
If Not .EOF And Not .BOF Then
Debug.Print "record count: " & rs.Fields(0)
I, 1) open the connection, 2) start a transaction, 3) do a select on the table by calling countRows, 4) insert data into my InnoDB table, 5) do a select on the table by calling countRows(), 6) commit or rollback
OK, in countRows(), when I call getDBConn() to get the database connection I have opened, it returns the wrong count. If I use the dbConn variable directly, i.e.
rs.Open strSql, dbConn, adOpenStatic, adLockReadOnly
it works fine and picks up the inserted rows. So it seems the issue is with whatever getDBConn() is returning.
Now, the really confusing thing is that the id for whatever is returned in getDBConn is the same as the initial dbConn opened (I have checked the value of dbConn in every part of the code that uses it, and it returns the same value each time). I assume the way to check the connection ID is simply to do something like 'Debug.print dbConn'.
My 'quick-fix' solution is to make dbConn public, and then access it directly in whatever other modules make use of it to talk to the database, as opposed to calling an accessor method getDBConn(), but I really don't like this solution.
I think getDBConn isn't returning what I would expect because of 'passing by reference/value' rules, but I really am not sure.
If I do a 'select count()...' from the mySQL command prompt after I do the insert, it does not pick up my changes until a commit is done (which is expected).