How do i get the last identity for a INSERT Operation calling a SProc

[dsscsystems]

I have not seen any examples that show how to return the value for the last Identity that was last generated, how do i get the last generated ID. I need example for calling stored procedures? The current INSERT a Record into the database but doesn't retrieve the ID that Generated.

 

 

 

Sample SQL ran from work bench, record is created

SET @Id := 0;
call uspIns_ExcelExport(@Id, 'Admin', 'Filename', 0,0,null,'');
SELECT LAST_INSERT_ID();

 

Table

delimiter $$

CREATE TABLE `excelexport` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) DEFAULT NULL,
  `filename` varchar(256) DEFAULT NULL,
  `success` bit(1) DEFAULT NULL,
  `started` bit(1) DEFAULT NULL,
  `finished` datetime DEFAULT NULL,
  `error` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8$$

 

 

Store Procedure:

DELIMITER $$

/**** INSERT Stored Procedures ****/
CREATE PROCEDURE `uspIns_ExcelExport`(
OUT OUT_Id BIGINT, 
IN IN_userId VARCHAR(100), 
IN IN_filename NVARCHAR(256), 
IN IN_success INT, 
IN IN_started INT, 
IN IN_finished DATETIME, 
IN IN_error NVARCHAR(2000))
BEGIN
INSERT INTO `ExcelExport`
(`userId`, `filename`, `success`, `started`, 
`finished`, `error`)
VALUES  (IN_userId, IN_filename, IN_success, IN_started, 
IN_finished, IN_error);

SELECT * FROM `ExcelExport` WHERE `Id` = LAST_INSERT_ID();

END$$

 

 

PHP MySQL Code

 

public function Create() {

        $returnValue = false;

        if (isset($this->userId)) {

            $mysqli = new mysqli(__MYSQL_HOST__, __MYSQL_USERNAME__,
                __MYSQL_PASSWORD__, __MYSQL_DBNAME__);
            
            if (!$mysqli->connect_errno){
                
                $Id = -1;
                
                $stmt = $mysqli->prepare("call uspIns_ExcelExport (?,?,?,?,?,?,?)");
                if ( !$stmt ) {
                    die('mysqli error: '.$mysqli->errno);
                }
                if (!$stmt->bind_param('issiiss', $Id, $this->userId, $this->filename, intval($this->success),
                        intval($this->started), $this->finished, $this->error)){
                    die( 'stmt error: '.$stmt->errno );
                }
                
                if (!$stmt->execute()){
                    die( 'stmt error: '.$stmt->errno );
                }
                
                $rtnId = 0;
                
                $stmt->store_result();
                $stmt->bind_result($rtnId);
                
                while  ($stmt->fetch()){
                    printf ("%s\n", $rtnId);
                }
                
                $stmt->free_result();
                
                $stmt->close();
                
//                $this->Id = $stmt->insert_id;                
            }
            else{
                echo "Failed to connect to MySQL: " . mysqli_connect_error();
            }
            
            /* close connection */
            mysqli_close($cn);
            
            if ($this->Id > 0)
                $this->exist = true;
            if ($this->Id > 0)
                $returnValue = true;
        }
        
        return $returnValue;
    }

 

[fenway]

I'm not certain LAST_INSERT_ID() is meaningful after that last SELECT -- why do you need to get the values you just inserted? Why not do with inside a function?