php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69552 PDO INSERT BLOB in oracle 12c database
Submitted: 2015-04-30 13:59 UTC Modified: 2015-05-01 17:29 UTC
From: michael dot lane at fadq dot gouv dot qc dot ca Assigned: sixd (profile)
Status: Not a bug Package: PDO OCI
PHP Version: 5.4.40 OS: Debian 3.2.65-
Private report: No CVE-ID: None
 [2015-04-30 13:59 UTC] michael dot lane at fadq dot gouv dot qc dot ca
Description:
------------
I try all possibility but I'm pretty sure it's not possible to insert a file in an Oracle 12C database BLOB with PDO. All the other column have a value but my BLOB data length is always 0.

Table description:
Table: LOCI_DOCU

Column Name     id      Data type           Null?
ID	        1	NUMBER (10)	     N
FK_SH_ID	2	NUMBER (10)	     N
NOM_PHYS_DOCU	3	VARCHAR2 (150 Byte)  N
TIMB_MAJ	4	DATE	             N
USAG_MAJ	5	VARCHAR2 (12 Byte)   N
ADR_DOCU	6	VARCHAR2 (200 Byte)  Y
DOCU	        7	BLOB	             Y
TAIL_DOCU	8	NUMBER (13)	     Y
MIME_TYPE	9	VARCHAR2 (200 Byte)  Y

Test script:
---------------
$connection = $this->getConnection();

$sql = "INSERT INTO LOCI_DOCU 
        (fk_sh_id, nom_phys_docu, tail_docu, mime_type, docu) 
        VALUES (:FK, :NAMEFILE, :SIZEFILE, :MIMEFILE, EMPTY_BLOB()) 
        RETURNING DOCU INTO :FILELOB";

$stmt = $connection->prepare($sql);
        
$stmt->bindParam(":FK", 1, PDO::PARAM_INT); 
$stmt->bindParam(":NAMEFILE", 'MyFileName.jpg, PDO::PARAM_STR); 
$stmt->bindParam(":SIZEFILE", 278743, PDO::PARAM_INT); 
$stmt->bindParam(":MIMEFILE", "image/jpeg", PDO::PARAM_STR); 
$stmt->bindParam(":FILELOB", 50, PDO::PARAM_LOB);         

$stmt->execute();

Expected result:
----------------
Row insert with no error.
Get a data length of 278743 and be able to get the file working if downloaded.

Actual result:
--------------
Row insert with no error.
Data length of 0 of the BLOB in the database.
When downloading file, can not show result of the original inserted file, broken file with no content.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-04-30 14:36 UTC] michael dot lane at fadq dot gouv dot qc dot ca
I forgot in my example the value of the  PDO::PARAM_LOB.

$fp = fopen($uploadfile['tmp_name'], 'rb');

$stmt->bindParam(":FILELOB", $fp, PDO::PARAM_LOB);
 [2015-05-01 17:28 UTC] sixd@php.net
-Status: Open +Status: Not a bug
 [2015-05-01 17:28 UTC] sixd@php.net
Please post questions on a mail list or forum before logging a bug.
One forum is https://community.oracle.com/community/development_tools/php

Try something like:

function do_insert($db, $id, $data1, $data2)
{
	$db->beginTransaction(); 
	$stmt = $db->prepare("insert into mytable (id, data1, data2) values (:id, empty_blob(), empty_blob()) returning data1, data2 into :blob1, :blob2");
	$stmt->bindParam(':id', $id);
	$stmt->bindParam(':blob1', $blob1, PDO::PARAM_LOB);
	$stmt->bindParam(':blob2', $blob2, PDO::PARAM_LOB);
	$blob1 = null;
	$blob2 = null;
	$stmt->execute();

	fwrite($blob1, $data1);  
	fclose($blob1);	
	fwrite($blob2, $data2);  
	fclose($blob2);	
	$db->commit();
}
 [2015-05-01 17:29 UTC] sixd@php.net
-Assigned To: +Assigned To: sixd
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 11:01:30 2024 UTC