Hari ini saya membaca artikel yang sangat menarik di asktom tentang keuntungan dan kerugian menyimpan file ke dalam database. Tom menjawab bahwa dia sampai saat ini tidak menemukan keuntungan menyimpan data dari file diluar database. Tom memaparkan keuntungan-keuntungan yang didapatkan kalau data file disimpan ke dalam database, diantaranya:
- data akan dikelola dengan profesional oleh DBMS
- data akan ikut di-backup
- dapat di-rollback / di-recover bersama data-data lainnya
- data ikut terlindungi / aman
- scalable
- locking
- konsistensi data
Saya pun mencoba membuat halaman sederhana untuk upload file yang akan disimpan ke database, dan menampilkan file yang tersimpan di database. Saya menggunakan framework codeigniter dan untuk akses database saya menggunakan adodb. Untuk DBMS sendiri saya menggunakan OracleXE.
Berikut ini potongan struktur tabel, dan potongan kode yang saya buat. Silahkan dipelajari dan dikembangkan lebih lanjut.
Tabel
CREATE TABLE "T_BLOB"
("ID" NUMBER NOT NULL ENABLE,
"TITLE" VARCHAR2(255 CHAR),
"FILE_DATA" BLOB,
"CONTENT_TYPE" VARCHAR2(100 CHAR),
CONSTRAINT "T_BLOB_PK" PRIMARY KEY ("ID"));
Sequence
CREATE SEQUENCE "SEQ_BLOB" INCREMENT BY 1 START WITH 1;
Package
CREATE OR REPLACE PACKAGE "P_PUBLIC_TYPES" AS
TYPE theCursor IS REF CURSOR;
END P_Public_Types;
/
CREATE OR REPLACE PACKAGE "PG_BLOB" as
function get_single(v_id in INTEGER) return p_public_types.theCursor;
function get_all(v_order in VARCHAR2) return p_public_types.theCursor;
function add_blob(v_title in VARCHAR2
,v_content_type in VARCHAR2) return INTEGER;
function delete_blob(v_id in integer) return INTEGER;
end pg_blob;
/
CREATE OR REPLACE PACKAGE BODY "PG_BLOB" as
function get_single(v_id in INTEGER) return p_public_types.theCursor as
cc p_public_types.thecursor;
begin
open cc for '
SELECT B.*
FROM T_BLOB B
WHERE B.ID = ' || v_id || '
';
return cc;
end get_single;
function get_all(v_order in VARCHAR2) return p_public_types.theCursor as
cc p_public_types.thecursor;
orderby VARCHAR2(255);
begin
if v_order is not null then
orderby := ' ORDER BY ' || orderby;
end if;
open cc for '
SELECT B.*
FROM T_BLOB B
' || orderby || '
';
return cc;
end get_all;
function add_blob(v_title in VARCHAR2
,v_content_type in VARCHAR2) return INTEGER as
new_id INTEGER;
BEGIN
select SEQ_BLOB.nextval into new_id from dual;
INSERT INTO T_BLOB (ID, TITLE, FILE_DATA, CONTENT_TYPE)
VALUES (new_id, v_title, empty_blob(), v_content_type);
if SQL%ROWCOUNT > 0 then
return new_id;
else
return 0;
end if;
end add_blob;
function delete_blob(v_id in integer) return INTEGER as
begin
DELETE FROM T_BLOB
WHERE ID = v_id;
return SQL%rowcount;
end delete_blob;
end pg_blob;
/
Controller function index
function index()
{
echo '<p><a href="'.site_url('/testblob/add').'">Add new data</a></p>';
$rs =& $this->adodb->db->ExecuteCursor('begin'
.' :result := pg_blob.get_all(:v_order);'
.'end;'
,'result'
,array('v_order' => ''));
if ($rs && $rs->RecordCount() > 0)
{
$this->load->library('table');
$this->table->set_heading('No'
, 'Title'
, 'Content-Type'
, 'File');
$i = 1;
foreach ($rs as $row)
{
$this->table->add_row($i++
, $row['TITLE']
, $row['CONTENT_TYPE']
, $row['FILE_DATA'] ?
'<a href="'.site_url('/testblob/file/'.$row['ID']).'">Download File</a>'
: 'No File');
}
echo $this->table->generate();
}
else
{
echo '<p>No Data yet</p>';
}
}
Controller function add
function add()
{
if ($this->input->post('submit'))
{
$uploaded = false;
if ($_FILES['file']['name'] != '')
{
//upload image
$conf_upload['upload_path'] = realpath(BASEPATH.'../').'/tmp/';
$conf_upload['allowed_types'] = 'gif|jpg|png';
$conf_upload['encrypt_name'] = true;
$this->upload->initialize($conf_upload);
if ( ! $this->upload->do_upload('file'))
{
//echo '<p>'.$this->upload->display_errors().'</p>';
}
else
{
$data_upload = $this->upload->data();
$uploaded = true;
}
}
//insert data
$sql = 'declare'
.' result integer;'
.'begin'
.' :result := pg_blob.add_blob(:v_title'
.', :v_content_type);'
.'end;';
$stmt =& $this->adodb->db->PrepareSP($sql);
$this->adodb->db->InParameter($stmt, $this->input->post('title'), 'v_title', 255);
$this->adodb->db->InParameter($stmt, $data_upload['file_type'], 'v_content_type', 255);
$this->adodb->db->OutParameter($stmt, $result, 'result');
$this->adodb->db->Execute($stmt);
if ($result > 0)
{
if ($uploaded)
{
$this->adodb->db->UpdateBlobFile('T_BLOB', 'FILE_DATA', $data_upload['full_path'], 'ID='.$result, 'BLOB');
unlink ($data_upload['full_path']);
}
redirect('/testblob/index');
}
}
echo form_open_multipart('/testblob/add', array('name' => 'form_add', 'id' => 'form_add'));
echo '<ol>';
echo '<li>'.form_label('Title', 'title').form_input('title', $this->input->post('title'), 'id="title"').'</li>';
echo '<li>'.form_label('File', 'file').form_upload('file', '', 'id="file"').'</li>';
echo '<li>'.form_submit('submit', 'Add');
echo '</ol>';
echo form_close();
}
Controller function file
function file()
{
$id = intval($this->uri->segment(3));
if (! $id)
{
redirect('/testblob/index');
}
$rs =& $this->adodb->db->ExecuteCursor('begin'
.' :result := pg_blob.get_single(:v_id);'
.'end;'
,'result'
,array('v_id' => $id));
if ($rs && $rs->RecordCount() > 0)
{
if ($rs->fields['FILE_DATA'])
{
header('Content-type: '.$rs->fields['CONTENT_TYPE']);
echo $rs->fields['FILE_DATA'];
}
else
{
echo '<p>No File</p>';
}
}
else
{
echo '<p>Data no longer exist</p>';
}
}
Semoga tulisan singkat ini dapat memberikan gambaran kepada pembaca yang ingin menyimpan file ke dalam database dan menampilkan data file dari database.
Silahkan juga baca pembahasan dan contoh di link berikut ini:
