2

I am new to the Mule ESB and I am trying to find a solution to what is hopefully a common business requirement.

I have a payload where one of its elements will be inserted into an Oracle database as a BLOB type. I have seen several discussions about setting up a "custom-transformer" where I could create Java code to convert the payload into a byte-array (also saw that Mule has this build in transformer object-to-byte-array).

Is there a way with a payload to isolate the map-payload element (in this case an "attachment" file) and convert it into a byte-array which then simply becomes an element(along with the rest of the other map-payload elements) in the SQL insert statement I have created?

Not sure if Mule also supports an in-line transformer on an element without calling/referencing a "custom-transformer"? I have seen several posting struggling with this issue, but I have not seen an answer that I can readily apply.

General Grievance
  • 4,555
  • 31
  • 31
  • 45
RAYMOND MCMANEMIN
  • 21
  • 3
  • I don't understand the type of message you're dealing with: you're saying the message payload is a `java.util.Map` and one of it's entry has a value that is a `java.io.File` that points to a file? Or is it something else? – David Dossot Mar 15 '13 at 16:16
  • the problem: I have a "jdbc:inbound" selecting records from one database table, and then a "jdbc:outbound" that will use these map-payload elements from the first table and turn around and insert these records into a different/second database table. One of the map-payload elements that I seem to have problems with is a BLOB Oracle database type. When I include this element in the "outbound" insert call, it throws the message "table or view does not exist". When I leave this specific BLOB type element off the elements being inserted, it is a clean insert to the database table. Thanks Dave. – RAYMOND MCMANEMIN Mar 15 '13 at 21:22
  • Thanks for the extra precision: a flow XML in your question would have helped. May I ask you to check the Java type of the BLOB field in the Mule message created by the JDBC inbound? – David Dossot Mar 15 '13 at 21:25
  • Dave the person running this flow has left already so I will try to get back to you on those details; just didn't want you waiting for the response right away. Thanks. – RAYMOND MCMANEMIN Mar 15 '13 at 21:31
  • I was able to successfully test a BLOB insert from source to target database without any problems. It seems that the issue may now lie with trying to send a BLOB through the firewall - not sure if the Firewall restricts BLOBs in general or if they use a specific port to transmit a BLOB? Googling still - but any suggestions/direction still appreciated. – RAYMOND MCMANEMIN Apr 01 '13 at 18:47
  • Update - the problem seems to be that whenever I am selecting and then inserting between two different database environments I am getting the Oracle 00942 error which maybe due to the BLOB selected from the source database has NOT completely "uploaded" before I am doing my INSERT of this BLOB against another database on a different server. Any thoughts on a BLOB having extra overhead between two different databases before trying to do an INSERT with it (I am NOT seeing this error when the SELECT and INSERT are against tables in the same database)? – RAYMOND MCMANEMIN Apr 10 '13 at 16:18
  • 1
    Could you either post the solution as an answer or close/delete your question? – David Dossot Apr 25 '13 at 19:15
  • The solution for passing a BLOB from one database to another was with a java component where the PreparedStatement used "setBinaryStream" instead of "setBlob" and this worked. The example syntax is 3 lines of Java code where "blob_attachment" is taken from the payload: int len = (int) blob_attachment.length(); byte[] data = blob_attachment.getBytes(1, len); dbSelect.setBinaryStream(11, new ByteArrayInputStream(data),data.length); Hope this helps anyone encountering this problem; it took me a bit to discover this. – RAYMOND MCMANEMIN May 08 '13 at 21:26
  • Please don't use comments to write answers. Comments may be deleted at any time. – General Grievance Apr 07 '22 at 14:30

0 Answers0