barnarp
asked on
Oracle BLOB field SaveToFile unsuccessful
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?
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?
ASKER
Oracle version 8.0.6.3, the field type is BLOB.
Connection is BDE and I'm using ttable:
TBlobField(FieldByName('CP LANT_BLOB' )).LoadFro mFile(file 2blobname) ;
TBlobField(FieldByName('CP LANT_BLOB' )).SaveToF ile(curdir + '\' + curfilename.Text);
Seems all .txt files works fine, even over 1 Mb.
Connection is BDE and I'm using ttable:
TBlobField(FieldByName('CP
TBlobField(FieldByName('CP
Seems all .txt files works fine, even over 1 Mb.
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?
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?
ASKER
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').As String := docseed else
FieldByName('DOCUMENT').As String := docid;
TBlobField(FieldByName('CP LANT_BLOB' )).LoadFro mFile(file 2blobname) ;
FieldByName('DOCUMENT_SIZE ').Asinteg er := TBlobField(FieldByName('CP LANT_BLOB' )).BlobSiz e;
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.
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').As
FieldByName('DOCUMENT').As
TBlobField(FieldByName('CP
FieldByName('DOCUMENT_SIZE
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.
what does your blobexist?
where come it from?
(usual i'm a bit confused with the logic you have)
where come it from?
(usual i'm a bit confused with the logic you have)
ASKER
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?
If it is already in the table the code is TTable.Edit if not TTable.Insert
I think this is correct?
make sure file2blobname is FULL path name containing the drive, path and file name.
not just file name ...
not just file name ...
ASKER
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?
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?
ASKER
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').As String := $docid;
TBlobField(FieldByName('CP LANT_BLOB' )).SaveToF ile($docpa thandfilen ame);
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.
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').As
TBlobField(FieldByName('CP
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.
from your code:
with TTable do
begin
DB.StartTransaction;
Open;
Edit;
FieldByName('DOCUMENT').As String := $docid;
TBlobField(FieldByName('CP LANT_BLOB' )).SaveToF ile($docpa thandfilen ame);
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').As String := $docid;
TBlobField(FieldByName('CP LANT_BLOB' )).SaveToF ile($docpa thandfilen ame);
end
end;
with TTable do
begin
DB.StartTransaction;
Open;
Edit;
FieldByName('DOCUMENT').As
TBlobField(FieldByName('CP
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').As
TBlobField(FieldByName('CP
end
end;
ASKER
When using your code, I get the message: Dataset not in Edit or Insert mode
ASKER
Hi,
I now have the following code:
with dm.tblBLOB do
begin
Open;
Locate('DOCUMENT',curdocid .Text,[loC aseInsensi tive]);
TBlobField(FieldByName('CP LANT_BLOB' )).SaveToF ile(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?
I now have the following code:
with dm.tblBLOB do
begin
Open;
Locate('DOCUMENT',curdocid
TBlobField(FieldByName('CP
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?
Use the following methods, 101% working :)
To save a file to BLOB:
blob := dm.tblBLOB.CreateBlobStrea m(dm.tblBL OB.FieldBy Name('CPLA NT_BLOB'), bmWrite);
try
blob.Seek(0, soFromBeginning);
fs := TFileStream.Create('c:\you r_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.CreateBlobStrea m(dm.tblBL OB.FieldBy Name('CPLA NT_BLOB'), bmRead);
try
blob.Seek(0, soFromBeginning);
with TFileStream.Create('c:\you r_name.jpg ', fmCreate) do
try
CopyFrom(blob, blob.Size)
finally
Free
end;
finally
blob.Free
end;
To save a file to BLOB:
blob := dm.tblBLOB.CreateBlobStrea
try
blob.Seek(0, soFromBeginning);
fs := TFileStream.Create('c:\you
try
blob.CopyFrom(fs, fs.Size)
finally
fs.Free
end;
finally
blob.Free
end;
To load from BLOB:
blob := dm.tblBLOB.CreateBlobStrea
try
blob.Seek(0, soFromBeginning);
with TFileStream.Create('c:\you
try
CopyFrom(blob, blob.Size)
finally
Free
end;
finally
blob.Free
end;
ASKER
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.F ieldByName ('DOC_BLOB '), bmReadWrite);
stBLOB.Seek(0, soFromBeginning);
fsBLOB := TFileStream.Create(SaveDia log1.Initi alDir + '\' + 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?
I need to update the table and modified the code slightly.
with tblBLOBP do
begin
try
Open;
Edit;
tblBLOBP.FieldByName('DOC_
stBLOB := tblBLOBP.CreateBlobStream(
stBLOB.Seek(0, soFromBeginning);
fsBLOB := TFileStream.Create(SaveDia
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
how do you connect to oracle (bde,ado,other,..)?
which oracle-version do u use?