Tested with PowerSchool 6.1.0 running under Mac OS X Server 10.4/PPC; Fedora Core 9 Linux x86, kernel 2.6.27.25-78.2.56.fc9.i686 with all current updates and SELinux DISABLED; php version 5.2.9 from base RPMs.

Prereqs:

On the PowerSchool server:

• Allow inbound TCP/1521 to PS on any firewall(s)

• Set a password for PSNavigator (via PowerSchool system admin pages)

• Enable ODBC for custom fields if you care about them (see PowerSource)

On the linux server, install these:

(via yum):

	unixODBC
	php-odbc
	libaio

(via Oracle.com Instant Client for Linux downloads page):

	oracle-instantclient11.2-basic-11.2.0.1.0-1.i386.rpm
	oracle-instantclient11.2-odbc-11.2.0.1.0-1.i386.rpm

Make sure to install those and any dependencies before you continue.

1. Set up library paths. I've established that the actual driver is libsqora.so.11.1. Other key libraries are under /usr/lib/oracle/11.2/client/lib/ too, so that needs to be in the LD path. So create /etc/ld.so.conf.d/oracle.conf and in it, put

   	/usr/lib/oracle/11.2/client/lib
   

   Then run ldconfig to update the cache.

2. Set up environment variables. Update /etc/profile and add lines for Oracle paths. I put my block after the HISTSIZE line and before the export PATH line:

   	LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/lib/oracle/11.2/client/lib"
   	export LD_LIBRARY_PATH
   	TNS_ADMIN="/usr/lib/oracle/11.2/client/bin"
   	export TNS_ADMIN
   	ORACLE_HOME="/usr/lib/oracle/11.2/client/bin"
   	export ORACLE_HOME
   

   Then type "source /etc/profile" to load the changes.

3. Create some config files. Go to /usr/lib/oracle/11.2/client/bin/ (or whatever you set the TNS_ADMIN and ORACLE_HOME paths to in /etc/profile) and create three files: listener.ora, sqlnet.ora, and tnsnames.ora. Populate them accordingly as shown below. Note that, in this example, the IP address of my PowerSchool server is 10.1.2.3. The port should remain 1521. If you kept the defaults during your PS install/upgrade, then the name of the PowerSchool server should remain "PSPRODDB". If yours is different, see the Troubleshooting section below.

   I only nominally understand the purposes and interaction of these files. I have color-paired the values that I've concluded must match each other across all these files.

   sqlnet.ora:

   SQLNET.AUTHENTICATION_SERVICES= (NTS)
   NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
   

   listener.ora:

   LISTENER =
      (DESCRIPTION_LIST =
         (DESCRIPTION =
            (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.3)(PORT = 1521))
            )
            (SERVICE_NAME = PSPRODDB)
            (SID = PSPRODDB)
         )
      )
   

   tnsnames.ora:

   POWERSCHOOL =
      (DESCRIPTION =
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.3)(PORT = 1521))
         )
      (CONNECT_DATA =
         (SERVICE_NAME = PSPRODDB)
         (SID = PSPRODDB)
      )
   )
   

4. Create some MORE config files. Go to /etc/ and create two files: odbcinst.ini and odbc.ini. Contents below. Again, color pairings to show relationships across these and the previous files.

   odbcinst.ini:

   [Oracle]
   Description     = ODBC for Oracle
   Driver	  = /usr/lib/oracle/11.2/client/lib/libsqora.so.11.1
   Setup	   = 
   FileUsage       = 1
   CPTimeout       = 
   CPReuse	 = 
   

   odbc.ini:

   [ps]
   Application Attributes = T
   Attributes = W
   BatchAutocommitMode = IfAllSuccessful
   BindAsFLOAT = F
   CloseCursor = F
   DisableDPM = F
   DisableMTS = T
   Driver = Oracle
   DSN = ps
   EXECSchemaOpt =
   EXECSyntax = T
   Failover = T
   FailoverDelay = 10
   FailoverRetryCount = 10
   FetchBufferSize = 64000
   ForceWCHAR = F
   Lobs = T
   Longs = T
   MetadataIdDefault = F
   QueryTimeout = T
   ResultSets = T
   ServerName = POWERSCHOOL
   SQLGetData extensions = F
   Translation DLL =
   Translation Option = 0
   DisableRULEHint = T
   UserID =
   

