Executing a multiple line statement

Discussion in 'Perl' started by MrTrix, Oct 2, 2003.

  1. MrTrix

    MrTrix Guest

    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
    MrTrix, Oct 2, 2003
    #1
    1. Advertising

  2. 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
    Jürgen Exner, Oct 3, 2003
    #2
    1. Advertising

  3. MrTrix

    MrTrix Guest

    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" <> wrote in message news:<0H3fb.40066$>...

    >
    > This has not even a remote resemblance to Perl.
    > Are you sure you are in the right NG?
    >
    > jue
    MrTrix, Oct 3, 2003
    #3
  4. MrTrix

    Kris Wempa Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message

    news:<0H3fb.40066$>...
    >
    > >
    > > This has not even a remote resemblance to Perl.
    > > Are you sure you are in the right NG?
    > >
    > > jue
    Kris Wempa, Oct 3, 2003
    #4
  5. MrTrix

    Roy Johnson Guest

    (MrTrix) wrote in message news:<>...
    > 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.)
    Roy Johnson, Oct 3, 2003
    #5
  6. MrTrix

    JohnnyQ Guest

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

    Thanks!
    JohnnyQ, Oct 3, 2003
    #6
  7. (MrTrix) wrote in message news:<>...
    > 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
    MIchael Peppler, Oct 5, 2003
    #7
  8. MrTrix

    MrTrix Guest

    It worked!!! Thanks so much for your help

    - john
    MrTrix, Oct 6, 2003
    #8
  9. MrTrix

    Roy Johnson Guest

    (MrTrix) wrote in message news:<>...
    > 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.
    Roy Johnson, Oct 6, 2003
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Faden
    Replies:
    1
    Views:
    393
    Rick L. Ratzel
    May 21, 2004
  2. Replies:
    0
    Views:
    192
  3. Sean
    Replies:
    4
    Views:
    171
    Ala Qumsieh
    Dec 5, 2006
  4. Replies:
    11
    Views:
    234
    Dr.Ruud
    May 22, 2007
  5. eli m
    Replies:
    30
    Views:
    330
    Frank Millman
    Jan 25, 2013
Loading...

Share This Page