PDO Execute Returns Nothing

Question

Assume that I have a class:

class pdoc {
    private static $db_connect_pool;
    public static function openConnect() {
        try {
            $connect_options_arr = array(PDO::ATTR_PERSISTENT => true);

            self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''] = new PDO("".DB_DRIVER.":host=".DB_HOST.";db_name=".DB_NAME."", DB_USER, DB_PASS, $connect_options_arr);
        } catch (Exception $e) {
            print_r($e);
        }
    }

    public static function getConnection() {
        return self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''];
    }

    public static function qry($sql) {
        self::openConnect();
        $db_handle = self::getConnection();
        $st_handle = $db_handle->prepare($sql);
        return $st_handle->execute();           
    }
}

Then, to call the class:

$sql = "SELECT * FROM sometable";

if(pdoc::qry($sql))  echo "y";
else                 echo "n";

Why the code always return n? I have check the connection that has been connected successfully, but while I've tried to execute some query, it returns nothing. Any ideas? Thanks.

UPDATE (@Robbie's code)

class pdoc {
    private static $db_connect_pool;
    public static function openConnect() {
        try {
            $connect_options_arr = array(PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
            self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''] = new PDO("".DB_DRIVER.":host=".DB_HOST.";db_name=".DB_NAME."", DB_USER, DB_PASS, $connect_options_arr);
        } catch (Exception $e) {
            print_r($e);
        }
    }

    public static function getConnection() {
        return self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''];
    }

    public static function qry($sql) {
        self::openConnect();
        $db_handle = self::getConnection();
        try {
            $st_handle = $db_handle->prepare($sql);
            $retval = $st_handle->execute(); //--> Got error on this line       
        } catch (Exception $e) {
            Die('Need to handle this error. $e has all the details');
        }
        return $retval;  
    }
}

The error said: exception 'PDOException' with message 'SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected'.

ANSWER

Change:

... new PDO("".DB_DRIVER.":host=".DB_HOST.";db_name=".DB_NAME."", ...

into:

... new PDO("".DB_DRIVER.":host=".DB_HOST.";dbname=".DB_NAME."", ...

After catch the error message (from the updated code) and referring to this thread, I found that dbname part has written as dn_name on my code. So I change it into dbname and it works perfect! Thanks @Robbie for your code! :)

score 6 · Accepted Answer · answered Nov 16 '12 at 06:05

6

If it returns nothing, then it failed. You need to call the error functions (errorInfo, errorCode) to find out why.

But the tricky part is you don't know if the error is on the database or the statement, so the best trick is to use exception error reporting, wrap all the functions in a try catch, and the exception, when trapped, will belong to either the DB or the statement. Much easier to handle.

Your code would be:

class pdoc {
    private static $db_connect_pool;
    public static function openConnect() {
        try {
            $connect_options_arr = array(PDO::ATTR_PERSISTENT => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
            self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''] = new PDO("".DB_DRIVER.":host=".DB_HOST.";db_name=".DB_NAME."", DB_USER, DB_PASS, $connect_options_arr);
        } catch (Exception $e) {
            print_r($e);
        }
    }

    public static function getConnection() {
        return self::$db_connect_pool[''.DB_DRIVER.'_'.DB_NAME.''];
    }

    public static function qry($sql) {
        self::openConnect();
        $db_handle = self::getConnection();
        try {
            $st_handle = $db_handle->prepare($sql);
            $retval = $st_handle->execute();            
        } catch (Exception $e) {
            Die('Need to handle this error. $e has all the details');
        }
        return $retval;  
    }
}

answered Nov 16 '12 at 06:05

Robbie

17,605
4
35
72

Oh - also check for presence of the connection in `openConnect()` before you call a second time. If it exists (in your static class var) then there's no need to open a second time. – Robbie Nov 16 '12 at 06:10
Hi, thanks. It returns a value, when I tried to echo `$st_handle->rowCount()`, it does returns `0`. I am not quite sure if the query is wrong. So, when I used try catch, it also returns nothing.. – mrjimoy_05 Nov 16 '12 at 06:10
Did you add the `PDO::ATTR_ERRMODE` in the PDO attributes? Otherwise it won';t throw an exception. – Robbie Nov 16 '12 at 06:12
Thanks for the code. I've tried and got: `exception 'PDOException' with message 'SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected'` on `..->execute();..` line. Don't I have attached the `DB_Name` on it? – mrjimoy_05 Nov 16 '12 at 06:13
1

OK - you're in business, catchign the error. Check your variables etc (e.g. DB_Name, or DB_NAME - they are different). – Robbie Nov 16 '12 at 06:20
Hey thanks! after got the error message (using your code) and soon check this thread: `http://stackoverflow.com/questions/6300446/write-php-pdo-queries-as-dbname-tablename-as-opposed-to-tablename-why`, it because my `dbname` written as `db_name`. Thanks! :) – mrjimoy_05 Nov 16 '12 at 06:20
Also check you're not referening a table / database in your sql statement too. If you have 'select * FROM `database`.`table`` and the database doesn't exist, I think you get this error. – Robbie Nov 16 '12 at 06:21

score 3 · Answer 2 · answered Nov 16 '12 at 06:21

try in this way.. 

public static function qry($sql) {
try {
    $statement = $db_handle->prepare($sql);
    $retval = $statement->execute();
    if ($statement->rowCount() >= 1) { 
        //do something               
    }else {
        $errors = $statement->errorInfo();
        echo $errors[2] . ", " . $errors[1] . " ," . $errors[0];
    }
} catch (Exception $e) {
    echo $e->getMessage();
    }        
}

PDO Execute Returns Nothing

UPDATE (@Robbie's code)

ANSWER

2 Answers2