Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Inserting Blob data through SQL

    Dear All,

    Does any one tried inserting a blob data through SQL.
    When i try to insert using dbaccess i get the below error -
    " (U0001) - blob_input: cannot convert LO from argument string "
    I know there is a workaround to do it. Appreciate you reply.

    Best Regards,

    Lloyd

  2. #2
    Join Date
    Oct 2003
    Posts
    29
    You need to use FILETOBLOB function for BLOBs
    or FILETOCLOB for inserting in CLOBs

    Here are examples
    You specift "client" or "server" depending on weather the
    file is located on server machine or on client .
    insert into blobtab values (FILETOBLOB("c:\\Uninstal.EXE","client"));

    insert into blobtab values (FILETOCLOB("c:\\README.txt","client"));

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Hi Amit,

    Thanks for your reply. I just want to insert some text data in blob rather than a file, something like - insert into blob_tab values(1, "My text").

    Best Regards,

    Lloyd

    Originally posted by Amit Dandekar
    You need to use FILETOBLOB function for BLOBs
    or FILETOCLOB for inserting in CLOBs

    Here are examples
    You specift "client" or "server" depending on weather the
    file is located on server machine or on client .
    insert into blobtab values (FILETOBLOB("c:\\Uninstal.EXE","client"));

    insert into blobtab values (FILETOCLOB("c:\\README.txt","client"));

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Lloyd,

    Workaround is available in the form of LOAD ... INSERT INTO ...
    Create a file containing input:

    inp.txt
    1|aaaaa|
    2|bbbbb|
    3|cccccc|

    Load input file contents into a table:
    load from inp.txt insert into blobtab;

    Regards,
    Shriyan

  5. #5
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Hi Shriyan,

    Thanks for your prompt reply. So i will have to creat a file and insert all the blob data & then load that file. There is no way to do it thru Insert clause.

    Best Regards,

    Lloyd

    Originally posted by vpshriyan
    Hi Lloyd,

    Workaround is available in the form of LOAD ... INSERT INTO ...
    Create a file containing input:

    inp.txt
    1|aaaaa|
    2|bbbbb|
    3|cccccc|

    Load input file contents into a table:
    load from inp.txt insert into blobtab;

    Regards,
    Shriyan

  6. #6
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi Lloyd,

    Workaround is available in the form of LOAD ... INSERT INTO ...
    Create a file containing input:

    inp.txt
    1|aaaaa|
    2|bbbbb|
    3|cccccc|

    Load input file contents into a table:
    load from inp.txt insert into blobtab;

    Regards,
    Shriyan
    Hi Shriyan,

    I tried the above steps but i still get an error

    2: No such file or directory
    847: Error in load file line 1.
    My blob file contains 2 fields and my load syntax is
    load from blb.unl insert into blobtable(blobid, descr)

    Please advice.

    Regards.

    Lloyd

  7. #7
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by Amit Dandekar
    You need to use FILETOBLOB function for BLOBs
    or FILETOCLOB for inserting in CLOBs

    Here are examples
    You specift "client" or "server" depending on weather the
    file is located on server machine or on client .
    insert into blobtab values (FILETOBLOB("c:\\Uninstal.EXE","client"));

    insert into blobtab values (FILETOCLOB("c:\\README.txt","client"));
    Hi Amit,

    I tried the above syntax in dbaccess it gives an error,
    "Number of columns in INSERT does not match number of VALUES."
    The no. of columns in the file properly match with that with the table.
    I even tried running the above sql thru Winsql, but it gives me the same error. Please advice.

    Regards,

    lloyd

  8. #8
    Join Date
    Oct 2003
    Posts
    29
    well ,
    insert into blobtab values (FILETOBLOB("c:\\Uninstal.EXE","client"));
    will work only if there is just one column in the table !
    You need to specify column name if you have more than one column.
    insert into blobtab (blobcol) values (FILETOBLOB("c:\\Uninstal.EXE","client"));

  9. #9
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by Amit Dandekar
    well ,
    insert into blobtab values (FILETOBLOB("c:\\Uninstal.EXE","client"));
    will work only if there is just one column in the table !
    You need to specify column name if you have more than one column.
    insert into blobtab (blobcol) values (FILETOBLOB("c:\\Uninstal.EXE","client"));
    Hi Amit,

    I have an id field and a blob field in a table. I want to insert data in each of them through insert clause. I don't have a blob file. i was looking for normal sql statement like -
    insert into blobtab (blobid, blobdescr)
    values(1, "Desc")
    This way i need to inser around 200 data. Also for load command too i have 2 fields in my file which i need to insert in the same table.
    Is this possible throuh SQL. One of my developer friend said that he could write a java program which would insert the data directly. I was looking out if it could be done thru dbaccess. Thanks once again.

    Best Regards,

    lloyd

  10. #10
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi lloyd,

    My solution was based on blob - text data type. Load from ... insert into ... would not work for blob - byte data type.

    Hence, to solve this jinx, either you may change the data type to text if your business rule permits, or change it to varchar(255) if your kind of data can be ported that way.

    BYTE data type, best suited/used for pure binary objects like sound / movie clips, photographs whereas TEXT data type can used for unpredictable length of data having control-characters.

    Regards,
    Shriyan

  11. #11
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi lloyd,

    My solution was based on blob - text data type. Load from ... insert into ... would not work for blob - byte data type.

    Hence, to solve this jinx, either you may change the data type to text if your business rule permits, or change it to varchar(255) if your kind of data can be ported that way.

    BYTE data type, best suited/used for pure binary objects like sound / movie clips, photographs whereas TEXT data type can used for unpredictable length of data having control-characters.

    Regards,
    Shriyan

    Hi Shriyan,

    Thanks for your feedback. One of my java developers will write a program to load the data.

    Best Regards,

    Lloyd

Posting Permissions

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