Giving up on Yii Oracle Clobs

Started off yesterday with the intention of trying to implement functional Oracle Clobs in Yii similar to how I implemented it in CakePHP a couple years ago. As yesterday went on I kept busting through boundaries and was feeling great about my progress. But Then I hit the roadblock and I wasn’t able to continue. It’s not worth spending another couple days on when I have a deadline for a pilot by Fall. 4000 characters is enough for the pilot.

The result has been to alter the Yii COciSchema. Just changing the declaration of text to a varchar2(4000).

   public $columnTypes=array(
        'pk' => 'NUMBER(10) NOT NULL PRIMARY KEY',
        'string' => 'VARCHAR2(255)',
        //'text' => 'CLOB',
        'text' => 'VARCHAR2(4000)',
        'integer' => 'NUMBER(10)',
        'float' => 'NUMBER',
        'decimal' => 'NUMBER',
        'datetime' => 'TIMESTAMP',
        'timestamp' => 'TIMESTAMP',
        'time' => 'TIMESTAMP',
        'date' => 'DATE',
        'binary' => 'BLOB',
        'boolean' => 'NUMBER(1)',
		'money' => 'NUMBER(19,4)',
    );

The issue is Yii (PDO) doesn’t support LOBs well at all, so that declaration didn’t make any sense to begin with.

1 Comment

  1. apaoww says:

    Hi,

    Try using nvl function in oracle. For model that contain clob column. Using sql data provider instead.

    For example. Using data provider in yii2 in controller action index

    $count = Yii::$app->db->createCommand(‘
    SELECT COUNT(*) FROM POST
    ‘)->queryScalar();

    $dataProvider = new SqlDataProvider([
    ‘sql’ => “SELECT ID, NVL(TEXT_WITH_CLOB, ‘None’) FROM POST”,
    ‘key’ => ‘ID’,
    ‘totalCount’ => $count,
    ‘sort’ => [
    ‘attributes’ => [
    ‘ID’,
    ‘TEXT_WITH_CLOB’,
    ],
    ],
    ‘pagination’ => [
    ‘pageSize’ => 20,
    ],
    ]);

    return $this->render(‘index’, [
    ‘dataProvider’ => $dataProvider,
    ]);

Leave a comment