ZF-6473: Can not insert data in Oracle BLOB column

Description

I have oracle 10g as backend database. We need to store user uploaded files in a table with BLOB column. I attempted to do so by passing form variable and then with using php global $_FILES array. Both cases oracle throws error identifying passed value as string. I did not see any documentation if Zend supports Oracle BLOB datatype management. We are using zend core 2.5.0. Is this functionality supported?

Comments

We started using ZF at version 1.5 or so, and at that time it didn't seem to support BLOBs - I haven't looked recently to see if this has changed.

We have a My_Db_Adapter_Oracle which overrides Zend_Db_Adapter_Oracle and overrides the insert() function as follows (there is some extra stuff to track whether a transaction is already open):


    /**
     * @var boolean
     */
    protected $_transactionOpen = null;

    /**
     * Identifies whether the adapter has an open transaction
     *
     * @return boolean
     */
    protected function _hasOpenTransaction()
    {
        return $this->_transactionOpen;
    }

    /**
     * Leave autocommit mode and begin a transaction.
     *
     * Overloads Zend_Db_Adapter_Oracle::_beginTransaction to
     * track the open transaction
     *  
     * @return void
     */
    protected function _beginTransaction()
    {
        // Do the parent code
        parent::_beginTransaction();

        $this->_transactionOpen = true;       
    }

    /**
     * Commit a transaction and return to autocommit mode.
     *
     * Overloads Zend_Db_Adapter_Oracle::_commit to track
     * the open transaction
     * 
     * @return void
     * @throws Zend_Db_Adapter_Oracle_Exception
     */
    protected function _commit()
    {
        $this->_transactionOpen = false;

        // Do the parent code
        parent::_commit();        
    }

    /**
     * Roll back a transaction and return to autocommit mode.
     *
     * Overloads Zend_Db_Adapter_Oracle::_rollBack to track
     * the open transaction
     * 
     * @return void
     * @throws Zend_Db_Adapter_Oracle_Exception
     */
    protected function _rollBack()
    {
        $this->_transactionOpen = false;
        
        // Do the parent code
        parent::_rollBack();
    }

    /**
     * Inserts a table row with specified data.
     *
     * Overloads Zend_Db_Adapter_Oracle to support BLOB columns
     *
     * Oracle does not support anonymous ('?') binds.
     *
     * @param mixed $table The table to insert data into.
     * @param array $bind Column-value pairs.
     * @return int The number of affected rows.
     */
    public function insert($table, array $bind)
    {
        // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }

        // Get the table metadata
        $columns = $this->describeTable($table);
        
        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::insert($table, $bind);

        } else {            
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            // Extract and quote col names from the array keys
            $i = 0;
            $cols = array();
            $vals = array();
            foreach ($bind as $col => $val) {
                $cols[] = $this->quoteIdentifier($col, true);
                if (in_array($col, $lobs)) {
                    $vals[] = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                } elseif ($val instanceof Zend_Db_Expr) {
                    $vals[] = $val->__toString();
                    unset($bind[$col]);
                } else {
                    $vals[] = ':'.$col.$i;
                    unset($bind[$col]);
                    $bind[':'.$col.$i] = $val;
                }
                $i++;
            }
            
            // build the statement
            $sql = "INSERT INTO "
                 . $this->quoteIdentifier($table, true)
                 . ' (' . implode(', ', $cols) . ') '
                 . 'VALUES (' . implode(', ', $vals) . ') '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();
            
            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {            
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }
            
        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }
        
        // Return result
        return $result;

    }

It shouldn't be too hard for you to take this approach with your application.

It should also be possible to add this code into Zend_Db_Adapter_Oracle if any of the maintainers are interested.

I would be happy to help with it if one of the maintainers can tell me what needs to be done.

We use zend framework at work with a Oracle Database this feature would definitely make things easier to deal with BLOBs.

I would like to give my contribution. Advantage the insert function, I changed it to recognize the situation where hava SCHEMA and TABLE in $table var, and we can use this idea to the update option: \ \


