MySQL script inside set statement

Multi tool use
Multi tool use












1














I got a hint from O.jones and went to create a stored function.



That's what I created:



DELIMITER $$
CREATE FUNCTION change_int(colres VARCHAR(500)) RETURNS INT(11)

BEGIN



    DECLARE res int(11);
DECLARE leng int(11);
DECLARE newres int(11);
DECLARE mult int(11);
DECLARE temp1 int(11);
DECLARE temp2 int(11);

SET res = CAST(colres AS UNSIGNED);
SET leng = CHAR_LENGTH(CAST( colres AS CHAR));
SET newres = 0;
SET mult = 1;
SET temp1 = 0;
SET temp2 = 0;


WHILE (res > 0) DO

SET temp1 = MOD(res , 10 );
SET res = (res DIV 10);
SET temp2 = MOD(res , 10 );
SET newres = (newres +((temp1 + temp2 ) * mult));
SET mult = mult*10;
END WHILE;
SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END $$
DELIMITER ;


But I get error when I ty to run it on line 6 :



#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''res' = CAST(colres AS CHAR)' at line 6


Added a delimiter, new error:



 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END' at line 27


Greate now it worked.
But I can not evoke her:



Ran a test with:



SHOW FUNCTION STATUS;


And it exists.



When I try to ran it like this:



UPDATE `table` SET `col1` = function_name(`col1`);


Also tried:



UPDATE `table` SET `col1` = db.function_name(`col1`);


No luck.










share|improve this question




















  • 2




    This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
    – O. Jones
    Dec 27 at 13:42










  • @O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
    – Hellpless
    Dec 27 at 14:22










  • Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
    – P.Salmon
    Dec 27 at 14:27
















1














I got a hint from O.jones and went to create a stored function.



That's what I created:



DELIMITER $$
CREATE FUNCTION change_int(colres VARCHAR(500)) RETURNS INT(11)

BEGIN



    DECLARE res int(11);
DECLARE leng int(11);
DECLARE newres int(11);
DECLARE mult int(11);
DECLARE temp1 int(11);
DECLARE temp2 int(11);

SET res = CAST(colres AS UNSIGNED);
SET leng = CHAR_LENGTH(CAST( colres AS CHAR));
SET newres = 0;
SET mult = 1;
SET temp1 = 0;
SET temp2 = 0;


WHILE (res > 0) DO

SET temp1 = MOD(res , 10 );
SET res = (res DIV 10);
SET temp2 = MOD(res , 10 );
SET newres = (newres +((temp1 + temp2 ) * mult));
SET mult = mult*10;
END WHILE;
SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END $$
DELIMITER ;


But I get error when I ty to run it on line 6 :



#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''res' = CAST(colres AS CHAR)' at line 6


Added a delimiter, new error:



 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END' at line 27


Greate now it worked.
But I can not evoke her:



Ran a test with:



SHOW FUNCTION STATUS;


And it exists.



When I try to ran it like this:



UPDATE `table` SET `col1` = function_name(`col1`);


Also tried:



UPDATE `table` SET `col1` = db.function_name(`col1`);


No luck.










share|improve this question




















  • 2




    This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
    – O. Jones
    Dec 27 at 13:42










  • @O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
    – Hellpless
    Dec 27 at 14:22










  • Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
    – P.Salmon
    Dec 27 at 14:27














1












1








1







I got a hint from O.jones and went to create a stored function.



That's what I created:



DELIMITER $$
CREATE FUNCTION change_int(colres VARCHAR(500)) RETURNS INT(11)

BEGIN



    DECLARE res int(11);
DECLARE leng int(11);
DECLARE newres int(11);
DECLARE mult int(11);
DECLARE temp1 int(11);
DECLARE temp2 int(11);

SET res = CAST(colres AS UNSIGNED);
SET leng = CHAR_LENGTH(CAST( colres AS CHAR));
SET newres = 0;
SET mult = 1;
SET temp1 = 0;
SET temp2 = 0;


WHILE (res > 0) DO

SET temp1 = MOD(res , 10 );
SET res = (res DIV 10);
SET temp2 = MOD(res , 10 );
SET newres = (newres +((temp1 + temp2 ) * mult));
SET mult = mult*10;
END WHILE;
SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END $$
DELIMITER ;


But I get error when I ty to run it on line 6 :



#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''res' = CAST(colres AS CHAR)' at line 6


Added a delimiter, new error:



 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END' at line 27


Greate now it worked.
But I can not evoke her:



