0

I'm trying to fill a table with some columns from another table giving an id, but I got this error:

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 'type of products.CategoryID; DECLARE t_productid type of products.ProductID;' at line 4

I don't know how to fix it, I hope you can help me.

 DELIMITER //
 Create procedure getProducts(v_categoryid int)
 BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE t_categoryid type of products.CategoryID;
    DECLARE t_productid type of products.ProductID;
    DECLARE t_productname type of products.ProductName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE cur1 CURSOR(p_categoryid int)
    FOR
    SELECT CategoryID, ProductID, ProductName 
    FROM products
    WHERE CategoryID = p_categoryid; 

    create table IF NOT exists curProducts(
    CategoryID int(10),
    ProductID int(10),
    ProductName varchar(40));

    truncate table curProducts;
    open cur1(v_categoryid);
    read_loop: LOOP
       FETCH cur1 INTO t_categoryid, t_productid, t_productname;
       IF done THEN
          LEAVE read_loop;
       END IF;
       INSERT INTO curProducts VALUES (t_categoryid, t_productid, t_productname);
    END LOOP;
    close cur1;

    select * from curProducts;
END // 
DELIMITER ;
Ester Uscanga
  • 3
  • 2
  • 1
    What is `type of` supposed to be doing? I don't think I've ever seen that syntax. – Tim Biegeleisen Nov 20 '18 at 01:47
  • to get the data type of a giving column, it will be assing to the variable – Ester Uscanga Nov 20 '18 at 02:07
  • @TimBiegeleisen I hadn't either but here it is: https://mariadb.com/kb/en/library/declare-variable/ – Nick Nov 20 '18 at 02:14
  • 1
    What version of MariaDB are you using? `TYPE OF` was only introduced in 10.3 – Nick Nov 20 '18 at 02:14
  • Could it be you're trying to use `TYPE OF` before the table is created. db-fiddle of same thing: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=e5c51e5ab18063ecf501a70c0abe1c7c – danblack Nov 20 '18 at 02:23
  • I have this one: 10.1.32-MariaDB, but here https://mariadb.com/kb/en/library/declare-variable/ it says that it works till 10. version – Ester Uscanga Nov 20 '18 at 02:24
  • It was a typo. Corrected now. Earlier on that page it did say: `TYPE OF and ROW TYPE OF anchored data types for stored routines were introduced in MariaDB 10.3.` – danblack Nov 20 '18 at 02:30
  • I changed the row type for explicit types, and I got an error there – Ester Uscanga Nov 20 '18 at 02:52

2 Answers2

0

You can use explicit types, or upgrade to mariadb-10.3 where the TYPE OF syntax was introduced.

danblack
  • 12,130
  • 2
  • 22
  • 41
0

This solution avoids the syntax error by not needing those statements!

DECLARE done INT DEFAULT FALSE;
DECLARE t_categoryid type of products.CategoryID;
DECLARE t_productid type of products.ProductID;
DECLARE t_productname type of products.ProductName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE cur1 CURSOR(p_categoryid int)
FOR
SELECT CategoryID, ProductID, ProductName 
FROM products
WHERE CategoryID = p_categoryid; 

open cur1(v_categoryid);
read_loop: LOOP
   FETCH cur1 INTO t_categoryid, t_productid, t_productname;
   IF done THEN
      LEAVE read_loop;
   END IF;
   INSERT INTO curProducts VALUES (t_categoryid, t_productid, t_productname);
END LOOP;
close cur1;

All of that can be replaced by merely

INSERT INTO curProducts VALUES (t_categoryid, t_productid, t_productname)
    SELECT CategoryID, ProductID, ProductName 
    FROM products
    WHERE CategoryID = p_categoryid;
Rick James
  • 135,179
  • 13
  • 127
  • 222