We’re one step closer to the happy world of SSN Remediation. Our development SIS database is now using the HP-ID, which is the new primary key we’ve designated to replace the SSN in SIS. HP-IDs look distinctly familiar: They’re 9-digit numbers just like SSNs. That way, we cleverly avoid having to rewrite most of the SIS code to deal with the new IDs.

On the myUMBC side of things, I just need to change the code to look for the HP-ID LDAP attribute, instead of socialSecurityNumber. Which brings up a temporary problem: HP-IDs aren’t in LDAP yet. So how do I look them up? Well, right now, I’m still querying the SSN in LDAP, then bouncing that against a super-top-secret SSN-to-HP-ID translation table in SIS. That does the job nicely for testing purposes, but I must stress that it’s only a temporary measure.

Next up, I’m doing some hacking on the class list code in the Perl myUMBC codebase, to add in some new features (part of the big drive to improve student retention rates). I’m looking at the class list code, and I’m thinking I could really speed it up if I changed it to use prepared statements with bind variables, but the code is oh-so not set up for that. I may take a closer look at it tomorrow to see how hard it would be to do.

[More:]

The class list code, as it currently is, works like this:

foreach ( student in the class )
   create perl object with student info
   tell perl object to fetch info from DB
   display student info in table
end

The student info object it creates, has all of the SQL necessary to pull the student’s data out of SIS. It’s doing this stuff over and over, which is where we could benefit from prepared statements. To do it, though, we’d need to create the prepared statements ahead of time (outside the loop), then pass them to each student object that we create. I don’t think it’d be really hard to do, just need to figure out the best way to do it.

Followup… tried this out, seems to work. The challenge is going to be figuring out where to create all the prepared statements, and how to manage them, without obfuscating the code even more than it already is. Seems like it might make sense to do it as part of the db_connect and db_disconnect routines… will mess with it more tomorrow.

Actually, I think I’ll go with a slight variation on that theme. Rather than creating all the prepared statements when I connect, I’ll modify my db_prepare routine to maintain a hash of statement handles. The first time it handles a particular statement, it’ll prepare it and cache the statement handle. Then on subsequent calls, it’ll just retrieve the previously cached handle. When I disconnect, I’ll just walk through the hash and call finish on all of them. Should work great, with a minimum amount of surgery on the code.