Using ODBC Data Connections In Your Schemas

 

When you initially create your SQL Schema, whether for Microsoft SQL Server or for MySQL you have the option of whether you are going to be using a "Native" connection to the database OR an ODBC connection.

 

 

 

 

 

IMPORTANT: After you have SAVED your Schema profile you cannot ever change the Connection Type again.

 

Equally IMPORTANT: This document is not meant to be an exhaustive discussion about what ODBC is, what ODBC "drivers" are, or how to create an ODBC data source. The UltraPort SQL Schema Engine is about allowing you (the customer) to use YOUR database (either Microsoft SQL Server or MySQL) as a platform for consuming or creating HL7 messages. Our assumption is always that YOU (the customer) are the expert on how to use YOUR database platform.

 

With all of the above being said here is how you use the ODBC Connection type when creating your SQL Schemas.

 

1.Check ODBC Connection in the Connection Type area.

2.Enter the DSN name of the ODBC Connection in the ODBC DSN Name field. This MUST BE a System DSN.

3.Some ODBC drivers allow you to store the database login credentials right in the DSN (most do NOT), so you will likely need to check the Pass Credentials to DSN box and enter your SQL User ID and Password.

4.That's it. You just complete the rest of the form creating your Schema Profile (see MS SQL Profiles, or MySQL Profiles).

 

Important Considerations When Using ODBC

 

The #1 issue we receive support calls about when customers cannot get the Schema Engine to recognize their DSN are because of the following:

 

The DSN is the wrong type. It must be a System DSN.

---

Trying to use a 32-Bit ODBC Driver on a 64-Bit Machine. The ODBC DSN and driver MUST match the computer platform. The UltraPort SQL Schema Engine is native 64-bit software, although it is compiled to run on both 32 and 64 bit machines for backward compatibility. This means that EVEN IF you specifically downloaded and installed the "32 Bit" installation of the SQL Schema Engine and it was installed WITH other 32 bit software in the Program Files (x86) folder on the machine it will STILL know that it's a 64-Bit machine and it will run in 64-Bit mode.

 

This can be confusing even for experienced database users because Microsoft still puts TWO different ODBC Data Source Administrator snap-ins on every windows box, a 32-bit and a 64-bit. Compounding matters they are located in different places yet look IDENTICAL. This is why we put the 2 buttons in the window to launch the ODBC Data Source Administrator.

---

Question: I'm on a 64-bit machine and I want to use a 3rd party ODBC Driver which does not have a 64-Bit version?

 

Answer: We are sorry but this is a limitation on our software that we do not see the need to remedy. One solution might be for you to create a "true" 32-bit virtual machine and run the Schema Engine software inside that VM. A better solution might be to contact the vendor providing the ODBC driver and ask them to look at a calendar and note the date, since in today's world for at least the past 10 years you would have to go to a pawn shop or flea market to actually buy a real 32-bit Windows machine.