Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Executing a multiple line statement

Reply
Thread Tools

Executing a multiple line statement

 
 
MrTrix
Guest
Posts: n/a
 
      10-02-2003
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
 
Reply With Quote
 
 
 
 
Jürgen Exner
Guest
Posts: n/a
 
      10-03-2003
MrTrix wrote:
> 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


 
Reply With Quote
 
 
 
 
MrTrix
Guest
Posts: n/a
 
      10-03-2003
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

"Jürgen Exner" <(E-Mail Removed)> wrote in message news:<0H3fb.40066$(E-Mail Removed)>. ..

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

 
Reply With Quote
 
Kris Wempa
Guest
Posts: n/a
 
      10-03-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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
>
> "Jürgen Exner" <(E-Mail Removed)> wrote in message

news:<0H3fb.40066$(E-Mail Removed)>. ..
>
> >
> > This has not even a remote resemblance to Perl.
> > Are you sure you are in the right NG?
> >
> > jue



 
Reply With Quote
 
Roy Johnson
Guest
Posts: n/a
 
      10-03-2003
http://www.velocityreviews.com/forums/(E-Mail Removed) (MrTrix) wrote in message news:<(E-Mail Removed). com>...
> 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.)
 
Reply With Quote
 
JohnnyQ
Guest
Posts: n/a
 
      10-03-2003
If Sybase has a line terminator, I'm not aware of it. I may have to
do it programatically in Perl...

Thanks!
 
Reply With Quote
 
MIchael Peppler
Guest
Posts: n/a
 
      10-05-2003
(E-Mail Removed) (MrTrix) wrote in message news:<(E-Mail Removed). com>...
> 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
 
Reply With Quote
 
MrTrix
Guest
Posts: n/a
 
      10-06-2003
It worked!!! Thanks so much for your help

- john
 
Reply With Quote
 
Roy Johnson
Guest
Posts: n/a
 
      10-06-2003
(E-Mail Removed) (MrTrix) wrote in message news:<(E-Mail Removed). com>...
> 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Else statement executing when it shouldnt eli m Python 30 01-25-2013 08:15 AM
executing an sql statement in perl MMWJones@googlemail.com Perl Misc 11 05-22-2007 08:00 PM
breaking a long arithmetic statement into multiple line Sean Perl Misc 4 12-05-2006 06:30 AM
dbi ODBC problem executing prepared statement taking datetime type niall.macpherson@ntlworld.com Perl Misc 0 04-27-2006 03:23 PM
better way of executing expression/statement in C? David Faden Python 1 05-21-2004 09:21 PM



Advertisments