CUBRID support for Yii

Hi,

Recently I am developing PDO driver for CUBRID (An open source RDBMS highly optimized for Web Applications, CUBRID), and I choose Yii to test this PDO driver.

I implement the schema classes for CUBRID, which you can find in attachment files, wishing it can work with Yii blog demo. Unfortunately, although blog/index.php can be displayed correctly, when I click “Welcome!”, it displays errors. The error message is in attachments. I am trying to find the cause of the bug now, but there are so many codes to read. :(

I hope anyone who is familiar with Yii can help to find the cause. And in future I hope Yii will support CUBRID.

The PDO driver source codes for CUBRID is at CUBRID PDO.

Thanks a lot! :lol:

The error you got is not from Yii but from PDO - SQLSTATE[HY000]: General error: -2019 CLIENT, No more data fetched

This means there are no data any more. In blog demo, the first “Welcome!” don’t have any comments, so I think it is not PDO’s bug. :)

But this works with different PDO driver… so if you haven’t changed anything else… the problem is in the driver…

Maybe the problem is in the dirver, but I have no idea what the problem really is. I write test cases using PDO methods to test the driver, and it works. I also compared this pdo driver with mysql and other drivers, but I still can’t find any significant problems.

Maybe the problems is in schema files in attachements?

I also check the db and it is correct. The sql files is show below:


CREATE TABLE tbl_lookup

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,


"name" VARCHAR(128) NOT NULL,


code VARCHAR(128) NOT NULL,


"type" VARCHAR(128) NOT NULL,


"position" INTEGER NOT NULL

);

CREATE TABLE tbl_user

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,


username VARCHAR(128) NOT NULL,


password VARCHAR(128) NOT NULL,


salt VARCHAR(128) NOT NULL,


email VARCHAR(128) NOT NULL,


profile STRING

);

CREATE TABLE tbl_post

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,


title VARCHAR(128) NOT NULL,


content STRING NOT NULL,


tags STRING,


status INTEGER NOT NULL,


create_time INTEGER,


update_time INTEGER,


author_id INTEGER NOT NULL,


CONSTRAINT FK_post_author FOREIGN KEY (author_id)


	REFERENCES tbl_user (id) ON DELETE CASCADE ON UPDATE RESTRICT

);

CREATE TABLE tbl_comment

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,


content STRING NOT NULL,


status INTEGER NOT NULL,


create_time INTEGER,


author VARCHAR(128) NOT NULL,


email VARCHAR(128) NOT NULL,


url VARCHAR(128),


post_id INTEGER NOT NULL,


CONSTRAINT FK_comment_post FOREIGN KEY (post_id)


	REFERENCES tbl_post (id) ON DELETE CASCADE ON UPDATE RESTRICT

);

CREATE TABLE tbl_tag

(

id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,


"name" VARCHAR(128) NOT NULL,


frequency INTEGER DEFAULT 1

);

INSERT INTO tbl_lookup (“name”, “type”, “code”, “position”) VALUES (‘Draft’, ‘PostStatus’, ‘1’, 1);

INSERT INTO tbl_lookup (“name”, “type”, “code”, “position”) VALUES (‘Published’, ‘PostStatus’, ‘2’, 2);

INSERT INTO tbl_lookup (“name”, “type”, “code”, “position”) VALUES (‘Archived’, ‘PostStatus’, ‘3’, 3);

INSERT INTO tbl_lookup (“name”, “type”, “code”, “position”) VALUES (‘Pending Approval’, ‘CommentStatus’, ‘1’, 1);

INSERT INTO tbl_lookup (“name”, “type”, “code”, “position”) VALUES (‘Approved’, ‘CommentStatus’, ‘2’, 2);

INSERT INTO tbl_user (username, password, salt, email) VALUES (‘demo’,‘2e5c7db760a33498023813489cfadc0b’,‘28b206548469ce62182048fd9cf91760’,‘webmaster@example.com’);

INSERT INTO tbl_post (title, content, “status”, create_time, update_time, author_id, tags) VALUES (‘Welcome!’,'This blog system is developed using Yii. It is meant to demonstrate how to use Yii to build a complete real-world application. Complete source code may be found in the Yii releases.

Feel free to try this system by writing new posts and posting comments.’,2,1230952187,1230952187,1,‘yii, blog’);

INSERT INTO tbl_post (title, content, “status”, create_time, update_time, author_id, tags) VALUES (‘A Test Post’, ‘Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.’, 2,1230952187,1230952187,1,‘test’);

INSERT INTO tbl_comment (content, “status”, create_time, author, email, post_id) VALUES (‘This is a test comment.’, 2, 1230952187, ‘Tester’, ‘tester@example.com’, 2);

