OTN Discussion Forums : inserting data into BLOB field ...

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

inserting data into BLOB field
Posted: Oct 8, 2003 10:44 AM

I have been trying to insert an uploaded file from php script to database blob field , but I am not able to do it.

below is my code, Please help me out with this !!

<?php

include_once("header.inc.php");
include_once("config.php");
include_once("adodb/adodb.inc.php");

$file_name = $_FILES['userfile']['name'];
$uploaddir = "check/". $_FILES['userfile']['name'];
$up = move_uploaded_file($_FILES['userfile']['tmp_name'], $uploaddir);
chmod($uploaddir,0644);
$folder_name="check";
$doc_file=fread(fopen("$uploaddir","r"),filesize("$uploaddir"));

$app_id=63;
$app_mat_id=1;

$main_query = "INSERT INTO submitted_material_list
(Application_Material_ID,
Application_ID,
Physical_Copy,
Rec_By,
Rec_Date)
VALUES ($app_mat_id,
$app_id,
$doc_file,
$app_id,
SYSDATE)";
$result = $db->Execute($main_query) or die($db->ErrorMsg());
?>

I read online somewhere.. that I can upload it using "LOAD_FILE()" , can it be done with LOAD_FILE() ??

PLEASE HELP !!

Thanks in advance

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 8, 2003 8:25 PM

in response to: nirali_vaghela@...

Reply

<?php
 
/*

    Here's a contrived example that uploads an image file and inserts it
    into a BLOB column.  The image is retrieved back from the column and
    displayed.  Make sure there is no whitespace before "<?php" else the
    wrong HTTP header will be sent and the image won't display properly.

    This was tested with ADOdb 3.60 against Oracle 8.1.7 and 9.2

      -- CJ

*/
 
include_once("adodb.inc.php");
 
define('MYBLOBID', 1);
 
function checkfile()
{
  $fn = $_FILES['userfile']['tmp_name'];
  if (!file_exists($fn))
    error('File check', 'Can not find file '.$fn);
  return($fn);
}
 
function myconnect()
{
  $db = ADONewConnection("oci8");
  //  $db->debug = true;
  if (!@$db->Connect(false, "scott", "tiger", "MYDB"))
    error('Connect', $db->ErrorMsg());
 
  $db->Execute('DROP TABLE BTAB');
  if (!$db->Execute('CREATE TABLE BTAB (BLOBID NUMBER, BLOBDATA BLOB)'))
    error('Setup', $db->ErrorMsg());
  return $db;
}
 
function insertfile($db, $id, $path)
{
  if (!$db->Execute('INSERT INTO BTAB (BLOBID, BLOBDATA) VALUES ('.$id.', EMPTY_BLOB())'))
    error('Insert failed', $db->ErrorMsg());
  if (!$db->UpdateBlobFile('BTAB', 'BLOBDATA', $path,'BLOBID='.$id, 'BLOB'))
    error('UpdateBlobFile failed', $db->ErrorMsg());
}
 
function displayimage($db, $id)
{
  if (!$rs = $db->Execute("SELECT BLOBDATA FROM BTAB WHERE BLOBID = ".$id))
    error('Error selecting', $db->ErrorMsg());
 
  // If any text (or whitespace!) is printed before this header is sent,
  // the text won't be displayed and the image won't display properly.
  // Comment out this line to see the text and debug such a problem.
  header("Content-type: image/JPEG");
 
  $row = $rs->FetchRow();
  print $row[0];
}
 
function error($w, $m)
{
  echo "$w: $m";
  die;
}
 
if (isset($_FILES['userfile'])) {
  $fn = checkfile();
  $db = myconnect();
  insertfile($db, MYBLOBID, $fn);
  displayimage($db, MYBLOBID);
}
 
?>
 
<html>
<head><title>Upload BLOB images with ADOdb</title></head>
<body>
<form enctype="multipart/form-data"
action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
Image Filename: <input name="userfile" type="file">
<input type="submit" value="Upload">
<input type="hidden" name="MAX_FILE_SIZE" value="3000">
</form>
</body>
</html>

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

Re: inserting data into BLOB field
Posted: Oct 9, 2003 7:31 AM

in response to: cjbjones

Reply

Thank you very much for your reply.

Well I tried it your way .. It inserts the EMPTY_BLOB() and then when I try for UpdateBlobFile() it gives me parse error.

Below is my code snippet, Please let me know where I am wrong... I am not sure about the parameters passed in UpdateBlobFile().

