kuroyi.net

Don’t forget to link with the new header files (sql.h), instead of the default system  installed version. Do this using “-I/usr/local/include” or whatever the new path is. You should also add “-L/usr/local/lib” to link with the new libraries.

Just be careful because there are now two versions of the same libraries installed, so strange issues could crop up if software compiled with the headers from one version uses the library from the other version (usually segfaults in the library with code that should run correctly).

I ran into some issues accessing Oracle tables via ODBC. It turns out that sqlplus automatically uppercases all the table and column names when you create a table. The table can be accessed fine with sqlplus which transparently handles everything in uppercase. However, unixODBC is case sensitive by default. So I just have to be aware of the difference and specify everything in uppercase.

You can quote the table name in Oracle to prevent this behaviour, but it is not recommended.

After investigating some more, it turns out that each databases handles it differently.

MySQL is case sensitive on Linux:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

PostgreSQL lowercases table and column names:

http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

I’ve been testing unixODBC with MySQL and PostgresSQL and recently tried using it with Oracle 11g. After getting everything setup I kept getting the following error

28000:1:1017:[unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

I finally discovered that this is because Oracle uses “UserID” instead of “User” in the ODBC configuration of the DSN.

The current version of unixODBC (odbcinst1debian1 2.2.11-16build3) does not work with Oracle 11g on Ubuntu. When correctly setup it gives the following error (‘isql -v oracledb’):

isql: symbol lookup error: /opt/oracle/product/11.1.0/db_1/lib/libsqora.so.11.1: undefined symbol: SQLGetPrivateProfileStringW

To fix this, you need to install a newer version of the unixodbc libraries. Until the updated packages are available for Ubuntu, the easiest way to fix it is to download unixODBC from http://www.unixodbc.org and install it in /usr/local.

Extract the source archive:

tar zxvf unixODBC-2.2.14.tar.gz

cd unixODBC

By default, unixODBC will install into /usr/local which is what we want so that it doesn’t conflict with the existing packaged version. However, we also want it to use the config files in /etc instead of /usr/local/etc. Luckily, there is a configure option for this:

./configure –sysconfdir=/etc

Note that you need to install libqt4-dev in order to do the full build. However, you can use ‘–enable-gui=no’ if you don’t plan to use the gui (which is what I did).

sudo make install

Now it is compiled and installed. Make sure that /usr/local/lib is in your ld.so search path so that the dynamic linker picks up the new versions of the libraries. Since I had to add an entry for oracle anyway, I created a new file called /etc/ld.so.conf.d/oracle.conf  and inserted the following lines:

/opt/oracle/product/11.1.0/db_1/lib/

/usr/local/lib/

Save the file, and then run ‘sudo ldconfig’ to update the dynamic linker cache.

Now you should be good to go.