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]

Re: Executing a multiple line statement

MrTrix wrote:[color=blue]
> 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[/color]

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

jue

[MrTrix]

Re: Executing a multiple line statement

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:S ybase:server=$d bHost", $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(${quer y});
$sth->execute;

$query = "set rowcount 100000";
$sth = $dbh->prepare(${quer y});
$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(${quer y});
$sth->execute;

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

Nor can I combine statements to do something like:

$query = "declare @rowct int\n select @rowct =1";
$sth = $dbh->prepare(${quer y});
$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

"Jürgen Exner" <jurgenex@hotma il.com> wrote in message news:<0H3fb.400 66$ZR1.39556@nw rddc01.gnilink. net>...
[color=blue]
>
> This has not even a remote resemblance to Perl.
> Are you sure you are in the right NG?
>
> jue[/color]

[Kris Wempa]

Re: Executing a multiple line statement

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.


"MrTrix" <eurojohnny@yah oo.com> wrote in message
news:f1dec016.0 310030518.13097 6d9@posting.goo gle.com...[color=blue]
> 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:S ybase:server=$d bHost", $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(${quer y});
> $sth->execute;
>
> $query = "set rowcount 100000";
> $sth = $dbh->prepare(${quer y});
> $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(${quer y});
> $sth->execute;
>
> $query = "select @rowct =1";
> $sth = $dbh->prepare(${quer y});
> $sth->execute;
>
> Nor can I combine statements to do something like:
>
> $query = "declare @rowct int\n select @rowct =1";
> $sth = $dbh->prepare(${quer y});
> $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
>
> "Jürgen Exner" <jurgenex@hotma il.com> wrote in message[/color]
news:<0H3fb.400 66$ZR1.39556@nw rddc01.gnilink. net>...[color=blue]
>[color=green]
> >
> > This has not even a remote resemblance to Perl.
> > Are you sure you are in the right NG?
> >
> > jue[/color][/color]

[Roy Johnson]

Re: Executing a multiple line statement

eurojohnny@yaho o.com (MrTrix) wrote in message news:<f1dec016. 0310021329.5f70 22c3@posting.go ogle.com>...[color=blue]
> 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".[/color]

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]

Re: Executing a multiple line statement

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

Thanks!

[MIchael Peppler]

Re: Executing a multiple line statement

eurojohnny@yaho o.com (MrTrix) wrote in message news:<f1dec016. 0310021329.5f70 22c3@posting.go ogle.com>...[color=blue]
> 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.[/color]

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]

Re: Executing a multiple line statement

It worked!!! Thanks so much for your help

- john

[Roy Johnson]

Re: Executing a multiple line statement

eurojohnny@yaho o.com (MrTrix) wrote in message news:<f1dec016. 0310060743.32db 3d3c@posting.go ogle.com>...[color=blue]
> It worked!!! Thanks so much for your help
>
> - john[/color]

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