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:[email protected]
Please consider the environment before printing this email.
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