/**
     * Inserts a table row with specified data.
     *
     * Overloads Zend_Db_Adapter_Oracle to support BLOB columns
     *
     * Oracle does not support anonymous ('?') binds.
     * Alteração para funcionar com campos BLOB e CLOB
     * @param mixed $table The table to insert data into.
     * @param array $bind Column-value pairs.
     * @return int The number of affected rows.
     */
    public function insert($table, array $bind)
    {
        // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }
        $tb = explode(".", $table);
        if(count($tb) == 2)
        {
            $tbName = $tb[1];
            $tbSchema = $tb[0];
        }else
            $tbName = $tb[0];
        // Get the table metadata
        $columns = $this->describeTable($tbName, @$tbSchema);

        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::insert($table, $bind);

        } else {
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            // Extract and quote col names from the array keys
            $i = 0;
            $cols = array();
            $vals = array();
            foreach ($bind as $col => $val) {
                $cols[] = $this->quoteIdentifier($col, true);
                if (in_array($col, $lobs)) {
                    $vals[] = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                } elseif ($val instanceof Zend_Db_Expr) {
                    $vals[] = $val->__toString();
                    unset($bind[$col]);
                } else {
                    $vals[] = ':'.$col.$i;
                    unset($bind[$col]);
                    $bind[':'.$col.$i] = $val;
                }
                $i++;
            }

            // build the statement
            $sql = "INSERT INTO "
                 . $this->quoteIdentifier($table, true)
                 . ' (' . implode(', ', $cols) . ') '
                 . 'VALUES (' . implode(', ', $vals) . ') '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();

            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }

        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }

        // Return result
        return $result;

    }
    /**
     * 
     * Updates table rows with specified data based on a WHERE clause.
     *
     * @param  mixed        $table The table to update.
     * @param  array        $bind  Column-value pairs.
     * @param  mixed        $where UPDATE WHERE clause(s).
     * @return int          The number of affected rows.
     */
    public function update($table, array $bind, $where = '')
    {
       // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }
        $tb = explode(".", $table);
        if(count($tb) == 2)
        {
            $tbName = $tb[1];
            $tbSchema = $tb[0];
        }else
            $tbName = $tb[0];
        // Get the table metadata
        $columns = $this->describeTable($tbName, @$tbSchema);

        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::update($table, $bind, $where);

        } else {
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            /**
             * Build "col = ?" pairs for the statement,
             * except for Zend_Db_Expr which is treated literally.
             */
            $set = array();
            $i = 0;
            foreach ($bind as $col => $val) {
                if (in_array($col, $lobs))
                {
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                    $val = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                }elseif ($val instanceof Zend_Db_Expr) {
                    $val = $val->__toString();
                    unset($bind[$col]);
                } else {
                    if ($this->supportsParameters('positional')) {
                        $val = '?';
                    } else {
                        if ($this->supportsParameters('named')) {
                            unset($bind[$col]);
                            $bind[':'.$col.$i] = $val;
                            $val = ':'.$col.$i;
                            $i++;
                        } else {
                            /** @see Zend_Db_Adapter_Exception */
                            require_once 'Zend/Db/Adapter/Exception.php';
                            throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
                        }
                    }
                }
                $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
            }

            $where = $this->_whereExpr($where);

            /**
             * Build the UPDATE statement
             */
            $sql = "UPDATE "
                 . $this->quoteIdentifier($table, true)
                 . ' SET ' . implode(', ', $set)
                 . (($where) ? " WHERE $where" : ''). ' '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();

            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }

        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }

        // Return result
        return $result;
    }

I don't like the solution using describeTable(), this seems like a serious performance hit for users that don't realize that insert() and update() need this function and therefore a cache should be wrapped around it.

Since you retrieve a OCI-LOB instance anways from a select on a BLOB or CLOB field, wouldn'it be much more easy just to allow this methods to handle instances of that type correctly?

For example:


$lob = oci_new_descriptor($db->getConnection(), OCI_DTYPE_LOB);
$lob->write($text);
$data = array(
    'name' => $name,
    'text' => $lob,
);
$db->insert('table', $data);

I just realized my solution isnt enough, the information about CLOB or BLOB is missing for the insert function, forget it :-)

Any updates on this issue?

A litle solution for Zend models. See.

Model Cars

class Custom_Row_Cars extends Zend_Db_Table_Row_Abstract {

public function init() { 

    foreach($this->_data as $key => $value ) { 
        if( is_resource($value) ) { 
           $this->_data[$key] = stream_get_contents($value);
        }
    }

} 

}

class Application_Model_DbTable_Cars extends Zend_Db_Table_Abstract {

protected $_name = 'CARS';
protected $_primary = 'ID';
protected $_rowClass = 'Custom_Row_Cars'; // Custom Row for data manipulation

public function getById($id) {
    return $this->fetchRow($this->select()->where('ID = ?', $id));
}

}

CLOB field are resource. In case are read with function stream_get_contents