Work with BLOB field on ORACLE using Codeigniter

Question

I have a system where will store certain files in a BLOB type column in my ORACLE database, but I did not find anything about it in the documentation of Codeigniter itself of how to do this, how can I manipulate these files, where I need to feed this table and also display the file that is stored in this table?

Answer 1

Here is an example of how to update a BLOB column with a file received via upload. The secret is the concatenation of 0x with the string transformation of binary content to hexadecimal.

public function updateColunaBlob($id_cliente, $file_name, $file_data) {
    $this->db->set('FILE_NAME', $file_name, false);
    $this->db->set('FILE_DATA','0x'.bin2hex($file_data), false);
    $this->db->where('ID_CLIENTE', $id_cliente, false);
    $this->db->update('CLIENTES');
}

But, if by chance, the file is too big and there is no possibility to configure to more this limit, You can do the same upload piece by piece, without loss as to the content. Example for Mysql, which instead of 0x uses only X before the hexadecimal string. Here very explicitly; query is not being used and the column is upgraded from 512 Kb to 512 kB:

public function updateColunaBlob($id_cliente, $file_name, $file_data) {
    $handle = fopen($_FILES['nome_arquivo_upload']['tmp_name'], "rb");
    $contents = '';
    $i = 0;
    while (!feof($handle)) {
        $contents = fread($handle, 512000);

        if(!$i)
            $this->db->query("
                UPDATE
                    CLIENTES
                SET
                    FILE_NAME = ?,
                    FILE_DATA = X?
                WHERE
                    ID_CLIENTE = ?
            ", array($file_name, bin2hex($contents), $id_cliente));     
        else
            $this->db->query("
                UPDATE
                    CLIENTES
                SET
                    FILE_DATA = CONCAT(FILE_DATA, X?)
                WHERE
                    ID_CLIENTE = ?
            ", array(bin2hex($contents), $id_cliente));     
        $i++;
    }
    fclose($handle);
}