ODBC Driver Failure

[michaelkrauklis]

When connected to a data source, I've only done this with MsSQL2k but the same most likely holds true for all ODBC Drivers, usign a CDatabase object you must call CDatabase::Rollback() if your transaction fails. eg:

CDatabase db;
db.open("[ODBC Source Name]");
try{
  //create SQL statement
  CString SQL;
  //...

  //this is where the exception is thrown
  db.ExecuteSQL(SQL);

  if(!db.CommitTrans()){
    cerr<<&quot;db.CommitTrans() failed&quot;<<endl;
  }

}//end try
catch(CDBException *e){
  char *error=new char[1000];
  e->GetErrorMessage(error,1000);
  cerr<<&quot;Error: &quot;<<error<<endl;
  cerr<<&quot;Attempt CDatabase::Rollback()&quot;<<endl;
  if(db.Rollback()){
    cerr<<&quot;Rollback Sucessful\nContinuing...&quot;<<endl;
  }
  else{
    cerr<<&quot;Rollback Fail\nInvaild SQL Driver &quot;
        <<&quot;State\nTerminating Program&quot;<<endl;
    exit(1);
  }
}

If you don't call Rollback the SQL transaction that failed is still in a pending state and an assertion in cdbcore.cpp fails when you attempt to make another transaction(the assertion is that there isn't already a transaction pending). This causes your driver to fail in the same manner as if you had called db.ExecuteSQL(SQL) two times in a row without db.CommitTrans() and db.BeginTrans() again. Hope this helps. May save someone the headache I had. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

Sorry, inside my example's try block there should be a while loop so you are sending more than one SQL statement. Otherwise the connection would just close and you wouldn't have any problems. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

I'm an idiot. Ok, the while loop should be outside the try block, but after you call db.Open(). If it was inside when the exception was thrown you would just kick out of the loop and the connection would close. If you had it before the db.Open() you would be opening and closing your conenction like crazy, which is very costly. Opening the connection is the most costly part of the whole process. I do assume that you call db.Close() at the end of this example too. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

Here's the correct example just to make sure I don't confuse anyone:

CDatabase db;
db.open("[ODBC Source Name]");

while(more SQL statements to be made){
  try{
    //create SQL statement
    CString SQL;
    //...

    //this is where the exception is thrown
    db.ExecuteSQL(SQL);
  
    if(!db.CommitTrans()){
      cerr<<&quot;db.CommitTrans() failed&quot;<<endl;
    }
  
  }//end try
  catch(CDBException *e){
    char *error=new char[1000];
    e->GetErrorMessage(error,1000);
    cerr<<&quot;Error: &quot;<<error<<endl;
    cerr<<&quot;Attempt CDatabase::Rollback()&quot;<<endl;
    if(db.Rollback()){
      cerr<<&quot;Rollback Sucessful\n&quot;
          <<&quot;Continuing...&quot;<<endl;
    }
    else{
      cerr<<&quot;Rollback Fail\nInvaild SQL Driver &quot;
          <<&quot;State\nTerminating Program&quot;<<endl;
      exit(1);
    }
  }//end catch
}//end while
db.Close();

I hope I got it right this time... MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[Zyrenthian]

Have you tried db.BeginTrans() before the execute call???

Matt

 

[michaelkrauklis]

doh... ok, one last time...:

CDatabase db;
db.open("[ODBC Source Name]");

while(more SQL statements to be made){
  try{
    //

BEGIN TRANS!

db.BeginTrans();

    //create SQL statement
    CString SQL;
    //...

    //this is where the exception is thrown
    db.ExecuteSQL(SQL);
  
    if(!db.CommitTrans()){
      cerr<<&quot;db.CommitTrans() failed&quot;<<endl;
    }
  
  }//end try
  catch(CDBException *e){
    char *error=new char[1000];
    e->GetErrorMessage(error,1000);
    cerr<<&quot;Error: &quot;<<error<<endl;
    cerr<<&quot;Attempt CDatabase::Rollback()&quot;<<endl;
    if(db.Rollback()){
      cerr<<&quot;Rollback Sucessful\n&quot;
          <<&quot;Continuing...&quot;<<endl;
    }
    else{
      cerr<<&quot;Rollback Fail\nInvaild SQL Driver &quot;
          <<&quot;State\nTerminating Program&quot;<<endl;
      exit(1);
    }
  }//end catch
}//end while
db.Close();

I guess I should have looked over that better before I sent it up the first time. Sorry. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

And Zyrenthian, in case you were thinking that might fix the problem it doesn't. Nice job spotting my error though. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[Zyrenthian]

LOL, just a side note

Matt

 

[michaelkrauklis]

Another side note, you can't call ExecuteSQL without calling BeginTrans. I have it in my code but just had forgotten to put it in my example. I am the helpful tip master aren't I!:-Q MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra