call mysql stored procedure with mysqli

752 views
Skip to first unread message

vlauciani

unread,
Jun 18, 2014, 5:36:26 AM6/18/14
to xata...@googlegroups.com
Hi all

Using MySQL Stored Procedure, I'm developing a simple section into "delegate class" like this:
    function section__jsonMessage(&$record) {
        $app =& Dataface_Application::getInstance();
        $db = $app->db();
        
        $id = $record->val('id');
        
        $q = "call sp_get_message($id);";
        $result = xf_db_query($q,$app->db());
    
        if (!$result) {
            return array();
        }
        
        $num_rows=xf_db_num_rows($result);
        if ($num_rows == 0) {
            return array();
        } else {
            $content = '';
            while ($row = xf_db_fetch_assoc($result)) {
                $content .= $row["message"];
            }      
        }

        return array(
            'content' => $content,
            'class' => 'main',
            'label' => 'Message',
            'order' => 1
        );
    }    
        



but running the page, I receive this strange message:
Fatal error: Uncaught exception 'Exception' with message 'Error performing mysql query to get column information from table 'vw_all_fcp_info'. The mysql error returned was : 'Commands out of sync; you can't run this command now' in /home/testuser/testsite/xataface-master/Dataface/Table.php:570 Stack trace: #0 /home/testuser/testsite/xataface-master/Dataface/Table.php(492): Dataface_Table->Dataface_Table('vw_all_fcp_info', Object(mysqli), false) #1 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(457): Dataface_Table::loadTable('vw_all_fcp_info', Object(mysqli)) #2 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(413): Dataface_Relationship->_normalizeColumns() #3 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(111): Dataface_Relationship->_init(Array) #4 /home/lauciani/svnwork/cat/trunk/CAT/modules/tsunamifa in /home/testuser/testsite/xataface-master/Dataface/Table.php on line 570

What does It mean?

The "relationships.ini" file contains only a single relationship with View:
[vw_all_fcp_info]
__sql__ = "SELECT *
            FROM vw_all_fcp_info 
            WHERE fcp_value__fk_message='$id'"

Thank you,
Valentino

Steve Hannah

unread,
Jun 18, 2014, 10:05:00 AM6/18/14
to vlauciani, xata...@googlegroups.com
This thread suggests a couple of solutions.

The simplest solution to try seems to be removing the semi-colon from the end of the query.  E.g.
change
  $q = "call sp_get_message($id);";
to
  $q = "call sp_get_message($id)";

If this actually works, I'll be surprised.  Please let me know how it goes.  The mysqli support is brand new and not well tested yet so there might be some gotchas yet to be uncovered.

Steve

--
Steve Hannah
Web Lite Solutions Corp.

vlauciani

unread,
Jun 18, 2014, 12:06:19 PM6/18/14
to xata...@googlegroups.com, vlau...@gmail.com
Hi Steve

The simplest solution doesn't work; removing the semi-colon from the end, the result is the same.

A friend of mine had the same problem and he has solved the problem creating, into the file conf/ApplicationDelegate.php, a method like this below that  seems to work.
I'll implement this solution tomorrow and I'll let you know.

function call_stored_procedure(& $CALL){
    $ret['code']=1; //default return value is not error 
    $ret['error']=$CALL.' :';//default return value is the call line

    $error=0;

    $app =& Dataface_Application::getInstance();
    $conf = $app->conf();

    $mysqli = new mysqli($conf['_database']['host'], $conf['_database']['user'], $conf['_database']['password'] , $conf['_database']['name']);

    /* check connection */
    if (mysqli_connect_errno()) {
      $message="Connect failed: %s\n".mysqli_connect_error();
      $ret['code']=$error;
      $ret['error'].=$message;
    } else {
      $mysqli->autocommit(FALSE);

      if ($mysqli->multi_query($CALL)) {
        do {
            /* store first result set */
            if ($result = $mysqli->store_result()) {
              while ($row = $result->fetch_row()) {
                if (substr( $row[0], 0, strlen('ERROR') ) == 'ERROR') {
                  if ( ! (substr( $row[0], 0, strlen('ERROR: channel already linked to address') ) == 'ERROR: channel already linked to address') ) {
                    $ret['code']=$error;
                    $ret['error'].=$row[0];
                  }
                }
              }
              $result->free();
            }
        } while ($mysqli->next_result());
      }
      if ($mysqli->errno) {
        $message=$mysqli->error;
        $ret['code']=$error;
        $ret['error'].=$message;
        $mysqli->rollback();
      }
      else {
        $mysqli->commit();
      }
    $mysqli->close();
  }
  return $ret;
}

Valentino

Steve Hannah

unread,
Jun 18, 2014, 1:48:21 PM6/18/14
to vlauciani, xata...@googlegroups.com
This seems like a rather heavy solution since it requires opening a new SQL connection.  This will come with some performance implications and possibly other implications as well...

I do notice that, in his solution he is doing something that was mentioned in the thread I posted as another possible solution to the problem.  He is using $mysqli->store_result(), and while($mysqli->next_result()) for looping through the dataset.  This is necessary for reasons explained in the stackoverflow thread I posted.

With the xf_db_xxx() functions you don't have these, but you can use the procedural versions of these.  

Steve

Valentino Lauciani

