MySQL script inside set statement

Multi tool use
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
add a comment |
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
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
add a comment |
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
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
mysql stored-procedures
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
You have missing column before END WHILE
. It must be END WHILE;
Working example
One last thing, cant seem to call it. tried: UPDATEgd_accounts
SETaccount_number
= change_int(account_number
); UPDATEgd_accounts
SETaccount_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
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
You have missing column before END WHILE
. It must be END WHILE;
Working example
One last thing, cant seem to call it. tried: UPDATEgd_accounts
SETaccount_number
= change_int(account_number
); UPDATEgd_accounts
SETaccount_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
add a comment |
You have missing column before END WHILE
. It must be END WHILE;
Working example
One last thing, cant seem to call it. tried: UPDATEgd_accounts
SETaccount_number
= change_int(account_number
); UPDATEgd_accounts
SETaccount_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
add a comment |
You have missing column before END WHILE
. It must be END WHILE;
Working example
You have missing column before END WHILE
. It must be END WHILE;
Working example
answered Dec 27 at 14:30


Simonare
4,88811434
4,88811434
One last thing, cant seem to call it. tried: UPDATEgd_accounts
SETaccount_number
= change_int(account_number
); UPDATEgd_accounts
SETaccount_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
add a comment |
One last thing, cant seem to call it. tried: UPDATEgd_accounts
SETaccount_number
= change_int(account_number
); UPDATEgd_accounts
SETaccount_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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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