Why is this string: javax.sql.rowset.serial.SerialClob being included in an output file instead of the actual value

Question

I am using Mirth 3.1.0.x, to extract data from a Sql Server 2012 database and write the resultset to a flat file.

The code was working prior to a new column being added.

Here is the code in a transformer script for a Mirth Channel destination:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://10.10.1.74:1433/rRIS_California','rRIS_California','rRIS_California');    
var result1 = dbConn.executeCachedQuery("exec [z_SelectChannelData] 'AAI'");   

while(result1.next())
{         
   tmp.row += <row>
                      <LastName>"{result1.getString('LastName')}"</LastName>
                      <FirstName>"{result1.getString('FirstName')}"</FirstName>
                      <HomePhone>"{result1.getString('HomePhone')}"</HomePhone>
                      <MRN>"{result1.getString('MRN')}"</MRN>
                      <EncounterID>"{result1.getString('EncounterID')}"</EncounterID>
                      <Accession>"{result1.getString('Accession')}"</Accession>
                      <Modality>"{result1.getString('Modality')}"</Modality>
                      <Procedure>"{result1.getString('ProcedureCode')}"</Procedure>
                      <Location>"{result1.getString('Location')}"</Location>                         
                      <PreferredLanguage>"{result1.getString('PreferredLanguage')}"</PreferredLanguage>
                      <Gender>"{result1.getString('Gender')}"</Gender>
                      <EmailAddress>"{result1.getString('EmailAddress')}"</EmailAddress>
                      <MobilePhoneNumber>"{result1.getString('MobilePhoneNumber')}"</MobilePhoneNumber>
                      <Room>"{result1.getString('Room')}"</Room>                                  
                      <billing_code>"{result1.getString('billing_code')}"</billing_code>
                      <copay>"{result1.getString('copay')}"</copay>
                 </row>; 
}    
dbConn.close();

In the file out, the last column is not being populated by the actual value but the following string:

"copay"

"javax.sql.rowset.serial.SerialClob@1b795929"

"javax.sql.rowset.serial.SerialClob@5693cfb7"

Below, is the T-SQL for the last column:

Select    
CASE WHEN v.copay is null THEN '' WHEN v.copay =0 THEN '' ELSE cast(v.copay as varchar(max)) END as copay
FROM <table> as v

Using JavaScript? This code doesn't look like it would compile? I suspect this is Java and you're calling toString on a SerialClob object. — Evan Knowles, Apr 13 '15 at 14:18
I'm new to Mirth and the code fragment shown in the entire script tied to a transformer class for a defined destination in Mirth Connect. — Gary Kindel, Apr 13 '15 at 14:24

score 3 · Answer 1 · edited Oct 03 '16 at 21:37

3

You might try something like this.

var clob = result1.getClob("copay");
var result = clob.getSubString(1,clob.length());

and then put the result inside the copay tag.

edited Oct 03 '16 at 21:37

p.campbell

98,673
67
256
322

answered Apr 13 '15 at 14:29

kucing_terbang

4,991
2
22
28

Gary Kindel · Accepted Answer · 2019-04-16T16:01:21.590

3

Changed cast from varchar(max) to varchar(20) and the actual column value is shown by the sql below. Found the answer thanks to Evan Knowles and kucing_terbang.

SELECT
CASE WHEN v.copay is null THEN '' WHEN v.copay =0 THEN '' ELSE cast(v.copay as varchar(20)) END as copay
FROM <table> v

edited Apr 16 '19 at 16:01

answered Apr 13 '15 at 14:38

Gary Kindel

17,071
7
49
66

I ran into the same problem using VARCHAR(MAX) and this helped me from chasing rabbits. May I suggest rewording or simplifying the answer? It was the number one search for me in Google. – solbs Apr 16 '19 at 15:24

score 0 · Answer 3 · answered Aug 18 '22 at 16:21

You can read nvarchar(max), varchar(max), text, and ntext columns by using getClob(col) and converting the clob to a string with IOUtils. See below for example:

var rs = dbConn.executeCachedQuery("SELECT CAST('Hello' as nvarchar(max))");
var column1 = String(org.apache.commons.io.IOUtils.toString(result.getClob(1).getCharacterStream()));
// the variable column1 now contains the javascript string "Hello"

Why is this string: javax.sql.rowset.serial.SerialClob being included in an output file instead of the actual value

3 Answers3