Bug in SQLQuery RecordCount?

[Cefe]

Hello,

I have found a possible bug in RecordCount property of SQLQuery as show in this code in C++:

Code: Select all

 //Initialization
 TSQLConnection *SQLConnection;
 SQLConnection=new TSQLConnection(NULL);
 
 SQLConnection->ConnectionName = "MySQL by Core Lab";
 SQLConnection->DriverName = "MySQL by Core Lab";
 SQLConnection->GetDriverFunc = "getSQLDriverMySQL";
 SQLConnection->LibraryName = "dbexpmda30.dll";
 SQLConnection->LoginPrompt = False;
 SQLConnection->Params->Add("BlobSize=-1");
 SQLConnection->Params->Add("HostName=localhost");
 SQLConnection->Params->Add("DataBase=datos");
 SQLConnection->Params->Add("DriverName=MySQL by Core Lab");
 SQLConnection->Params->Add("User_Name=usuario");
 SQLConnection->Params->Add("Password=clave");
 SQLConnection->Params->Add("FetchAll=True");
 SQLConnection->Params->Add("EnableBoolean=False");
 SQLConnection->VendorLib = "libmysql.dll";
 
 TSQLQuery *SQLQuery;
 SQLQuery=new TSQLQuery(NULL);

 SQLQuery->SQLConnection = SQLConnection; 
 
 //I need to now how many record returns this query:
 //     Select T2.id, T2.nombre 
 //     from capasperusu T1
 //     left join capasper T2 on (T2.id=T1.idCapa) 
 //     where T1.idUsuario=1
 //


 // CASE 1: THE PROBLEM
 // If we assign the query via Text property in one line, it crashes
 SQLQuery->SQL->Text=
   "select T2.id, T2.nombre "
   "from capasperusu T1 "
   "left join capasper T2 on (T2.id=T1.idCapa) "
   "where T1.idUsuario=1";
 
 SQLQuery->Open();
 Caption=SQLQuery->RecordCount; //CRASH!!!


 //CASE 2: Breaking the query in serveral lines 
 //             using Add method off SQL property
 //IN THIS CASE IT WORKS
   SQLQuery->SQL->Add("select T2.id, T2.nombre");
   SQLQuery->SQL->Add("from capasperusu T1");
   SQLQuery->SQL->Add("left join capasper T2 on (T2.id=T1.idCapa)");
   SQLQuery->SQL->Add("where T1.idUsuario=1");
   SQLQuery->Open();
   Caption=SQLQuery->RecordCount; //it works OK!!!
 
 //CASE 3: Breaking the query in serveral lines 
 //             using Delimiter and DelimitedText of SQL property
 SQLQuery->SQL->Delimiter=' ';
 SQLQuery->SQL->DelimitedText="select T2.id, T2.nombre "
   "from capasperusu T1 "
   "left join capasper T2 on (T2.id=T1.idCapa) "
   "where T1.idUsuario=1";
  }
 
   SQLQuery->Open();
   Caption=SQLQuery->RecordCount; //it works OK!!!

As you can see, if we assign a long query text with long lines, RecordCount crashes, but if we break query in several lines, it works OK.

I think that this is a bug.

Best Regards,
Cefe

[Jim Maurer]

We too had a problem with RecordCount. The WHERE clause included an alias but the sql command generated by the driver did not include the FROM part of the commandtext so the alias was not defined.

We were able to remove the use of the alias but the generated sql for the RecordCount method should have included the FROM part.

Thank you,
Jim Maurer
Northstar Data Systems

[Antaeus]

We have tested the problem you reported. This is dbExpress bug. It happens because dbExpress component generates wrong SQL statement to get record count. Behaviour is the same with standard dbExpress driver for MySQL Server.