Page 1 of 1

Parsing Procedure Parameters Possible?

Posted: Wed Oct 14, 2009 7:20 pm
by RegularKid
Hi!

I've got a MySQL stored procedure and would like to get a list of it's parameter names and types. I'm able to get a single list of procedure parameters by doing the following MySQL SELECT:

Code: Select all

 
$sql = "SELECT param_list FROM mysql.proc WHERE db = '" . $dbName . "' AND name = '" . $storedProcedureName . "';";
$res = $mysqli->query( $sql );
while( $row = $res->fetch_assoc() )
{
    foreach( $row as $value )
    {
        echo $value;
    }
}
 
This gives me something like the following result ( as a single text field ):

Code: Select all

 
IN p_name VARCHAR( 255 ),
IN p_description VARCHAR( 255 ),
OUT p_userID INT( 15 )
 
What I'd like to do is parse this and generate 3 arrays:
1. Param type ( ie. IN / OUT / INOUT )
2. Param name ( ie. p_name, p_userID, etc. )
3. Param var type ( ie. INT( 15 ), VARCHAR( 255 ), etc. )

This way I'd be able to loop through each array to get information about each parameter like so:

Code: Select all

 
$idx = 1;
foreach( $paramNames as $name )
{
    echo "PARAM #" . $idx . " NAME: " . $name . "<br/>";
    $idx = $idx + 1;
}
 
Is this possible? How would I go about trying to parse this string to pull out the information about each parameter?

Any help would be great! Thanks!

Re: Parsing Procedure Parameters Possible?

Posted: Wed Oct 14, 2009 9:09 pm
by RegularKid
Ok, got it solved :)

A little messy, but it gets the job done:

Code: Select all

 
// Build up stored procedure call and do error checking for parameter values
$sql = "SELECT param_list FROM mysql.proc WHERE db = 'contraptionii' AND name = 'SaveChallenge';";
$res = $mysqli->query( $sql );
while( $row = $res->fetch_assoc() )
{
    echo $row[ "param_list" ];
 
    echo "<br/><br/>";
    echo "<pre>";
 
    if( preg_match_all( "/[a-zA-Z0-9()_ ]+(,|\))/", $row[ "param_list" ], $matches, PREG_PATTERN_ORDER ) )
    {
        echo "FOUND " . count( $matches[ 0 ] ) . " MATCHES:<br/>";
        $idx = 1;
        foreach( $matches[ 0 ] as $match )
        {
            echo "MATCH #" . $idx . ": " . $match . "<br/>";
            $idx = $idx + 1;
 
            // Pull out parameter types
            $regExp = "/^IN|OUT|INOUT$/";
            if( preg_match_all( $regExp, $match, $subMatches, PREG_PATTERN_ORDER ) )
            {
                foreach( $subMatches[ 0 ] as $subMatch )
                {
                    echo "    PARAMETER TYPE: " . $subMatch . "<br/>";
                    $paramType = $subMatch;
                    array_push( $paramTypes, $paramType );
                }
            }
            else
            {
                echo "    NO PARAMETER TYPE MATCH FOUND!<br/>";
            }
 
            // Pull out parameter names
            $regExp = "/" . $paramType . "[ ]+[a-zA-Z0-9_]+/";
            if( preg_match_all( $regExp, $match, $subMatches, PREG_PATTERN_ORDER ) )
            {
                foreach( $subMatches[ 0 ] as $subMatch )
                {
                    $regExp = "/" . $paramType . "[ ]+/";
                    $paramName = preg_replace( $regExp, "", $subMatch );
                    echo "    PARAMETER NAME: " . $paramName . "<br/>";
                    array_push( $paramNames, $paramName );
                }
            }
            else
            {
                echo "    NO PARAMETER NAME MATCH FOUND!<br/>";
            }
 
            // Pull out parameter variable types
            $regExp = "/" . $paramType . "[ ]+" . $paramName . "[ ]+" . "[a-zA-Z0-9_]+(\(|,)/";
            if( preg_match_all( $regExp, $match, $subMatches, PREG_PATTERN_ORDER ) )
            {
                foreach( $subMatches[ 0 ] as $subMatch )
                {
                    $regExp = "/" . $paramType . "[ ]+" . $paramName . "[ ]+/";
                    $tempVarType = preg_replace( $regExp, "", $subMatch );
                    $paramVariableType = preg_replace( "/\(|,/", "", $tempVarType );
                    echo "    PARAMETER VARIABLE TYPE: " . $paramVariableType . "<br/>";
                    array_push( $paramVariableTypes, $paramVariableType );
                }
            }
            else
            {
                echo "    NO PARAMETER TYPE MATCH FOUND!<br/>";
            }
 
            // Pull out parameter variable type lengths
            $regExp = "/[ ]*[0-9]+[ ]*/";
            if( preg_match_all( $regExp, $match, $subMatches, PREG_PATTERN_ORDER ) )
            {
                foreach( $subMatches[ 0 ] as $subMatch )
                {
                    $paramVariableTypeLength = preg_replace( "/[ ]*/", "", $subMatch );
                    echo "    PARAMETER VARIABLE TYPE LENGTH: " . $paramVariableTypeLength . "<br/>";
                    array_push( $paramVariableTypeLengths, $paramVariableTypeLength );
                }
            }
            else
            {
                echo "    NO PARAMETER TYPE LENGTH MATCH FOUND!<br/>";
            }
        }
    }
    else
    {
        echo "NO MATCHES FOUND!<br/><br/>";
    }
 
    echo "</pre>";
}