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@[...].uk
Please consider the environment before printing this email.
 
 

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