unread,
Jun 18, 2014, 4:48:18 PM6/18/14
to Steve Hannah, xata...@googlegroups.com
On 18 Jun 2014, at 19:48, Steve Hannah <st...@weblite.ca> wrote:

This seems like a rather heavy solution since it requires opening a new SQL connection.  This will come with some performance implications and possibly other implications as well…
ok, It is true.

[ CUT ]
With the xf_db_xxx() functions you don't have these, but you can use the procedural versions of these.  
What do you mean with "you can use the procedural versions"? I can use It instead of creating method into conf/ApplicationDelegate.php to use Stored Procedures?
Do you think that with the xf_db_xxx() functions there will be possible to use CALL MySQL Stored Procedures in the future?

Valentino

Steve Hannah

unread,
Jun 18, 2014, 4:53:50 PM6/18/14
to Valentino Lauciani, xata...@googlegroups.com
On Wed, Jun 18, 2014 at 1:48 PM, Valentino Lauciani <vlau...@gmail.com> wrote:
On 18 Jun 2014, at 19:48, Steve Hannah <st...@weblite.ca> wrote:

This seems like a rather heavy solution since it requires opening a new SQL connection.  This will come with some performance implications and possibly other implications as well…
ok, It is true.

[ CUT ]
With the xf_db_xxx() functions you don't have these, but you can use the procedural versions of these.  
What do you mean with "you can use the procedural versions"?

You can use mysqli_store_result($app->db())

And  mysqli_next_result($app->db())
 
I can use It instead of creating method into conf/ApplicationDelegate.php to use Stored Procedures?
Yes.   The reason his example works is because he is using store_result and next_result to manage the buffer.  The secondary SQL connection is unnecessary.
 
Do you think that with the xf_db_xxx() functions there will be possible to use CALL MySQL Stored Procedures in the future?
It is already possible.

Steve

vlauciani

unread,
Jun 19, 2014, 9:12:29 AM6/19/14
to xata...@googlegroups.com, vlau...@gmail.com
Hi Steve

I tried with "procedural versions" but I mistake something.

Changing my section into "delegate class" with this:
function section__jsonMessage(&$record) {
        $app =& Dataface_Application::getInstance();
        $db = $app->db();

        $q = "call sp_get_message(27);";

        $result = mysqli_multi_query($db,$q);
}

I receive the same error:
Fatal error: Uncaught exception 'Exception' with message 'Error performing mysql query to get column information from table 'vw_all_fcp_info'. The mysql error returned was : 'Commands out of sync; you can't run this command now' in /home/testuser/testsite/xataface-master/Dataface/Table.php:570 Stack trace: #0 /home/testuser/testsite/xataface-master/Dataface/Table.php(492): Dataface_Table->Dataface_Table('vw_all_fcp_info', Object(mysqli), false) #1 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(457): Dataface_Table::loadTable('vw_all_fcp_info', Object(mysqli)) #2 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(413): Dataface_Relationship->_normalizeColumns() #3 /home/testuser/testsite/xataface-master/Dataface/Relationship.php(111): Dataface_Relationship->_init(Array) #4 /home/lauciani/svnwork/cat/trunk/CAT/modules/tsunamifa in /home/testuser/testsite/xataface-master/Dataface/Table.php on line 570

But creating a new PHP MySQL "link" like this, it works:
function section__jsonMessage(&$record) {
        $app =& Dataface_Application::getInstance();
        $conf = $app->conf();

        $q = "call sp_get_message(27);";

        $mysqli = xf_db_connect($conf['_database']['host'], $conf['_database']['user'], $conf['_database']['password'], $conf['_database']['name']);
        $result = mysqli_multi_query($mysqli,$q);
}

Could you show the correct way?

Thank you,
Valentino

Steve Hannah

unread,
Jun 19, 2014, 12:24:15 PM6/19/14
to vlauciani, xata...@googlegroups.com
How are you fetching the results of your query.  This is the crux of it.  You need to use mysqli_store_result() and mysql_next_result() because mysqli uses deferred execution.

Steve
Message has been deleted

vlauciani

unread,
Jun 20, 2014, 9:48:52 AM6/20/14
to xata...@googlegroups.com, vlau...@gmail.com
Hi Steve

Reading the post:

I solved the problem.

This is my "delegate class":
    function section__jsonMessage(&$record) {
        $app =& Dataface_Application::getInstance();
        $db = $app->db();
        $conf = $app->conf();
        $id = $record->val('id');

        $q = "call sp_get_json_message_from_msgid($id);";

        if(mysqli_multi_query($db,$q)){      
            do{
                $result = mysqli_store_result($db);
                
                if($result){
                    $row = xf_db_fetch_assoc($result);
                    $json = json_decode($row['json_message']);
                    $content = "<pre>".json_encode($json, JSON_PRETTY_PRINT)."</pre>";
                    xf_db_free_result($result);
                }
            } while(mysqli_more_results($db) && mysqli_next_result($db));
        } else {
            return array();        
        }

        return array(
            'content' => $content,
            'class' => 'main',
            'label' => 'Json Message',
            'order' => 10
        );
    } 

Thank you for your support,
Valentino

Steve Hannah

unread,
Jun 20, 2014, 4:01:08 PM6/20/14
to vlauciani, xata...@googlegroups.com
Glad to hear you got it working.  Thanks for posting the solution.

Steve
Reply all
Reply to author
Forward
0 new messages