mysql

Khóa học miễn phí MySQL – Resignal nhận dự án làm có lương

Posted on by user

MySQL – RESIGNAL Statement

Table of content

When working with stored procedures in MySQL, it is important to manage exceptions that may arise during their execution. These exceptions could otherwise lead to an abrupt termination of the procedure.

To address this issue, MySQL offers a way to handle exceptions through error handlers. These handlers can be declared using the DECLARE … HANDLER statement.

The MySQL RESIGNAL Statement

The MySQL RESIGNAL statement is used to provide error information to handlers, applications, or clients when an exception occurs within a stored procedure.

RESIGNAL is specifically used within error handlers and must always include attributes. These attributes specify the SQL state, error code, and error message to be associated with the raised error.

Customizing Error Messages

The RESIGNAL statement allows you to customize error messages using the SET MESSAGE_TEXT command, ensuring smoother procedure execution.

Syntax

Following is the syntax of the MySQL RESIGNAL Statement −

RESIGNAL condition_value [SET signal_information_item]

Where,

  • condition_value represents the error value to be returned, which can be either a “sqlstate_value” or a “condition_name”.

  • signal_information_item allows you to set additional information related to the error condition. You can specify various signal information items like CLASS_ORIGIN, SUBCLASS_ORIGIN, MESSAGE_TEXT, MYSQL_ERRNO, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, or CURSOR_NAME.

Example

In this example, we create a procedure that accepts the short form of degrees and returns their full forms. If we provide an invalid degree i.e. value other than BBA, BCA, MD and ITI, an error message is generated using the RESIGNAL statement −

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
DECLARE wrong_choice CONDITION FOR SQLSTATE ''45000
DECLARE EXIT HANDLER FOR wrong_choice
RESIGNAL SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
IF degree=''BBA'' THEN SET full_form = ''Bachelor of Business Administration 
ELSEIF degree=''BCA'' THEN SET full_form = ''Bachelor of Computer Applications 
ELSEIF degree=''MD'' THEN SET full_form = ''Doctor of Medicine
ELSEIF degree=''ITI'' THEN SET full_form = ''Industrial Training Institute
ELSE 
SIGNAL wrong_choice;
END IF;
END //
DELIMITER ;

You can call the above procedure to retrieve the result as shown below −

CALL example(''MD'', @fullform);

You can retrieve the value of the variable using the following SELECT statement −

SELECT @fullform;

Following is the output obtained −

@fullform
Doctor of Medicine

If you pass an invalid value to the procedure, it will generate an error message as follows −

CALL example (''IIT'', @fullform);

The output obtained is as follows −

ERROR 1001 (45000): Given degree is not valid

Handling Warnings with RESIGNAL

Let us see another example where we do not pass optional attributes to the RESIGNAL statement −

DELIMITER //
CREATE PROCEDURE testexample (num INT)
BEGIN
DECLARE testCondition1 CONDITION FOR SQLSTATE ''01000
DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL;
IF num < 0 THEN
SIGNAL testCondition1;
END IF;
END //
DELIMITER ;

You can call the above procedure by passing two values. But, any SQLSTATE value that starts with ”01” refers to a warning, so the query is executed with a warning as shown below −

CALL testexample(-15);

The output obtained is as follows −

Query OK, 0 rows affected, 1 warning (0.00 sec)

Resignal Statement Using Client Program

We can also perform resignal Using Client Program.

Syntax

To perform the resignal statement through a PHP program, we need to execute the “Stored Procedure” using the mysqli function query() as follows −

$sql = "CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
   IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
   ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
   ELSE 
      RESIGNAL SQLSTATE ''01000''
   SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
      RESIGNAL SQLSTATE ''45000''
   SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
   END IF;
END";
$mysqli->query($sql);

To perform the resignal statement through a JavaScript program, we need to execute the “Stored Procedure” using the query() function of mysql2 library as follows −

var createProcedureSql = `
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
    IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology 
    ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology 
    ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
    ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
    ELSE 
        RESIGNAL SQLSTATE ''01000'' -- Raise a warning
        SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
        RESIGNAL SQLSTATE ''45000'' -- Raise an error
        SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
    END IF;
END`;
con.query(createProcedureSql);

To perform the resignal statement through a Java program, we need to execute the “Stored Procedure” using the JDBC function execute() as follows −

String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology 
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology 
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science 
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science 
ELSE RESIGNAL SQLSTATE ''01000'' SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121; 
RESIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001; 
END IF; 
END";
statement.execute(sql);

To perform the resignal statement through a Python program, we need to execute the “Stored Procedure” using the execute() function of the MySQL Connector/Python as follows −

resignal_statement = ''CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE 
RESIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END;''
cursorObj.execute(resignal_statement)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');
//lets generate error messege using RESIGNAL statement
$sql = "
CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
   BEGIN
      IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
      ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
      ELSE 
         RESIGNAL SQLSTATE ''01000''
      SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
         RESIGNAL SQLSTATE ''45000''
      SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
      END IF;
   END";
if($mysqli->query($sql)){
    printf("Resignal statement created successfully....!n");
}
//lets call the above procedure
$sql = "CALL example_new(''BSC'', @fullform)";
if($mysqli->query($sql)){
    printf("Procedure called successfully...!n");
}
//lets retirve the value variable using SELECT statement...
$sql = "SELECT @fullform";
if($result = $mysqli->query($sql)){
    printf("Variable value is: n");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as shown below −

Resignal statement created successfully....!
Procedure called successfully...!
Variable value is: 
Array
(
   [0] => Bachelor of Science
   [@fullform] => Bachelor of Science
)   


var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");
   // Create a new database
   sql = "Create Database TUTORIALS";
   con.query(sql);
   sql = "USE TUTORIALS";
   con.query(sql);
   // Create the example procedure
   var createProcedureSql = `
       CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
       BEGIN
           IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology 
           ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology 
           ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
           ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
           ELSE 
               RESIGNAL SQLSTATE ''01000'' -- Raise a warning
               SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
               RESIGNAL SQLSTATE ''45000'' -- Raise an error
               SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
           END IF;
       END;
   `;
   con.query(createProcedureSql, function (err) {
       if (err) throw err;
       console.log("Procedure example created!");
       console.log("--------------------------");
     });
   //Passing BSC value to the procedure to get the fullform
   callExampleProcedureSql = "CALL example(''BSC'', @fullform);";
   con.query(callExampleProcedureSql)
   selectFullFormSql = ''SELECT @fullform;
   con.query(selectFullFormSql, function (err, result) {
       if (err) throw err;
       console.log("Full form of degree:");
       console.log(result);
       console.log("--------------------------");
      });
     
   //Passing an invalid value to the procedure
   callNonExistingProcedureSql = "CALL procedureEx (''BBC'', @fullform);";
   con.query(callNonExistingProcedureSql);
   con.query(selectFullFormSql, function (err, result) {
         if (err) throw err;
         console.log("Full form of BBC will leads to an error:");
         console.log(result);
         con.end();
      });
});

Output

The output obtained is as shown below −

Connected!
--------------------------
Procedure example created!
--------------------------
Full form of degree:
[ { ''@fullform'': ''Bachelor of Science'' } ]
--------------------------
C:UsersLenovodesktopJavaScriptconnectDB.js:61
          if (err) throw err;
                             ^

Error: PROCEDURE tutorials.procedureEx does not exist
    at Packet.asError (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacketspacket.js:728:17)
    at Query.execute (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libcommandscommand.js:29:26)
    at Connection.handlePacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:478:34)
    at PacketParser.onPacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:97:12)
    at PacketParser.executeStart (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libpacket_parser.js:75:16)
    at Socket. (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:104:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
Emitted ''error'' event on Query instance at:
    at Query.execute (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libcommandscommand.js:39:14)
    at Connection.handlePacket (C:UsersLenovodesktopJavaScriptnode_modulesmysql2libconnection.js:478:34)
    [... lines matching original stack trace ...]
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: ''ER_SP_DOES_NOT_EXIST'',
  errno: 1305,
  sqlState: ''42000'',
  sqlMessage: ''PROCEDURE tutorials.procedureEx does not exist'',
  sql: "CALL procedureEx (''BBC'', @fullform);",
  fatal: true
}   

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Resignal {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //lets generate error message using RESIGNAL statement
            String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
            BEGIN IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology 
            ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology 
            ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science 
            ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science 
            ELSE RESIGNAL SQLSTATE ''01000'' SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121; 
            RESIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001; 
            END IF; 
            END";
            st.execute(sql);
            System.out.println("Resignal statement created successfully....!");
            //lets call the above procedure
            String sql1 = "CALL example(''BSC'', @fullform)";
            st.execute(sql1);
            System.out.println("Procedure called successfully....!");
            //lets retrieve the value variable using SELECT statement...
            String sql2 = "SELECT @fullform";
            rs = st.executeQuery(sql2);
            System.out.println("variable value is: ");
            while(rs.next()) {
               String var = rs.getNString(1);
               System.out.println(var);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Resignal statement created successfully....!
Procedure called successfully....!
variable value is: 
Bachelor of Science

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Using the RESIGNAL Statement to generate an error message
resignal_statement = ''''''
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree=''B-Tech'' THEN SET full_form = ''Bachelor of Technology
ELSEIF degree=''M-Tech'' THEN SET full_form = ''Master of Technology
ELSEIF degree=''BSC'' THEN SET full_form = ''Bachelor of Science
ELSEIF degree=''MSC'' THEN SET full_form = ''Master of Science
ELSE 
RESIGNAL SQLSTATE ''01000''
SET MESSAGE_TEXT = ''Choose from the existing values'', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE ''45000''
SET MESSAGE_TEXT = ''Given degree is not valid'', MYSQL_ERRNO = 1001;
END IF;
END;
''''''
cursorObj.execute(resignal_statement)
print("Stored procedure ''example'' created successfully!")
# Call the above procedure
call = "CALL example(''BSC'', @fullform);"
cursorObj.execute(call)
print("Procedure ''example'' called successfully!")
# You can retrieve the value of the variable using SELECT statement
retrieve = "SELECT @fullform;"
cursorObj.execute(retrieve)
result = cursorObj.fetchone()
print("Retrieved full_form value:", result[0])
# If you pass an invalid value to the procedure, it will generate an error message
pass_invalid = "CALL procedureEx (''BBC'', @fullform);"
try:
    cursorObj.execute(pass_invalid)
except mysql.connector.Error as err:
    print("Error occurred:", err)
# Closing the cursor and connection
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Stored procedure ''example'' created successfully!
Procedure ''example'' called successfully!
Retrieved full_form value: Bachelor of Science
Error occurred: 1305 (42000): PROCEDURE tut.procedureEx does not exist

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user