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 ( [at] driveletter CHAR(1)) RETURNS
NUMERIC(20)
BEGIN
DECLARE [at] rs INTEGER, [at] fso INTEGER, [at] getdrive VARCHAR(13), [at] drv
INTEGER, [at] drivesize VARCHAR(20)
SET [at] getdrive = 'GetDrive("' + [at] driveletter + '")'
EXEC [at] rs = sp_OACreate 'Scripting.FileSystemObject', [at] fso OUTPUT
IF [at] rs = 0
EXEC [at] rs = sp_OAMethod [at] fso, [at] getdrive, [at] drv OUTPUT
IF [at] rs = 0
EXEC [at] rs = sp_OAGetProperty [at] drv,'TotalSize', [at] drivesize OUTPUT
IF [at] rs<> 0
SET [at] drivesize = NULL
EXEC sp_OADestroy [at] drv
EXEC sp_OADestroy [at] fso
RETURN [at] 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 (\ [at] driveletter CHAR(1))
RETURNS NUMERIC(20)\nBEGIN\nDECLARE \ [at] rs INTEGER, \ [at] fso INTEGER,
\ [at] getdrive VARCHAR(13), \ [at] drv INTEGER, \ [at] drivesize VARCHAR(20)\nSET
\ [at] getdrive = 'GetDrive(\"' + \ [at] driveletter + '\")'\nEXEC \ [at] rs =
sp_OACreate 'Scripting.FileSystemObject', \ [at] fso OUTPUT\nIF \ [at] rs =
0\nEXEC \ [at] rs = sp_OAMethod \ [at] fso, \ [at] getdrive, \ [at] drv OUTPUT\nIF \ [at] rs =
0\nEXEC \ [at] rs = sp_OAGetProperty \ [at] drv,'TotalSize', \ [at] drivesize
OUTPUT\nIF \ [at] rs<> 0\nSET \ [at] drivesize = NULL\nEXEC sp_OADestroy
\ [at] drv\nEXEC sp_OADestroy \ [at] fso\nRETURN \ [at] 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 [at] monsoon.co.uk
Please consider the environment before printing this email.



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

From: "David Evans" <devans [at] monsoon.co.uk>
> 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 [at] 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 [at] listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs