multiple SQL line query via Perl

Discussion in 'Perl Misc' started by JohnnyQ, Oct 3, 2003.

  1. JohnnyQ

    JohnnyQ Guest

    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
     
    JohnnyQ, Oct 3, 2003
    #1
    1. Advertising

  2. JohnnyQ

    Greg Bacon Guest

    In article <>,
    JohnnyQ <> wrote:

    : [...]
    : 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
    --
    Kelso: I'm not shallow, Fez. I just judge chicks by their looks.
     
    Greg Bacon, Oct 4, 2003
    #2
    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. Rob
    Replies:
    1
    Views:
    661
  2. Anonymous
    Replies:
    0
    Views:
    1,513
    Anonymous
    Oct 13, 2005
  3. Jeff Elkins

    SQL Query via python

    Jeff Elkins, May 21, 2005, in forum: Python
    Replies:
    6
    Views:
    737
    Frithiof Andreas Jensen
    May 24, 2005
  4. Steve Holden

    Re: SQL Query via python

    Steve Holden, May 22, 2005, in forum: Python
    Replies:
    1
    Views:
    345
    Scott David Daniels
    May 23, 2005
  5. Replies:
    5
    Views:
    235
    ara.t.howard
    Nov 6, 2007
Loading...

Share This Page