Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Cool Unanswered: Load file into table

    Hi experts.

    I'm looking for a way to load files into clob field, I've got the table but i haven't foud a way to load a file into the field, I've got a path where I've got a lot of files (excel files) and I wanna load them into the table. I wanna make it with a stored procedure.

    Best regards.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do it with an SQL Stored Procedure as it cannot access any directories to get the data from. You can just look at the LOAD and IMPORT CLP commands to do this. You can call the ADMIN_CMD built in (V9) stored procedure to do the LOAD or IMPORT, but the directory of the files will need to be on the server.

    Andy

  3. #3
    Join Date
    Sep 2011
    Posts
    57
    Ok, I got it, but with an import or load I can also save files like pdf, txt, etc..?? just that i've used to import some fields from excel files

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, yes you can.

    Andy

  5. #5
    Join Date
    Sep 2011
    Posts
    57
    the last question, i'm trying to insert a file but when i excecute the query insert row by row instead the archive, this is my query
    Code:
    LOAD FROM '/informatica/ftp/con/IUA0329E.txt' of DEL INSERT INTO AETADEO.PRUEBA_CLOB(ARCHIVO);

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is not how LOAD works. Please read the documentation for LOAD.

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ARWinner View Post
    You cannot do it with an SQL Stored Procedure as it cannot access any directories to get the data from.
    On DB2 9.7 you can, actually, with the help of the built-in UTL_FILE module. In any case you must have LOB source files on the database server, regardless of the method you use.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ricci View Post
    the last question, i'm trying to insert a file but when i excecute the query insert row by row instead the archive, this is my query
    Code:
    LOAD FROM '/informatica/ftp/con/IUA0329E.txt' of DEL INSERT INTO AETADEO.PRUEBA_CLOB(ARCHIVO);
    Place files that you want to load into the LOB column somewhere on the database server. Make sure they are accessible by the instance owner.

    Create a text file containing full file names of the LOB files.

    Load that text file, specifying the LOBSINFILE option.

  9. #9
    Join Date
    Sep 2011
    Posts
    57
    It works thanks a lot, but if I wanna save a picture or pdf, is the same way?

    this is the sentence what i'm using

    Code:
    LOAD FROM 'D:\test\prueba_load.txt' of DEL modified by lobsinfile  INSERT INTO contab.PRUEBA_CLOB(ARCHIVO);
    Inside the file contains the name of the file that i wanna load. I tryed but inserts a null value instead the file.

    Regards.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by ricci View Post
    It works thanks a lot, but if I wanna save a picture or pdf, is the same way?

    this is the sentence what i'm using

    Code:
    LOAD FROM 'D:\test\prueba_load.txt' of DEL modified by lobsinfile  INSERT INTO contab.PRUEBA_CLOB(ARCHIVO);
    Inside the file contains the name of the file that i wanna load. I tryed but inserts a null value instead the file.

    Regards.

    Can you post the contents of the input file?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Sep 2011
    Posts
    57
    I only keep en the input file the name of the file that i'm gonna insert, like

    HTML Code:
    hola.png

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ricci View Post
    I only keep en the input file the name
    You must specify the full path.

  13. #13
    Join Date
    Sep 2011
    Posts
    57
    when I wrote the whole path send me an error, but when I set only the file name (with txt file) it loads perfect in the table.

  14. #14
    Join Date
    Sep 2011
    Posts
    57

    Question

    One more question, can I use import inside a SP?

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Only if you use the ADMIN_CMD stored Procedure.

    Andy

Posting Permissions

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