Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Is this related to your <post:=596,738>post 596,738</post:>?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    ....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...
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •