MDB2, MySQL and Stored Procedures

[hannson]

Hi,

I'm having some issues with MDB2 and stored procedures. What I'm creating in abstract is a really simple CMS script that loads smarty templates and content from database using stored procedures extensively to battle SQL injection attacks and at the same time separating the Model, View and Controller further.

How ever I ran into a problem I can't solve. I get a MDB2 Error. The Native SQL error is: "Commands out of sync; you can't run this command now" in short I don't read the whole resultset before I execute the next procedure.

Both procedures work from MySQL command line.

Can someone with more experience with MDB2 and stored procedures point me in the right direction?

Is MDB2 maybe not the best library to use with stored procedures?

index.php

<?php

require_once("dbconnect.php");

/**
 * Sækir síðu eftir ID dálkinum
 */
function getpagebyid($pid, &$con)
{    
    // Reyna að fá síðu eftir ID
    //$page = $con->query("call getpagebyid(".$pid.")");
    $query =& $con->executeStoredProc('getpagebyid', array($pid));
        
    if(PEAR::isError($query))
    {
        die ($query->getMessage().' - '.$query->getUserinfo());
    }

    // skilar amk tómum array
    if ($query->numRows() == 0)
    {
        $page = Array();
    }
    else
    {
        $page = $query->fetchAll();
        $query->nextResult();
        $query->free();
    }
    return $page;
}


/** 
 * Sækir síðu eftir "alias" dálkinum 
 */
function getpagebyalias($alias, &$con)
{
    $query =& $con->executeStoredProc('getpagebyalias', array($alias));
    if(PEAR::isError($query))
    {
        die ($query->getMessage().' - '.$query->getUserinfo());
    }
    
    // skilar amk tómum array
    if ($query->numRows() == 0)
    {
        $page = Array();
    }
    else
    {
        $page = $query->fetchAll();
        $query->nextResult();
        $query->free();
    }
    return $page;
    
}

if(isset($_GET['page']))
{
    if(is_numeric($_GET['page']))
    {
        $page = getpagebyid($_GET['page'], $con);
    }
    
    if(empty($page))
    {
        $page = getpagebyalias($_GET['page'], $con);
    }
    
    if(empty($page))
    {
        die("404"); // 404
    }
    
}
else
{
// index page
}

$smarty->assign("title", $page[0][0]);
$smarty->assign("content", $page[0][1]);
$smarty->display("db:".$page[0][2]);

dbconnect.php

<?php

require_once("MDB2.php");
require_once("libs/Smarty.Class.php");

//error_reporting(E_ALL ^ E_NOTICE | E_WARNING);

$dsn = array ( 'phptype' => 'mysql',
 'hostspec' => 'localhost:3306',
 'username' => 'root',
 'password' => '',
 'database' => 'simplecms',
 'client_flags' => 65536
 );

$con =& MDB2::connect($dsn);
unset($dsn);

$con->loadModule('Function');
global $con;

$smarty = new Smarty();


if(PEAR::isError($con))
{
    die("Villa við tengingu gagnagrunns : " . $con->getMessage());
}


/**
 */
function db_get_template($tpl_name, &$tpl_source, &$smarty_obj)
{
    global $con;

    $template = $con->executeStoredProc("gettemplate", array($tpl_name));
    if(PEAR::isError($template))
    {
        die ($template->getMessage().' - '.$template->getUserinfo());
    }
    $templateArray = $template->fetchAll();
    $template->nextResult(); 
    
    $tpl_source = $templateArray['template'];
    return true;
}

function db_get_timestamp($tpl_name, &$tpl_timestamp, &$smarty_obj)
{

    global $con;
    $template = $con->executeStoredProc("gettemplatetimestamp", array($tpl_name));

    if(PEAR::isError($template))
    {
        echo "db_get_timestamp";
        die ($template->getMessage().' - '.$template->getUserinfo());
    }
    
    $timestampArray = $template->fetchAll();
    $tpl_timestamp = $timestampArray['updated'];
    $template->nextResult();
    
    return true;
}


function db_get_secure($tpl_name, &$smarty_obj)
{
    return true;
}

// Not used
function db_get_trusted($tpl_name, &$smarty_obj){}

// Register the db driver for smarty
$smarty->register_resource("db", array("db_get_template",
                                       "db_get_timestamp",
                                       "db_get_secure",
                                       "db_get_trusted"));


?>

[hannson]

I've tried calling $resultset->nextResult(); and $resultset->free(); after every database call but with no luck...

Does no one have an idea of what's going wrong here?
How do I clean up the SQL client for the next query?

Update:

I tried switching from stored procedures to "normal" SELECT queries but still get the same error. I've tried to google "MDB2 Commands out of sync; you can't run this command now" but ironically this forum post is the first result.

[hannson]

Does anyone have an idea of what's going wrong here?

[hannson]

Update:

I still haven't debugged the issue. The data is returned from the first procedure but fails in the second. If I replace the $con->executeStoredProc() with an identical $con->query() it works perfectly.

I've double checked and both procedures work flawlessly from command line.

Does nobody here use stored procedures?

[revdpeter3rd]

Hi Hannson,

Did you ever manage to resolve this issue? I am experiencing the exact same problem that you have detailed here. Runs perfectly from the client/command line, but bombs out from php.

Thanks