Archive for July, 2009

table and column name case sensitivity

Friday, July 17th, 2009

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:

PostgreSQL lowercases table and column names:

ORA-01017: invalid username/password; logon denied

Thursday, July 16th, 2009

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.

Using unixODBC with Oracle 11g on Ubuntu 9.04

Wednesday, July 15th, 2009

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/ 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 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 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/  and inserted the following lines:



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

Now you should be good to go.