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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: michael dot lane at fadq dot gouv dot qc dot ca
New email:
PHP Version: OS:

 

 [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 17:01:58 2024 UTC