Announcement

Collapse
No announcement yet.

Access Blob from database

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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. [[email protected]

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

    Thanks!

  • #2
    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'));
    
    ==========

    Comment


    • #3
      I usually use getBytes/setBytes for blobs, perhaps that'll work for you...
      Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

      Nicholas Rupley
      Work: 949-237-6069
      Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


      - How do I foo?
      - You just bar.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #6
            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!

            Comment


            • #7
              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

              Comment

              Working...
              X