Query with COUNT function

[bedla.czech]

Hi, i have one problem that i cannot solve

i have query that counting records with and without null values. if i post this query directly to mysql (using phpmyadmin) the result is one row and two columns with right numbers. But if a do it in C++ Builder the result is one row and two columns but the first column is type String and Value is "\x03" and second column type is String and Value is "\x02". Right values is Integer with value 3 and Integer with value 2.

thanks for your help

Code: Select all

  qryCommand->Close();
  qryCommand->SQL->Clear();
  qryCommand->SQL->Add("SELECT COUNT( pmc.id ) AS pocetPmc, COUNT( pm.id ) AS pocetPm FROM product_metadata_category AS pmc LEFT OUTER JOIN product_metadata AS pm ON pmc.id = pm.id_metadata_category WHERE pmc.id_category = :idCategory");
  qryCommand->ParamByName("idCategory")->AsInteger = qryCategory->FieldByName("id")->AsInteger;
  qryCommand->Open();

  bool hasProductAttributes_AllOK = false;

  if ( !qryCommand->Eof )
  {
   if ( qryCommand->FieldByName("pocetPmc")->AsInteger == qryCommand->FieldByName("pocetPm")->AsInteger )
    hasProductAttributes_AllOK = true;
  } // end if

  qryCommand->Close();

[Ikar]

May be you set FieldsAsString property to True?

If "Not", then please specify script to create tables. Also specify exact MyDAC and MySQL server versions.

[bedla.czech]

so, when i am makeing example application for you, maybe i found solution
this unexpeted behaviour occurs when AutoPrepare is True, when it is False everything is OK. Fields has names (opposite NULL when True) and right integer values (opposite that "\x03" rubbish). I havent tried FieldAsString, because in AutoPrepare is error.

i am using MyDac Prof 4.00.0.2 04.08.05 and MySQL Server 4.1.12a-nt

here is db tables:

this time relation is (as you can see in that query)

category <- product_metadata_category <- product_metadata

table category is tree structure represented with TMyQuery qryCategory

Code: Select all

create table category
(
   id                             int                            not null AUTO_INCREMENT,
   id_parent                      int,
   category_name                  varchar(128),
   category_order                 int,
   active                         tinyint(1),
   edit_datetime                  datetime,
   primary key (id)
)
type = InnoDB;

table product_metadata_category stores product categories

Code: Select all

create table product_metadata_category
(
   id                             int                            not null AUTO_INCREMENT,
   id_category                    int,
   meta_category_name             varchar(128),
   meta_category_order            int,
   edit_datetime                  datetime,
   primary key (id)
)
type = InnoDB;

table product_metadata describes product attributes

Code: Select all

create table product_metadata
(
   id                             int                            not null AUTO_INCREMENT,
   id_category                    int,
   id_metadata_category           int,
   meta_data_name                 varchar(128),
   meta_data_datatype             int,
   meta_data_values               blob,
   meta_data_mandatory            tinyint(1),
   edit_datetime                  datetime,
   meta_data_order                int,
   primary key (id)
)
type = InnoDB;

[Ikar]

We still can't reproduce the problem. We think it is MySQL Server bug. However if you send us a complete small sample (mydac*crlab*com) we try to check it more precisely.

[bedla.czech]

you can see code snippet in my first post. if i set AutoPrepare to false the error do not occurs. so as you mentioned, maybe it is server error.