INSERT INTO tbl_tag (“name”) VALUES (‘yii’);

INSERT INTO tbl_tag (“name”) VALUES (‘blog’);

INSERT INTO tbl_tag (“name”) VALUES (‘test’);

And I found that problem is relate to the var “commentCount”. :mellow:

That is the relation name in Post model… line 67




...

'commentCount' => array(self::STAT, 'Comment', 'post_id', 'condition'=>'status='.Comment::STATUS_APPROVED),

...



Try enabling SQL requests log in main.php:


'db'=>array(

         …

        'enableProfiling'=>true,

        'enableParamLogging' => true,

),

'log'=>array(

    'class'=>'CLogRouter',

    'routes'=>array(

        …

        array(

            'class'=>'CProfileLogRoute',

            'levels'=>'profile',

            'enabled'=>true,

        ),

    ),

),

Thanks, I will try it. I hope after this weekend, I will solve this problem. :)

And I wonder why PDO don’t provide standard methods to get database schema info. It will be more easier to get different driver to work with framework like Yii.

I also tried yii-blogdemo-extended.

http://code.google.com/p/yii-blogdemo-extended/

And it can’t work too. It gives the same error info. :unsure:


Description

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: -2019 CLIENT, No more data fetched

Source File

/usr/local/apache2/htdocs/yii/framework/db/CDbCommand.php(387)

If I submit the same reply on this forum several times, how can I delete them? :blink:

Just drop a PM to Yii team member. We all do have moderation rights.

I have solved the problem mentioned above.

The problem is caused by CUBRID PDO driver. When all results has been fetched, the driver will return 0. But just before it returns, I wrongly set the statement error, and it is not an error.

But I find another problem. It seems that Yii blog demo only supports DBMS which use named placeholder, but CUBRID only supports positional placeholder. Am I right?

==============================================================

It gives the error below:


Description

CDbCommand failed to prepare the SQL statement: SQLSTATE[HY000]: General error: -493 DBMS, Syntax: syntax error, unexpected TYPE

Source File

/usr/local/apache2/htdocs/yii/framework/db/CDbCommand.php(122)


00110:     {

00111:         if($this->_statement==null)

00112:         {

00113:             try

00114:             {

00115:                 $this->_statement=$this->getConnection()->getPdoInstance()->prepare($this->getText());

00116:                 $this->_params=array();

00117:             }

00118:             catch(Exception $e)

00119:             {

00120:                 Yii::log('Error in preparing SQL: '.$this->getText(),CLogger::LEVEL_ERROR,'system.db.CDbCommand');

00121:                 $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;

00122:                 throw new CDbException(Yii::t('yii','CDbCommand failed to prepare the SQL statement: {error}',

00123:                     array('{error}'=>$e->getMessage())),(int)$e->getCode(),$errorInfo);

00124:             }

00125:         }

00126:     }

00127: 

00128:     /**

00129:      * Cancels the execution of the SQL statement.

00130:      */

00131:     public function cancel()

00132:     {

00133:         $this->_statement=null;

00134:     }

And it is caused by sql statement "SELECT * FROM "tbl_lookup" "t" WHERE type=:type ORDER BY position"

In blog demo:

protected/models/Lookup.php


/**

  * Loads the lookup items for the specified type from the database.

  * @param string the item type

  */

  private static function loadItems($type)

  {

          self::$_items[$type]=array();

          $models=self::model()->findAll(array(

                  'condition'=>'type=:type',

                  'params'=>array(':type'=>$type),

                  'order'=>'position',

          ));

          foreach($models as $model)

                  self::$_items[$type][$model->code]=$model->name;

  }


How can I change it to support positional placeholder? :unsure:

Named placeholders are used extensively. Looks like you have to pre-process them somehow and convert to positional ones. Also it will be great to report this issue to CUBRID developers.

OK, thanks. :)

Any news on CUBRID database support in Yii or CUBRID supporting named placeholders? Just in case I have requested this feature to be implemented in CUBRID (bit.ly / h3LHiE).

thorny-road, is there anything I can help you with developing the CUBRID DB layer for Yii?

Hi kadishmal,

I have just come back to work from a marriage leave. The PDO driver for CUBRID 8.3.1 has been developed. After doing code reviews and tests on it, I will release it and work on CUBRID DB layer for Yii.

The 8.3.1 CUBRID PDO driver can’t get the foreign keys of some table, and this issue has been fixed in 8.3.2 version. And it is the right chance to develop CUBRID DB layer for Yii.

And I believe that CUBRID should support named placeholders.