CODEWITHSUNDEEP
mysqlsqlstored-procedures
jmasterx
jmasterx

Reputation: 54133

How to know if insert query succeeded in stored proceedure?

I am writing a stored procedure to create a player:

CREATE PROCEDURE `create_player` (
firstName TEXT CHARACTER SET utf8,
lastName TEXT CHARACTER SET utf8,
username TEXT CHARACTER SET utf8,
password TEXT CHARACTER SET utf8,
email TEXT CHARACTER SET utf8,
gender ENUM('m','f'),
avatar INTEGER,
OUT result INTEGER)
BEGIN
DECLARE insertVal INTEGER DEFAULT 0;

INSERT INTO `Players` 
(`PlayerFirstName`, 
`PlayerLastName`, 
`PlayerName`, 
`PlayerPassword`, 
`PlayerEmail`,
`PlayerGender`, 
`PlayerAvatar`, 
`PlayerJoinDate`) VALUES (
 firstName,lastName,username,player_hash_password(password),email,gender,avatar,NOW());
END

Say I wanted to let the user know if this insert succeeded or not, how can I find out if it succeeded or not (number of rows affected). I tried to set an integer variable = to the insert statement but that did not work.

What is the typical way of error checking for stored procedures?

Upvotes: 2

Views: 6968

Answers (3)

Singh T
Singh T

Reputation: 161

When you insert data through ExecuteNonQuery() method. This method returns number of rows affected in database as integer.

For Example.

int i=0;
i= cmd.ExecuteNonQuery();
if( i >0)
 {
  msg ="Inserted Successfully";
 }
else
  msg="Not Inserted";

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

As per MySQL documentation on ROW_COUNT(),

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful

After insert statement you can read row_count() into OUT parameter result. Use the same result value in the calling program to let the user know the number of rows affected.

Change your procedure as follows: 

DROP PROCEDURE IF EXISTS `create_player`;

delimiter //

CREATE PROCEDURE `create_player` (
    firstName TEXT CHARACTER SET utf8,
    lastName TEXT CHARACTER SET utf8,
    username TEXT CHARACTER SET utf8,
    password TEXT CHARACTER SET utf8,
    email TEXT CHARACTER SET utf8,
    gender ENUM('m','f'),
    avatar INTEGER,
    OUT result INTEGER )
BEGIN
    DECLARE insertVal INTEGER DEFAULT 0;

    INSERT INTO `Players`(
        `PlayerFirstName`, 
        `PlayerLastName`, 
        `PlayerName`, 
        `PlayerPassword`, 
        `PlayerEmail`,
        `PlayerGender`, 
        `PlayerAvatar`, 
        `PlayerJoinDate`)
    VALUES (
        firstName, lastName, username, 
        player_hash_password( password ), 
        email, gender, avatar, NOW() );

    SELECT ROW_COUNT() INTO result;
END;

//

delimiter ;

Upvotes: 7

rose
rose

Reputation: 7

using php code would help you

<?php  
    include("connect.php");//..your db connection file
    $result = mysql_query("SELECT * FROM Players"); 
    if (mysql_num_rows($result)){
       echo "Data entered successfully";
    }
    else
    {echo "failed";}

?>

Upvotes: 0

Related Questions