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;
}