Add BLOB to DB (Win XP / Excel 2003 / UK)

Topic

New Reply

Hi,

I’m creating a small tool in Excel. It adds various texts and in some cases PDF files to an oracle database. In return I get some unique report ID’s.

Most of the setup is now in place, but I’m struggling a bit with the last piece…

I need to add the PDF file as a BLOB to the Oracle database.

I’m wondering if I can have Excel as the GUI only, so that I as a BLOB, append the PDF directly to Oracle.

What I had in mind is something like;
1. Display std. dialog and get user to identify PDF file (“FileOpen” or similar)
2. Retrieve and hold the filename and path identified by user.
3. Through ADO in Excel – Create “INSERT INTO” statement that executes against Oracle and adds PDF as BLOB
4. Upon completion, have Report ID returned from Oracle…
5. ….continue with rest of necessary steps.

All suggestions are more than welcome…

Reply | Quote

Replies

Is this related to your post 596,738?

Reply | Quote

Hi Hans,

Apologies for late reply.

Yes it’s the same question – just trying to provide some more info on the subject…

I have made some progress, but are currently trapped with some triggers that fires in Oracle “before insert”.
In this way they lock the record before I’m fully through with the operation.
I need the insertion to throw back a unique ID, that I have to add in another table at a later stage. Current problem is to get hold of the ID.
A SELECT on tableMAX does not return the ID and I don’t quite understand why. So I’ve been through various attempts.

1. ADO recordset object
2. ADO command object
3. ORACLE dynaset…

So far no luck, but the battle goes on…. / ;o)

Bests,
Henrik

Reply | Quote

Henrik,
You may want to have a look at this page which demonstrates the difference between SQL Server and Oracle – apparently you can’t just try and retrieve @@IDENTITY – you need to look at the nextval property of the Sequence column.
HTH

Reply | Quote

Hi Rory

Thanks for the hint. I’ve come a lot of the way by now and I think I’m almost there.
PDF is added OK as BLOB to the DB, only remaining problem is a cryptic Oracle error message that occurs on the ADO .execute command and then getting the unique PubID returned.
The table that I want to insert the BLOB into is called: BINARY_OBJECTS and has four columns: ( ‘PUB_ID’, ‘FILE_NAME’, ‘TIME_STAMP’, ‘CONTENT’ (BLOB)), where PUB_ID is the SEQ column.

I’ve also noticed the .nextval issue in a book, p.247 + example it relates to (ISBN: 1-861001-78-9, Wrox, “Visual Basic Oracle 8” , by Dov Trietsch)

By now, I’ve been trying 3-4 different approaches

1. As a recordset, where a dummy value is inserted initially and an ADO stream object is applied for the BLOB.
2. As a recordset, where a seperate SEQ function is called (.nextval) to get the SEQ number (book p. 247 inspired)
3. As a command object (code below)
4. As ORADB Dynaset

Attached as TXT is my command object code. The trigger from the ORACLE table is below, this combination seems to have come the closest to solving the problem.
(My setup: Win XP, Office 2003, ADO 2.7, Oracle 8i, OracleInProcServer 4.0 Type library)

The trigger on the ORACLE table looks like this:
CREATE OR REPLACE TRIGGER BINARY_OBJECTS_TR
BEFORE INSERT
ON ORADB .BINARY_OBJECTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if :new.binary_object_id is null then
select P_SEQ.nextval into :new.binary_object_id from dual;
end if;
end;

Any help are appreciated,

Tia.

Reply | Quote

….found a solution to the problem.

Apparently the key lies in the relationship between the Trigger value and the BLOB.
So to solve it one has to add the records as a two step solution (Step1 = GetTriggerID, Step2=Add BLOB)

I’ve added my solution for inspiration…

Reply | Quote

Thanks for sharing the solution to this elusive problem. It may well help others.

Reply | Quote