All times are UTC + 1 hour




Post new topic Reply to topic  [ 3 posts ] 
  Print view

concat queries in getrows()
Author Message
PostPosted: Tue Oct 24, 2017 4:41 am 
Regular
User avatar
Offline

Joined: Thu Aug 25, 2011 9:31 pm
Posts: 308
Location: Mumbai, India
in postupdate() of a a normal node i m trying to fire $db->query(), $db->getrows() on a query which is concat of two mysql queries with a semicolon demarking.

But atk gives mysql error.. When i getrows() them separately it works.

The concat query when i echo and run the query thru phpmyadmin - it works.


Code:
        $qry = "SELECT @pStore:=_latin1'$store' COLLATE latin1_general_ci p
             ,@pItem_id:=_latin1'$item_id' COLLATE latin1_general_ci p2;

             select *,  balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih
             FROM inv_itemstock_for_store_item v
             ";

        // echo  $qry; die();



  $qty=$db->getrows($qry); // gives mysql error at select *,  balance_unchk+ .....
  $qty=$db->query($qry); // gives mysql error at select *,  balance_unchk+ .....


Top
 Profile  
 

Re: concat queries in getrows()
PostPosted: Tue Oct 24, 2017 5:03 am 
Regular
Offline

Joined: Wed Aug 26, 2009 4:49 am
Posts: 353
Location: Queenstown, New Zealand
Looks like this is by design
All the documentation comments use the phrase "the query" and not "the queries" !
Regards
Wayne


Top
 Profile  
 

Re: concat queries in getrows()
PostPosted: Sun Nov 05, 2017 1:14 pm 
Regular
User avatar
Offline

Joined: Thu Aug 25, 2011 9:31 pm
Posts: 308
Location: Mumbai, India
yes multi-queries do NOT work in $db->getrows()/query()

To make it work lot of changes will have to be done starting in atk/db/atkMysqliDb.inc
But this is complicated and NOT DONE.
Code:
atk/db/atkMysqliDb.inc
:
:

   protected function _query($query, $isSystemQuery)
   {
     if (atkconfig("debug") >= 0)
     {
       atkimport("atk.utils.atkdebugger");
       atkDebugger::addQuery($query, $isSystemQuery);
     }

     //---- mysqli_multi_query for semi colon delimited but fetching rows logic differs - also it is known security injunction threat when used as generic query executon
     //return @mysqli_multi_query($this->m_link_id, $query); //The @ symbol is the error control operator (AKA the "silence" or "shut-up" operator).
  //end Thu.26.Oct.2017


     return @mysqli_query($this->m_link_id, $query); //The @ symbol is the error control operator (AKA the "silence" or "shut-up" operator).
   }




instead the multi-qeury was re-written as single query .... to pass @ params to the View
#2 seprate dummy fns() defined to create @params in the view as below


DELIMITER $$
DROP FUNCTION IF EXISTS f_store_param_to_views$$
-- Sun.22.Oct.2017 used in inv_itemstock_for_store_item
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select * FROM inv_itemstock_for_store_item v where 1=1;
CREATE DEFINER=`root`@`localhost` FUNCTION f_store_param_to_views() RETURNS VARCHAR(20) CHARSET latin1
NO SQL
DETERMINISTIC
RETURN @pStore$$
DELIMITER ;

DELIMITER $$
DROP FUNCTION IF EXISTS f_item_id_param_to_views$$
-- Sun.22.Oct.2017 used in inv_itemstock_for_store_item
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select * FROM inv_itemstock_for_store_item v where 1=1;
CREATE DEFINER=`root`@`localhost` FUNCTION f_item_id_param_to_views() RETURNS VARCHAR(20) CHARSET latin1
NO SQL
DETERMINISTIC
RETURN @pItem_id$$
DELIMITER ;


DELIMITER $$
DROP VIEW IF EXISTS `inv_itemstock_for_store_item`$$
CREATE ALGORITHM=UNDEFINED DEFINER=root@`localhost`
SQL SECURITY DEFINER VIEW `inv_itemstock_for_store_item` AS
-- Sun.22.Oct.2017 needs @pItem_id and @pStore
-- usage :
-- SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
-- ,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
-- select *, balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih FROM inv_itemstock_for_store_item v where 1=1;

SELECT
`i`.`sto_locn` AS `sto_locn`,
`i`.`store` AS `store`,
`i`.`item_id` AS `item_id`,
SUM(IF((`i`.`doctype` = _latin1'Grn'),`i`.`recd_qty`,IF((`i`.`doctype` = _latin1'Cr'),(-(1) * `i`.`chk_qty`),0))) AS `balance_unchk`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`accpt_qty`)) AS `balance`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`rej_qty`)) AS `balance_rej`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`cut_pc_qty`)) AS `balance_cut_pc`,
SUM((IF((`i`.`doctype` IN (_latin1'Cr',_latin1'Jr',_latin1'Sa',_latin1'Si')),1,IF((`i`.`doctype` IN (_latin1'Iss',_latin1'St',_latin1'Mrn')),-(1),0)) * `i`.`second_qty`)) AS `balance_second`
FROM `gen_inventory` `i`
WHERE `i`.`item_id` > 0
and i.store = f_store_param_to_views() -- <==== dummy fns() to define @Values
and i.item_id = f_item_id_param_to_views() -- <==== dummy fns() to define @Values
GROUP BY `i`.`sto_locn`,`i`.`store`,`i`.`item_id`
$$
DELIMITER ;

-- following works - multiQuery - it does NOT work in atk $db->getrows()/Query() etc
SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2;
select *, balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih FROM inv_itemstock_for_store_item v where 1=1;


-- following does NOT work because after FROM table-v is used before and on-the-fly param is 2nd table the @values used in Fst table-v
select * FROM inv_itemstock_for_store_item v
,(SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2
) param
where 1=1

-- following works
select * FROM
(SELECT @pStore:=_latin1'T-Store' COLLATE latin1_general_ci p
,@pItem_id:=_latin1'54126' COLLATE latin1_general_ci p2
) param
,inv_itemstock_for_store_item v
where 1=1

in the atk php inc it is used as this

Code:

:
        $item_id = $this->getAttribute('item_id')->value2db($record);
        $store = $user[store];

  //     Note : @p shud be fst table and view using these @p shud be 2nd table
        $qry = "
            select v.*
            ,balance_unchk+balance+balance_rej+balance_cut_pc+balance_second AS tot_sih
            FROM
            (SELECT @pStore:=_latin1'$store' COLLATE latin1_general_ci p
                     ,@pItem_id:=_latin1'$item_id' COLLATE latin1_general_ci p2
                ) param
            ,inv_itemstock_for_store_item v
            where 1=1
             ";


        $qty=$db->getrows($qry);
:


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 10 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group