5. Test the connection:

   isql -v ps PSNavigator password
   

   Hopefully you'll see something like this:

   	+---------------------------------------+
   	| Connected!                            |
   	|                                       |
   	| sql-statement                         |
   	| help [tablename]                      |
   	| quit                                  |
   	|                                       |
   	+---------------------------------------+
   	SQL>
   

   You can now execute an SQL query at the prompt. For example, to get a count of all active students:

   	SQL> select count(*) from students where enroll_status = 0;
   	+-----------------------------------------+
   	| COUNT(*)                                |
   	+-----------------------------------------+
   	| 1955                                    |
   	+-----------------------------------------+
   	SQLRowCount returns -1
   	1 rows fetched
   

6. PHP. I went round and round with trying to set PATHs in both the php script itself and the apache config file; what ended up finally working was, making symlinks to the three Oracle files under /etc:

   	[root@ns2 ~]# cd /etc/
   	[root@ns2 etc]# ln -s /usr/lib/oracle/11.2/client/bin/tnsnames.ora .
   	[root@ns2 etc]# ln -s /usr/lib/oracle/11.2/client/bin/listener.ora .
   	[root@ns2 etc]# ln -s /usr/lib/oracle/11.2/client/bin/sqlnet.ora .
   

   Then, make a sample connect script:

   $user = "PSNavigator";
   $pass = "password";
   $dsn = "ps";  // needs to match the DSN defined in odbc.ini
   
   $link = odbc_connect($dsn, $user, $pass) 
   	or die( "Couldn't connect to ODBC: ".odbc_error() . "\n");
   
   $sql = "SELECT count(*) FROM students";
   
   $result = odbc_exec($link, $sql);
   echo "got " . odbc_num_fields($result). " results.\n";
   

   Load that in a browser; if you see "got 1 results." then you're golden. If not, check the httpd error logs, and check/correct the .ora files and retry.

Troubleshooting/Caveats/TO-DOs:

By default, the Mac OS X Server firewall configuration on the PowerSchool server did not allow connections to port 1521. Make sure you have basic connectivity between the linux box and the PS server:

[mbates@ns2 ~]$ telnet 10.1.2.3 1521
Trying 10.1.2.3...
Connected to 10.1.2.3.
Escape character is '^]'.

If you don't get the "Connected to" line, you might still have a firewall issue.

Many of my early problems were due to missing libraries, or library path problems. I learned from some Googling around that you can see whether a given library is missing some dependent/linked libraries with the "ldd" tool:

[root@ns2 ~]# ldd /usr/lib/oracle/11.2/client/lib/libsqora.so.11.1
	linux-gate.so.1 =>  (0x0096a000)
	libdl.so.2 => /lib/libdl.so.2 (0x009a6000)
	libm.so.6 => /lib/libm.so.6 (0x008af000)
	libpthread.so.0 => /lib/libpthread.so.0 (0x00d7c000)
	libnsl.so.1 => /lib/libnsl.so.1 (0x0092f000)
	libclntsh.so.11.1 => /usr/lib/oracle/11.2/client/lib/libclntsh.so.11.1 (0xb6274000)
	libodbcinst.so.1 => /usr/lib/libodbcinst.so.1 (0x00110000)
	libc.so.6 => /lib/libc.so.6 (0x00125000)
	/lib/ld-linux.so.2 (0x0079b000)
	libnnz11.so => /usr/lib/oracle/11.2/client/lib/libnnz11.so (0x00b2c000)
	libaio.so.1 => /usr/lib/libaio.so.1 (0x0028e000)

Each line of output here shows a library that the Oracle ODBC driver is looking for. If you run this command and see any lines that say "not found", then you're missing something or you have a bad path. In my case, I was missing libaio.so.1 which is part of the libaio package. (shrug)

If you think you've done everything right but you still can't get a connection, perhaps your database name is different. Log in to the PowerSchool server directly (ssh or Remote Desktop or whatever), get a Terminal window open, and execute the following to become the Oracle user, connect directly to the database, and ask for the db name. BE CAREFUL, you have full write privileges with this account and you can destroy the world with a few keystrokes.

	ps:~ admin$ sudo su - oracle
	Password: (enter the server's administrator password)
	ps:~ oracle$ sqlplus / as sysdba
	
	SQL*Plus: Release 10.1.0.5.0 - Production on Fri Feb 5 14:09:43 2010
	
	Copyright (c) 1982, 2005, Oracle.  All rights reserved.
		
	Connected to:
	Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
	With the Partitioning, OLAP and Data Mining options
	
	SYS@PSPRODDB AS SYSDBA> 
	     ^ substitute whatever it prints here 
	     for all the PSPRODDB occurrences in config files

References: Too many to list...Google searches for various combinations of the following terms: linux, odbc, oracle, dsn, (the text of all error messages), unixODBC, isql...you get the idea. I came across a variety of FAQs, wiki sites for DBAs, and Oracle docs, but by far the most useful information was from other sysadmins on forums and mailing lists asking questions and posting sample config files, etc.