Database URL

[JanVanGent]

I have some trouble connecting to an MS Access database ( or even an Oracle XE database ). Both problems are probably database-URL related.
Concerning the Access DB: I created a datasource and used the URL from a previous post ( jdbcdbc:datasource_name ), but this doesn't seem to work.

For the Oracle database i tried the following URL: jdbcracle://127.0.0.1:1524/XE
( and several other combinations to construct an url ).

Whatever url i use, i keep getting the following error-description:
java.lang.IllegalArgumentException: Write statement should be an insert / update / delete sql statement
at org.mule.providers.jdbc.JdbcMessageDispatcher.doDi spatch(JdbcMessageDispatcher.java:95)
at org.mule.providers.AbstractMessageDispatcher$Worke r.run(AbstractMessageDispatcher.java:257)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.runTask(ThreadPoolExecutor. java:650)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :675)
at java.lang.Thread.run(Unknown Source)

{}

additional info:
I tested the INSERT statement on the database(s) and it was Ok. So no problem in the insert command.
Logging on with another tool to the database(s) works ( so usern./passw. is correct ).

Does someone have any experience in logging on to:
1. an Access database
2. an Oracle XE database

thanks for any help :-)

Jan

[arumpke]

Re: Database URL

I am not sure how to set up the Oracle URL, but I used Microsoft SQL and this is my URL if it helps:
jdbc:jtds:sqlserver://SERVERNAME:1433/DATABASE;

For Access, you probably have to run it through an odbc driver, but I don't know that for sure.
jdbcdbc://

Sorry I couldn't be of more help.

[jbartels]

Re: Database URL

There is an Oracle 10.2 driver that comes with Mirth 1.2 (found in %mirthpath%/conf/dbdrivers.xml) .

Google on "oracle.jdbc.OracleDriver" "connection string" (keep the quotes) and you'll see some examples of connection strings. Hope that helps.

[JanVanGent]

Re: Database URL

Well i expected the url to be the "usual" one ( jdbcracle:thin:@127.0.0.1:1524/XE ), but that didn't work so i tried the one from the manual ( + all other combinations ).

Thanks anyway :-)

[chrisl]

Re: Database URL

Can you post your channel export?

[JanVanGent]

Re: Database URL

tried these url' s as well
jdbcracle:thin:@localhost:1524:XE ( this seems to be the correct format => OTN )
and jdbcracle:thin://@localhost:1524/XE ( from SQL developer connection properties )

but still the same error.

This is my channel export:

<com.webreach.mirth.model.Channel>
<id>18751002-f124-4724-a8c1-bcfbb7bf2dfc</id>
<name>IN_2</name>
<description></description>
<enabled>true</enabled>
<version>1.2.0</version>
<revision>0</revision>
<direction>INBOUND</direction>
<protocol>HL7</protocol>
<mode>BROADCAST</mode>
<sourceConnector>
<name>sourceConnector</name>
<properties>
<property name="sortAttribute">date</property>
<property name="pollingFrequency">1000</property>
<property name="DataType">File Reader</property>
<property name="fileAge">0</property>
<property name="moveToPattern">C:\Temp\Mirth\processed</property>
<property name="checkFileAge">0</property>
<property name="autoDelete">1</property>
<property name="host">C:/Temp/Mirth/IN</property>
<property name="moveToDirectory"></property>
</properties>
<transformer>
<steps>
<com.webreach.mirth.model.Step>
<sequenceNumber>0</sequenceNumber>
<name>firstName</name>
<script>localMap.put(&#039;firstName&#039;, msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.2&#039;].toString());</script>
<type>Mapper</type>
<data class="map">
<entry>
<string>Mapping</string>
<string>msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.2&#039;].toString()</string>
</entry>
<entry>
<string>Variable</string>
<string>firstName</string>
</entry>
<entry>
<string>isGlobal</string>
<string>0</string>
</entry>
</data>
</com.webreach.mirth.model.Step>
<com.webreach.mirth.model.Step>
<sequenceNumber>1</sequenceNumber>
<name>lastName</name>
<script>localMap.put(&#039;lastName&#039;, msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.1&#039;].toString());</script>
<type>Mapper</type>
<data class="map">
<entry>
<string>Mapping</string>
<string>msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.1&#039;].toString()</string>
</entry>
<entry>
<string>Variable</string>
<string>lastName</string>
</entry>
<entry>
<string>isGlobal</string>
<string>0</string>
</entry>
</data>
</com.webreach.mirth.model.Step>
</steps>
<template>MSH|^~\&|ADT-HIS||HL7INSP||20060101100000||ADT^A01|1|P|2.3
EVN|A01|20060101||
PID||||4711|Doe^John||19701024|M|||Sesamstreet 11^76137^Karlsruhe^D|||||GS|EV||||
PV1||I|S2^13^3^CHI^^21||||||||||||||||1234|||||||| |||||||||||||||||200601010930||||||||</template>
</transformer>
<filter>
<rules/>
</filter>
<transportName>File Reader</transportName>
</sourceConnector>
<destinationConnectors>
<com.webreach.mirth.model.Connector>
<name>Destination 1</name>
<properties>
<property name="URL">jdbcracle:thin:@localhost:1524:XE</property>
<property name="DataType">Database Writer</property>
<property name="password">XXXXXX</property>
<property name="host">query</property>
<property name="driver">oracle.jdbc.OracleDriver</property>
<property name="query">INSERT IGNORE INTO TEST( TEST1 ) VALUES (${firstName});</property>
<property name="username">TEST</property>
</properties>
<transformer>
<steps/>
</transformer>
<filter>
<rules/>
</filter>
<transportName>Database Writer</transportName>
</com.webreach.mirth.model.Connector>
</destinationConnectors>
<properties>
<property name="initialState">started</property>
<property name="store_messages">true</property>
<property name="recv_xml_encoded">false</property>
<property name="encryptData">false</property>
<property name="transactional">false</property>
<property name="max_message_age">-1</property>
<property name="error_messages_only">false</property>
</properties>
<preprocessingScript>// Modify the message variable below to pre process data

return message;</preprocessingScript>
</com.webreach.mirth.model.Channel>

[JanVanGent]

Re: Database URL

this is the correct url to connect to an Oracle database:
jdbcracle:thin:@localhostort:db
( ex. jdbcracle:thin:@localhost:1524:xe )

Remark:
The insert statement should look like this:
INSERT IGNORE INTO TEST ( test1 ) VALUES( ${LastName} )

NOT like this
INSERT IGNORE INTO TEST ( test1 ) VALUES( ${LastName} )[red];[/red]

Thanks to everyone who tried to help !!

[lgiuriollo]

Hi!

I tried theses url's:

jdbcracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = @YourHostName)(PORT = @YourPort)) )(CONNECT_DATA =(SERVICE_NAME = @YourServiceName)))

jdbcracle:thin:@192.168.10.151:1521racle

jdbcracle:thin:@yourHost\YourDbName

and not success.

Toad sql be connected normaly.

Any idea?

Thank's