location where my files are stored is : check/filename.pdf
table name : Submitted_Material_List
field name : Physical_Copy
data type : BLOB
where condition : Application_Material_ID = $app_mat_id

so accordingly this is what I have given in my code :

/**************************************************/

$file_name = $_FILES['userfile']['name'];

$uploaddir = "check/".$file_name;

$up = move_uploaded_file($_FILES['userfile']['tmp_name'], $uploaddir);

chmod($uploaddir,0644);

$folder_name="check";
$app_id=63;
$app_mat_id=1;

$main_query = "INSERT INTO submitted_material_list
(Application_Material_ID,
Application_ID,
Physical_Copy,
Rec_By,
Rec_Date)
VALUES ($app_mat_id,
$app_id,
EMPTY_BLOB(),
$app_id,
SYSDATE)";
$result = $db->Execute($main_query) or die($db->ErrorMsg());

if(!$db->UpdateBlobFile('Submitted_Material_List', 'Physical_Copy', $uploaddir, 'application_materail_id'=$app_mat_id, 'BLOB'))
error('UpdateBlobFile() Failed : ', $db->ErrorMsg());
else
echo " DONE !!";
?>

PLEASE HELP !!

Thanks in advance.

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 9, 2003 5:22 PM

in response to: nirali_vaghela@...

Reply

Maybe it's because the BlobUpdateFile call has a
misspelling of 'application_materail_id' and needs
the equal sign inside the quotes and the data value
string concatenated using a period. E.g. try:

  'application_material_id='.$app_mat_id

If this doesn't resolve the problem, please give us
standard problem solving details: platforms, versions,
exact error messages.

-- CJ

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

Re: inserting data into BLOB field
Posted: Oct 10, 2003 6:56 AM

in response to: cjbjones

Reply

Thank you very much...It worked !!

Would like to know more about it.. Now as I have the file into the database.. how can I display it using PHP Script ??

when I try to do a select on that BLOB field , It gives me an error : SP2-0678: Column or attribute type can not be displayed by SQL*Plus

table : submitted_material_list

Name

APPLICATION_MATERIAL_ID NUMBER(3)
APPLICATION_ID NUMBER(9)
PHYSICAL_COPY BLOB
REC_BY NUMBER(9)
REC_DATE DATE

I assume this is because its a big size column... so then how can I select and display it on the browser using PHP ????

Thanks in advance.

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 12, 2003 4:39 PM

in response to: nirali_vaghela@...

Reply

SQL*Plus is unable to query BLOB columns.

My image-display example is one PHP solution
for retrieving and displaying the BLOB.

If your BLOBs are not images, try searching the web
for PHP solutions. There are a lot of PHP packages
out that that work on other kinds of binary data.

-- CJ

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

Re: inserting data into BLOB field
Posted: Oct 13, 2003 6:48 AM

in response to: cjbjones

Reply

Thank you very much for the help.

Can you please paste your code for retrieving images... so as go give me the idea of what to do...

My files are ".pdf files" so will be looking on the internet for it.

Thanks once again.

jchava

Posts: 4
Registered: 10/16/98

Re: inserting data into BLOB field
Posted: Oct 13, 2003 8:58 AM

in response to: nirali_vaghela@...

Reply

If you are not using the ADOdb Library, you can try the OC8 extension functions.

you can see the un-official documentation (with many samples)
[] http://conf.php.net/pres/slides/oci/paper.txt
and several slides on
[] http://www.phpconference.com/downloader/downloader.php?CatID=0&NewsID=367

== save a BLOB

to save the BLOB you need...
1) insert a row using EMPTY_BLOB()
2) create a descriptor (ie: $blob)
3) use $blob->save() (for a stream) or $blob->savefile() (to save a file)

an example (from the slides)...

<?php
$conn = OCILogon("scott", "tiger");
$stmt = OCIParse($conn, "
INSERT INTO names
VALUES (8, 'Maxim', 'Maletsky', EMPTY_BLOB())
RETURNING picture INTO :picture");

$lob = OCINewDescriptor($conn);
OCIBindByName($stmt, ':picture', &$lob, -1, SQLT_BLOB);
OCIExecute($stmt, OCI_DEFAULT);

$lob->savefile('/tmp/uploaded/picture.jpg');
OCICommit($conn);
$lob->free() ;
OCIFreeStatement($stmt);
?>

== load the BLOB

<?php

$db = OCILogon("scott","tiger");
$stmt = OCIParse($db,"select * from blobdemo");
OCIExecute($stmt);

while (OCIFetchInto($stmt,$arr,OCI_ASSOC)) {
echo "id: ".$arr[ "ID" ]."\n";
echo "size: ".strlen($arr[ "LOB" ]->load())."\n";
}
?>

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

Re: inserting data into BLOB field
Posted: Oct 13, 2003 11:06 AM

in response to: jchava

Reply

Thank you for your reply.

well I use the ADOdb abstraction and so am not quite sure of how to do it. Looked on internet but then I am not able to find any helpful material for "ADOdb + PHP + oracle + BLOB".

If you know any website/documentation for it please let me know.

Thank you.

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 13, 2003 4:01 PM

in response to: nirali_vaghela@...

Reply

can you please paste your code for retrieving images...

It is already posted; see the function "displayimage".

-- CJ

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 13, 2003 6:27 PM

in response to: nirali_vaghela@...

Reply

To display a PDF using the sample code I posted previously in this
thread, add this function:

function displaypdf($db, $id)
{
  if (!$rs = $db->Execute("SELECT BLOBDATA FROM BTAB WHERE BLOBID = ".$id))
    error('Error selecting', $db->ErrorMsg());
 
  $row = $rs->FetchRow();
 
  // If any text (or whitespace!) is printed before this header is sent,
  // the text won't be displayed and the pdf won't load properly.
  // Make sure there are no blank lines before the "<?php" tag at 
  // the start of this file.
  // Comment out all the "header" lines below to debug such a problem.
  header("Content-Type: application/pdf");
  // http://www.php.net/header has some interesting comments on
  // sending a "Content-Disposition" header
  header("Content-Disposition: attachment; filename=suggestedfilename.pdf");
  header("Content-Length: " . strlen($row[0]));
  print $row[0];
}

and replace the original call to displayimage() with a call to
displaypdf().

-- CJ

nirali_vaghela@...

Posts: 113
Registered: 08/19/02

Re: inserting data into BLOB field
Posted: Oct 14, 2003 10:47 AM

in response to: cjbjones

Reply

Thank you very much for the help.

I tried doing it your way and for a single file it works... I need to upload multiple files..

Is there a way I can show a link to that file and then on clicking on the link it goes to the download file option ??

Thanks once again.

cjbjones

Posts: 790
Registered: 11/17/99

Re: inserting data into BLOB field
Posted: Oct 14, 2003 4:47 PM

in response to: nirali_vaghela@...

Reply

If you haven't done this before, a very simple way is with a pair of scripts.
Script1.php can create and show links:

  <a href="http://host.domain/script2.php?id=1>File 1</a>
  <a href="http://host.domain/script2.php?id=2>File 2</a>

And script2.php does a query for the PDF file with the given id, and sends it off.
Script2.php would not display any other output or prompt the user in any way.
It solely "serves" up a PDF file.

If you want examples, just search the web!

-- CJ

tommy.yan@hp.com

Posts: 6
Registered: 02/05/04

Re: inserting data into BLOB field
Posted: May 30, 2005 1:16 AM

in response to: cjbjones

Reply

Hello CJ,

I use your code as an example to plug jpeg image into oracle database 10g with php4.3.6+apache 2.0.54 + ado oci version V4.11 27 .

But when I insert then display picture I got a 'cross' sign, seems the picture does not display well.
When I enable sql debugging I got below information when executing insert.php:

file tmpname is:c:\tmp\php6.tmp
file type is:image/pjpeg
file size is:18913
file information is:0
Array
(
[userfile] => Array
(
[name] => box1.JPG
[type] => image/pjpeg
[tmp_name] => c:\tmp\php6.tmp
[error] => 0
[size] => 18913
)

)
<hr />
(oci8): ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' <code></code>
<hr />
filename=c:\tmp\php6.tmp
dbname=Object
imagenum=7
id=7
path=c:\tmp\php6.tmp
<hr />
(oci8): INSERT INTO BTAB (BLOBID, BLOBDATA) VALUES (7, EMPTY_BLOB()) <code></code>
<hr />
<hr />
(oci8): UPDATE BTAB set BLOBDATA=EMPTY_BLOB() WHERE BLOBID=7 RETURNING BLOBDATA INTO :blob <code>[ (blob=>'Array') ]</code>
<hr />
name=:blob var=Object len=-1 type=113
<hr />
(oci8): SELECT BLOBDATA FROM BTAB WHERE BLOBID = 7 <code></code>
<hr />
Ã¿ÃÃ¿Ã JFIFÂ°Â°Ã¿ÃCÃ¿ÃCÃ¿Ãâ¬â¬"Ã¿Ã
Ã¿ÃÂµ}!1AQa"q2ÂâÂ¡#BÂ±ÃRÃÃ°$3brâ
%&'()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxyzÆââ¦â â¡Ëâ°Å ââââ¢ââËâ¢Å¡Â¢Â£Â¤Â¥Â¦Â§Â¨Â©ÂªÂ²Â³Â´ÂµÂ¶Â·Â¸Â¹ÂºÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃ¡Ã¢Ã£Ã¤Ã¥Ã¦Ã§Ã¨Ã©ÃªÃ±Ã²Ã³Ã´ÃµÃ¶Ã·Ã¸Ã¹ÃºÃ¿Ã
Ã¿ÃÂµw!1AQaq"2ÂBâÂ¡Â±Ã #3RÃ°brÃ
......

