MySQL Stored Procedure not throwing error if it contains a query that succeedsstored procedure returns ERROR...
Why restrict private health insurance?
Having the player face themselves after the mid-game
(Codewars) Linked Lists-Sorted Insert
Does the US political system, in principle, allow for a no-party system?
What does *dead* mean in *What do you mean, dead?*?
When an outsider describes family relationships, which point of view are they using?
Would those living in a "perfect society" not understand satire
Why aren't there more Gauls like Obelix?
How should I solve this integral with changing parameters?
How do spaceships determine each other's mass in space?
Help! My Character is too much for her story!
Why do phishing e-mails use faked e-mail addresses instead of the real one?
Movie: boy escapes the real world and goes to a fantasy world with big furry trolls
How can a demon take control of a human body during REM sleep?
Is there a way to make cleveref distinguish two environments with the same counter?
What is better: yes / no radio, or simple checkbox?
Is there a math expression equivalent to the conditional ternary operator?
How do we create new idioms and use them in a novel?
Trocar background-image com delay via jQuery
What do you call someone who likes to pick fights?
Either of .... (Plural/Singular)
Why is there an extra space when I type "ls" on the Desktop?
Is there a logarithm base for which the logarithm becomes an identity function?
I can't die. Who am I?
MySQL Stored Procedure not throwing error if it contains a query that succeeds
stored procedure returns ERROR 1305 (42000): FUNCTION does not existWorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT?What's causing this ERROR 2000 (HY000): Unknown MySQL errorAccess denied to user@localhostMysql stored procedure SQLEXCEPTIONMysql/InnoDB says it doesn't have access rights to create temp tables, but it doesTable was deleted during ALTER, how to recover?MySQL 5.6 - ERROR 1292 (22007): Incorrect datetime value: '2015-03-29 01:01:12'Unexplained MySQL behavior - SP returning wrong results (sometimes)MySQL stored procedure fails to populate first column, gives strange error
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2, does not throw any error.
CREATE PROCEDURE `test_procedure_1`()
BEGIN
select 'gary';
CALL raise_error;
END
It returns the result set:
[0] => Array
(
[0] => Array
(
[gary] => gary
)
)
However...
CREATE PROCEDURE `test_procedure_2`()
BEGIN
CALL raise_error;
select 'gary';
END
...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.
Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!
I'm using
- Ubuntu: 16.04, and
- PHP: 7.0.15
- mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:
Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ
The output of the Mysqli version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------
The output of the PDO version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------
mysql stored-procedures php error-handling
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2, does not throw any error.
CREATE PROCEDURE `test_procedure_1`()
BEGIN
select 'gary';
CALL raise_error;
END
It returns the result set:
[0] => Array
(
[0] => Array
(
[gary] => gary
)
)
However...
CREATE PROCEDURE `test_procedure_2`()
BEGIN
CALL raise_error;
select 'gary';
END
...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.
Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!
I'm using
- Ubuntu: 16.04, and
- PHP: 7.0.15
- mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:
Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ
The output of the Mysqli version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------
The output of the PDO version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------
mysql stored-procedures php error-handling
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49
|
show 1 more comment
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2, does not throw any error.
CREATE PROCEDURE `test_procedure_1`()
BEGIN
select 'gary';
CALL raise_error;
END
It returns the result set:
[0] => Array
(
[0] => Array
(
[gary] => gary
)
)
However...
CREATE PROCEDURE `test_procedure_2`()
BEGIN
CALL raise_error;
select 'gary';
END
...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.
Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!
I'm using
- Ubuntu: 16.04, and
- PHP: 7.0.15
- mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:
Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ
The output of the Mysqli version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------
The output of the PDO version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------
mysql stored-procedures php error-handling
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2, does not throw any error.
CREATE PROCEDURE `test_procedure_1`()
BEGIN
select 'gary';
CALL raise_error;
END
It returns the result set:
[0] => Array
(
[0] => Array
(
[gary] => gary
)
)
However...
CREATE PROCEDURE `test_procedure_2`()
BEGIN
CALL raise_error;
select 'gary';
END
...this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.
Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!
I'm using
- Ubuntu: 16.04, and
- PHP: 7.0.15
- mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:
Mysqli Version: https://pastebin.com/rATMST4H
PDO version: https://pastebin.com/Wiq3yFmQ
The output of the Mysqli version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------
The output of the PDO version is:
Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN select 'gary'; CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary )
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN CALL raise_error; select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist )
----------
mysql stored-procedures php error-handling
mysql stored-procedures php error-handling
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
edited Feb 6 at 20:40
Gary Reckard
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
asked Apr 10 '18 at 23:36
Gary ReckardGary Reckard
213
213
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49
|
show 1 more comment
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49
|
show 1 more comment
1 Answer
1
active
oldest
votes
That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.
The only way to achieve something similar would be something like this:
drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database())
then
select 'gary';
CALL raise_error;
end if;
END$$
DELIMITER ;
call test_procedure_3;
So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f203557%2fmysql-stored-procedure-not-throwing-error-if-it-contains-a-query-that-succeeds%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
That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.
The only way to achieve something similar would be something like this:
drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database())
then
select 'gary';
CALL raise_error;
end if;
END$$
DELIMITER ;
call test_procedure_3;
So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
add a comment |
That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.
The only way to achieve something similar would be something like this:
drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database())
then
select 'gary';
CALL raise_error;
end if;
END$$
DELIMITER ;
call test_procedure_3;
So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
add a comment |
That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.
The only way to achieve something similar would be something like this:
drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database())
then
select 'gary';
CALL raise_error;
end if;
END$$
DELIMITER ;
call test_procedure_3;
So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
That's not possible. You can not prevent execution of statements based on if later statements will throw an error in a stored procedure.
The only way to achieve something similar would be something like this:
drop procedure if exists test_procedure_3;
DELIMITER $$
CREATE PROCEDURE `test_procedure_3`()
BEGIN
if exists (select 1 from information_schema.routines where routine_name = 'raise_error' and routine_schema = database())
then
select 'gary';
CALL raise_error;
end if;
END$$
DELIMITER ;
call test_procedure_3;
So you just check, if the procedure exists beforehand. There's no other way (I tried with transactions, signal, prepared statements and what not).
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
edited Apr 12 '18 at 8:23
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
answered Apr 12 '18 at 8:11
tombomtombom
2,34511222
2,34511222
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
add a comment |
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
Hi there @tombom. I'm not trying to prevent the execution of the "select 'gary'" statement. I'm just saying that even in my procedure has the "select 'gary'" in it, if it later contains a statement that should throw an error.... the procedure should report that error in some way.
– Gary Reckard
Feb 6 at 20:40
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f203557%2fmysql-stored-procedure-not-throwing-error-if-it-contains-a-query-that-succeeds%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
I think it ought to be returning two result sets - the first would be the 'gary' rs, and a second one with the error. Have you tried using mysqli, just to see if you get a different result? Also, how are you calling this from PHP /PDO?
– dbdemon
Apr 11 '18 at 7:34
@dbdemon I have added some examples of both mysqli and PDO methods of calling those procedures from PHP, as well as the output I get from those examples. Thanks for your help!
– Gary Reckard
Apr 11 '18 at 14:15
You seem to have swithed the order of selecting 'gary' and calling raise_error, but to me it looks like the mysqli output looks correct? Whereas the pdo output is still wrong.
– dbdemon
Apr 11 '18 at 15:03
@dbdemon In both examples (mysqli and PDO), I am calling both procedures. One selects and then raises error, the other raises error and then selects. In both situations, both mysqli and pdo are NOT showing me the error in the test_procedure_1. I did notice an issue with my example code, where I'm not calling next_result() to get more result sets, but even after I modify it, I'm still getting no error when I would expect one.
– Gary Reckard
Apr 11 '18 at 15:17
You don't seem to be calling the procedures, but only creating them.
– mustaccio
Apr 11 '18 at 16:49