SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MDB2, MySQL and Stored Procedures

    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 Code:
    <?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 Code:
    <?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"));


    ?>
    Last edited by hannson; Jan 4, 2009 at 17:14.

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by hannson; Jan 4, 2009 at 21:49.

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does anyone have an idea of what's going wrong here?

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Member revdpeter3rd's Avatar
    Join Date
    Feb 2007
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •