2nd While Loop with result.next(); throws invalid pointer error

[areutera]

Hello there!

Accessing a custom database I am trying to build BAR P12 messages with an unknown number of DG1 segments. The database does not contain hl7 messages, but keeps the the element information in lines with id, messageid, segment, element, subelement, content, and segementsetid as columns.

While I may have a workaround for my problem by fudging with the SQL statement (it's very much simplified here for development/testing purposes) I'd like to better understand how to access the array my database query is giving me.

This code actually works (abbreviated)

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver', 'jdbc:postgresql://localhost:****/****', '****', '****');
var sqlCommand = "SELECT * FROM **** WHERE messageid IN ( '78673004', '78702620', '78945674', '78945719', '78945680' ) ORDER BY id DESC";
var loaded = dbConn.executeCachedQuery(sqlCommand);

var dgcounter = 0;
var hauptdiagnoseid = "";

while(loaded.next() ) { // 1. Durchgang, um Anzahl der DG1-Elemente und Hauptdiagnose zu ermitteln
	var messageid = loaded.getString("messageid");
	var segment = loaded.getString("segment");
	var element = loaded.getString("element");
	var content = loaded.getString("content");
	var segmentsetid = loaded.getString("segmentsetid");

if ( "DG1" == segment && "15" == element && "1" == content) {	
hauptdiagnoseid = messageid;
logger.info("MessageID Primary Diagnosis: "+hauptdiagnoseid);
}
if ( "DG1" == segment && "15" == element ) {	
dgcounter++;
}

} // end while 

As soon as I try to go through the LOADED array a second time, it throws this error message, presumably because the pointer has arrived at the last element and doesn't reset when I call another while loop.

Transformer error
ERROR MESSAGE: Error evaluating transformer
com.mirth.connect.server.MirthJavascriptTransformerException: 
CHANNEL:	SFM-SendMessage
CONNECTOR:	BAR P12 senden
SCRIPT SOURCE:	TRANSFORMER
SOURCE CODE:	
148: 
149: } // end while 1. Durchgang
150: 
151: logger.info("DG1.length: "+dgcounter );
152: 	
153: while(loaded.next() ) { // 2. Durchgang
154: 	var messageid = loaded.getString("messageid");
155: 	var segment = loaded.getString("segment");
156: 	var element = loaded.getString("element");
157: 	var content = loaded.getString("content");
LINE NUMBER:	153
DETAILS:	Wrapped java.sql.SQLException: Ungültige Cursorposition

So far I have not found a way to reset the pointer (something I recall being possible in PHP, ages ago). Is there a way to run through the query results a second time?

Or manipulate the pointer?

[ab-mg-23]

You want to create a scrollable ResultSet and call .beforeFirst() to reset.

References:
https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#beforeFirst--
https://www.baeldung.com/jdbc-resultset

[areutera]

You want to create a scrollable ResultSet and call .beforeFirst() to reset.

References: https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#beforeFirst-- https://www.baeldung.com/jdbc-resultset

Thank you, I'll look into that.

[pacmano1]

Why not just return JSON (or an xml list) in your SQL with the full result set as an array?

[areutera]

Why not just return JSON (or an xml list) in your SQL with the full result set as an array?

Possibly because I don't have any documentation on hand that allows me how to do that. The only way I found and could make work is in that example above.

What you are implying (XML List), sounds much easier. Can you point me to where I can find more about it?

Edit: I am currently looking at the PostgreSQL documentation on formatting results as XML output via something like an 'FOR XML' statement at he end of my query.

[pacmano1]

You query is currently a simple SELECT.

Change it to return JSON (since you use postgres and postgres JSON features are very good). Note in your sql you may want to explicitly return an index so your ordering is explicit in the result set. That is a SQL exercise of course. Or if your result set has something already usable to order, you can use that (e.g. like the message ID, a date, etc.).

SELECT json_agg(t) as stuff
FROM **** t
WHERE messageid IN ('78673004', '78702620', '78945674', '78945719', '78945680');

then

var result = dbConn.executeCachedQuery(sqlCommand);
result.next() // assuming a result here of course but you can coalesce the above query to return an empty array if no match is found
$c('stuff',JSON.parse(result.getString(1)); // now you have an object to iterate over as you see fit again and again. 
$c('stiff').find(item => ....... ) // <all the array prototype functions in rhino are available, e.g. .find, .forEach, etc.>