multiple SQL line query via Perl

[JohnnyQ]

Hello:

I posted this in the comp.lang.perl, but I saw a thread that said it
was abandoned. Sorry if this is a multiple post.

This is a question about formulating Sybase SQL queries in Perl.

I can certainly do single command lines in perl using:

$dbh = DBI->connect("dbi:Sybase:server=$dbHost", $dbUser, $dbPass);

if (!defined $dbh) { death ("Could not connect to database\n."); }
else { print LOGFILE "Connected to database.\n"; }

$query = "use ${dbDatabase}";
$sth = $dbh->prepare(${query});
$sth->execute;

$query = "set rowcount 100000";
$sth = $dbh->prepare(${query});
$sth->execute;

However, as fas as I can tell Sybase needs to have variables in the
executable block that they are used. So, I can't do something like:

$query = " declare @rowct int";
$sth = $dbh->prepare(${query});
$sth->execute;

$query = "select @rowct =1";
$sth = $dbh->prepare(${query});
$sth->execute;

Nor can I combine statements to do something like:

$query = "declare @rowct int\n select @rowct =1";
$sth = $dbh->prepare(${query});
$sth->execute;

So, my problem is that I have to find a way to group the following SQL
statements together and have them execute at once:

declare @rowct int
select @rowct = 1
while (@rowct > 0)
begin
delete Foo where creationDate < dateadd(day, -5, getdate())
select @rowct = @@rowcount
end

Thanks,

John

 

[Greg Bacon]

: [...]
: So, my problem is that I have to find a way to group the following SQL
: statements together and have them execute at once:
:
: declare @rowct int
: select @rowct = 1
: while (@rowct > 0)
: begin
: delete Foo where creationDate < dateadd(day, -5, getdate())
: select @rowct = @@rowcount
: end

What about the following?

$dbh->do(q{
declare @rowct int
select @rowct = 1
while (@rowct > 0)
begin
delete Foo where creationDate < dateadd(day, -5, getdate())
select @rowct = @@rowcount
end
});

Greg