Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: insert data in fields clob/blob.

    Hi Colleagues,

    somebody can say me how to insert data in fields clob and blob, I have a table with this structure,

    a integer,
    b char,
    c char,
    d clob,
    e blob

    I need insert, read,update or delete data in this table.

    thank you for you help.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just use the INSERT statement.

    The real question is how you get the data into your application. If you want to use the DB2 CLP for the INSERT, you can't specify a file name because that would just be a string. DB2 has to know that the string is a file and the file content has to be read. So you'll need some sort of function:
    Code:
    INSERT INTO ... VALUES ( ..., readfile('filename'), ...);
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you just have a small number of rows to update manuualy, then there are some freeware Windows apps that do this (loads contents windows file into a DB2 lob column). I used a program like this about 3 years ago that I found on the Internet. I don't remember the name of the program, but maybe you can find one like it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hi Colleagues,

    I am executing this command

    db2 "insert into ARTISTS values (1,'Esto es una Prueba de Insert','P',readfile('/Pantallazo.png'), readfile('/Pantallazo.png') ";

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0440N No authorized routine named "READFILE" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Somebody can explain me how to execute the command correct without errors.

    Thank you for you help.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Knut expects you to create a UDF (using C) called readfile that reads a file. He wrote an article about how to create one a long time ago (maybe on IBM website Develperworks). It does not come with DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Marcus is right. One example how to write such a UDF can be found here: Making Operating System Calls from SQL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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