Storing and retreiving Images in Oracle

[Jean Paul]

hi.. I'm getting trouble storing and retrieving images in oracle, the thing is I think I'm able to store it as a 'Blob', but just can't retrieve it.. when I try I only get a blank image..so can anyone help me out on this...
thanx

[Beksy Kurian]

How are you retrieving the blob from database? pl/sql or Java? Are you using 9ias?
Give us a piece of code which you are writing so that we can troubleshoot!
Regards
Beksy

[Mark Spritzler]

"vitalix"-
Welcome to the JavaRanch! Please adjust your displayed name to meet the
JavaRanch Naming Policy.
You can change it
here.
Thanks! and welcome to the JavaRanch!
Mark

[Jean Paul]

hey..its I vitalix I had to change my name.. anyway.. I'm using Delphi 6 .. I stored it as a blob...
I used the same code I usually do to retreive stuff from the database..
anyway here is the code:
ELSE IF trnchk = 'EDIT'then
begin
dm_task.t_Event.close;
dm_task.t_Event.open;
if dm_task.t_Event.FindKey([filenum])=true then
begin
dm_task.t_Event.Edit;
tresid := dbe_contactId.Text;
tcaseid := dbe_caseID.Text;
trout := db_test1.Text;
trelev := dbe_ref.Text;
eventdt.Date := dm_task.t_EventDATERECEIVED.AsDateTime;
if dm_task.t_Resource.FindKey([tresid])=true then
begin
la_name.Caption := dm_task.t_ResourceRESOURCENAME.AsString;
la_surname.Caption := dm_task.t_ResourceSURNAME.AsString;
end;
if dm_task.t_Resource.FindKey([trout])=true then
begin
la_nameb.Caption := dm_task.t_ResourceRESOURCENAME.AsString;
la_surnameb.Caption := dm_task.t_ResourceSURNAME.AsString;
end;
if dm_task.t_Case.FindKey([tcaseid])=true then
begin
e_casedes.Text := dm_task.t_CaseCASENAME.AsString;
end;
q_relat.Close;
q_relat.SQL.Clear;
sqltext := ' select * '+
' from tevent e '+
' where e.eventid = '+''''+trelev+'''';
q_relat.SQL.Text := sqltext;
q_relat.Open;
e_subject.Text := q_relatPURPOSE.AsString;
dbe_det.Text := q_relatDETAILS.AsString;
end;
end;
end;

[Nigel Browne]

To start with I must point out that Delphi 6 uses ADO/OLEDB to connect to the oracle database and I am unsure as to why you have posted your question on a predominately Java site. I therefore answer your question with the JDBC code that I know works.
A BLOB is basically an array of bytes stored in the database.The java.sql.Blob object wraps a byte pointer known as a LOCATOR in database terms.java.sql.
When a BLOB locator is retrieved from the database, an instance of the java.sql.Blob, or oracle.sql.BLOB, class is used to hold the locator in your Java program. These classes hold the BLOB locator, not the actual data. To get the actual data, you must use one of the Blob, or BLOB, methods to read the data from the database as a stream or to get the data into a byte array.
Note also that the first byte in the array is at position 1

private void runGetBLOB() 
{
     try
     {   // Prepare a Statement:
         PreparedStatement stmnt = conn.prepareStatement("select for update aBlob from BlobTable"); // [I]select for update[/I] creates a lock on the blob.
         // Execute
         ResultSet rs = stmnt.executeQuery();
         while(rs.next())
         {
            try
            {
              [B] // Get as a BLOB
               Blob aBlob = rs.getBlob(1);
               byte[] allBytesInBlob = aBlob.getBytes(1, (int) aBlob.length());[/B]
            }
            catch(Exception ex)
            {
               // The driver could not handle this as a BLOB...
               // Fallback to default (and slower) byte[] handling
               byte[] bytes = rs.getBytes(1);
            }
         }
       // Close resources
       rs.close();
       stmnt.close();
     }
     catch(Exception ex)
     {
       this.log("Error when trying to read BLOB: " + ex);
     }
}

[ September 25, 2002: Message edited by: Nigel Browne ]