Error 42000 Mydac TMyquery sql script wrong parse

308 views Asked by At

Need help Mydac TMyquery not work this script in navicat and sqlfiddle work but tmyquery not work

SET @@group_concat_max_len = 32000;
SELECT
  group_concat(concat('sum(ifnull(if(s.id=',s.id,',m.qty,0),0))`',s.sizes,'`'))eval,
  group_concat(concat('i.`',s.sizes,'`'))list
INTO @eval, @list
from(
  SELECT DISTINCT s.id, s.sizes
  FROM property p
  JOIN size_goods s ON s.id=p.id_sizes
  WHERE p.id_goods IN (6,7,8)
  ORDER BY s.id
  )s;
SELECT group_concat(p.id)
INTO @where
  FROM property p
  WHERE p.id_goods IN (6,7,8)
  ;
SET @sql=concat_ws(' ',
  'select g.id, g.name, g.model,',
  @list,',i.Total,i.price,i.cargo_payment,i.Cost from(select p.id_goods id,',@eval,
  ',sum(ifnull(m.qty,0))Total',
  ',ifnull(sum(price*qty)/sum(qty),0)price',
  ',ifnull(sum(cargo_payment*qty)/sum(qty),0)cargo_payment',
  ',sum(ifnull(m.qty*(m.price+m.cargo_payment),0))Cost',
  'from property p',
  'join size_goods s on s.id=p.id_sizes',
  'left join (',
    'select id_property, id_actions, qty*(3-2*id_actions)qty, price, cargo_payment from moves',
    ') m on m.id_property=p.id',
  'where p.id in (',@where,')',
  'group by p.id_goods',
  ')i left join  goods g on g.id=i.id;'
  );  
SELECT @sql;
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

error is 42000 sql syntax nearest

1

There are 1 answers

0
Devart On

The point is that MySQL cannot prepare such SQL statement. You cannot use API calls for preparing the PREPARE, EXECUTE, or DEALLOCATE PREPARE statements. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html . To avoid the problem, you should call the TMyQuery.Open method without calling the TMyQuery.Prepare method. To get the result value, you can use the AsString property. Here is a code example:

MyQuery.Open;
ShowMessage(MyQuery.Fields[0].AsString);