Saturday, June 3, 2017

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

below is my mysql(MariaDB) server stored procedure code


# call `pkrss_get_rssitem_vo`(2, 0, 0, 0, 1, 0, 0, 0);
#   flag = 1: select items
#   flag =-1: select items count
#   flag = 2: select items with image mode
#   flag =-2: select items with image mode count
#   flag = 3: get top 1 item with each table
#   flag = 4: get top n item with each table
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512);
    DECLARE s2 VARCHAR(512);
    DECLARE t VARCHAR(128);
    
    DECLARE selImgMode INT DEFAULT 0;
    DECLARE selCountMode INT DEFAULT 0;
    
    IF flag = -1 THEN
     SET selCountMode = 1;
    END IF;
    
    IF flag = -2 THEN
     SET selCountMode = 1;
     SET selImgMode = 1;
 END IF;
    IF flag = 2 THEN
     SET selImgMode = 1;
 END IF;
 
 SET s = '';
        
    IF selCountMode = 1 THEN
     SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
     
     SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
     
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF selImgMode = 1 THEN
         SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
     
     SET @t2 = s;
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
 ELSE
    
  SET s = CONCAT(s,'SELECT a.*');
  
  IF tid = 0 THEN
         SET s = CONCAT(s,',t.title AS table_name');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
     END IF;
 
  SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
  
     IF tid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
     END IF;
 
     SET s = CONCAT(s,' WHERE 1=1');
     
     IF selImgMode = 1 THEN
      SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
 
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF flag = 3 THEN
      # get top 1 item with each table
      SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
      # select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
     ELSEIF flag = 4 THEN
   # get top 3 records with each table   
   SET s = CONCAT('SET @num := 0, @tid := 0;', s);
   SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
   SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
   # set @num := 0, @tid := 0;
   # select b.* from (
   #  select a.*, 
   #  @num := if(@tid=tid, @num + 1, 1) as row_number, 
   #  @tid := a.tid as other_tid 
   #  from pkrss_rssitem_l2 as a 
   #  order by a.tid,a.id desc
   # ) as b 
   # where b.row_number <= 3 order by b.tid,b.id desc limit 20;
     END IF;
 
     SET s = CONCAT(s,' ORDER BY');
 
     IF idOrderBy != 0 THEN
         IF idOrderBy > 0 THEN
             SET s = CONCAT(s,' a.id ASC');
         ELSE
             SET s = CONCAT(s,' a.id DESC');
         END IF;
     ELSE
      IF timeOrderBy > 0 THEN
           SET s = CONCAT(s,' a.item_pubdate ASC');
         ELSE
           SET s = CONCAT(s,' a.item_pubdate DESC');
         END IF;
     END IF;
 
     SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
          
     SET t = CONCAT('_tmp_',UUID());
     SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
    `tid` int(11),
    `cid` int(11),
    `item_pubdate` datetime,
    `item_title` varchar(127) COLLATE utf8_bin,
    `item_desc` varchar(246) COLLATE utf8_bin,
    `item_link` varchar(246) COLLATE ascii_bin,
    `item_authors` varchar(64) COLLATE utf8_bin,
    `item_imgs` text COLLATE utf8_bin,
    `table_name` varchar(127) COLLATE utf8_bin,
    `catalog_name` varchar(64) COLLATE utf8_bin,
    `catalog_cls` varchar(32) COLLATE utf8_bin
    );');
    
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
     IF tid = 0 THEN
         SET s2 = CONCAT(s2,',`table_name`');
     END IF;
 
     IF cid = 0 THEN
         SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
     END IF;
     
     SET @t2 = CONCAT(s2,') ', s, ';');
     
  PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     set @t2 = CONCAT('SELECT * FROM `',t,'`');
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
    END IF;
   
END;;
DELIMITER ;

And mysql return below error message

CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512)
MySQL 返回: 文档

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

How to fixed

add "DELIMITER ;;" to front and end rows:

DELIMITER ;;
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512);
    DECLARE s2 VARCHAR(512);
    DECLARE t VARCHAR(128);
    
    DECLARE selImgMode INT DEFAULT 0;
    DECLARE selCountMode INT DEFAULT 0;
    
    IF flag = -1 THEN
     SET selCountMode = 1;
    END IF;
    
    IF flag = -2 THEN
     SET selCountMode = 1;
     SET selImgMode = 1;
 END IF;
    IF flag = 2 THEN
     SET selImgMode = 1;
 END IF;
 
 SET s = '';
        
    IF selCountMode = 1 THEN
     SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
     
     SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
     
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF selImgMode = 1 THEN
         SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
     
     SET @t2 = s;
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
 ELSE
    
  SET s = CONCAT(s,'SELECT a.*');
  
  IF tid = 0 THEN
         SET s = CONCAT(s,',t.title AS table_name');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
     END IF;
 
  SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
  
     IF tid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
     END IF;
 
     SET s = CONCAT(s,' WHERE 1=1');
     
     IF selImgMode = 1 THEN
      SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
 
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF flag = 3 THEN
      # get top 1 item with each table
      SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
      # select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
     ELSEIF flag = 4 THEN
   # get top 3 records with each table   
   SET s = CONCAT('SET @num := 0, @tid := 0;', s);
   SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
   SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
   # set @num := 0, @tid := 0;
   # select b.* from (
   #  select a.*, 
   #  @num := if(@tid=tid, @num + 1, 1) as row_number, 
   #  @tid := a.tid as other_tid 
   #  from pkrss_rssitem_l2 as a 
   #  order by a.tid,a.id desc
   # ) as b 
   # where b.row_number <= 3 order by b.tid,b.id desc limit 20;
     END IF;
 
     SET s = CONCAT(s,' ORDER BY');
 
     IF idOrderBy != 0 THEN
         IF idOrderBy > 0 THEN
             SET s = CONCAT(s,' a.id ASC');
         ELSE
             SET s = CONCAT(s,' a.id DESC');
         END IF;
     ELSE
      IF timeOrderBy > 0 THEN
           SET s = CONCAT(s,' a.item_pubdate ASC');
         ELSE
           SET s = CONCAT(s,' a.item_pubdate DESC');
         END IF;
     END IF;
 
     SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
          
     SET t = CONCAT('_tmp_',UUID());
     SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
    `tid` int(11),
    `cid` int(11),
    `item_pubdate` datetime,
    `item_title` varchar(127) COLLATE utf8_bin,
    `item_desc` varchar(246) COLLATE utf8_bin,
    `item_link` varchar(246) COLLATE ascii_bin,
    `item_authors` varchar(64) COLLATE utf8_bin,
    `item_imgs` text COLLATE utf8_bin,
    `table_name` varchar(127) COLLATE utf8_bin,
    `catalog_name` varchar(64) COLLATE utf8_bin,
    `catalog_cls` varchar(32) COLLATE utf8_bin
    );');
    
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
     IF tid = 0 THEN
         SET s2 = CONCAT(s2,',`table_name`');
     END IF;
 
     IF cid = 0 THEN
         SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
     END IF;
     
     SET @t2 = CONCAT(s2,') ', s, ';');
     
  PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     set @t2 = CONCAT('SELECT * FROM `',t,'`');
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
    END IF;
   
END;;
DELIMITER ;






No comments: