web stats
Access Blob from database - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2012, 12:24 PM
pbugni pbugni is offline
Mirth Newb
 
Join Date: Sep 2008
Posts: 11
pbugni
Default Access Blob from database

Attempting to read a BLOB from an external database (tried using both MySQL and PostgreSQL), produces the JDBC Connector error:

org.mozilla.javascript.WrappedException: Wrapped java.sql.SQLException: Data Type Mismatch


This happens using either getBlob() or getClob() on the BLOB column. (datatype 'LONGBLOB' in MySQL, 'bytea' in PostgreSQL)

The pertinent snippet from the javascript channel destination:

var qs = "SELECT patient_clinical_id, cda FROM CDA WHERE patient_clinical_id = '" + $('id') + "'";
result = dbConn.executeCachedQuery(qs);

while(result.next()) {
var pid = result.getString(1);
var cda = result.getBlob(2);
}
dbConn.close();


If, I use a "getString(2)" statement for the BLOB column, I get back a string that looks like an object address, i.e. [B@5a65eba5

Is there a trick to loading the blob into the mirth channel space?

Thanks!
Reply With Quote
  #2  
Old 03-07-2012, 12:54 PM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

You can try something like this....albeit it is for SQL, but it might give you an idea on how to proceed

Code:
===========
var expression = 'SELECT DocumentText FROM DocumentList WHERE DocumentID = 1' ; 

var result = dbConn.executeCachedQuery(expression);

result.next(); 
var cl = result.getClob(1);

var strOut = new java.lang.StringBuffer();
var aux = new java.lang.String();

// We access to stream, as this way we don't have to use the CLOB.length() which is slower...
var br = new java.io.BufferedReader(cl.getCharacterStream());

while ((aux = br.readLine())!= null)
strOut.append(aux);

localMap.put('gDocTextString', strOut);
logger.info('gDocTextString is: ' + globalMap.get('gDocTextString'));

==========
Reply With Quote
  #3  
Old 03-07-2012, 01:01 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

I usually use getBytes/setBytes for blobs, perhaps that'll work for you...
Reply With Quote
  #4  
Old 03-07-2012, 02:40 PM
pbugni pbugni is offline
Mirth Newb
 
Join Date: Sep 2008
Posts: 11
pbugni
Default

Quote:
Originally Posted by upstart33 View Post
You can try something like this....albeit it is for SQL, but it might give you an idea on how to proceed

Code:
===========
var expression = 'SELECT DocumentText FROM DocumentList WHERE DocumentID = 1' ; 

var result = dbConn.executeCachedQuery(expression);

result.next(); 
var cl = result.getClob(1);

var strOut = new java.lang.StringBuffer();
var aux = new java.lang.String();

// We access to stream, as this way we don't have to use the CLOB.length() which is slower...
var br = new java.io.BufferedReader(cl.getCharacterStream());

while ((aux = br.readLine())!= null)
strOut.append(aux);

localMap.put('gDocTextString', strOut);
logger.info('gDocTextString is: ' + globalMap.get('gDocTextString'));

==========
Any invocation of getClob() raises the same exception here.
Reply With Quote
  #5  
Old 03-07-2012, 03:17 PM
pbugni pbugni is offline
Mirth Newb
 
Join Date: Sep 2008
Posts: 11
pbugni
Default

Quote:
Originally Posted by narupley View Post
I usually use getBytes/setBytes for blobs, perhaps that'll work for you...
Thanks for the tip. Indeed, getBytes() gets me closer, but I'm failing to convert the byte array back to a string. If you have some sample code that works for this, please post.

BUT, I'm not sure why we chose to store the CDA in a BLOB in the first place. I've found using the MySQL LONGTEXT type (or PostgreSQL TEXT) achieves the same goal, and in such a case getString() just works.
Reply With Quote
  #6  
Old 03-07-2012, 03:32 PM
dans dans is offline
Mirth Employee
 
Join Date: Apr 2007
Location: Irvine, CA
Posts: 590
dans is an unknown quantity at this point
Default

try this:
Code:
var cda = new java.lang.String(result.getBytes(2));
__________________
Daniel Svanstedt
Software Engineer
Mirth Corporation

Want professional services, support, and enterprise or virtual appliances? It's all available from the Mirth Corporation:
Mirth Support | Mirth Training | Mirth Appliances | Online Training | Developer Q&A

Don't forget, Mirth Support gives you access to all of our online training videos, and silver support gives you access to developer Q&As!
Reply With Quote
  #7  
Old 04-05-2017, 08:00 AM
mutz mutz is offline
What's HL7?
 
Join Date: Oct 2016
Posts: 4
mutz is on a distinguished road
Default Access Bytea from a Postgresql database table

We were able to return the text version of a bytea field in Mirth Connect Channel by using a encode statement with the Select.

It then returned the text of the field a.data (bytea) as ASCII text ccd_data

select f.label pract_name, n.last, n.first, n.middle,
encode(a.data::bytea, 'escape') as ccd_data,
a.document_oid, a.clinical_item_key
from attachment a, facility f, clinical_item ci, subject s, name n
where a.document_oid = '<somedocumentID>'
and a.clinical_item_key = ci.clinical_item_key
and ci.source_facility_key = f.facility_key
and ci.subject_key = s.subject_key
and s.current_name_key = n.name_key

Thanks

Mike
Reply With Quote
Reply

Tags
blob

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 10:19 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation