MDB2 Error: unknown error

PHP
1

Hi,

I get this weird error from PEAR MDB2:

MDB2 Error: unknown error

When I tried changing the password field in the factory to an incorrect password I got “MDB2 Error: Connection refused” or something like that…

I get the unknown error when I request “localhost/?page=1”.

I’ve got 2 stored procedures that tested OK from command line.

Any ideas?

Here’s my code below:


<?php

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


function getpagebyid($pid, &$con)
{	

	// Reyna að fá síðu eftir ID
	$page = $con->query("call getpagebyid(".$pid.")");
	
	if(PEAR::isError($page))
	{
		die('Villa við tengingu gagnagrunns : ' . $page->getMessage());
		
	}

	
	return $page;
}

function getpagebyalias($alias, &$con)
{
	$page = $con->query("call getpagebyalias(".$alias.")");
	if(PEAR::isError($page))
	{
		die('Villa við tengingu gagnagrunns : '. $page->getMessage());
	}
	
	
	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))
	{
		// 404
	}
	
}
else
{
// index page
}

$smarty = new Smarty();

?>

And db.php:


<?php

require_once("MDB2.php");

$url = "mysql://root:*****@localhost/simplecms";

$con = MDB2::factory($url);

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

}

?>
2

If there is nothing wrong with your stored proc, then maybe its because you seem to be quoting an integer? (not sure how db2 handles that).

$page = $con->query(“call getpagebyid(”.$pid.“)”);

try

$page = $con->query(“call getpagebyid($pid)”);

Try and get some feedback from your $con, maybe var_dump( $con ) in getpagebyid() or look in your php error logs or your db2 error log files.

3

I don’t think I’m quoting an integer,

“call getpagebyid(”. $pid .“)” translates to “call getpageidby(1)”

When I hardcode “call getpagebyid(1)” it gives me the same error.

When I hardcode “SELECT * FROM pages WHERE 1=1” it gives me the same error.

As for the logs… I can’t find them. I’m using xampp on Windows… do I specifically need to enable logs…?

4

[feeble_excuse]
Sorry about that, I have not looked at any code for 6 days.
[/feeble_excuse]

To then further muddy the water for you, you allocate $page to the query, but from what I read here you should then do the equivalent of:


$row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)

Which sounds as if it is supposed to behave much like PDO, but in PDO you do fetch or fetchAll

var_dump( $page ); and that might help you.

5

From the link you provided:


<?php
// load library as above.
// connect to db as above. ($con = ... )
// check $con validity as per above example.

$sql = "SELECT * FROM the_table";
$resultset = $con->query($sql);
if(PEAR::isError($resultset)) {
    [B]die('Failed to issue query, error message : ' . $resultset->getMessage());[/B]
}

while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    foreach($row as $field => $value) {
        echo "$field / $value \
";
    }
}

My code is almost a direct translation of the code on top. (I googled before I posted :)). How ever, my code does die (bolded) before anything is done with $page/$resultset. I already var_dumped ($page) after calling the query but I don’t see the results. I also var_dumped ($con) and located the correct database information as well as the running version of mysql so to the best of my knowledge the connection is valid.

Also, I was thinking I could return the results and then iterate over them in the code (not the function):


<?php
 $page = getpagebyid(1, $con);
 $row = $page->fetchRow(MDB2_FETCHMODE_ASSOC);
?>

I won’t have to iterate really because the database forbids more than one result by using a primary key (pageID) and an unique index (alias)

6

More detailed error report (die()) using the code below (for future reference):


function getpagebyid($pid, &$con)
{	



	$page =& $con->query('call getpagebyid(1)');
	
	if(PEAR::isError($page))
	{
		die ($page->getMessage().' - '.$page->getUserinfo());
	}
 
       // Worst case scenario: returns an empty array.
	return $page;
}

MDB2 Error: unknown error - _doQuery: [Error message: Could not execute statement] [Last executed query: call getpagebyalias(1)] [Native code: 1312] [Native message: PROCEDURE simplecms.getpagebyalias can’t return a result set in the given context]

Seems that I need to feed MDB2 with some client flags according to comments on http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

'client_flags' => 65536

Update:
Apperantly I should do $con->loadModule(‘Function’); to enable stored procedures in MDB2 and then execute via: "$page = $con->executeStoredProc(“getpagebyid”, array($pid));

it should be free from sql injection attacks.

7

For anyone who might stumble upon this thread, here’s the solution.

Summary:

  1. Put ‘client_flags’ => 65536 into the DSN string when creating a connection
  2. For SQL injection free queries enable stored procedure module with $con->loadModule(‘Function’);
  3. Switch $result = $con->query("procedure()) to $con->executeStoredProc(“storedprocedure”, Array(“parameter1”, “parameter2”)
  4. voila :slight_smile:

The most helpful was the snippet for better error reporting:


    if(PEAR::isError($page))
    {
        die ($page->getMessage().' - '.$page->getUserinfo());
    }

Working code using stored procedures


<?php

require_once("MDB2.php");

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

$con =& MDB2::connect($dsn);
$con->loadModule('Function');

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

and index.php:


<?php

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

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

	// skilar amk t&#243;mum array
	return $page;
}


/** 
 * S&#230;kir s&#237;&#240;u eftir "alias" d&#225;lkinum 
 */
function getpagebyalias($alias, &$con)
{
	$page =& $con->executeStoredProc('getpagebyalias', array($alias));
	if(PEAR::isError($page))
	{
		die ($page->getMessage().' - '.$page->getUserinfo());
	}
	
	// skilar amk t&#243;mum array
	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))
	{
		// 404
	}
	
}
else
{
// index page
}


$page = $page->fetchRow(MDB2_FETCHMODE_ASSOC);


$smarty = new Smarty();
//$smarty->assign("title", $page['title']);
//$smarty->assign("content", $page['content']);
$smarty->display($page['template']);

?>

Sorry about the icelandic comments. :stuck_out_tongue:

Thanks for your help!