Command Out Of Sync

Command Out Of Sync

Postby Massimo » Mon 24 Nov 2008 14:40

I have developed an gps/gprs application on wince os, that use the MyDirect .NET Mobile ver. 4.20.19.1.
I would like to know how i must close the database object
and restore the initial state, in my application, when
"Command out of sync" error occurs.

[CUT]
oCmd = new MySqlCommand();
oCmd.Connection = oMySqlConn;
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.CommandText = "GEvPal";
oCmd.Parameters.Add("iIdNodo", VG.IdNodo);
oCmd.Parameters.Add("iLastID", lastID);
oTab = new MySqlDataTable(oCmd, oMySqlConn);

This command usually works, but if the connection falls or for other reasons generates the error.

I'have tryed to close the connection like this:
....
oMySqlConn.Close();
oMySqlConn = null;
....

and reopens after:
...
MySqlConnection oMySqlConn = new MySqlConnection();
....

but without solving the problem.

For the moment i'm restarting the device for solve the problem.

Thanks, Massimo
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby AndreyR » Tue 25 Nov 2008 09:02

The text of the error indicates the wrong order of client functions calling. If your connection is used in several threads, create separate MySqlConnection object for every thread and invoke the Open() method before you use it, the Close() method - after.
The global usage of MySqlConnection object is not clear from the code fragment you have provided.
This error can also be caused by the internal provider implementation that thows this exception in your particular case.
Try using the latest version of our product.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Massimo » Tue 25 Nov 2008 14:49

Thanks for your response.
Below is a block of code more detailed:

private void TestThRead() {
MySqlCommand oCmd;
MySqlDataReader oDati = null;
MySqlConnection oMySqlConn;

oMySqlConn = new MySqlConnection(...........);

while (bStop) {
try {
//Verific connection state
if (oMySqlConn.State != ConnectionState.Open) {
oMySqlConn.Open();
}

//oMySqlConn is global
oCmd = new MySqlCommand("SELECT * FROM dati", oMySqlConn);
oDati = oCmd.ExecuteReader();
//Read dati
while (oDati.Read()) {
//..........
}
oDati.Close();
} catch (Exception ex) {
if (ex.Message.IndexOf("Commands out of sync") > -1) {
//
}
} finally {
if (oDati != null) {
oDati.Close();
}
}
}

oMySqlConn.Close();
}


What are the steps to restore connection in the block catch?
Can i have a c# example code?

Thanks, Massimo
Massimo
 
Posts: 13
Joined: Mon 28 May 2007 15:18

Postby AndreyR » Wed 26 Nov 2008 14:18

For avoiding such errors, we recommend you to use the separate connection for each thread. Then the code would be like the following:
Code: Select all
while (bStop) {
  MySqlConnection oMySqlConn = new MySqlConnection(...........);
  try {
    oMySqlConn.Open();
    MySqlCommand oCmd = new MySqlCommand("SELECT * FROM dati", oMySqlConn);
    MySqlDataReader oDati = oCmd.ExecuteReader();
    while (oDati.Read()) {
    //..........
    }
  catch (Exception ex) {
  // Handle exceptions here, no "Command out of sync" error should appear
  }
  finally {
    oDati.Close();
    oMySqlConn.Close();
  }
}

For connection quantity control, use connection pooling.
More information on that you can get in our FAQ and in MSDN:
http://msdn.microsoft.com/en-us/library/bb399543.aspx
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for MySQL