Help with DBI to get HDD size of C drive of remote PC running MS-SQL

Hi Guys

I hope someone can help me. I know the SQL works fine, but I can't work
out how to get it to work with DBIx::SQLEngine.

Cheers


Dave

This is the SQL I want to execute (and works from SQL Management Studio
2005):
*********Begin SQL Code*********
IF EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'GetDriveSize'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.GetDriveSize
PRINT 'Dropped dbo.GetDriveSize'
END

GO

CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1)) RETURNS
NUMERIC(20)
BEGIN
DECLARE @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv
INTEGER, @drivesize VARCHAR(20)
SET @getdrive = 'GetDrive("' + @driveletter + '")'
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT
IF @rs = 0
EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
IF @rs = 0
EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
SET @drivesize = NULL
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso
RETURN @drivesize
END

GO

SELECT round(dbo.GetDriveSize('C')/(1073741824),0)
*********End SQL Code*********

This is my Perl:

*********Begin Perl Code*********
use DBIx::SQLEngine;

$sqllist1 = "IF EXISTS (\nSELECT 1 FROM
INFORMATION_SCHEMA.ROUTINES\nWHERE ROUTINE_NAME = 'GetDriveSize'\nAND
ROUTINE_SCHEMA = 'dbo'\nAND ROUTINE_TYPE = 'FUNCTION'\n)\nBEGIN\nDROP
FUNCTION dbo.GetDriveSize\nEND\nGO";
$sqllist2 = "CREATE FUNCTION dbo.GetDriveSize (\@driveletter CHAR(1))
RETURNS NUMERIC(20)\nBEGIN\nDECLARE \@rs INTEGER, \@fso INTEGER,
\@getdrive VARCHAR(13), \@drv INTEGER, \@drivesize VARCHAR(20)\nSET
\@getdrive = 'GetDrive(\"' + \@driveletter + '\")'\nEXEC \@rs =
sp_OACreate 'Scripting.FileSystemObject', \@fso OUTPUT\nIF \@rs =
0\nEXEC \@rs = sp_OAMethod \@fso, \@getdrive, \@drv OUTPUT\nIF \@rs =
0\nEXEC \@rs = sp_OAGetProperty \@drv,'TotalSize', \@drivesize
OUTPUT\nIF \@rs<> 0\nSET \@drivesize = NULL\nEXEC sp_OADestroy
\@drv\nEXEC sp_OADestroy \@fso\nRETURN \@drivesize\nEND\nGO";
$sqllist3 = "SELECT round(dbo.GetDriveSize('C')/(1073741824),0)";


$ip = "1.1.1.1";
print STDERR "ip = **$ip**\n";

my $dsnSQL_Store = "Driver={SQL Server};Server=$ip;Database=DB";

if (my $dbhstr = DBI->connect( "dbi:ODBC:$dsnSQL_Store", "sa", "wibble"
)) {
print STDERR "Connected to $device_id database!\n";
print STDERR "sql = $sql\n";
my $fish1;
print STDERR "sqllist = $sqllist1\n";
$fish1 = $dbhstr->prepare($sqllist1);
$fish1->execute();
print STDERR "sqllist = $sqllist2\n";
$fish2 = $dbhstr->prepare($sqllist2);
$fish2->execute();
my $fish = $dbhstr->selectall_array($sqllist3);
print STDERR "Free Gb = $fish\n"
}
else {
warn "Can't connect to $ip";
print "***ERR***\n";
}
*********End Perl Code*********


*********Start Sys Info*********
Windows XP SP3
Perl 5.8.8 Build 822
DBI 1.607
DBIx-SQLEngine 0.93
MS-SQL 2005 Express
*********End Sys Info*********

David Evans
Monsoon Accessorize Limited
1 Nicholas Road
London
W11 4AN

Tel:+44(0)2033723411
Fax:+44(0)2033723040
E-mail:devans@monsoon.co.uk
Please consider the environment before printing this email.



_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

Re: Help with DBI to get HDD size of C drive of remote PC running

From: "David Evans"
> This is my Perl:
>
> *********Begin Perl Code*********
> use DBIx::SQLEngine;
>
> $sqllist1 = "IF EXISTS (\nSELECT 1 FROM
> INFORMATION_SCHEMA.ROUTINES\nWHERE ROUTINE_NAME = 'GetDriveSize'\nAND
> ROUTINE_SCHEMA = 'dbo'\nAND ROUTINE_TYPE = 'FUNCTION'\n)\nBEGIN\nDROP
> FUNCTION dbo.GetDriveSize\nEND\nGO";

AWWWWWW.
First, you can put normal newlines into string literals in Perl and
second you can use the "quote-like operators" (see perldoc perlop) or
here-docs so that you do not have to keep on escaping quotes:

$sqllist1 = qq{
IF EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'GetDriveSize'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.GetDriveSize
PRINT 'Dropped dbo.GetDriveSize'
END

GO
};

or

sqllist1 = <<'*END*';
IF EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'GetDriveSize'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.GetDriveSize
PRINT 'Dropped dbo.GetDriveSize'
END

GO
*END*

> )) {
> print STDERR "Connected to $device_id database!\n";
> print STDERR "sql = $sql\n";
> my $fish1;
> print STDERR "sqllist = $sqllist1\n";
> $fish1 = $dbhstr->prepare($sqllist1);
> $fish1->execute();
> print STDERR "sqllist = $sqllist2\n";
> $fish2 = $dbhstr->prepare($sqllist2);
> $fish2->execute();
> my $fish = $dbhstr->selectall_array($sqllist3);
> print STDERR "Free Gb = $fish\n"
> }
> ...

And the error you received was ... ?

Jenda
===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs