How to handle any error in mysql stored procedure.

Question

1.00/5 (1 vote)

See more:

I have created a stored procedure with using some handlers 1062 handler and 1264 handler
1062 handler working fine.but 1264 handler is not working.After creating sp and while executing

--showing 1062 error correct

call insert_article_tags_2(1,1)

--it is not showing out of range errror
call insert_article_tags_2(155555555555555,1877777755555555555)

why please help me on this.thanks in advance.

What I have tried:

SQL

DELIMITER $$
CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT)
BEGIN
 
 declare exit handler for sqlexception
 select 'sql exception invoked';
 
 
 DECLARE EXIT HANDLER FOR 1062 
 
        SELECT 'MySQL error code 1062 invoked';
        
 DECLARE EXIT HANDLER FOR 1264

	SELECT 'Out of range exception';


 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
   VALUES(article_id,tag_id);
    
END

Posted 27-Aug-17 18:43pm

Member 13153537

Updated 27-Aug-17 23:01pm

v4

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-08-27T23:00:00

Solution 1

It depends from where you are calling

call insert_article_tags_2(155555555555555,1877777755555555555)

The passed arguments are beyond the values that can be hold by a 32-bit integer (the used type for SQL INT). The language / shell or from where you are calling it may just truncate the passed arguments:

155555555555555 = 8D7A19A1B8E3 hex

That may be truncated to

19A1B8E3 hex = 430029027

which is a valid value.

Posted 27-Aug-17 23:00pm

Jochen Arndt

Comments

Member 13153537 28-Aug-17 5:05am

thanks for the reply.i am calling mysql workbench itself.what i want is if i pass those values(155555555555555,1877777755555555555) i want the out of range error.What have i created above using handler 1264.

Jochen Arndt 28-Aug-17 5:20am

I don't know how MySQL Workbench parses and handles the input. But in your case the Workbench itself should (at least) generate a warning that the input numbers are truncated.

Just a guess what is happening:
The Workbench is parsing the input and converts the numbers from text to numeric values.

If the Workbench supports 64-bit numbers, it should issue a warning, cast the numbers to 32 bit (INT) and passes them.

If the Workbench does not support 64-bit numbers, it should check the input for large values itself and throw an error.

In all cases it is a problem of the Workbench that finally passes a (valid) 32-bit INT to MySQL so that MySQL never gets an invalid value.

You might test it with a value between 2147483647 and 4294967295. These are invalid signed INTs but valid unsigned integers and might get passed through by the Workbench.

Member 13153537 28-Aug-17 5:28am

workbench throwing error when i passing these values like this call insert_article_tags_2(4300290271,4300290271) Error Code: 1264. Out of range value for column 'article_id' at row 1

i want show this error instead of mysql showing error.I am handling this error using this
DECLARE EXIT HANDLER FOR 1264
SELECT 'Out of range exception'

Jochen Arndt 28-Aug-17 5:45am

I'm sorry, but I still don't get it now what kind of error is shown when (values) by which (SQL or Workbench).

If you call the procedure with numeric INT values (not text), there will be never an out of range error from SQL because all possible 32-bit INT values are valid.

To get the error generated by SQL, you have to pass it as text so that SQL does the conversion (untested):

call insert_article_tags_2('155555555555555','1877777755555555555')

Member 13153537 28-Aug-17 5:55am

Anyway thanks for the help.But this doesn't solve my problem.

Jochen Arndt 28-Aug-17 6:12am

You have two parts: The database and a user interface.
Both can (and should) check for errors, but - while related - are two independant things.

Please try to explain your problem again (in other words). The database is acting as expected and you have added error handlers.

So your problem can be only in the user interface part. But the behaviour of the UI can't be changed by parts like stored procedures belonging to the database.

Member 13153537 28-Aug-17 6:25am

this is my table and below is the stored procedure.

create table customer(firstname varchar(100) unique,lastname varchar(10) unique)

DELIMITER $$
CREATE PROCEDURE insertcustomer(IN firstname varchar(100), IN lastname varchar(100))
BEGIN
declare exit handler for sqlexception
select 'sql exception invoked';

DECLARE EXIT HANDLER FOR 1062

SELECT 'MySQL error code 1062 invoked';

-- insert a new record into article_tags
INSERT INTO customer(firstname,lastname)
VALUES(firstname,lastname);

-- return tag count for the article
END

and below is the php code

#conatiner{
text-align:center;
padding-top:18%;
}

<title>Add

<?php include("connection2.php");?>

<?php
if(isset($_POST['submit'])) {
$link = mysqli_connect("localhost", "root", "Admin@123", "Test");

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];

$res=mysqli_query($link, "CALL insertcustomer('$firstname', '$lastname')");

if($res===false){

echo mysql_errno."".mysql_errno;
}

}
?>

First Name:

Last Name:

What i want is in my table i created two columns as unique.if i pass the same data second i want show the inside stored procedure handler message.
How to do this please help me on this.Thanks in advance.

Jochen Arndt 28-Aug-17 7:01am

But that is completely different from your initial question (strings instead of INT, using PHP instead of Workbench).

If you try to insert an already existing unique key, you will get a SQL error. So why do you need to handle these within the procedure?

In your PHP code you show the SQL error number but not the text set by the handler. To be honest, I don't know at the moment if and how the error text can be accessed by PHP and what is returned.

BTW: From my poimnt of view the SELECT statement in the handlers should be enclosed by BEGIN and END:

DECLARE EXIT HANDLER FOR 1062
    BEGIN
        SELECT 'MySQL error code 1062 invoked';
    END;

Member 13153537 28-Aug-17 7:10am

Ok Thank you.