Tags: view all tags

Database Interface Upgrades

Compatibility with newer versions of popular databases is among the many features of APPX 4.3.

Database Interface Upgrades
- Oracle
- ODBC
- Comments:

Oracle

Oracle 10g R2 (10.2.0.1)
Oracle 11g

The Oracle connectivity is provided by the Oracle Instant Client

Quick installation steps:

Install Oracle Instant Client Basic.
Make the Oracle Instant Client library files visible to the system with the ldconfig tool or it's equivalent.
Set, in either the APPX Login Manager's daemon environment or the appx.env file, the APPX environment variable APPX_OCI_DIR to the directory containing the Oracle Instant Client library files.

Detailed installation example:

Here are the steps I used to make an Oracle connection with APPX 4.3.0 and Oracle 10gR2 on the same Red Hat Enterprise 5.1 32bit server.

mkdir -p /usr/local/src/oracle/instantclient/ 
cd /usr/local/src/oracle/instantclient/

Download the Oracle Instant Client files for Linux. APPX requires at least the Basic package, but it is recommended that you grab the SQL*Plus package too for debugging.

For 10gR2, oracle-instantclient-basic-10.2.0.4-1.i386.zip (34,687,861 bytes), and oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip (744,730 bytes)

For 11g, basic.zip (42,285,643 bytes), and sqlplus.zip (784,417 bytes)

Place the basic and sqlplus zipped files into /usr/local/src/oracle/instantclient/, and unzip them:

[root@tubes instantclient]# pwd
/usr/local/src/oracle/instantclient/
[root@tubes instantclient]# ls -la
total 34656
drwxr-xr-x 2 root root     4096 Jun  4 08:41 .
drwxr-xr-x 7 root root     4096 Jun  3 16:03 ..
-rw-r--r-- 1 root root 34687861 Jun  3 16:03 oracle-instantclient-basic-10.2.0.4-1.i386.zip
-rw-r--r-- 1 root root   744730 Jun  4 08:41 oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
[root@tubes instantclient]# 
[root@tubes instantclient]# unzip oracle-instantclient-basic-10.2.0.4-1.i386.zip 
Archive:  oracle-instantclient-basic-10.2.0.4-1.i386.zip
  inflating: instantclient_10_2/BASIC_README  
  inflating: instantclient_10_2/classes12.jar  
  inflating: instantclient_10_2/genezi  
  inflating: instantclient_10_2/libclntsh.so.10.1  
  inflating: instantclient_10_2/libnnz10.so  
  inflating: instantclient_10_2/libocci.so.10.1  
  inflating: instantclient_10_2/libociei.so  
  inflating: instantclient_10_2/libocijdbc10.so  
  inflating: instantclient_10_2/ojdbc14.jar  
[root@tubes instantclient]# unzip oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip 
Archive:  oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
  inflating: instantclient_10_2/SQLPLUS_README  
  inflating: instantclient_10_2/glogin.sql  
  inflating: instantclient_10_2/libsqlplus.so  
  inflating: instantclient_10_2/libsqlplusic.so  
  inflating: instantclient_10_2/sqlplus  
[root@tubes instantclient]# ls /usr/local
appx  bin  etc  games  include  lib  libexec  sbin  share  src

Now move the unzipped directory (instantclient_10_2) that contains both the basic and SQL*Plus files from the Oracle Instant Client, into the desired permanent directory. In my example, I'm using /usr/local/, so that the Oracle files will be in a directory /usr/local/instantclient_10_2/.

[root@tubes instantclient]# mv instantclient_10_2/ /usr/local/
[root@tubes instantclient]# ls /usr/local
appx   bin  etc  games  include  instantclient_10_2  lib  libexec  sbin  share  src
[root@tubes instantclient]# 
[root@tubes local]# cd /usr/local/instantclient_10_2/

Now we must register the Oracle Instant Client library files with the system to make the shared library's path known. I'm going to use ldconfig.

[root@tubes instantclient_10_2]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
[root@tubes instantclient_10_2]# echo /usr/local/instantclient_10_2/ >> /etc/ld.so.conf
[root@tubes instantclient_10_2]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/local/instantclient_10_2/
[root@tubes instantclient_10_2]# ldconfig
[root@tubes instantclient_10_2]# ldconfig -v|grep instant
/usr/local/instantclient_10_2:
[root@tubes instantclient_10_2]# ldconfig -v|grep libclnt
	libclntsh.so.10.1 -> libclntsh.so.10.1
[root@tubes instantclient_10_2]#

Now we must set the APPX environment variable APPX_OCI_DIR to the location that contains the Oracle Instant Client library files. Again, in our case that is /usr/local/instantclient_10_2/.

[root@tubes instantclient_10_2]# echo APPX_OCI_DIR=/usr/local/instantclient_10_2/ >> /usr/local/appx/data/appx.env
[root@tubes instantclient_10_2]# tail /usr/local/appx/data/appx.env
#
#  Setting APPX_UNIXIO_PROCS will cause Appx to include the current process name
#  in the logfile.
#
#APPX_UNIXIO_STATS=/tmp/file_stats
#APPX_UNIXIO_PROCS=1
#APPX_MONITOR_KEY=12345
#APPX_MONITOR_SLOTS=100

APPX_OCI_DIR=/usr/local/instantclient_10_2/
[root@tubes instantclient_10_2]#

Now let's verify that we can connect using Oracle's SQL*Plus with user=scott, password=password, server=tubes, port=1521, and Oracle Instance=appx. Substitute your own values for instance, server, port, and user.

[root@tubes instantclient_10_2]# /usr/local/instantclient_10_2/sqlplus scott/password@tubes:1521/appx

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 4 09:17:55 2008

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "SQL*Plus Report" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SCOTT"
verify ON
wrap : lines will be wrapped
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[root@tubes instantclient_10_2]#