Mirth Community

Mirth Community (http://www.mirthcorp.com/community/forums/index.php)
-   Support (http://www.mirthcorp.com/community/forums/forumdisplay.php?f=6)
-   -   ORA data will not get returned (http://www.mirthcorp.com/community/forums/showthread.php?t=7631)

MikeCaldwell 09-06-2012 02:04 PM

ORA data will not get returned
 
I built a chanel, works with SQL2008R2, the Channel Reads an HL7, pulls and ID the HL7 runs a stored procedure on a SQL server passing the ID, Data gets returned, I updated the HL7 Files with returned data and I move on.
--
I tried this with Oracle and no luck. I have the same type of code setup to do the same data pull but it is going against Oracle database and WILL NOT RETURN data EXCEPT when I make the source tab a direct connect (not use Java) it can get data in XML format. I have tried everything ot pull the data into transformation using JAVA and it resturns this thig. I need it in java, this is what I have tested. Again the connectio string works on a direct connect using the GUI, I get tables and Columns returned. The variable $('usr_loginname') always comes back blank which should come back with somehting like 'gsmith' etc....
--
I keep getting this in result>> com.sun.rowset.CachedRowSetImpl@122e772
--
OR I get .getString(0) is not a function.
--
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:oracle:t hin:@x.x.x.x:1521:orasid','login','pass');
var thequery = "SELECT USR.loginname AS usr_loginname FROM ML.USR WHERE ROWNUM=1";
//
var result = dbConn.executeCachedQuery(thequery);
var last_updated_date = "";
//
//if(result.first()){
// usr_loginname = result.getString(0);
channelMap.put('result',result);
channelMap.put('usr_loginname',$('usr_loginname')) ;
//}
dbConn.close()

I've tried almost everything i can think, stillnot getting back data! HELP!

narupley 09-06-2012 03:24 PM

If you're using a Database Reader in JavaScript mode, then you need to return a CachedRowSet. If you're using a JavaScript Reader, then you need to return a string or List of strings.

MikeCaldwell 09-06-2012 03:38 PM

Can you throw me a bone (example)??

I thought I was managing a string array with result.x?

BTW This worked fine with MSSQL 2008 R2

I hear ya, just not sure what command to change. Thanks

ami199102 09-06-2012 08:18 PM

try this
Code:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbcracle:t hin:@x.x.x.x:1521rasid','login','pass');
var thequery = "SELECT USR.loginname AS usr_loginname FROM ML.USR WHERE ROWNUM=1";
var result = dbConn.executeCachedQuery(thequery);
var last_updated_date = "";
if(result.first()){
var loginname = result.getString(1); or var loginname=result.getString('loginname');
channelMap.put('result',result);
channelMap.put('usr_loginname',loginname,) ;
}
dbConn.close()


narupley 09-07-2012 11:02 AM

Here's an example with a JavaScript Reader:

Code:

var list = new java.util.ArrayList();

importPackage(java.sql);
var dbConn = DriverManager.getConnection(url,user,pass);
var ps = dbConn.prepareStatement('SELECT * FROM whatever');
var rs = ps.executeQuery();

while(rs.next())
        list.add(rs.getString('somecolumn'));

rs.close();
ps.close();
dbConn.close();

return list;

And with a Database Reader in JavaScript mode:

Code:

importPackage(java.sql);
var dbConn = DriverManager.getConnection(url,user,pass);
var ps = dbConn.prepareStatement('SELECT * FROM whatever');
var rs = ps.executeQuery();
var crs = new com.sun.rowset.CachedRowSetImpl();
crs.populate(rs);

rs.close();
ps.close();
dbConn.close();

return crs;


nicovn 09-08-2012 06:18 AM

Hi,

You're using a driver for SqlServer/Sybase (net.sourceforge.jtds.jdbc.Driver)... instead of the Oracle driver to get an Oracle connection...

Best Regards

Nico


All times are GMT -8. The time now is 09:36 PM.

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