I am using MySQLDirect 3.50.11 in Visual Studio 2005 in C# On Windows XP SP2; MySQL Server 5.0.21
The Issue I am having is the BeginExecuteReader();
Look at the code below:
Code: Select all
try {
DbDataReader myReader = null;
IAsyncResult result = mySelectCommand.BeginExecuteReader(null, null, CommandBehavior.Default);
if (!result.IsCompleted) {
while(!result.IsCompleted){
// Wait untill it is complete
// you can do Polling in here.
}
}
myReader = mySelectCommand.EndExecuteReader(result);
if (myReader.HasRows && myReader.Read()) {
myReader.Close();
updateResult = DialogResult.OK;
}
else {
// The record was deleted from the Database by a diferent user
//throw MySqlException();
}
}
catch (MySqlException ex) {
MessageBox.Show(ex.Message);
}
NOTE: my "innodb_lock_wait_timout" is set to 10
I think, It should raise the Exception first, so that it could be caught.
So just by my luck, the Exception is raised on the
Code: Select all
myReader = mySelectCommand.EndExecuteReader(result);
More issues I encounter when I am trying to use the mechanism above
(BeginExecuteReader()) to lock a parent Record in one table and all the Child records in the other table for update:
Code: Select all
myTransaction = myConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
DialogResult updateResult = DialogResult.Retry;
while (updateResult == DialogResult.Retry) {
try {
DbDataReader myReader = null;
IAsyncResult result = mySelectCommand.BeginExecuteReader(null, null, CommandBehavior.Default);
if (!result.IsCompleted) {
while (!result.IsCompleted) {
Console.Write(".");
}
myReader = mySelectCommand.EndExecuteReader(result);
if (myReader.HasRows && myReader.Read()) {
myReader.Close();
updateResult = DialogResult.OK;
}
}
catch (MySqlException ex) {
// this MessageBox displays options to cancel or to Retry
// If you hit Retry button it will execute the mySelectCommand.BeginExecuteReader() again.
// and that is where the "Command out of sync" Exception is raised!
updateResult = MessageBox.Show(ex.Message, "Error occured", MessageBoxButtons.RetryCancel);
}
}
"Commands out of sync" Exception.
And again, The Wait Timeout exceeded exception Should be raised before the ASyncResult is "Completed".
I am desperatly needing the resolution to this issue.
Thank you for your time and consideration.
Let me know if you need the Sample Project (VS2005 C#)
Also here is the Schema for the tables:
Code: Select all
USE test;
CREATE TABLE test.parenttable (
parentid INT(11) NOT NULL AUTO_INCREMENT,
parentname VARCHAR(20),
PRIMARY KEY (parentid)
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE test.childtable (
childid INT(11) NOT NULL AUTO_INCREMENT,
parentid INT(11) NOT NULL,
childname VARCHAR(20),
PRIMARY KEY (childid),
INDEX childtable_FK1 USING BTREE (parentid),
CONSTRAINT childtable_FK1 FOREIGN KEY (parentid)
REFERENCES parenttable(parentid) ON DELETE CASCADE ON UPDATE CASCADE
)
AUTO_INCREMENT=1
CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO parenttable(parentname) values("Parent 1");
INSERT INTO parenttable(parentname) values("Parent 2");
INSERT INTO parenttable(parentname) values("Parent 3");
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5" FROM parenttable t1 WHERE parentid = 1;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5-2" FROM parenttable t1 WHERE parentid = 2;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 1-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 2-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 3-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 4-3" FROM parenttable t1 WHERE parentid = 3;
INSERT INTO childtable(parentid, childname) SELECT t1.parentid, "Child 5-3" FROM parenttable t1 WHERE parentid = 3;