How to get data from database

[shifting_18]

Hi everyone!
I'm using Database Reader as my connector type. The question I came up to is how will I get the data found on the database? Can you please give some script on how to do so?? By the way, I'm using SqlServer as my driver.

Thanks in advance!

[huubvandemortel]

Re:How to get data from database

When you enter a select statement in the SQL area of the source-tab you will get a XML structure for every matching record. These XML structures will be the incoming messages for a destination.

[shifting_18]

Re:How to get data from database

Hi! Thank you for replying!

Mirth seems like declaring them as variables. So how do I map these values in the transformer? I can't see the xml here. When I deploy channels, I keep having this error:

org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to
reconnect receiver on endpoint "jdbc://query"

By the way, should I put a javascript like this:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://saleslip2k:1443/mirthdb','sa','cardtech');
var expression = "SELECT patient_id from patient_info";

var result = dbConn.executeCachedQuery("expression");


dbConn.close();
return result;


Or just a simple sql will do? For example:

SELECT patient_id FROM patient_info


Thanks again! I hope you could help me out..:side:

Tonet =)

Post edited by: shifting_18, at: 05/03/2007 02:19

[shifting_18]

Re:How to get data from database

I followed what you said entering the sql I posted earlier.
i used
tmp['PID']['PID.1'] as message segment
${patient_id} as mapping value

but I have this error:

org.mule.config.ConfigurationException: Failed to parse configuration resource "C:\\Program Files\\Mirth\\conf\\mule-config.xml"
Caused by: org.mule.umo.lifecycle.InitialisationException: Initialisation Failure: missing ) after argument list (b9b0d38f-ace1-4a7c-99bc-7e70e3b23c47#6)

[huubvandemortel]

Re:How to get data from database

In the incoming Data tab on the destination you should paste an example of the XML structure that is returned. You could find one thru the
Dashboard/View messages if the query succeeded. You can map any part of the XML-values to a name although that is not needed. You can also use the tmp['xmltag'] structure directly in your transformer javascript.

Use Validate java script to check if you have a syntax error in your script.

You don;t need a javascript to query the database. On the source tab of the Database Reader you can configure the minumum that is needed.

Post edited by: huubvandemortel, at: 05/03/2007 03:21

[shifting_18]

Re:How to get data from database

HI! I got this problem here. I still cannot start my channel. The error is said below. Can you please give me a sample transformer javascript.. please?I am now confused on what to use.

I used this select stmt now:
SELECT patient_id from patient_info
FOR XML AUTO

I was surprised to see an xml on the incoming message on the transformer but now, I don't know how to type the javascript..Can't I make use of Message builder? Hehe..Thanks!

Thank you so much!!

Post edited by: shifting_18, at: 05/03/2007 19:20

[huubvandemortel]

Re:How to get data from database

You can use the incoming and outgoing templates. Paste the XML to the incoming and a HL7 message to the outgoing data.
Transorm like

tmp['PID']['PID.5']['PID.5.1'] = msg['PatientName'].toString() PatientName is a Tag in your incoming XML (a column in your database)

[shifting_18]

Re:How to get data from database

hi! i tried what you said but I can't see the results. but I really really am confused with the error.

org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"

I can't start EVEN start my channel..gosh!

[huubvandemortel]

Re:How to get data from database

Is'nt there also a login failed message in the trace ?

I had that problem trying to login on a domain account. I could not make that work. Creating a new local SQL Server account ( not using Windows Authemtication) made the login work.

Stil have no idee why a windows domain account does not work.

Post edited by: huubvandemortel, at: 05/06/2007 23:33

[shifting_18]

Re:How to get data from database

Hi!
The complete error is below. I can't seem to find a login failure. It was pretty much concern with the connection I think. I've checked the parameter and it's correct.
It worked when I previously used a file to db writer..

org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"
at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:34 )
at org.mule.providers.AbstractConnectionStrategy.conn ect(AbstractConnectionStrategy.java:67)
at org.mule.providers.AbstractMessageReceiver.start(A bstractMessageReceiver.java:396)
at org.mule.providers.AbstractConnector.registerListe ner(AbstractConnector.java:518)
at org.mule.impl.model.AbstractModel.registerListener s(AbstractModel.java:221)
at org.mule.impl.model.AbstractModel.start(AbstractMo del.java:353)
at org.mule.MuleManager.start(MuleManager.java:730)
at org.mule.config.builders.MuleXmlConfigurationBuild er.configure(MuleXmlConfigurationBuilder.java:207)
at org.mule.config.builders.MuleXmlConfigurationBuild er.configure(MuleXmlConfigurationBuilder.java:194)
at com.webreach.mirth.server.Mirth.startMule(Mirth.ja va:163)
at com.webreach.mirth.server.Mirth.restartMule(Mirth. java:145)
at com.webreach.mirth.server.Mirth.run(Mirth.java:101 )
Caused by: org.mule.providers.FatalConnectException: ReconnectStrategy "org.mule.providers.SingleAttemptConnectionStrateg y" failed to reconnect receiver on endpoint "jdbc://query"
at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:34 )
at org.mule.providers.AbstractConnectionStrategy.conn ect(AbstractConnectionStrategy.java:67)
at org.mule.providers.AbstractMessageReceiver.connect (AbstractMessageReceiver.java:353)
at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:32 )
... 11 more
Caused by: org.mule.providers.ConnectException: Initialisation Failure: Network error IOException: Connection refused: connect
at com.webreach.mirth.server.mule.providers.jdbc.Jdbc MessageReceiver.doConnect(JdbcMessageReceiver.java :91)
at org.mule.providers.AbstractMessageReceiver.connect (AbstractMessageReceiver.java:360)
at org.mule.providers.SingleAttemptConnectionStrategy .doConnect(SingleAttemptConnectionStrategy.java:32 )
... 14 more
Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(C onnectionJDBC2.java:372)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(C onnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.ja va:178)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.webreach.mirth.server.mule.providers.jdbc.Jdbc Connector.getConnection(JdbcConnector.java:452)
at com.webreach.mirth.server.mule.providers.jdbc.Jdbc MessageReceiver.doConnect(JdbcMessageReceiver.java :89)
... 16 more
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocke tForJDBC3(SharedSocket.java:289)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(Shar edSocket.java:250)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(C onnectionJDBC2.java:297)
... 22 more

Post edited by: shifting_18, at: 05/07/2007 18:34

[huubvandemortel]

Re:How to get data from database

Are you sure someone is listning to the port on your database connector
I get the same messages when I change my port to a number no one is listning to ...

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

[huubvandemortel]

Re:How to get data from database

Are you sure someone is listning to the port on your database connector
I get the same messages when I change my port to a number no one is listning to ...

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

[huubvandemortel]

Re:How to get data from database

Are you sure someone is listning to the port on your database connector
I get the same messages when I change my port to a number no one is listning to ...

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

[huubvandemortel]

Re:How to get data from database

Are you sure someone is listning to the port on your database connector
I get the same messages when I change my port to a number no one is listning to ...

Caused by: java.sql.SQLException: Network error IOException: Connection refused: connect

[shifting_18]

Re:How to get data from database

hi!!
Thank you very much for your advices. Now it's working! I'm very happy on it! I'll start now with learning triggers. By the way, do have any idea on how to manipulate the HL7 in Javascript? I made a thread entitled "Javascript help!" I really wish you could help me out. Many thanks to you again!!1