Member Avatar for bharanidharanit

hi,
I hav a created a field with unique constraint, so if it got duplicated error must be returned from procedure. Wats wrong here?

$dbconn = new DbConn;
		$dbc = $dbconn->Fn_CreateDbConn();
		$Qry = $dbc->prepare("CALL CMS_School_Insert(:SchoolID,:SchoolName)");	
		$Qry -> bindParam(":SchoolID",$SchoolID);
		$Qry -> bindParam(":SchoolName",$SchoolName);
                try
                {
		    $Qry -> execute();
                 }catch(PDOException $e)
                  {
                      return "Error: ".$e->getMessage();
                  }

Here s my procedure

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `CMS_School_Insert`(_SchoolID INT, _SchoolName VARCHAR(100))
BEGIN
    IF (SELECT COUNT(*) FROM CMT_School WHERE School_ID=_SchoolID) = 0 THEN
        INSERT INTO CMT_School (School_Name) VALUES (_SchoolName);
    ELSE
        UPDATE CMT_School SET School_Name=_SchoolName WHERE School_ID=_SchoolID;
    END IF;
END//
  • 2 Contributors
  • 1 Reply
  • 63 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Your stored proc never returns an error, because it updates the existing record when found.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.