on 09-09-2013 3:58 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.