cancel
Showing results for 
Search instead for 
Did you mean: 

NumberFormatException when using JDBC receiver for blob

former_member245887
Participant
0 Kudos

Hi All,

I'm using JDBC receiver adapter to send data to a Oracle database.

INSERT into the database works. Then I tried a stored procedure with a VARCHAR and a DATE and it also works fine

However, when I try to do the same for a BLOB, I get NumberFormatException.

Below the structure I use:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_DB_JDBC_INSERT xmlns:ns0="http:/xxx">

<INSERT_BLOB>

<insert_rp action="EXECUTE">

<DOCUMENT hasQuot="No" type="BLOB">JVBERi0xLjQKJaqrrK0KVPhr8QPIKJSVFT0YK</DOCUMENT>

</insert_rp>

</INSERT_BLOB>

</ns0:MT_DB_JDBC_INSERT>

The DOCUMENT node contains some base64 encoded data.


And the error:

JDBC Message processing failed, due to Error processing request in sax parser: Error when executing statement for table/stored proc. 'insert_rp' (structure 'INSERT_BLOB'): java.lang.NumberFormatException: For input string: "Ri"

Anyone any idea what I'm doing wrong?

Kind Regards

Edmond Paulussen

Accepted Solutions (1)

Accepted Solutions (1)

iaki_vila
Active Contributor
0 Kudos

Hi Edmond,

Are you sure that the stored procedure has not another parameter that it would numeric?

Is your ORACLE JDBC Driver version 10.1.0.4 or higger?

Im not an DB expert, but if you are using alphanumeric data why dont you use a CLOB type?, BLOB type is more relationed with  binary data.

Regards.

former_member245887
Participant
0 Kudos

Hi, Thanks for the reply.

I try to insert a pdf. According the our oracle admin I have to use a BLOB for that kind of data.

In the mean time I have found the reason for the error: I have to convert the convert the data string that

goes to the jdbc receiver adapter to hexadecimal format.

Kind Regards

Edmond Paulussen

Former Member
0 Kudos

Hi Edmond

I am facing the same issue. trying it through a procedure with blob input parameter.

                           <xsd:element name="t_blob_data">

                              <xsd:complexType>

                                 <xsd:simpleContent>

                                    <xsd:extension base="xsd:hexBinary">

                                       <xsd:attribute name="isinput" type="xsd:string" />

                                       <xsd:attribute name="type" type="xsd:string" />

                                    </xsd:extension>

                                 </xsd:simpleContent>

                              </xsd:complexType>

                           </xsd:element>

This is my data type element for blob data. I am mapping type attribute to a constant blob.

Error Message :

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'procedure name here' (structure 'UpdateInsertStatementAttachment'): java.lang.NumberFormatException: For input string: "Ri"

Please help.

former_member245887
Participant
0 Kudos

Hi

Below the structure that I use for action=EXECUTE on a Oracle DB

<xsd:element name="SDS">

   <xsd:complexType>

      <xsd:simpleContent>

         <xsd:extension base="xsd:hexBinary">

            <xsd:attribute name="type" type="xsd:string" use="required" />

            <xsd:attribute name="isInput" type="xsd:boolean" />

            <xsd:attribute name="isOutput" type="xsd:boolean" />

         </xsd:extension>

      </xsd:simpleContent>

   </xsd:complexType>

</xsd:element>

I use this element to send a PDF, which I converted to hex before with the method below:

private String convertToHex (byte[] byteData) {

  char[] hexArray = {'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'};

  //byte [] byteData = var1.getBytes();

  char [] hexChars = new char [byteData.length * 2];

  int v;

  for (int j = 0; j < byteData.length; j++) {

  v = byteData[j] & 0xFF;

  hexChars[j * 2] = hexArray[v >>> 4];

  hexChars[j * 2 + 1] = hexArray[v & 0x0F];

  }

  return new String (hexChars);

}

And to get it working with an Oracle DB (don't know about other DB's), I had to add a prefix of 1 random byte to the HEX string.

Hope this helps.

Kind Regards

Edmond Paulussen

Former Member
0 Kudos

Thank You Edmond

Now I am able to insert blob data into oracle table but the pdf file generated from there is not getting opened.

it is giving message that it is either not a supported file or damaged file.

former_member245887
Participant
0 Kudos

Hi,

Did you add a prefix of 1 (random) byte to the hexString?

Here is what I do in a Java Mapping

String hexStr = new String("aa");

...

String contentID = itCol.next().toString();

Attachment pdfData = attachments.getAttachment(contentID);

byte[] byteData = pdfData.getContent();

hexStr = hexStr.concat(convertToHex(byteData));

// Attach new node with the content of the pdf to the XML

Element PDF = new Element("PDF");

PDF.addContent(hexStr);

payload.addContent(PDF);

After the java mapping comes a graphical message mapping where the content of element PDF is assigned to SDS (see my previous post).

Kind Regards

Edmond Paulussen

Former Member
0 Kudos

Hi

I am not using java mapping actually. Can I use the logic of adding 1 random byte in UDF ?

former_member245887
Participant
0 Kudos

Hi,

You can try it.

But it's my experience that it's tricky. Converting byte[] to string, pass them to the next UDF, convert back to byte[], etc

For my java mapping is much more reliable in such cases.

Kind Regards

Edmond Paulussen

Former Member
0 Kudos

Hi Edmond

I will try to explain my whole situation

Step 1:

call function 'BAPI_DOCUMENT_GETDETAIL2'

      exporting

        documenttype    = wa_drap-dokar

        documentnumber  = wa_drap-doknr

        documentpart    = wa_drap-doktl

        documentversion = wa_drap-dokvr

        getdocfiles     = 'X'

      importing

        return          = ls_return

      tables

        documentfiles   = t_file.

Step 2:

call function 'SCMS_DOC_READ'

        exporting

          mandt                 = sy-mandt

          stor_cat              = wa_file-storagecategory

          doc_id                = wa_file-file_id

        tables

          access_info           = lt_access

          content_bin           = lt_sdokcntbin

        exceptions

          bad_storage_type      = 1

          bad_request           = 2

          unauthorized          = 3

          comp_not_found        = 4

          not_found             = 5

          forbidden             = 6

          conflict              = 7

          internal_server_error = 8

          error_http            = 9

          error_signature       = 10

          error_config          = 11

          error_format          = 12

          error_parameter       = 13

          error                 = 14

          others                = 15.

Step 3: 

call function 'SCMS_BINARY_TO_XSTRING'

          exporting

            input_length = l_strlen

          importing

            buffer       = w_bindata

          tables

            binary_tab   = lt_sdokcntbin

          exceptions

            failed       = 1

            others       = 2.

Step 4:

Passign w_bindata to PI proxy (field type hexbinary in PI)

Step 5:

Passign it in UDF

byte[] stringArray;

stringArray = var1.getBytes();

String hexStr = new String("aa"); //(not sure when to append this prefix))

char[] hexArray = {'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'};

  char [] hexChars = new char [stringArray.length * 2];

  int v;

  for (int j = 0; j < stringArray.length; j++) {

  v = stringArray[j] & 0xFF;

  hexChars[j * 2] = hexArray[v >>> 4];

  hexChars[j * 2 + 1] = hexArray[v & 0x0F];

}

return new String (hexChars);

Step 6:

Passing output of UDF to procedure in oracle that inserts data into blob field in oracle.

former_member245887
Participant
0 Kudos

Hi,

Ask I wrote in my previous post: it is tricky to do such conversions in UDF.

In such cases I always use a java mapping.

Kind Regards

Edmond Paulussen

Former Member
0 Kudos

Hi

Is there anyway we can use  java mapping without NWDS.

We are currently using graphical mapping in ESR.

Regards

Puja

Answers (0)