I'd post my code here fyi:
<?php

include_once("adodb.inc.php");

define('MYBLOBID', 1); // should really be a unique id e.g. a sequence number

define("ORA_CON_UN_01", "sh");
define("ORA_CON_PW_01", "sh");
define("ORA_CON_DB_01", "ORACA");

function checkfile()
{
$fn = $_FILES['userfile']['tmp_name'];
// echo "file tmpname is:".$fn."\n";
// echo "file type is:".$_FILES['userfile']['type']."\n";
// echo "file size is:".$_FILES['userfile']['size']."\n";
// echo "file information is:".$_FILES['userfile']['error']."\n";
// print_r($_FILES);

if (!file_exists($fn))
error('File check', 'Can not find file '.$fn);
return($fn);
}

function myconnect()
{
$db = ADONewConnection("oci8");
// $db->debug = true;
if (!@$db->Connect(false, ORA_CON_UN_01, ORA_CON_PW_01, ORA_CON_DB_01))
error('Connect', $db->ErrorMsg());
return $db;
}

function deleteblob($db, $id)
{
if (!$db->Execute('DELETE FROM BTAB WHERE BLOBID='.$id))
error('Delete failed', $db->ErrorMsg());
}

function insertblob($db, $id, $path)
{
echo "id="."$id"."\n";
echo "path="."$path"."\n";

if (!$db->Execute('INSERT INTO BTAB (BLOBID, BLOBDATA) VALUES ('.$id.', EMPTY_BLOB())'))
error('Insert failed', $db->ErrorMsg());
if (!$db->UpdateBlobFile('BTAB', 'BLOBDATA', $path,'BLOBID='.$id, 'BLOB'))
error('UpdateBlobFile failed', $db->ErrorMsg());
}

function displayimage($db, $id)
{
if (!$rs = $db->Execute("SELECT BLOBDATA FROM BTAB WHERE BLOBID = ".$id))
// if (!$rs = $db->Execute("SELECT BLOBDATA FROM BTAB WHERE BLOBID = "4"))
error('Error selecting', $db->ErrorMsg());
// If any text (or whitespace!) is printed before this header is sent,
// the text won't be displayed and the image won't display properly.
// Comment out this line to see the text and debug such a problem.
header("Content-type: image/pJPEG");
$row = $rs->FetchRow();
print $row[0];
}

function error($w, $m)
{
echo "$w: $m";
die;
}

if (isset($_FILES['userfile'])) {
$fn = checkfile();
$db = myconnect();
$imagenum=$_POST['imagenum'];
print "filename="."$fn"."\n";
// echo "dbname="."$db"."\n";
// echo "imagenum="."$imagenum"."\n";
// deleteblob($db, MYBLOBID);
insertblob($db, $imagenum , $fn);
displayimage($db, $imagenum);
exit;
}

?>

<html>
<head><title>Upload BLOB images with ADOdb</title></head>
<body>
<form enctype="multipart/form-data"
action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
Image Filenumber: <input name="imagenum" type="text">

Image Filename: <input name="userfile" type="file">

<input type="submit" value="Upload">
<input type="hidden" name="MAX_FILE_SIZE" value="3000">
</form>

<HR>
<table align='center' border='0'>
<tr>
<td align='center' width='150'>
<form Action='display.php'>
<INPUT TYPE="submit" VALUE="Display">
</form>
</td>
<td align='center' width='150'>
<form>
<input type="button" value="Back" onclick="self.history.back();">
</form>
</td>
<td align='center' width='150'>
<form Action='index.html'>
<INPUT TYPE="submit" VALUE="Back to Main">
</form>
</td>
</tr>
</table>
<HR>
</BODY>
</HTML>

