CDbCommand->queryAll() problem

Y11 · August 22, 2009, 5:13pm

DB-Structure:


CREATE TABLE IF NOT EXISTS `language` (

  `id` tinyint(1) unsigned NOT NULL auto_increment,

  `bit` tinyint(128) unsigned NOT NULL,

  `identifier` varchar(2) NOT NULL,

  `supportedCountryCodes` text NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


INSERT INTO `language` (`id`, `bit`, `identifier`, `supportedCountryCodes`) VALUES

(1, 1, 'de', 'a:3:{i:0;s:2:"at";i:1;s:2:"ch";i:2;s:2:"de";}'),

(2, 2, 'en', 'a:1:{i:0;s:1:"*";}');

DB-Query:


$languages = Yii::app()->db->createCommand("SELECT `id`, `bit`, `identifier`, `supportedCountryCodes` FROM `language`")->queryAll();

var_dump($languages) returns:


array(2) {

  [0]=>

  array(4) {

    ["id"]=>

    string(1) "1"

    ["bit"]=>

    string(1) "1"

    ["identifier"]=>

    string(2) "de"

    ["supportedCountryCodes"]=>

    string(45) "a:3:{i:0;s:2:"at";i:1;s:2:"ch";i:2;s:2:"de";}"

  }

  [1]=>

  array(4) {

    ["id"]=>

    string(1) "2"

    ["bit"]=>

    string(1) "2"

    ["identifier"]=>

    string(2) "en"

    ["supportedCountryCodes"]=>

    string(18) "a:1:{i:0;s:1:"*";}"

  }

}

Why is "id" and "bit" returned as a string?

pestaa · August 22, 2009, 5:18pm

It is probably due to PHP auto-converting. Why can’t you use this in this form?

Y11 · August 22, 2009, 5:23pm

Because I always use the === operator. Now I would have to use “=== b[/b]$array[0]” and that sucks. Is there a way to turn of that php-behavior? Why is it on anyway, doesn’t make any sense to me.

pestaa · August 22, 2009, 5:37pm

It is truly controversial whether it is a bless or a curse.

Using === operator does not make sense to me in such scenarios (it is useful when comparing empty values).

Since database is restrictive enough when it comes to variable types (you cannot store string in a tinyint field), you don’t have to make sure about its type PHP-side.

And I don’t think you can turn it off.

Y11 · August 22, 2009, 6:05pm

Well I just don’t like automatic type-conversions. It’s a habit and I guess === is also faster. If I want to compare 2 values, I expect them to be of the same type.

Ok thank you. Well Yii is my first framework and I really can’t remember running into such a problem before. Maybe it’s set by Yii or a wrongly compiled php? If anyone knows please tell me. I will google now

pestaa · August 22, 2009, 6:24pm

I could hardly believe it, so I checked http://www.phpbench.com/ and you’re right. (Edit: see control structures section if you are interested.) Using == is nearly 2 times slower than ===. (The overhead is totally insignificant in real development though.)

== array[0] is much easier to read and maintain than === (int) array[0]. This should not be a problem for you.

If you find anything useful in your search results, please share.

Y11 · August 22, 2009, 6:49pm

Well your thought was obviously correct, I can’t turn it off. It seems to be a standard behavior (didn’t found anything @ google). I just really wonder that I didn’t noticed that before - strange. I will now use === (int) array[0] anyway Like I said it’s a habit. Also I do comparsions like this to do less mistakes: if (null !== $test) { … My own “coding standards” basicly.

But thanks for your help.

qiang · August 23, 2009, 12:08am

This is done by PDO, not Yii. In fact, PDO returns all results in string. The main reason behind is to keep precision (e.g. decimal numbers could lose precision when transferring from DB to PHP in native types).

Y11 · August 23, 2009, 9:57am

Thank you, that’s it! I found out I have to set ATTR_STRINGIFY_FETCHES to false. I tried the following but it didn’t worked. You may have a solution?


Yii::app()->db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

EDIT:

On this page it says the new native mysql driver for php (>= 5.3.0) returns native data-types. I’ve just compiled php 5.3.0 with the mysqlnd support.

Now I get this error:


PHP Error

Description


PDO::__construct() [<a href='pdo.--construct'>pdo.--construct</a>]: [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock)

Source File


/var/www/htdocs/yii/framework/db/CDbConnection.php(283)


00271:      * @since 1.0.4

00272:      */

00273:     protected function createPdoInstance()

00274:     {

00275:         $pdoClass='PDO';

00276:         if(($pos=strpos($this->connectionString,':'))!==false)

00277:         {

00278:             $driver=strtolower(substr($this->connectionString,0,$pos));

00279:             if($driver==='mssql' || $driver==='dblib')

00280:                 $pdoClass='CMssqlPdoAdapter';

00281:         }

00282:         return new $pdoClass($this->connectionString,$this->username,

00283: $this->password,$this->_attributes);

00284:     }

00285: 

00286:     /**

00287:      * Initializes the open db connection.

00288:      * This method is invoked right after the db connection is established.

00289:      * The default implementation is to set the charset for MySQL and PostgreSQL database connections.

00290:      * @param PDO the PDO instance

00291:      */

00292:     protected function initConnection($pdo)

00293:     {

00294:         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

00295:         if($this->emulatePrepare && constant('PDO::ATTR_EMULATE_PREPARES'))


Stack Trace


#0 /var/www/htdocs/yii/framework/db/CDbConnection.php(283): PDO->__construct()

#1 /var/www/htdocs/yii/framework/db/CDbConnection.php(242): CDbConnection->createPdoInstance()

#2 /var/www/htdocs/yii/framework/db/CDbConnection.php(223): CDbConnection->open()

#3 /var/www/htdocs/yii/framework/db/CDbConnection.php(202): CDbConnection->setActive()

#4 /var/www/htdocs/yii/framework/base/CModule.php(353): CDbConnection->init()

#5 /var/www/htdocs/yii/framework/base/CModule.php(91): CWebApplication->getComponent()

#6 /var/www/htdocs/yii/application/shared/components/classes/I18n.php(31): CWebApplication->__get()

#7 /var/www/htdocs/yii/framework/base/CModule.php(353): I18n->init()

#8 /var/www/htdocs/yii/framework/base/CModule.php(441): CWebApplication->getComponent()

#9 /var/www/htdocs/yii/framework/base/CApplication.php(119): CWebApplication->preloadComponents()

#10 /var/www/htdocs/yii/framework/YiiBase.php(81): CWebApplication->__construct()

#11 /var/www/htdocs/yii/index.php(27): createWebApplication()

I guess Yii doesn’t support mysqlnd?

EDIT #2:

Alright I had to modify the mysql config files (my.cnf, debian.cnf) for the correct socket. Now it works but queryAll() still returns integers as strings. Also I tried again to set ATTR_STRINGIFY_FETCHES, but it doesn’t work.

Anyone have a clue about this?

EDIT #3

I’ve now got it using: Yii::app()->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

Output is now like expected. qiang, do you know if I run into any problems when disabling emulated prepares? Because I noticed it is set in CDbConnection.php

pestaa · August 23, 2009, 12:07pm

You may already have seen this: