Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Getting SQLCODE=-302, SQLSTATE=22001 while uploading BLOB files(except .txt files)

    Hi,

    I've to upload files irrespective of file content type into database. So, I preferred BLOB type to store the files. I'm using Spring MVC and Hibernate to upload BLOB files from user end to DB2. There is no issue, when I'm uploading file which is a text file. But when I upload other than Text files, I'm getting the SQLCODE=-302, SQLSTATE=22001 error. BLOB field length is 1MB. I'm getting the above mentioned error, even when I upload files(except text files) those have file size of very lesser than 1MB(ex:10KB).

    Please help me to get rid of this issue.

    Thanks,
    Rajkumar.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    probably you are doing a regular insert.. with lob you have to use a different method for inserting long values
    look in infocenter - there are different topics on handling long objects
    Inserting large objects in PHP (PDO) - IBM Database Driver
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    Hi Guy Przytula,

    I'm getting this error only when I'm trying upload files other than text files. How is it possible only to insert Text files? Is there special reason behind this?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have to be more specific..
    what are you doing ?
    how is this being done ?
    we can not see your screen...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Mar 2013
    Posts
    6
    We are using DB2 v9.7 in our project. We have to upload and download BLOB files into DB using java and hibernate. We are using JDBC driver for DB2 v9.7. But it fails in Uploading files into BLOB field(except Text file). We are getting the following error:

    DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.14.113

    For uploading BLOB files, the following query is used by hibernate.

    insert into "db2DWH"."TIS_SRVY_DATA_RQST_ATTACHMENT" (ATTACHMENT_DESC, ATTACHMENT_FILE, ATTACHMENT_TYP, INS_TIMESATMP, INS_USR, UPD_TMS, UPD_USR, ATTACHMENT_NM, SRVY_DATA_RQST_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
    The above query inserts Text files without any exception, but it throws above mentioned error while inserting Binary Files such as .jpg, .bmp, .pdf, etc.,
    Do we need to use any special query to insert Binary files? Behaviour of this BLOB seems like CLOB.

    Note: We are passing ATTACHMENT_FILE as java.sql.Blob object from Java.
    Entity side code
    ==============
    @Column(name="ATTACHMENT_FILE")
    @Lob //It doesn't make sense at any case even it is available or not.
    @Type(type="blob") //It doesn't make sense at any case even it is available or not.

    private Blob attachment_file; //java.sql.Blob

    public Blob getAttachment_file() {
    return attachment_file;
    }

    public void setAttachment_file(Blob attachment_file) {
    this.attachment_file = attachment_file;
    }

    Controller side code:
    ===============
    Blob blob = new SerialBlob(file.getBytes()); //java.sql.Blob
    attachment.setAttachment_file(blob);

    By using these methods only Hibernate uploads/downloads Blob.

    Error Log:
    =======

    com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.14.113
    at com.ibm.db2.jcc.am.ed.a(ed.java:669)
    at com.ibm.db2.jcc.am.ed.a(ed.java:60)
    at com.ibm.db2.jcc.am.ed.a(ed.java:127)
    at com.ibm.db2.jcc.am.oo.b(oo.java:2366)
    at com.ibm.db2.jcc.am.oo.c(oo.java:2349)
    at com.ibm.db2.jcc.t4.ab.l(ab.java:370)
    at com.ibm.db2.jcc.t4.ab.a(ab.java:62)
    at com.ibm.db2.jcc.t4.p.a(p.java:50)
    at com.ibm.db2.jcc.t4.rb.b(rb.java:220)
    at com.ibm.db2.jcc.am.po.qc(po.java:3497)
    at com.ibm.db2.jcc.am.po.b(po.java:4458)
    at com.ibm.db2.jcc.am.po.b(po.java:4629)
    at com.ibm.db2.jcc.am.po.ic(po.java:799)
    at com.ibm.db2.jcc.am.po.executeUpdate(po.java:777)
    at com.hcl.vicroads.file_attachment_manager.controlle r.AttachmentController.save(AttachmentController.j ava:83)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.bind.annotation.support.Ha ndlerMethodInvoker.invokeHandlerMethod(HandlerMeth odInvoker.java:176)
    at org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.invokeHandlerMethod(An notationMethodHandlerAdapter.java:414)
    at org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.handle(AnnotationMetho dHandlerAdapter.java:402)
    at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:771)
    at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:716)
    at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:647)
    at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:563)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker****n (JIoEndpoint.java:489)
    at java.lang.Thread****n(Unknown Source)...
    Last edited by rajkumargandhi; 03-14-13 at 07:18.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You say that the insert of text files was successful... Have you tried retrieving and making sure the data is correct and as you expect.

    SQL error -302 is :

    SQL0302N


    There is an example of operating with blobs. Check if Hibernate generated code does the same as in the example ...

    How to use LOB data type
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2013
    Posts
    6
    Hi Sathyaram,

    I've tried the following code also using direct JDBC connection. The following code matches with your example, but the error remains same.

    PreparedStatement pre_stmt = con.prepareStatement("insert into \"schema\".\"table\"(ID, name, type, desc, file) values(?, ?, ?, ?, ?)");
    pre_stmt.setInt(1, 1);
    pre_stmt.setString(2, attachment.getAttachment_nm());
    pre_stmt.setString(3, file.getContentType());
    pre_stmt.setString(4, attachment.getAttachment_desc());
    pre_stmt.setBlob(5, new SerialBlob(file.getBytes()));
    pre_stmt.executeUpdate();

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why don't you show us the table definition?
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Mar 2013
    Posts
    6
    Table Definition:
    ============

    Key Name Data type Length Nullable
    =========================================
    FK ID INTEGER 4 No
    CPK Name VARCHAR 50 No
    Type VARCHAR 10 Yes
    Desc VARCHAR 100 Yes
    FILE BLOB 1048576 Yes

  10. #10
    Join Date
    Mar 2013
    Posts
    6
    One more interesting suspense on this issue.

    When I'm trying to upload using simple java jdbc application, I'm able to insert binary files into DB2. But, I'm not able to insert binary files, when I'm using the same code to insert through web application.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rajkumargandhi View Post
    When I'm trying to upload using simple java jdbc application, I'm able to insert binary files into DB2. But, I'm not able to insert binary files, when I'm using the same code to insert through web application.
    Then it's not a DB2 problem, but your application's. Likely right here:

    at com.hcl.vicroads.file_attachment_manager.controlle r.AttachmentController.save(AttachmentController.j ava:83)
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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