thx.

tommy.yan@hp.com

Posts: 6
Registered: 02/05/04

Re: inserting data into BLOB field
Posted: May 30, 2005 1:52 AM

in response to: cjbjones

Reply

Hi,

I modified your code but cannot display the picture uploaded, only a 'cross' sign displayed.
I use apache 2.0.54, php 4.3.6, win2k, oracle 10g, ado 4.11.

When I enable sql-debugging and print_r($_FILEs), I got below infos when I click 'upload':
Array
(
[userfile] => Array
(
[name] => box1.JPG
[type] => image/pjpeg
[tmp_name] => c:\tmp\php16.tmp
[error] => 0
[size] => 18913
)

)
<hr />
(oci8): ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' <code></code>
<hr />
filename=c:\tmp\php16.tmp
id=1
path=c:\tmp\php16.tmp
<hr />
(oci8): INSERT INTO BTAB (BLOBID, BLOBDATA) VALUES (1, EMPTY_BLOB()) <code></code>
<hr />
<hr />
(oci8): UPDATE BTAB set BLOBDATA=EMPTY_BLOB() WHERE BLOBID=1 RETURNING BLOBDATA INTO :blob <code>[ (blob=>'Array') ]</code>
<hr />
name=:blob var=Object len=-1 type=113
<hr />
(oci8): SELECT BLOBDATA FROM BTAB WHERE BLOBID = 1 <code></code>
<hr />
Ã¿ÃÃ¿Ã JFIFÂ°Â°Ã¿ÃCÃ¿ÃCÃ¿Ãâ¬â¬"Ã¿Ã
Ã¿ÃÂµ}!1AQa"q2ÂâÂ¡#BÂ±ÃRÃÃ°$3brâ
%&'()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxyzÆââ¦â â¡Ëâ°Å ââââ¢ââËâ¢Å¡Â¢Â£Â¤Â¥Â¦Â§Â¨Â©ÂªÂ²Â³Â´ÂµÂ¶Â·Â¸Â¹ÂºÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃ¡Ã¢Ã£Ã¤Ã¥Ã¦Ã§Ã¨Ã©ÃªÃ±Ã²Ã³Ã´ÃµÃ¶Ã·Ã¸Ã¹ÃºÃ¿Ã
Ã¿ÃÂµw!1AQaq"2ÂBâÂ¡Â±Ã #3RÃ°brÃ
$4Ã¡%Ã±&'()*56789:CDEFGHIJSTUVWXYZcdefghijstuvwxyzâÆââ¦â â¡Ëâ°Å ââââ¢ââËâ¢Å¡Â¢Â£Â¤Â¥Â¦Â§Â¨Â©ÂªÂ²Â³Â´ÂµÂ¶Â·Â¸Â¹ÂºÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃÃ¢Ã£Ã¤Ã¥Ã¦Ã§Ã¨Ã©ÃªÃ²Ã³Ã´ÃµÃ¶Ã·Ã¸Ã¹ÃºÃ¿Ã?Ã³ÃÂ¼EcÃ£YeÃ°Ã·ÂÂ®Â4YÂ´Ã»Ã¸Â¬Ã¢Ã±Â´Å¡ÃzdW`âº7Ã5iÂµâ¹â°IPFÃâ Df(ÃÃÃ¨Å¾ Ã½â¢>&EÂÃ|<OÃxÃGÃ±=Ã¢]Ã¸>Ã¶ÃVÃÂ¿Â±<IÃ¿ÃÂ¸â¹VÃ°Â·Å Ã´ÃÅ¡ÃÃÃeâÃ¦)Ãâ¬Ã²Â¤âÂ¹Âµâ3Ã¬'â¦Â¿c_Ãâ¹Ã?OÅÂ¼UÃ£?
Ã¼ZÃ±^âcÅ¡Ã§OÃ¸YÃ¢=;PÃ&Â¶ââOÃ¬K_k:gÃ¶Â½Ã¢â¡ÃÃ <Â°ÂBÃÃwÃ?Â¿n?ÃCwÃ¡ÃÃÃ£Ã¡WÂ<%}

Seems the contents is processed but not displaying well.

I have set
file_uploads = On
upload_tmp_dir = "c:\tmp"
upload_max_filesize = 6M
in c:\program files\php.ini
and enabled this dir as everyone 'full control', when at this time, when I dir this folder, no php16.tmp is found.

here's my code:
<?php

include_once("adodb.inc.php");