Executing a multiple line statement

[MrTrix]

Hello:

I'm having a problem formulating the code to execute a multiple line
command. I'm trying to execute something like:

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

I know the change to rowcount will persist, but I'm having problems
formulating the rest of it. Specifically, I'm having problems
declaring the variable and executing the while loop.

$query = "declare @rowct int \n select @rowct = 1 \n" doesn't seem to
be working probably due to the interpretation of the "\n".

I was unable to find anything at perldoc or cpan searching for DBD or
DBI modules. Any assistance would be appreciated.

Thanks

John

 

[Jürgen Exner]

I'm having a problem formulating the code to execute a multiple line
command. I'm trying to execute something like:

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

This has not even a remote resemblance to Perl.
Are you sure you are in the right NG?

jue

 

[MrTrix]

Hello:

Sorry about the confusion. This is a question about formulating
Sybase SQL queries in Perl. Let me put it into more of a perl
context:

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

 

[Kris Wempa]

What is the statement separator in Sybase SQL ? In MySQL, you can execute
mulitple queries by separating them with a ";". Perhaps, you can do
something similar in Sybase SQL.

 

[Roy Johnson]

Hello:

I'm having a problem formulating the code to execute a multiple line
command. I'm trying to execute something like:

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

I know the change to rowcount will persist, but I'm having problems
formulating the rest of it. Specifically, I'm having problems
declaring the variable and executing the while loop.

$query = "declare @rowct int \n select @rowct = 1 \n" doesn't seem to
be working probably due to the interpretation of the "\n".

My advice would be to do your programming in Perl, rather than trying
to write Sybase code. That is, write the loop code in perl and have
several parameterized SQL statements for interacting with the database
as necessary. I don't know what the block is supposed to be doing. Is
there some reason you can't write it in Perl?

I can't speak for Sybase, but DBD::Oracle will accept blocks of
PL/SQL, if that's what I want to do. For that, each line would need to
have a semicolon on the end, and the whole thing would need to be
wrapped in a BEGIN/END. But that's Oracle, where such blocks are
processed as single statements. The rule is that you can only pass a
single statement via DBD. If Sybase understands the block above as
multiple statements, which are handled by its interactive interpreter,
you're out of luck. (In Oracle, an equivalent thing that wouldn't work
would be declaring something as VARIABLE.)

 

[JohnnyQ]

If Sybase has a line terminator, I'm not aware of it. I may have to
do it programatically in Perl...

Thanks!

 

[MIchael Peppler]

Hello:

I'm having a problem formulating the code to execute a multiple line
command. I'm trying to execute something like:

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

I know the change to rowcount will persist, but I'm having problems
formulating the rest of it. Specifically, I'm having problems
declaring the variable and executing the while loop.

You simply need to escape the '@' signs, like so:

my $sql = "
set rowcount 100000
declare \@rowct int
select \@rowct = 1
while (\@rowct > 0)
begin
delete Foo where creationDate < dateadd(day, -5, getdate())
select \@rowct = \@\@rowcount
end
";

Note that you really should reset rowcount to 0 after completing the operation.

Michael

 

[MrTrix]

It worked!!! Thanks so much for your help

- john

 

[Roy Johnson]

It worked!!! Thanks so much for your help

- john

If you're not interpolating variables or special characters, you
should be using single quotes, anyway.