Declare mariaDB error. How can fix this query?

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 ;

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

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 1:47

to get the data type of a giving column, it will be assing to the variable

– Ester Uscanga
Nov 20 '18 at 2:07

@TimBiegeleisen I hadn't either but here it is: mariadb.com/kb/en/library/declare-variable

– Nick
Nov 20 '18 at 2:14

1

What version of MariaDB are you using? TYPE OF was only introduced in 10.3

– Nick
Nov 20 '18 at 2:14

Could it be you're trying to use TYPE OF before the table is created. db-fiddle of same thing: dbfiddle.uk/…

– danblack
Nov 20 '18 at 2:23

|
show 3 more comments

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 ;

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

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 1:47

to get the data type of a giving column, it will be assing to the variable

– Ester Uscanga
Nov 20 '18 at 2:07

@TimBiegeleisen I hadn't either but here it is: mariadb.com/kb/en/library/declare-variable

– Nick
Nov 20 '18 at 2:14

1

What version of MariaDB are you using? TYPE OF was only introduced in 10.3

– Nick
Nov 20 '18 at 2:14

Could it be you're trying to use TYPE OF before the table is created. db-fiddle of same thing: dbfiddle.uk/…

– danblack
Nov 20 '18 at 2:23

|
show 3 more comments

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 ;

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

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 ;

mysql parameters mariadb procedure sql-pl

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

edited Nov 21 '18 at 1:25

asked Nov 20 '18 at 1:41

Ester Uscanga

asked Nov 20 '18 at 1:41

Ester Uscanga

asked Nov 20 '18 at 1:41

Ester Uscanga

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 1:47

to get the data type of a giving column, it will be assing to the variable

– Ester Uscanga
Nov 20 '18 at 2:07

@TimBiegeleisen I hadn't either but here it is: mariadb.com/kb/en/library/declare-variable

– Nick
Nov 20 '18 at 2:14

1

What version of MariaDB are you using? TYPE OF was only introduced in 10.3

– Nick
Nov 20 '18 at 2:14

Could it be you're trying to use TYPE OF before the table is created. db-fiddle of same thing: dbfiddle.uk/…

– danblack
Nov 20 '18 at 2:23

|
show 3 more comments

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 1:47

to get the data type of a giving column, it will be assing to the variable

– Ester Uscanga
Nov 20 '18 at 2:07

@TimBiegeleisen I hadn't either but here it is: mariadb.com/kb/en/library/declare-variable

– Nick
Nov 20 '18 at 2:14

1

What version of MariaDB are you using? TYPE OF was only introduced in 10.3

– Nick
Nov 20 '18 at 2:14

Could it be you're trying to use TYPE OF before the table is created. db-fiddle of same thing: dbfiddle.uk/…

– danblack
Nov 20 '18 at 2:23

What is type of supposed to be doing? I don't think I've ever seen that syntax.

– Tim Biegeleisen
Nov 20 '18 at 1:47

to get the data type of a giving column, it will be assing to the variable

– Ester Uscanga
Nov 20 '18 at 2:07

@TimBiegeleisen I hadn't either but here it is: mariadb.com/kb/en/library/declare-variable

– Nick
Nov 20 '18 at 2:14

What version of MariaDB are you using? TYPE OF was only introduced in 10.3

– Nick
Nov 20 '18 at 2:14

Could it be you're trying to use TYPE OF before the table is created. db-fiddle of same thing: dbfiddle.uk/…

– danblack
Nov 20 '18 at 2:23

|
show 3 more comments

2 Answers
2

active

oldest

votes

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

answered Nov 20 '18 at 2:31

danblack

2,0881317

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

add a comment |

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;

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53385063%2fdeclare-mariadb-error-how-can-fix-this-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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

answered Nov 20 '18 at 2:31

danblack

2,0881317

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

add a comment |

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

answered Nov 20 '18 at 2:31

danblack

2,0881317

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

add a comment |

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

answered Nov 20 '18 at 2:31

danblack

2,0881317

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

answered Nov 20 '18 at 2:31

danblack

2,0881317

answered Nov 20 '18 at 2:31

danblack

2,0881317

answered Nov 20 '18 at 2:31

danblack

2,0881317

answered Nov 20 '18 at 2:31

danblack

2,0881317

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

add a comment |

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

It was about the handler, as you showed me before. Thank you so much!

– Ester Uscanga
Nov 20 '18 at 3:23

add a comment |

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;

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

add a comment |

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;

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

add a comment |

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;

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

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;

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

answered Nov 20 '18 at 4:09

Rick James

68.7k559100

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

add a comment |

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

I know, but I had to do it in that way because of "school reasons", but thank you

– Ester Uscanga
Nov 20 '18 at 4:31

Grrr... Teaching inefficiency. My take on cursors: They are almost never useful.

– Rick James
Nov 20 '18 at 4:45

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk