Viewing Category: Perl  [clear category selection]

Windows TCP Stale Sockets

Last week I wrote a Perl script called that displays the executable filename of every process listening for, or with active, TCP connections. It turns out that when a process has been terminated, there can be a bit of time before the socket is cleaned up. In this situation, netstat will show that that process number zero created the socket. Doing a lookup on PID 0 returned unexpected data. I just checked in a version of the script that correctly handles stale sockets.

Perl RegEx with Negative Forward Lookahead

I've been hacking away at programming problems using Perl since 1996. However, I can't recall ever needing to construct a regular expression using a negative, forward lookahead. Until last week. And it was the perfect solution to a problem. Consider a set of files containing SQL SELECT statements from an old system that didn't require the AS keyword between an output column and alias name. Here's an example of the problem query:

SELECT COUNT(column) alias FROM table;

The desired statement is this:

SELECT COUNT(column) AS alias FROM table;

The following Perl regex matches only the statements that have the old syntax. The match groups are perfect for building a replacement statement.

my $sql = 'SELECT COUNT( rc FROM sys_user su'; if ($sql =~ /SELECT COUNT\(([_\.\*\w]+)\) (?!AS )([_\w]+)(.*)/) { $sql = "SELECT COUNT($1) AS $2$3"; }

After running this on all the files -- even multiple times -- the syntax will be fixed, and there won't be any statements with multiple AS keywords.