Announcement

Collapse
No announcement yet.

Using postgres function in sql where clause doesn't work

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

  • Using postgres function in sql where clause doesn't work

    Hi all,

    working with Mirth Connect Server 3.0.0.6931 I'm building a transformer for a destination.

    When I use the following sql statement as query, no result will return and no error occurs.

    Code:
    // Getting the case
    var fallnummer = msg['PV1']['PV1.19']['PV1.19.1'].toString();
    
    // SQL query
    var sql = "SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM careteam_member c, gpartner g WHERE c.fall_id = get_fall_id('"+fallnummer+"') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;";
    
    // Execute Query
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver', 'jdbc:postgresql://host/db', 'user', 'pass');
    var result = dbConn.executeCachedQuery(sql);
    I'm using PostgreSQL 9.0. When executing query in postgres directly, one row will return als result.

    Don't postgres functions work in the where clause or did I do something wrong?

    Thanks for your help!

  • #2
    You need to actually do something with that variable "result" after you execute the query. More info: http://docs.oracle.com/javase/7/docs...ResultSet.html
    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


    • #3
      I do something. Here is my complete code:

      Code:
      var count = 0;
      var setID = 1;
      
      // Holle die Fallnummer
      var fallnummer = msg['PV1']['PV1.19']['PV1.19.1'].toString();
      
      // SQL-Abfrage
      var sql = "SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = get_fall_id('"+fallnummer+"') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;";
      
      // Verbinde zur Datenbank
      var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver', 'jdbc:postgresql://host:port/db', 'user', 'pass');
      var result = dbConn.executeCachedQuery(sql);
      
      // Gehe jedes Result-Zeile durch
      while (result.next())
      {
      	// Variablen speichern
      	var caretype = result.getString(1);
      	var lanr = result.getString(2);
      	var name = result.getString(3);
      	var vorname = result.getString(4);
      	var titel = result.getString(5);
      	var von = result.getString(6);
      	var strasse = result.getString(7);
      	var ort = result.getString(8);
      	var plz = result.getString(9);
      
      	// Alle ROL Segmente unter das PV1
      	if(count == '0')
      		createSegmentAfter('ROL', msg.PV1);
      	else
      		createSegmentAfter('ROL', msg.ROL[count-1]);
      
      	msg['ROL'][count]['ROL.1']['ROL.1.1'] = setID.toString();
      	msg['ROL'][count]['ROL.3']['ROL.3.1'] = caretype;
      	msg['ROL'][count]['ROL.4']['ROL.4.1'] = lanr;
      	msg['ROL'][count]['ROL.4']['ROL.4.2'] = name;
      	msg['ROL'][count]['ROL.4']['ROL.4.3'] = vorname;
      	msg['ROL'][count]['ROL.4']['ROL.4.7'] = titel;
      	msg['ROL'][count]['ROL.4']['ROL.4.14'] = lanr;
      	msg['ROL'][count]['ROL.5']['ROL.5.1'] = von;
      	msg['ROL'][count]['ROL.6']['ROL.6.1'] = "99991231";
      	msg['ROL'][count]['ROL.11']['ROL.11.1'] = strasse;
      	msg['ROL'][count]['ROL.11']['ROL.11.3'] = ort;
      	msg['ROL'][count]['ROL.11']['ROL.11.5'] = plz;
      
      	count++;
      	setID++;
      }
      
      result.close();
      dbConn.close();
      When replacing the sql function
      Code:
      get_fall_id('"+fallnummer+"')
      to a fix value like
      Code:
      var sql = "SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = 207311 AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;";
      I get 1 row as result.

      In my sample message the variable
      Code:
      fallnummer
      is 13356881 and the function
      Code:
      get_fall_id('13356881')
      returns 207311. So when I run the sql
      Code:
      SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = get_fall_id('13356881') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;
      in my postgres tool it returns 1 row as result.

      So I think the problem is, that I can't use functions like
      Code:
      get_fall_id('"+fallnummer+"')
      in my sql statement. Is that correct or did I do something wrong.

      Comment


      • #4
        You should be able to use functions like that just fine. You're already doing it anyway with to_char, and I'm assuming that isn't breaking it. Try echoing out the "sql" variable to see exactly what it contains.
        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


        • #5
          I'm using the following HL7 message to test the code:

          Code:
          MSH|^~\&|i1 IS-OUT|Ein1^260710087|MARIS||20130611081843||ADT^A04|1234567|P|2.4|||AL|NE|DEU||
          EVN|A04|20130611081826|||roelingc|20130611081826
          PID|1||133085223^^^Ein1^PI~^^^^BSN||Test^Testvorname^^^^^L^A~^^^^^^B^A||19740101|M|||^^^^^D^H~^^^^^^O^^||^PRN^PH^^^^~^PRN^FX^^^^~^PRN^CP^^^^~^NET^X.400^^^^^PRN^PH^^^^~^PRN^FX|^WPN^PH^^^^^WPN^PH^^^^^^|Deutsch|||||||||N||||||N
          PV1|1|O|AMB^^^CA^^N|AmbUntersuch||^^^^^N|^^^^^|^^^^^|^^^^^|MVZ||J||||0|^^^^^||133341448^^^Ein1^VN|nullStandard^||||||||||||||||||||||||20130611081400||||||||
          IN1|1|AOK^Allgemeine Ortskrankenkasse|6315784^^^AOK Rheinland-Pfalz/Saarland^NII~410828^^^AOK Rheinland-Pfalz/Saarland^NIIP|null^AOK Rheinland-Pfalz/Saarland^^^^^^^|M├╝hlenbachstrasse 15^^Sinzig^^53489^^||02()642-9776XBC^^^^^^^^|||||20081105|99991231||F^||||^^^^^^^||||||||||||||||||||||||||||||^^^^^^^ 
          ZBE|1028024|20130611081400||A
          I've logged the sql query content before excetuing it:

          Code:
          var count = 0;
          var setID = 1;
          
          // Holle die Fallnummer
          var fallnummer = msg['PV1']['PV1.19']['PV1.19.1'].toString();
          
          // SQL-Abfrage
          var sql = "SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = get_fall_id('"+fallnummer+"') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;";
          
          logger.info(sql);
          
          // Verbinde zur Datenbank
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver', 'jdbc:postgresql://172.17.8.214:6903/marisdb', 'postgres', 'hSp2000');
          var result = dbConn.executeCachedQuery(sql);
          
          // Gehe jedes Result-Zeile durch
          while (result.next())
          {
          	// Variablen speichern
          	var caretype = result.getString(1);
          	var lanr = result.getString(2);
          	var name = result.getString(3);
          	var vorname = result.getString(4);
          	var titel = result.getString(5);
          	var von = result.getString(6);
          	var strasse = result.getString(7);
          	var ort = result.getString(8);
          	var plz = result.getString(9);
          
          	// Alle ROL Segmente unter das PV1
          	if(count == '0')
          		createSegmentAfter('ROL', msg.PV1);
          	else
          		createSegmentAfter('ROL', msg.ROL[count-1]);
          
          	msg['ROL'][count]['ROL.1']['ROL.1.1'] = setID.toString();
          	msg['ROL'][count]['ROL.3']['ROL.3.1'] = caretype;
          	msg['ROL'][count]['ROL.4']['ROL.4.1'] = lanr;
          	msg['ROL'][count]['ROL.4']['ROL.4.2'] = name;
          	msg['ROL'][count]['ROL.4']['ROL.4.3'] = vorname;
          	msg['ROL'][count]['ROL.4']['ROL.4.7'] = titel;
          	msg['ROL'][count]['ROL.4']['ROL.4.14'] = lanr;
          	msg['ROL'][count]['ROL.5']['ROL.5.1'] = von;
          	msg['ROL'][count]['ROL.6']['ROL.6.1'] = "99991231";
          	msg['ROL'][count]['ROL.11']['ROL.11.1'] = strasse;
          	msg['ROL'][count]['ROL.11']['ROL.11.3'] = ort;
          	msg['ROL'][count]['ROL.11']['ROL.11.5'] = plz;
          
          	count++;
          	setID++;
          }
          
          result.close();
          dbConn.close();
          Logger says:
          [2014-01-09 10:26:49,433] INFO (transformer:?): SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = get_fall_id('133341448') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;

          And the output HL7 message is:
          Code:
          MSH|^~\&|i1 IS-OUT|Ein1^260710087|MARIS||20130611081843||ADT^A04|8272246|P|2.4|||AL|NE|DEU||
          EVN|A04|20130611081826|||roelingc|20130611081826
          PID|1||133085223^^^Ein1^PI~^^^^BSN||Test^Testvorname^^^^^L^A~^^^^^^B^A||19740101|M|||^^^^^D^H~^^^^^^O^^||^PRN^PH^^^^~^PRN^FX^^^^~^PRN^CP^^^^~^NET^X.400^^^^^PRN^PH^^^^~^PRN^FX|^WPN^PH^^^^^WPN^PH^^^^^^|Deutsch|||||||||N||||||N
          PV1|1|O|AMB^^^CA^^N|AmbUntersuch||^^^^^N|^^^^^|^^^^^|^^^^^|MVZ||J||||0|^^^^^||133341448^^^Ein1^VN|nullStandard^||||||||||||||||||||||||20130611081400||||||||
          IN1|1|AOK^Allgemeine Ortskrankenkasse|6315014^^^AOK Rheinland-Pfalz/Saarland^NII~410828^^^AOK Rheinland-Pfalz/Saarland^NIIP|null^AOK Rheinland-Pfalz/Saarland^^^^^^^|M├╝hlenbachstrasse 15^^Sinzig^^53489^^||02()642-9776XBC^^^^^^^^|||||20081105|99991231||F^||||^^^^^^^||||||||||||||||||||||||||||||^^^^^^^ 
          ZBE|1028024|20130611081400||A
          The function get_fall_id(character varying) is:
          Code:
          CREATE OR REPLACE FUNCTION get_fall_id(character varying)
            RETURNS integer AS
          $BODY$DECLARE
            rec  RECORD;
            BEGIN
          
              SELECT INTO rec *
              FROM t_kis_fall
              WHERE hostcode = $1;
          
              IF NOT FOUND
              THEN
                RETURN NULL;
              END IF;
          
              RETURN rec.id;
          
            END;$BODY$
            LANGUAGE plpgsql VOLATILE
            COST 100;
          ALTER FUNCTION get_fall_id(character varying) OWNER TO postgres;
          So when I replace get_fall_id('133341448') in the sql statement with the number 203711 (what the function retuns executing it in postgres directly). The output HL7 message is:

          Code:
          MSH|^~\&|i1 IS-OUT|Ein1^260710087|MARIS||20130611081843||ADT^A04|8272246|P|2.4|||AL|NE|DEU||
          EVN|A04|20130611081826|||roelingc|20130611081826
          PID|1||133085223^^^Ein1^PI~^^^^BSN||Test^Testvorname^^^^^L^A~^^^^^^B^A||19740101|M|||^^^^^D^H~^^^^^^O^^||^PRN^PH^^^^~^PRN^FX^^^^~^PRN^CP^^^^~^NET^X.400^^^^^PRN^PH^^^^~^PRN^FX|^WPN^PH^^^^^WPN^PH^^^^^^|Deutsch|||||||||N||||||N
          PV1|1|O|AMB^^^CA^^N|AmbUntersuch||^^^^^N|^^^^^|^^^^^|^^^^^|MVZ||J||||0|^^^^^||133341448^^^Ein1^VN|nullStandard^||||||||||||||||||||||||20130611081400||||||||
          ROL|1||1|930514201^Kunz,Ch. & Schiek-Kunz^null^^^^Dres.^^^^^^^930514201|20120321|99991231|||||Saalfelder Str. 13^^Frankfurt^^65931
          ROL|2||2|930514201^Kunz,Ch. & Schiek-Kunz^null^^^^Dres.^^^^^^^930514201|20120321|99991231|||||Saalfelder Str. 13^^Frankfurt^^65931
          IN1|1|AOK^Allgemeine Ortskrankenkasse|6315014^^^AOK Rheinland-Pfalz/Saarland^NII~410828^^^AOK Rheinland-Pfalz/Saarland^NIIP|null^AOK Rheinland-Pfalz/Saarland^^^^^^^|M├╝hlenbachstrasse 15^^Sinzig^^53489^^||02()642-9776XBC^^^^^^^^|||||20081105|99991231||F^||||^^^^^^^||||||||||||||||||||||||||||||^^^^^^^ 
          ZBE|1028024|20130611081400||A
          So I think there's something wrong using the function in the sql query!

          I tried a workaround using this SQL statement:
          [2014-01-09 10:26:49,433] INFO (transformer:?): SELECT c.caretype, g.lanr, g.name, g.vorname, g.titel, to_char(c.von, 'YYYYMMDD'), strasse, ort, plz FROM v_maris_careteam_member c, v_stamm_gpartner g WHERE c.fall_id = (SELECT id FROM v_kis_fall WHERE hostcode = '133341448') AND c.bis is null AND c.gpartner_id = g.id AND g.typ = 1;

          The result is the same as I use the function! Is it possible that there is aproblem using brackets () in the sql statement?

          Comment


          • #6
            You say you used 203711 in your test query that worked, why didn't you use 133341448? Because if 133341448 isn't in your table, then this query would return 0 rows and 0 errors.

            Just trying to assist you in your analysis.

            -cp

            Comment


            • #7
              The function
              Code:
              get_fall_id('casenumber')
              returns an internal id of the case, as you see in the function.
              Code:
              c.fall_id
              is a foreign key of the column
              Code:
              id
              in
              Code:
              t_kis_fall
              .

              So there's everything right but thank you for your reply!

              As I said, it seems that there are problems using brackets. Could this be?

              Comment


              • #8
                Thread can be closed!

                It was my fault! In the JDBC connection string I used the wrong IP!

                So simple...but didn't see it till now!

                Comment

                Working...
                X