SQL Developer Queries
General ODBC Access 🙁
WAMP and PDO
Prerequisites
Strongly Consider Using SSL
Install Drivers
Use PDO to Connect to Database

This may be a niche setup, but I’m going to try to document it here, in case anyone else is interested in how this gets done.

If you are a school using hosted PowerSchool (i.e., it’s hosted with them as a Software-as-a-Service and not on your own in-house servers) and you are a technical contact for your school, you can request VPN/ODBC access for your school. You’re allowed only one active VPN session at a time, so keep that in mind.

This could be handy if you want to test (and troubleshoot) some SELECT queries directly on the database using SQL Developer before putting those into custom pages (e.g., using [tlist_sql]). You can also set up your own PHP-based reporting, which can be a lot more powerfully custom without having to do extremely elaborate SQL queries or push around badly-rendered HTML using JavaScript afterwards for clean-up.

After you get VPN access and credentials from PowerSchool, go ahead and download their recommended VPN client and connect using the credentials. If you can VPN into the server and then launch up SQL Developer and get a connection to your particular server (again, using the credentials PowerSchool provides you), go ahead and run a quick test query. Here’s an example:

For a lot of schools, that may be the extent of how they use the VPN access.

I did a lot of Googling and searching around for how to get ODBC access in Windows working. There was no clear documentation on this, and eventually—after many hours of trial and error—I did get something sort of working, but it took so much flailing that I didn’t have the time and energy to isolate what exactly worked. Unfortunately, PowerSchool’s own documentation on the issue is incomplete with no clarification: Oracle ODBC Configuration and Client Installation Guide for PowerSchool 9.x.

If you want to take the dive and aren’t an Oracle expert, be prepared for a lot of randomly downloaded .zips with random files inside and not many setup.exe executables with actual wizards.

WAMP is just a Windows-modified LAMP stack. So instead of Linux Apache MySQL PHP, WAMP is Windows Apache MySQL PHP. If you have a Windows 2008 or Windows 2012 server to put this on, great (you may have to apply this fix before installing WAMP). You can also install it on a Windows desktop computer.

1. Comfort with PHP. I actually picked a WAMP setup specifically because of my own comfort with PHP. If you aren’t already comfortable with PHP, you may be better off just learning some JQuery and JavaScript to learn how to work with custom pages. It’s a steep learning curve, but the idea with using a WAMP server is to take advantage of any existing comfort you have with PHP.
2. Physical Security. This WAMP server is going to be VPN-connected to PowerSchool with direct access to your database. If you can virtualize it in a secure server room or in the Cloud, great. If you’re using an old desktop, lock it up and treat it as a server in a server room.
3. You know how to use a text editor. Find your favorite one. If you don’t have a favorite, I’d recommend Notepad++.

If you are a networking pro, obviously install proper certificates and get everything set up the official way with a certificate authority. Even if you aren’t and even if your server is only an internal (not public-facing) server, please strongly consider enabling SSL.

I’m assuming most reports you’d run out of PowerSchool would have some kind of sensitive data, which means you’ll probably be enabling some kind of authentication to see it (PowerSchool single sign-on, Google sign-on, LDAP/Active Directory), which means, even with a self-signed certificate that will give your users a warning, encrypted transmission of data (https) is still better than none at all (http).

You can start with How To: Set up Self-signed SSL Certificate for WAMP, which is good but leaves out a couple of things.

First of all, when you’re editing C:\wamp\bin\apache\apache2.4.9\conf\extra\httpd_ssl.conf, be sure to scroll all the way to the bottom of the file and modify that last path as well to be in line with your other error log paths.

Also, even after following those instructions, your server still won’t be accessible, because it will silently fail in the background, because the server.key is encrypted. You’ll get yummy errors in your log like

In order to connect to the database using PDO, we’ll have to install drivers. Go to Oracle Instant Client Downloads. For Windows 64-bit, I downloaded Instant Client Package – Basic: All files required to run OCI, OCCI, and JDBC-OCI applications, which comes as a .zip file.

You can then extract the .zip file’s contents to a location of your choosing. I picked C:\Oracle\instantclient_12_1.

Next, launch up Advanced System Settings.

Click Environment Variables.

Select Path and then click Edit. Make sure to put the path to your Instant Client at the very beginning of the line. If you put the unzipped contents where I put mine, you should enter

It appears as if there’s a way in the menus to enable the php_pdo_oci extension on a WAMP server, but I didn’t find that to work, for some reason. You might have better luck with it.

Instead, I just went directly into the C:\wamp\bin\php\php5.5.12\php.ini file. There, I found and uncommented the

You may have to restart your WAMP services to have the change take effect.

Make sure you’ve connected via VPN.

Then open up the c:\wamp\www\index.php file using a text editor and try this, substituting in your database credentials for username and password, your server’s IP address for ipaddress, your port number for port, and your SID for sid.

One important thing to note here is the field names fetched via PDO using OCI will return as capitalized fields. If you try to

If it all worked, you should see 50 of your school’s students’ names appear when you go to the server’s webpage. Not that that’s all set up, you can put whatever authentication you want and start writing some more useful queries.

P.S. Turns out VPN automatically disconnects after about a week or so, so keep that in mind. You may be able to write some kind of reconnection script… or just manually reconnect when it happens.