...
continuation
Put it all Together:
As a summary of above discussion, l used following components:
- unixODBC 2.3.0 (available here)
- Oracle Instant Client 11g 11.2.0.2-both Basic and ODBC Supplement Packages (available here)
- unixODBC-devel 2.2.11 (Initially unixODBC 2.2.11 is also needed to identify the inter-dependency between both)
As said before, I’ll be using absolute paths for referring directories and file in this discussion. Also I executed all the commands as super user—root.
Following command installed unixODBC 2.2.11 and unixODBC -devel 2.2.11 from repository (you may install them by downloading rpm packages from internet).
yum –install unixodbc unixodbc-devel
Files related to both of these packages resided in different directories after installation which can be check using the commands ‘rpm -ql unixodbc-devel’ and rpm ‘rpm -ql unixodbc’. Here I was merely interested in identifying the dependency of unixODBC-devel on unixODBC. Apart from other files, both packages had their Static and Shared library files in /usr/lib/ directory. Some of the Shared library files of unixODBC-devel were actually soft links to the Shared library files of unixODBC which formed the dependency on unixODBC. I was interested only in these shared library files of unixODBC, others had no importance. My plan was to provide unixODBC-devel with these shared library files from unixODBC 2.3.0. That was the only purpose of installing unixODBC 2.2.11 and was supposed to be uninstalled after it had served its purpose. But before uninstalling it I decided to make a copy of all of its files especially shared library files, so that I could use them if I didn’t find any of the shared libraries from unixODBC 2.3.0. I used the following Shell script to copy all files of unixODBC 2.2.11 in a directory /usr/backup/ unixODBC/.
#!/bin/sh
for file in `rpm -ql unixODBC`
do
cp "$file" /usr/backup/unixODBC/
done
Now I could uninstall unixODBC 2.2.11 using command (yum –e unixodbc) but as unixODBC-devel had a dependency on unixODBC, Package Manager asked to remove unixODBC-devel too which I needed for Asterisk. I used the following command to remove unixODBC package forcing the package not to check the dependencies.
rpm –e --no-deps unixodbc
This removed the unixODBC package successfully but this made following soft linked shared library files of unixODBC-devel unstable/corrupted having no source file in the directory /usr/lib/ (try ls -lrt). As mentioned before, I had planned to provide these library files from unixODBC 2.3.0.
UnixODBC-devel 2.2.11 | UnixODBC 2.2.11 |
libtemplate.so | libtemplate.so.1.0.0 |
libtdsS.so | libtdsS.so.1.0.0 |
libsapdbS.so | libsapdbS.so.1.0.0 |
liboraodbcS.so | liboraodbcS.so.1.0.0 |
liboplodbcS.so | liboplodbcS.so.1.0.0 |
libodbctxtS.so | libodbctxtS.so.1.0.0 |
libodbcnnS.so | libodbcnnS.so.1.0.0 |
libodbcminiS.so | libodbcminiS.so.1.0.0 |
libodbcinst.so | libodbcinst.so.1.0.0 |
libodbcdrvcfg2S.so | libodbcdrvcfg2S.so.1.0.0 |
libodbcdrvcfg1S.so | libodbcdrvcfg1S.so.1.0.0 |
libodbccr.so | libodbccr.so.1.0.0 |
libnn.so | libnn.so.1.0.0 |
libmimerS.so | libmimerS.so.1.0.0 |
libgtrtst.so | libgtrtst.so.1.0.0 |
libesoobS.so | libesoobS.so.1.0.0 |
libboundparam.so | libboundparam.so.1.0.0 |
Next I downloaded and put unixODBC-2.3.0.tar.gz in the directory /usr/local/src/ and installed it by executing the following set of commands:
tar zxvf unixODBC-2.3.0.tar.gz (This created a directory named unixODBC-2.3.0)
cd unixODBC-2.3.0
./configure --prefix=/usr/
make
make install
This installed the unixODBC-2.3.0’s binary files in /usr/bin/, header files in /usr/include, ini files (odbc.ini, odbcinst.ini) in /usr/etc/ and shared library files in /usr/lib/. The prefix (--prefix=/usr/) passed to the ‘configure’ script purposely so that shared libray files of unixODBC 2.3.0 are put in /usr/lib/ directory to resolve the unstable linked shared library files of unixODBC-devel 2.2.11.
I noticed there were only two files (libodbcinst.so.1.0.0 and libodbccr.so.1.0.0) from unixODBC 2.3.0 which matched two of unstable/corrupted shared library files of unixODBC-devel (libodbcinst.so and libodbccr.so); highlighted in yellow in above table. For the rest of the files, I copied the original shared library files of unixODBC 2.2.11 of which I had made a backup in /usr/backup/ unixODBC/ previously. After copying these shared library files, all unstable files of devel were stable now. (Again try ls -lrt).
unixODBC can be checked by through following command:
odbcinst –j
Next I downloaded Oracle Instant Client 11g, both Basic and ODBC Supplement packages and unzipped both of them in same directory /opt/instantclient_11/.
/opt/instantclient_11/ directory contains a script called odbc_update_ini.sh. This script takes four arguments, three of which are optional. I passed only the compulsory argument and leave the optional arguments. The only argument I sent (the one which is mandatory) is the path to Driver Manager that simply means the prefix of etc/odbc.ini and etc/odbcinst.ini.
As I mentioned before when I installed unixODBC 2.3.0, it put ini files in /usr/etc/ so I executed the above script as below:
cd /opt/instantclient_11/
./odbc_update_ini.sh ‘/usr/’
This script automatically configures the Oracle Driver with unixODBC 2.3.0 by updating two files; /usr/etc/odbcinst.ini and /root/.odbc.ini. (If non-super user is logged in while executing this script, then /home/username/.odbc.ini is update)
In /usr/etc/odbcinst.ini, I had an entry like the one below:
[Oracle 11g ODBC driver]
Description = Oracle ODBC driver for Oracle 11g
Driver = /opt/instantclient_11_2/libsqora.so.11.1
Setup =
…………….
…………….
…………….
In /root/.odbc.ini, I had an entry like the one below:
[OracleODBC-11g]
Application Attributes = T
…………….
…………….
Driver = Oracle 11g ODBC driver
DSN = OracleODBC-11g
…………….
…………….
……………
I edited the above entry in /root/.odbc.ini, I added some new fields, commented some existing fields (using ;) and changed the values of some of the fields. After these changes its looks like the below:
[OracleODBC-11g]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 11g ODBC driver ; the entry from odbcinst.ini
DSN = OracleODBC-11g
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
Database = XE ;db name
ServerName = xxx.xxx.xxx.xxx ; db ip
Port = 1521 ;db port
;ServerName = 192.168.0.105:1521/xe ; has been commented, may be used as well
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
;UserID = testdb ;has been commented
UserName = testdb ;db user
Password = testdb ;db password
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
I copied this entry i.e. [OracleODBC-11g] in /root/.odbc.ini to /usr/etc/odbc.ini, so both ini files have the entry [OracleODBC-11g] with same fields and their values.
.odbc.ini (with a dot) is read when db connection is being made by the user himself or herself while odbc.ini (without a preceding dot) is read when a db connection is being made by an application such as Asterisk. (Try odbcinst –q –d to see the driver’s details)
I executed the following command of isql, which is a utility application shipped with unixODBC, to check my Oracle DB connection.
isql –v OracleODBC-11g
I got the following output showing that connection has been successful with the DB.
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
This successful connection does not mean that what I did for unixODBC-devel is okay, that could only be confirmed if Asterisk was successful in communicating with the databse. I could make a successful connection with database using isql from unixODBC 2.3.0 without even installing unixODBC-devel. However, unixODBC 2.2.11 wasn’t successful in making a connection with database through isql because that doesn’t work with Oracle Instant Client.
