[SOLVED] Calling a mysql stored function

[cleibesouza]

Hi all.

I have a stored function that I've been able to successfully call, but I'm having trouble getting the return value from the function.

The function is supposed to merge some system accounts and return a status code if success or failure. I'm able to merge the accounts, but haven't been able to get the return code yet.

Below is the function and the php code I'm using to call it.

 

Function

DELIMITER $$

DROP FUNCTION IF EXISTS `MergeAccounts` $$
CREATE FUNCTION `MergeAccounts`(
  inSystemCode int
, inMyAHEC     int
, inADL        int
, inAHCT       int
, inMAHEC      int
) RETURNS int(11)
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
  DECLARE ActCode    int default 110;          
  DECLARE ADLKey     int;                      
  DECLARE AHCTKey    int;                      
  DECLARE MAHECKey   int;                      
  DECLARE MyAHECRecs int;                      
  DECLARE ADLRecs    int;                      
  DECLARE AHCTRecs   int;                      
  DECLARE MAHECRecs  int;                      
  DECLARE LogNote    varchar(100) default '';  
  DECLARE Parms      varchar(500);             

  SET Parms = CONCAT('(', inSystemCode,
                     '~', inMyAHEC,
                     '~', inADL,
                     '~', inAHCT,
                     '~', inMAHEC, ')');

  IF (inSystemCode <> 1) THEN  
    SET LogNote = CONCAT('[MergeAccounts] Invalid SystemCode: "', inSystemCode, '"');
    SET inSystemCode = 99;
    SET ActCode = 201;

  ELSE  # verify accounts #
    SELECT count(*) INTO MyAHECRecs FROM User WHERE AHECUserID = inMyAHEC and MyAHEC_flag = '1';
    SELECT count(*) INTO ADLRecs    FROM User WHERE AHECUserID = inADL    and ADL_flag    = '1';
    SELECT count(*) INTO AHCTRecs   FROM User WHERE AHECUserID = inAHCT   and AHCT_flag   = '1';
    SELECT count(*) INTO MAHECRecs  FROM User WHERE AHECUserID = inMAHEC  and MAHEC_flag  = '1';

    IF (MyAHECRecs <> 1) THEN  
      SET ActCode = 111;
      SET LogNote = inMyAHEC;

    ELSEIF (ADLRecs <> 1) THEN  
      SET ActCode = 112;
      SET LogNote = inADL;

    ELSEIF (AHCTRecs <> 1) THEN  
      SET ActCode = 113;
      SET LogNote = inAHCT;

    ELSEIF (MAHECRecs <> 1) THEN  
      SET ActCode = 114;
      SET LogNote = inMAHEC;

    ELSE  
      IF (inADL > 0) THEN
        SELECT ADL_NameID INTO ADLKey FROM User WHERE AHECUserID = inADL;
        UPDATE User SET StatusCode = 3 WHERE AHECUserID = inADL;
        UPDATE User SET ADL_flag = '1', ADL_NameID = ADLKey WHERE AHECUserID = inMyAHEC;
        SET LogNote = CONCAT('ADL:', inADL, ' ');
      END IF;

      IF (inAHCT > 0) THEN
        SELECT AHCT_Xkey INTO AHCTKey FROM User WHERE AHECUserID = inAHCT;
        UPDATE User SET StatusCode = 3 WHERE AHECUserID = inAHCT;
        UPDATE User SET AHCT_flag = '1', AHCT_Xkey = AHCTKey WHERE AHECUserID = inMyAHEC;
        SET LogNote = CONCAT(LogNote, 'AHCT:', inAHCT, ' ');
      END IF;

      IF (inMAHEC > 0) THEN
        SELECT MAHEC_PID INTO MAHECKey FROM User WHERE AHECUserID = inMAHEC;
        UPDATE User SET StatusCode = 3 WHERE AHECUserID = inMAHEC;
        UPDATE User SET MAHEC_flag = '1', MAHEC_PID = MAHECKey WHERE AHECUserID = inMyAHEC;
        SET LogNote = CONCAT(LogNote, 'MAHEC:', inMAHEC, ' ');
      END IF;
      SET LogNote = CONCAT(LogNote, '==>> ', inMyAHEC);
    END IF;
  END IF;

  INSERT INTO ActivityLog (SystemCode,   ActivityCode, AHECUserID, Note,    InParms)
    VALUES                (inSystemCode, ActCode,      inMyAHEC,   LogNote, Parms);

  RETURN ActCode;
END $$

DELIMITER ;

 

PHP CODE

$query = "SELECT MergeAccounts(1, $myAHECUserID, $inADL, $inAHCT, $inMAHEC)";
$results = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($results) or die(mysql_error());

 

Again, I'm having trouble getting the ActCode from the function.

 

Thank you for any help.

[effigy]

Do you get any errors, warnings, or notes? You may have to call it from the CLI to see all of these things, I'm not sure. What return value are you getting now?

[cleibesouza]

Nope, not getting any errors. Like I said, the function is working fine. The accounts are merging if the right data is passed to function. I just don't know how to get the actCode off the function.

 

[effigy]

What about warnings and/or notes?

[cleibesouza]

Nothing and it shouldn't because the function is executing right. My problem is that I need to know which actCode the function returns to display a message (either success or failure).

If you look at the php code I'm not outputting the actCode because I don't know how to using a function. I can do it using a stored procedure. And the actCode isn't a variable passed but it's a value returned by the function.

 

[effigy]

It should be the same as any other retrieval. Try $row[0].

 

Notes and warnings can still occur even though the function executes properly.

[cleibesouza]

Ahhh... worked like a charm.

I had tried $row['ActCode'] but didn't think of $row[0];

 

Thanks a bunch!!!!

[effigy]

You can use ActCode if you create an alias:

 

SELECT MergeAccounts(1, $myAHECUserID, $inADL, $inAHCT, $inMAHEC) AS ActCode

 

When in doubt, use print_r on an array to see what's inside.