Using PDO on a WAMP server to run PHP reports from PowerSchool

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.

SQL Developer Queries

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:

SELECT * FROM (SELECT last_name, first_name FROM students WHERE enroll_status=0 ORDER BY last_name, first_name) WHERE ROWNUM=<50

You should get 50 of your students in the first part of the alphabet.

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

General ODBC 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 and PDO

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.

Prerequisites

  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++.

Strongly Consider Using SSL

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

AH02577: Init: SSLPassPhraseDialog builtin is not supported on Win32 (key file C:/wamp/bin/apache/apache2.4.9/conf/ssl.key/server.key)

or

AH02564: Failed to configure encrypted (?) private key C:/wamp/bin/apache/apache2.4.9/conf/ssl.key/server.key

More on that here:
Remove the passphrase from an existing OpenSSL key file.

Install Drivers

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

C:\Oracle\instantclient_12_1;

Apparently, there’s a way to have environment variables refresh without a reboot, but I would just reboot if you can.


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

extension=php_pdo_oci.dll

line (you can uncomment by removing the semi-colon from the beginning of the line).

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

Use PDO to Connect to Database

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.

<?php

$db_user=”username“;
$db_pass=”password“;

$dbc=new PDO(‘oci:dbname=ipaddress:port/sid‘, $db_user, $db_pass);

if(!$dbc){
echo “Not connected”;
} else {

$sth=$dbc->prepare(“SELECT * FROM
(SELECT last_name, first_name
FROM students
WHERE enroll_status=0
ORDER BY last_name)
WHERE ROWNUM<=50″);
$sth->execute();
while($row=$sth->fetch(PDO::FETCH_ASSOC)){
echo $row[‘LAST_NAME’] . ‘, ‘ , $row[‘FIRST_NAME’] . ‘<br />’;
// End fetching results
}

// Close the connection
$dbc=null;

// End checking the database connection worked
}

?>

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

echo $row[‘last_name‘]

you’ll get that the field doesn’t exist. You have to do

echo $row[‘LAST_NAME‘]

instead.

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.


Posted

in

by

Comments

3 responses to “Using PDO on a WAMP server to run PHP reports from PowerSchool”

  1. […] you don't want to use PDO on a WAMP server to connect to and report from your PowerSchool database, you may still want to do some sophisticated reporting from PowerSchool's custom […]

  2. Dubbs Avatar
    Dubbs

    Hey do you use a script to remain connected to the VPN for ODBC?

    Was wondering if you could share a script to remain connected because we are hosted users of PowerSchool also. I mostly just customize screens using TLIST_sql and jquery.
    But I am only beginner to intermediate at both.

    1. Alan Siu Avatar
      Alan Siu

      I don’t think PowerSchool allows you to be continuously connected to VPN.

Leave a Reply

Your email address will not be published. Required fields are marked *