StoredProc out issues

db96s1

I have developed the following and it works well.

THE PHP PART -----------------------------
<?php
//srvsRqst_sp_Rsvtn.php
if ( mysqli_multi_query($link, "CALL sp_Rsvtn('$activeStatus' , '$activeText' , '$serviceType' , '$clientID' ,'$deliveryLoc', '$xOutType', '$startDate', '$startTime', '$endDate', '$endTime','$billingType','$indexCode','$NOW_DATE', '$Institution_ID','$reservationType' )" )) { }

// get the MAX
$maxDtl = "SELECT Max(transAction.transActionID) AS MaxOftransActionID
FROM transAction
WHERE ( transAction.Institution_ID = $Institution_ID )
AND (transAction.clientID = $clientID ) ";
$maxDtl_results = mysqli_query($link, $maxDtl) or die(mysqli_error($link));
$numMaxDtl_rows = mysqli_num_rows($maxDtl_results);
if ($numMaxDtl_rows!= 0)
{
while ($row = mysqli_fetch_array($maxDtl_results,MYSQLI_ASSOC ) )
{
$transActionID =$row['MaxOftransActionID'] ;
$_SESSION['transActionID'] = $transActionID;
}
}
?>

THE MYSQL Stored procedure PART -----------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS moose.sp_Rsvtn$$
CREATE PROCEDURE moose.sp_Rsvtn( activeStatus INT , activeText VARCHAR(30) , serviceType VARCHAR(40) , clientID INT , deliveryLoc VARCHAR(40), chkOutType INT , startDate DATETIME , startTime DATETIME, endDate DATETIME,endTime DATETIME, billingType VARCHAR(30), indexCode VARCHAR(10), NOW_DATE DATETIME, Institution_ID INT, reservationType VARCHAR(50) )
BEGIN

INSERT INTO transAction (
active,
activeText,
serviceType,
clientID,
deliveryLoc,
chkOutType,
transStartDate,
transStartTime,
transEndDate,
transEndTime,
billingType,
indexCode,
addDate,
Institution_ID,
ReservationType
)
VALUES
(
activeStatus ,
activeText ,
serviceType ,
clientID ,
deliveryLoc ,
chkOutType ,
startDate ,
startTime ,
endDate ,
endTime ,
billingType ,
indexCode ,
NOW_DATE ,
Institution_ID ,
reservationType
) ;

END $$

DELIMITER ;

I am tring to move the php //get the MAX code to the stored procedure.
I have tried several approaches to this in the fololowing DEV version and am not getting it. I sure would appreciate your comments.

the PHP DEV code -------------------------------------
<?php
//srvsRqst_sp_Rsvtn.php
if ( mysqli_multi_query($link, "CALL sp_RsvtnDEV(OUT @new_transActionID INT, '$activeStatus' , '$activeText' , '$serviceType' , '$clientID' ,'$deliveryLoc' , '$xOutType', '$startDate', '$startTime', '$endDate', '$endTime','$billingType','$indexCode','$NOW_DATE', '$Institution_ID','$reservationType' )" ))

{

if (mysqli_multi_query($link, "select @new_transActionID"))
{
if ($result = mysqli_store_result($link))
{
while ($row = mysqli_fetch_row($result))
{
$transActionID = $row[0];
}
}

mysqli_free_result($result);

}

echo $transActionID;

exit();

the Stored Procedure DEV code--------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS moose.sp_RsvtnDEV $$
CREATE PROCEDURE sp_RsvtnDEV( OUT new_transActionID INT, activeStatus INT , activeText VARCHAR(30) , serviceType VARCHAR(40) , clientID INT , deliveryLoc VARCHAR(40), chkOutType INT , startDate DATETIME , startTime DATETIME, endDate DATETIME,endTime DATETIME, billingType VARCHAR(30), indexCode VARCHAR(10), NOW_DATE DATETIME, p_Institution_ID INT, reservationType VARCHAR(50) )
BEGIN

SELECT Max(transaction.transActionID) AS MaxOftransaction
INTO new_transActionID
FROM transActionID
WHERE transAction.Institution_ID = p_Institution_ID;

END $$

DELIMITER ;

can you see where i am missing it? Thanks in advance.

db96s1

bogu

U dont need to use "THE PHP PART ----------------------------- ", u can encapsulate your code in php tags ([ php ]...[ /php ]), something like this:

/* my code here */

What do u mean by this?

db96s1 wrote:
I am tring to move the php //get the MAX code to the stored procedure.

db96s1

the original code gets the auto-increment counter value with a php max query after the sp insert. What I am wanting to do after the insert is something like 'set transActionID = last_insert_id();' and return from the sp with an out parameter that php can receive.

I need an sp and php code audit.

thank you

db96s1

strangest thing happened i started taking it apart and found that this works just as is.

<?php
if ( mysqli_multi_query($link, "CALL sp_Rsvtn( '$activeStatus' , '$activeText' , '$serviceType' , '$clientID' ,'$deliveryLoc' , '$xOutType', '$startDate', '$startTime', '$endDate', '$endTime','$billingType','$indexCode','$NOW_DATE', '$Institution_ID','$reservationType' ) " ))
{
do
{
if (mysqli_multi_query($link, "select @new_transActionID"))
{
if ($result = mysqli_store_result($link))
{
while ($row = mysqli_fetch_row($result))
{
$transActionID = $row[0];
}
}
mysqli_free_result($result);
}
} while (mysqli_next_result($link));
$_SESSION['transActionID'] = $transActionID;
}
?>

php calls this....

DELIMITER $$

/* do as little processing as possible */

INSERT INTO transAction (
	active,
	activeText,
	serviceType,
	clientID,
	deliveryLoc,
	chkOutType,
	transStartDate,
	transStartTime,
	transEndDate,
	transEndTime,
	billingType,
	indexCode,
	addDate,
	Institution_ID,
	ReservationType
)
VALUES
(
	activeStatus 		,
	activeText 			,
	serviceType 		,
	clientID			,
	deliveryLoc			,
	chkOutType			,
	startDate			,
	startTime			,
	endDate				,
	endTime				,
	billingType			,
	indexCode			,
	NOW_DATE			,
	Institution_ID		,
	reservationType
) ;

set @new_transActionID = last_insert_id();

END $$

DELIMITER ;

so without the specific OUT declaration it worked, anybody know why?

db96s1