File size, file count, file age - batch util

I wanted to know each of this:

• If a filesize (eg. the newest/latest one in a directory) is above or below a certain threshold.
• If a number of files in a dir is equal, above or below a certain limit.
• If the age of a file (eg. the newest/latest one), is above or below a certain age in seconds.

I turned to the batch search/overview sites and looked for inspiration, my findings was

• FileSizeComp is an elegant example in batch, but requires you know the filename.
• GetDirStats returns number of files, elegantly using dirlist from compact.
• I did not find a batch way to get mtime of a file.

So: two problems: I would need a way to find "the latest file" and then pipe that to one of the batch scripts, and I didnt find a ressource kit tool or batch way to get mtime from a file.

So: I made a simple perl script that can handle all of the above. And it also works cross platform.

There was someone who did an mtime (file age) check script in vbscript, i did not use it though.

Query MSSQL from perl

I mentioned how to connect to MSSQL from batch, eg. using osql.exe, but today I wanted to do the same from Perl.

There are many samples on Google, using Win32::OLE or Win32::ODBC. Usually finding the right connection string is the hurdle.

For the ODBC connection strings it can look like this:

$DSN = 'driver={SQL Server};Server=$hostname\\$instance;database=$db;uid=$u;pwd=$p;';
if (!($db = new Win32::ODBC($DSN))){ die "Error: " . Win32::ODBC::Error() . "\n"; }

For Win32::OLE connection string with password can look like this:
my $ConnStr="Provider=SQLOLEDB;Initial Catalog=$db;Data Source=$server;User ID=$u;Password=$p;Network Library=DBMSSOCN";

But I really want to avoid the user and password in scripts. So for Win32::OLE connection string integrated security, without password, can look like this:
my $ConnStr="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$d;Data Source=$s;use Procedure for Prepare=0;Connect Timeout=4;Trusted_Connection=Yes";
# Provider=SQLOLEDB.1 or Provider=SQLNCLI.1

Testing connection, create a query, execute it, and work with is pretty straight forward:
my $Conn = Win32::OLE-> new('ADODB.Connection');
$Conn-> Open($ConnStr);
my $err = Win32::OLE::LastError();
if (not $err eq "0") { print"FATAL: no connection, OLE error 0x%08x: $err\n"; exit; } else { print"Connected OK\n"; }
my $Statement = "select servername from servertable where x = 0 AND id = 11";
if(! ($RS = $Conn->Execute($Statement)))
{ print Win32::OLE->LastError() ; exit; }
while (! $RS->EOF) {
$servername= $RS->Fields(0)->value;
print"servername is: $servername\n";
$RS->MoveNext; }
$RS->Close;
$Conn->Close;

Just for future reference the ODBC SQL update code could look like this:
$SqlStatement = "insert into dbo.MyTable values (\'$var1\',$var2,$number,getdate())";
if ($db->Sql($SqlStatement)){ print "Error: " . $db->Error() . "\n"; $db->Close(); exit; }