Solved

Oracle BLOB field SaveToFile unsuccessful

Posted on 2004-08-27
17
967 Views
Last Modified: 2010-04-04
Hi,

I am experiencing difficulties when retrieving files from an oracle blob field. I am using the savetofile and loadfromfile methods. It seems that uploading the file works, but when I try to open a retrieved file, say xls, then I get different error messages, for instance, file cannot be accessed or document name is invalid.

It works correct for small (2k) jpg files, but even 100k xls or word files does not want to open. When downloaded, the file shows the correct size.

Any ideas?



0
Comment
Question by:barnarp
17 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11911039
which fieldtype do u use in oracle?
how do you connect to oracle (bde,ado,other,..)?
which oracle-version do u use?
0
 

Author Comment

by:barnarp
ID: 11911080
Oracle version 8.0.6.3, the field type is BLOB.

Connection is BDE and I'm using ttable:

TBlobField(FieldByName('CPLANT_BLOB')).LoadFromFile(file2blobname);

TBlobField(FieldByName('CPLANT_BLOB')).SaveToFile(curdir + '\' + curfilename.Text);

Seems all .txt files works fine, even over 1 Mb.



0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11911247
usual it should work,
i had no problem in a project with this database (also using bde)
with word/excel/powerpoint files

well, one additional question
what is your dataset -> TTable or TQuery?
0
 

Author Comment

by:barnarp
ID: 11911546
TTable,

I picked up something. I think the problem might be with the upload of the file. I noticed whenever I update the existing BLOB table with a new document, it downloads and opens fine, but when I create a new record, the opening of the file on download gives errors.

Code:

with dm.tblBLOB do
  begin
    dm.Maximo.StartTransaction;
    Open;
    if blobexist = 'N' then Insert else Edit;

    if newDoc then FieldByName('DOCUMENT').AsString := docseed else
    FieldByName('DOCUMENT').AsString := docid;

    TBlobField(FieldByName('CPLANT_BLOB')).LoadFromFile(file2blobname);
    FieldByName('DOCUMENT_SIZE').Asinteger := TBlobField(FieldByName('CPLANT_BLOB')).BlobSize;
    if blobexist = 'N' then Post;
    dm.Maximo.Commit;
  end;

I am not sure what is causing it, and can also not understand the logic of:
    if blobexist = 'N' then Post;
I kept getting a unique constraint error when the code was just 'Post;' whenever I tried to upload a file, so I added the condition and it worked fine.


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 11911631
what does your blobexist?
where come it from?
(usual i'm a bit confused with the logic you have)
0
 

Author Comment

by:barnarp
ID: 11911721
blobexist is simply a boolean variable indicating whether the blob exist in the database, and is set after I do a select query on the BLOB table to see if the docuemnt is is there.

If it is already in the table the code is TTable.Edit if not TTable.Insert

I think this is correct?

0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11912344
make sure file2blobname is FULL path name containing the drive, path and file name.
not just file name ...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:barnarp
ID: 11912584
yes, it contains the full path on both edit and insert.

Table Script:

CREATE TABLE DOCBLOB
(
  DOCUMENT       VARCHAR2(9)                    NOT NULL,
  CPLANT_BLOB    BLOB,
  DOCUMENT_SIZE  NUMBER(10)
)

ALTER TABLE DOCBLOB ADD (
  CONSTRAINT DOCBLOB_PK PRIMARY KEY (DOCUMENT)

I have now changed the code : if blobexist = 'N' then Insert else Edit; to   edit;

Still the same. Is there not a way to see in the table if the BLOB has uploaded successfully?



0
 

Author Comment

by:barnarp
ID: 11913073
Hi,

I have used another Oracle Tool to downloaded the "faulty" BLOB file to a directory and I could open it 100%.

The faulty code must then be in my retrieval from the database:

pseudo:

try
  with TTable do
  begin
    DB.StartTransaction;
    Open;
    Edit;
    FieldByName('DOCUMENT').AsString := $docid;
    TBlobField(FieldByName('CPLANT_BLOB')).SaveToFile($docpathandfilename);
    DB.Commit;
  end;
  MessageDlg($DOCID+ ' successfully retrieved from the database.', mtInformation, [mbOK], 0 );
  except
  MessageDlg($DOCID + ' COULD NOT be retrieved from the database.', mtError, [mbOK], 0 );
  dm.Maximo.Rollback;
  Exit;
  end;

Any help please.
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 11914451
from your code:

with TTable do
  begin
    DB.StartTransaction;
    Open;
    Edit;
    FieldByName('DOCUMENT').AsString := $docid;
    TBlobField(FieldByName('CPLANT_BLOB')).SaveToFile($docpathandfilename);
    DB.Commit;
  end;

you don't need to call EDIT or StartTransaction, etc to retrieve the field's data
try this:

with TTable do //are you sure this is your table's name????? because TTable is the actual type
  begin
    if not (IsEmpty) then
    begin
      Open;
      FieldByName('DOCUMENT').AsString := $docid;
      TBlobField(FieldByName('CPLANT_BLOB')).SaveToFile($docpathandfilename);
    end
  end;
0
 

Author Comment

by:barnarp
ID: 11928681
When using your code, I get the message: Dataset not in Edit or Insert mode
0
 

Author Comment

by:barnarp
ID: 11929386
Hi,

I now have the following code:

 with dm.tblBLOB do
  begin
      Open;
      Locate('DOCUMENT',curdocid.Text,[loCaseInsensitive]);
      TBlobField(FieldByName('CPLANT_BLOB')).SaveToFile(curdir + '\' + curfilename.Text);
  end;

The file is 101kb and gets retrieved from the db successfully, even the bytes is 100%, but I cannot open the file. Don't know why.

When I open the table in TOAD and extract the file from there, I can open it with no problems.

Any other ideas?

0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 11967408
Use the following methods, 101% working :)

To save a file to BLOB:

blob := dm.tblBLOB.CreateBlobStream(dm.tblBLOB.FieldByName('CPLANT_BLOB'), bmWrite);
try
  blob.Seek(0, soFromBeginning);

  fs := TFileStream.Create('c:\your_name.jpg', fmOpenRead orfmShareDenyWrite);
  try
    blob.CopyFrom(fs, fs.Size)
  finally
    fs.Free
  end;
finally
  blob.Free
end;

To load from BLOB:

blob := dm.tblBLOB.CreateBlobStream(dm.tblBLOB.FieldByName('CPLANT_BLOB'), bmRead);
try
  blob.Seek(0, soFromBeginning);

  with TFileStream.Create('c:\your_name.jpg', fmCreate) do
    try
      CopyFrom(blob, blob.Size)
    finally
      Free
    end;
finally
  blob.Free
end;
0
 

Author Comment

by:barnarp
ID: 11973440
Thanks.

I need to update the table and modified the code slightly.

       with tblBLOBP do
         begin
             try
           Open;
           Edit;
           tblBLOBP.FieldByName('DOC_FILENAME').AsString := docid;
           stBLOB := tblBLOBP.CreateBlobStream(tblBLOBP.FieldByName('DOC_BLOB'), bmReadWrite);


               stBLOB.Seek(0, soFromBeginning);
               fsBLOB := TFileStream.Create(SaveDialog1.InitialDir + '\' + DocCopyComment, fmOpenRead or fmShareDenyWrite);
               stBLOB.CopyFrom(fsBLOB, fsBLOB.Size)
                  finally
                     //Post;
                    fsBLOB.Free;
                    stBLOB.Free;
                  end;
                end;

The code runs fine, but nothing gets populated in the blob field.

What is wrong here?
0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 13435853
PAQed with points refunded (200)

PAQ_Man
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Thread safe  opinion 7 126
Create a path if not exists 7 68
FMX and jaudiotracker playing memory stream 29 66
SUM 2 INTEGER ARRAYS INTO 1 10 93
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now