Ran a test with:



SHOW FUNCTION STATUS;


And it exists.



When I try to ran it like this:



UPDATE `table` SET `col1` = function_name(`col1`);


Also tried:



UPDATE `table` SET `col1` = db.function_name(`col1`);


No luck.










share|improve this question















I got a hint from O.jones and went to create a stored function.



That's what I created:



DELIMITER $$
CREATE FUNCTION change_int(colres VARCHAR(500)) RETURNS INT(11)

BEGIN



    DECLARE res int(11);
DECLARE leng int(11);
DECLARE newres int(11);
DECLARE mult int(11);
DECLARE temp1 int(11);
DECLARE temp2 int(11);

SET res = CAST(colres AS UNSIGNED);
SET leng = CHAR_LENGTH(CAST( colres AS CHAR));
SET newres = 0;
SET mult = 1;
SET temp1 = 0;
SET temp2 = 0;


WHILE (res > 0) DO

SET temp1 = MOD(res , 10 );
SET res = (res DIV 10);
SET temp2 = MOD(res , 10 );
SET newres = (newres +((temp1 + temp2 ) * mult));
SET mult = mult*10;
END WHILE;
SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END $$
DELIMITER ;


But I get error when I ty to run it on line 6 :



#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''res' = CAST(colres AS CHAR)' at line 6


Added a delimiter, new error:



 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;
END' at line 27


Greate now it worked.
But I can not evoke her:



Ran a test with:



SHOW FUNCTION STATUS;


And it exists.



When I try to ran it like this:



UPDATE `table` SET `col1` = function_name(`col1`);


Also tried:



UPDATE `table` SET `col1` = db.function_name(`col1`);


No luck.







mysql stored-procedures






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 27 at 15:03

























asked Dec 27 at 13:37









Hellpless

136




136








  • 2




    This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
    – O. Jones
    Dec 27 at 13:42










  • @O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
    – Hellpless
    Dec 27 at 14:22










  • Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
    – P.Salmon
    Dec 27 at 14:27














  • 2




    This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
    – O. Jones
    Dec 27 at 13:42










  • @O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
    – Hellpless
    Dec 27 at 14:22










  • Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
    – P.Salmon
    Dec 27 at 14:27








2




2




This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
– O. Jones
Dec 27 at 13:42




This kind of thing is called a stored function. The intertoobz have many examples and tutorials.
– O. Jones
Dec 27 at 13:42












@O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
– Hellpless
Dec 27 at 14:22




@O.Jones thank you so much, it really gave me a direction. Could you please look why is my statement getting an error?
– Hellpless
Dec 27 at 14:22












Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
– P.Salmon
Dec 27 at 14:27




Have you read dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html and have you set delimiters?
– P.Salmon
Dec 27 at 14:27












1 Answer
1






active

oldest

votes


















0














You have missing column before END WHILE. It must be END WHILE;



Working example






share|improve this answer





















  • One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    – Hellpless
    Dec 27 at 14:48













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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53946002%2fmysql-script-inside-set-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














You have missing column before END WHILE. It must be END WHILE;



Working example






share|improve this answer





















  • One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    – Hellpless
    Dec 27 at 14:48


















0














You have missing column before END WHILE. It must be END WHILE;



Working example






share|improve this answer





















  • One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    – Hellpless
    Dec 27 at 14:48
















0












0








0






You have missing column before END WHILE. It must be END WHILE;



Working example






share|improve this answer












You have missing column before END WHILE. It must be END WHILE;



Working example







share|improve this answer












share|improve this answer



share|improve this answer










answered Dec 27 at 14:30









Simonare

4,88811434




4,88811434












  • One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    – Hellpless
    Dec 27 at 14:48




















  • One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
    – Hellpless
    Dec 27 at 14:48


















One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
– Hellpless
Dec 27 at 14:48






One last thing, cant seem to call it. tried: UPDATE gd_accounts SET account_number = change_int(account_number); UPDATE gd_accounts SET account_number = db_name.change_int(account_number); GET: #1630 - FUNCTION db_name.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
– Hellpless
Dec 27 at 14:48




















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.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • 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














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53946002%2fmysql-script-inside-set-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







U5,t0kWhOGqnzqk,I wCiRb0Br0A8XVM,p3SxOs8PA 5kU3GuR2bwaL,k,xFlKx8J Zvh8SwRyvTJbGx9dF7yroUz
Be6Z Zsq9i M5hhHtWcNyeV8rO1Q5Su72 vYK8cO8Q

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas