Question

How to connect to "Single User Training" version of EMR 9.5 with Oracle DB.

Answer 1

To connect follow these instructions:

1. Ensure that QIE and the "Single User Training" version of EMR 9.5 are installed on the same machine.  It is not possible to connect to "Single User Training" remotely.
2. Place driver in QIE lib folder.
3. In QIE select the driver. System Administration -> System Administration -> Manage External Libraries
4. Restart QIE.
5. Configure connection URL using "oci" driver settings (not thin). See screen shot.

Note: To save yourself some pain.  Copy the driver from the jboss directory for the EMR you are attempting to connect to.  Found in:

...\Centricity EMR 9.8...\jboss\server\default\lib

Things to know about the Oracle OCI drivers:

First, the different drivers correspond to different versions of Java:

Jar version	Java version
classes12.jar	Java 1.2 and 1.3
ojdbc14.jar	Java 1.4
ojdbc5.jar	Java 5
ojdbc6.jar	Java 6
ojdbc7.jar	Java 7

 

Second, OCI requires a ocijdbcX.dll,  The version of the .dll should match the version of the .jar.  To make it frustrating version of the .dll and the .jar cannot be determined by the file names.  The best way to figure out if the .jar and the .dll match is to download them together.  See the Instant Client download link below.  

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Check for a match by cracking open the downloaded zip.  Check the names, dates, and file sizes againt what is installed to determine the best match.  This table gives a rough idea by version number:  

Driver Version	Driver and dll Files in Instant Client zip
10.1.0.5 thru 10.2.0.5	ocijdbc10.dll, classes12.jar, ojdbc14.jar
11.1.0.6.0 thru 11.2.0.4.0	ocijdbc11.dll, ojdbc5.jar, ojdbc6.jar
12.1.0.1.0 +	ocijdbc12.dll, ojdbc6.jar, ojdbc7.jar

The .dll should be available to QIE via the PATH environment variable.  If Oracle is installed the PATH variable should already have the Oracle bin folder included.  This error indicates that the .dll is not found:

java.lang.UnsatisfiedLinkError: no ocijdbc10 in java.library.path 

The above error was produced while trying to use ojdbc14.jar to connect to EMR 9.8.  Since, the oracle version for EMR 9.8 is 11g, the ocijdbc10.dll is not installed.  It is possible to install the ocijdbc10.dll along side the versions already installed but, it is probably better to use the matching version. To do so, first download the correct 32 or 64 bit version from the Instant Client link above. Then follow these steps:

1. Extract the ocijdbc10.dll and place it in the same Oracle bin as the ocijdbc11.dll
2. Extract the ojdbc14.jar and place this driver in QIE lib folder.  Note: you may need to remove any other versions of the driver.
3. In QIE select the driver. System Administration -> System Administration -> Manage External Libraries
4. Restart QIE. 

Versions that Qvera has found to work with "Single User Training" version of EMR 9.5.  Use ojdbc6.jar for version 11.2.0.2.0 with file size of 2,102 KB dated 8/26/2010.

Qvera has also had success using version 11.2.0.3.0 for EMR 9.8.   Use ojdbc6.jar for version 11.2.0.3.0 with file size of 2,650 KB dated 8/30/2011.

To download just the jars without the .dlls use this link.

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

For connecting remotely the thin client should be used.  When using the .jar to connect as a thin client the dll is not required.  For the thin client the URL should follow this pattern: 

jdbc:oracle:thin:@<host>:<port>:<database> 

jdbc:oracle:thin:@localhost:1521:EMR

Comments

For EMR 9.8 use the ojdbc6.jar that is in the ...\Centricity EMR 9.8 Evaluation\jboss\server\default\lib folder which is the 11.2.0.3 version of the driver.  

I you get error:  
     java.sql.SQLException: ORA-12541: TNS:no listener
Remeber to use the oci url that does not use a TNS listener as such does not need the host and port in the url:

     jdbc:oracle:oci:@sut90

---

When running the 64-bit version of QIE and attempting to use the "oci" url the following errors comes up:  ocijdbc11.dll can't load ia 32-bit .dll on a 64-bit platform

At this point, it appears that you must either (1) use the 32-bit version of QIE or (2) use the "thin" url.

This may also indicate that a 32bit dll was installed instead of the 64 bit dll or visa versa.  See more detail about the relationship between the driver and the dll above.

---

See http://java.sogeti.nl/JavaBlog/2008/02/03/oracle-thin-jdbc-to-tns-name/
if you get this error: java.sql.SQLRecoverableException: IO Error: Unknown host specified

Answer 2

Troubling shooting tips:

Error indicating that the driver has not been properly configured in QIE

org.h2.message.DbException: Class "oracle.jdbc.driver.OracleDriver" not found [90086-174]

Solution 1: Place driver (jar file) in QIE lib folder. In QIE select the driver. System Administration -> System Administration -> Manage External Libraries. Restart QIE.

Solution 2: In the QIE Database Connection Information, enter the correct driver class into the "Driver Class" field.  See screen shot above for an example.  

Error indicating mismatch on useing "oci" vs. "thin"

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

Solution: Switch the connection from jdbc:oracle:thin:@localhost:1521:sut90 to jdbc:oracle:oci:@sut90

Errors indicating Oracle version and Driver version do not match.

java.lang.UnsatisfiedLinkError: oracle.jdbc.driver.T2CConnection.t2cSetSessionTimeZone(JLjava/lang/String;)I

java.lang.UnsatisfiedLinkError: oracle.jdbc.driver.T2CConnection.t2cCreateState...

java.lang.Error: Incompatible version of libocijdbc[Jdbc:112030, Jdbc-OCI:112020

org.h2.message.DbException: General error: "oracle.jdbc.driver.OracleDriver" [50000-172]

java.lang.unsatisfiedlinkerror no ocijdbc in java.library.path

java.lang.UnsatisfiedLinkError: no ocijdbc10 in java.library.path 

java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path

Solution: Find the correct driver version for your Oracle install.  This is the ojdbcX.jar.  There may be several versions of ojdbc6.jar.  Also make sure the dll version matches the driver/.jar version.  This was covered in more detail above.

Also see this question:

https://www.qvera.com/kb/index.php/165/oracle-connection-listener-currently-connect-descriptor?show=165#q165