Announcement

Collapse
No announcement yet.

OBX Segments to database - Information displayed as [Ljava.lang.Object;@.

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

  • OBX Segments to database - Information displayed as [Ljava.lang.Object;@.

    Hello,

    I´m a newbie trying to get certain fields from an HL7 message for a variable number of OBX segments, I´m using a loop for this and then write everything to a postgres database.

    I´m using the following Mirth software:

    Mirth Connect Server 3.8.1
    Built on September 12, 2019
    Java version: 1.8.0_181

    The iteration works well and the different values are stored in an array, the information gets updated to the database but when reading
    it is always found as [Ljava.lang.Object;@....

    Code:
    INSERT INTO sample (num_orden, tipodoc, documento, cod_examen, num_peticion, fecha_resultado, cod_analito, nom_analito, resultado1, vr_minimo, vr_maximo, unidades, usu_valida, estado, resultado, idunica, nom_examen, fechaordenado, nom_medico_ordena)
    VALUES ('num_orden', 'Docu', ${PatientID}, 'cod_examen', 'num_peticion', ${ODate}, 'cod_analito', ${x}, ${OV}, ${RefMin}, ${RefMax}, ${Units}, 'usu_valida', 'estado', 'resultado', 'idunica', ${x}, 'fechaordenado', 'nom_medico_ordena')

    So I´ve tried using different data types (from charvar to text) but the result is always the same.

    I´ve also tried using a javascript there are no errors when deploying but nothing is written to de database.

    Code:

    Code:
    var dbConn;
    try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection ('org.postgresql.Driver','jdbc:postgresql://localhost:5432/production','postgres','postgres').getConnection() ;
    var ps = dbConn.prepareStatement("INSERT IGNORE INTO sample (num_orden, tipodoc, documento, cod_examen, num_peticion, fecha_resultado, cod_analito, nom_analito, resultado1, vr_minimo, vr_maximo, unidades, usu_valida, estado, resultado, idunica, nom_examen, fechaordenado, nom_medico_ordena) VALUES ('num_orden', 'Docu', $('Cedula'), 'cod_examen', 'num_peticion', $('xOD'), 'cod_analito', $('x'), $('xOV'), $('xRefMin'), $('xRefMax'), $('xUnits'), 'usu_valida', 'estado', 'resultado1', 'idunica', $('x'), 'fechaordenado', 'nom_medico_ordena')");
    ps.setArray(1, dbConn.createArrayOf('varchar', ['num_orden', 'Docu', $('Cedula'), 'cod_examen', 'num_peticion', $('xOD'), 'cod_analito', $('x'), $('xOV'), $('xRefMin'), $('xRefMax'), $('xUnits'), 'usu_valida', 'estado', 'resultado', 'idunica', $('x'), 'fechaordenado', 'nom_medico_ordena']));
    ps.execute();
    } finally {
    if (dbConn)
    try { dbConn.close(); } catch(e) {}
    }

    I´m a little lost getting the best way to have the specific field ordered as required.


    Can you please guide me to solve this issue?

    I would really appreciate some light in this.

    Attached Files

  • #2
    I've done only a little w/a SQL DB and nothing w/Postgres but it looks like the literal objects may be getting inserted into the DB instead of their values. Including a JavaScript SQL insertion from a channel I developed where the VALUES format is '"+<object variable>+"'.
    Code:
    dbConn.executeUpdate("INSERT INTO tblPatientVis (internal_id, last_name, first_name, middle_name, patient_class,  attend_phys_id, visit_number, admit_date, discharge_date) VALUES ('"+pat.id.internal+"','"+pat.name.last+"','"+pat.name.first+"','"+pat.name.middle+"','"+vis.pat_class+"','"+vis.attend_phys_id+"','"+vis.number+"','"+vis.admit_date+"','"+vis.discharge_date+"');")
    Last edited by jkrebs; 05-26-2020, 02:18 PM.

    Comment


    • #3
      Curious what you're trying to do. I've never seen sql arrays before. What database engine is this? in your javascript example, I don't see a ? in your prepared statement. When you are calling setArray you have a javascript array embedded in there, which I'm pretty certain will not work.

      Comment


      • #4
        Thanks for your responses, I was trying to complete a new code but the thing is that I always get an error when trying to write to de database. So I´m trying the following code that compiles and works, but the information is always displayed as "[Ljava.lang.Object;@".


        These are the two lines that I´m using for writing to the postgres db.
        Code:
        INSERT INTO sample (num_orden, tipodoc, documento, cod_examen, num_peticion, fecha_resultado, cod_analito, nom_analito, resultado1, vr_minimo, vr_maximo, unidades, usu_valida, estado, resultado, idunica, nom_examen, fechaordenado, nom_medico_ordena)
        VALUES ('num_orden', 'Docu', ${PatientID}, 'cod_examen', 'num_peticion', ${ODate}, 'cod_analito', ${x}, ${OV}, ${xRefMin}, ${RefMax}, ${Units}, 'usu_valida', 'estado', 'resultado', 'idunica', ${x}, 'fechaordenado', 'nom_medico_ordena' )

        But I´m still having the same problem.


        I did changed the script but maybe there´s a problem with the sintaxis, beacuse I can deploy the channel but nothing is written. This is the code that I´m also using:


        Code:
        var dbConn;
        try {
        dbConn = DatabaseConnectionFactory.createDatabaseConnection ('org.postgresql.Driver','jdbc:postgresql://localhost:5432/production','postgres','postgres').getConnection() ;
        var ps = dbConn.prepareStatement("INSERT IGNORE INTO sample (num_orden, tipodoc, documento, cod_examen, num_peticion, fecha_resultado, cod_analito, nom_analito, resultado1, vr_minimo, vr_maximo, unidades, usu_valida, estado, resultado, idunica, nom_examen, fechaordenado, nom_medico_ordena) VALUES (?)");
        ps.setArray(1, dbConn.createArrayOf('varchar', $('x')));
        ps.executeUpdate();
        
        } finally {
        if (dbConn)
        try { dbConn.close(); } catch(e) {}
        }
        What sintax am I missing in the preparedStatement line?

        Really appreciate your help.

        Comment


        • #5
          I don't understand why you are creating and passing arrays. I asked what database engine you are using. Do you have fields defined with an array type? Can you share the schema of the table into which you are trying to insert?

          How are you creating your map variables, and what do they hold? The [Ljava.lang.Object;@289d1c02 you are seeing is the toString() representation of a Java Array.

          Comment


          • #6
            Hi Agermano,

            I´m using postgres as the database engine. I´ve defined the fields as text, charvar but always getting the Ljava.lang.


            This is the schema for tha table that I´m using.


            production=# \d sample
            Tabla ½public.sample╗
            Columna | Tipo | Ordenamiento | Nulable | Por omisi¾n
            -------------------+------+--------------+---------+-------------
            num_orden | text | | |
            tipodoc | text | | |
            documento | text | | |
            cod_examen | text | | |
            num_peticion | text | | |
            fecha_resultado | text | | |
            cod_analito | text | | |
            nom_analito | text | | |
            resultado1 | text | | |
            vr_minimo | text | | |
            vr_maximo | text | | |
            unidades | text | | |
            usu_valida | text | | |
            estado | text | | |
            resultado | text | | |
            idunica | text | | |
            nom_examen | text | | |
            fechaordenado | text | | |
            nom_medico_ordena | text | | |


            I´m defining variables with this script:

            Code:
            var _x = Lists.list();
            var _OV = Lists.list();
            var _Units = Lists.list();
            var _RefMin = Lists.list();
            var _RefMax = Lists.list();
            for (var i = 0; i < getArrayOrXmlLength(msg['OBX']); i++) {
            
            var mapping;
            
            try {
            mapping = msg['OBX'][i]['OBX.3']['OBX.3.4'].toString();
            } catch (e) {
            logger.error(e);
            mapping = '';
            }
            
            _x.add(validate(mapping, '', new Array()));
            
            var mapping;
            
            try {
            mapping = msg['OBX'][i]['OBX.5']['OBX.5.1'].toString();
            } catch (e) {
            logger.error(e);
            mapping = '';
            }
            
            _OV.add(validate(mapping, '', new Array()));
            
            var mapping;
            
            try {
            mapping = msg['OBX'][i]['OBX.6']['OBX.6.1'].toString();
            } catch (e) {
            logger.error(e);
            mapping = '';
            }
            
            _Units.add(validate(mapping, '', new Array()));
            
            var mapping;
            
            try {
            mapping = msg['OBX'][i]['OBX.7']['OBX.7.1'].toString();
            } catch (e) {
            logger.error(e);
            mapping = '';
            }
            
            _RefMin.add(validate(mapping, '', new Array()));
            
            var mapping;
            
            try {
            mapping = msg['OBX'][i]['OBX.7']['OBX.7.2'].toString();
            } catch (e) {
            logger.error(e);
            mapping = '';
            }
            
            _RefMax.add(validate(mapping, '', new Array()));
            
            }
            globalChannelMap.put('x', _x.toArray());
            globalChannelMap.put('OV', _OV.toArray());
            globalChannelMap.put('Units', _Units.toArray());
            globalChannelMap.put('RefMin', _RefMin.toArray());
            globalChannelMap.put('RefMax', _RefMax.toArray());


            The messages are originated by an analyzer and OBX values are not constant, that why I used the loop. But my problem is that everything is created as an array and when reading from the db it´s not the specific value that needed for each OBX segment.

            I hope that this little information helps, Thanks for your support.

            Comment


            • #7
              You can't do it in a single INSERT statement. You need to loop over your arrays and perform an INSERT for each row. What you are seeing is an attempt to convert the array to text by calling .toString() on it in order to match the type of the column.

              Comment

              Working...
              X