web stats
Using postgres function in sql where clause doesn't work - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2014, 07:24 AM
JoeFox JoeFox is offline
Mirth Newb
 
Join Date: Jan 2014
Posts: 14
JoeFox is on a distinguished road
Question 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!
Reply With Quote
  #2  
Old 01-07-2014, 07:26 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 01-07-2014, 09:37 PM
JoeFox JoeFox is offline
Mirth Newb
 
Join Date: Jan 2014
Posts: 14
JoeFox is on a distinguished road
Exclamation

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.
Reply With Quote
  #4  
Old 01-08-2014, 06:18 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,123
narupley is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 01-09-2014, 12:38 AM
JoeFox JoeFox is offline
Mirth Newb
 
Join Date: Jan 2014
Posts: 14
JoeFox is on a distinguished road
Question

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?
Reply With Quote
  #6  
Old 01-09-2014, 05:50 AM
panickc panickc is offline
OBX.3 Kenobi
 
Join Date: Dec 2007
Posts: 127
panickc is an unknown quantity at this point
Default

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
Reply With Quote
  #7  
Old 01-12-2014, 03:54 AM
JoeFox JoeFox is offline
Mirth Newb
 
Join Date: Jan 2014
Posts: 14
JoeFox is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 01-13-2014, 06:57 AM
JoeFox JoeFox is offline
Mirth Newb
 
Join Date: Jan 2014
Posts: 14
JoeFox is on a distinguished road
Exclamation

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!
Reply With Quote
Reply

Tags
functions, postgres, query, sql

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 11:15 PM.


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