executing an sql statement in perl

Discussion in 'Perl Misc' started by MMWJones@googlemail.com, May 21, 2007.

  1. Guest

    If this is the wong board to post this question can someone guide me
    to the correct board...

    I have an sql statement that is in single quotes


    or $dbh->do ('update test set (mudid, date_of_last_entrym
    number_of_entries) = (select mudid, max(thetime) as
    date_of_last_entry, count(*) as number_of_entries from log@test l,
    user@test u
    where l.user = u.user
    and mudid = '$a'
    and thetime > sysdate - 90
    group by mudid)')

    where $a is defined as the id.

    however i don't think it likes the 2 sets of single quotes. I have to
    use single quotes as otherwise the @ symbols aren't read correctly.

    The below sql statement works fine directly in my sql program (TOAD)
    and this is why i think it is a perl problem:

    select id, max(thetime) as date_of_last_entry, count(*) as
    number_of_entries from log@test l, user@test u
    where l.user = u.user
    and id = 'MATT'
    and thetime > sysdate - 90
    group by id


    any ideas?

    Thanks,
     
    , May 21, 2007
    #1
    1. Advertising

  2. <> wrote:
    > If this is the wong board to post this question can someone guide me
    > to the correct board...
    >
    > I have an sql statement that is in single quotes
    >
    >
    > or $dbh->do ('update test set (mudid, date_of_last_entrym
    > number_of_entries) = (select mudid, max(thetime) as
    > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > user@test u
    > where l.user = u.user
    > and mudid = '$a'
    > and thetime > sysdate - 90
    > group by mudid)')
    >
    > where $a is defined as the id.
    >
    > however i don't think it likes the 2 sets of single quotes. I have to
    > use single quotes as otherwise the @ symbols aren't read correctly.



    You have to use double quotes or the $a won't be interpolated correctly.


    > any ideas?



    Use double quotes, and backslash the at-signs.


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
     
    Tad McClellan, May 21, 2007
    #2
    1. Advertising

  3. Paul Lalli Guest

    On May 21, 6:11 am, ""
    <> wrote:
    > If this is the wong board to post this question can someone guide me
    > to the correct board...
    >
    > I have an sql statement that is in single quotes
    >
    > or $dbh->do ('update test set (mudid, date_of_last_entrym
    > number_of_entries) = (select mudid, max(thetime) as
    > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > user@test u
    > where l.user = u.user
    > and mudid = '$a'
    > and thetime > sysdate - 90
    > group by mudid)')
    >
    > where $a is defined as the id.
    >
    > however i don't think it likes the 2 sets of single quotes. I have to
    > use single quotes as otherwise the @ symbols aren't read correctly.
    >
    > The below sql statement works fine directly in my sql program (TOAD)
    > and this is why i think it is a perl problem:
    >
    > select id, max(thetime) as date_of_last_entry, count(*) as
    > number_of_entries from log@test l, user@test u
    > where l.user = u.user
    > and id = 'MATT'
    > and thetime > sysdate - 90
    > group by id
    >
    > any ideas?


    Because you need interpolation to occur in this string, you NEED to
    use double quotes to surround the string. To prevent Perl from
    thinking the @ characters start an array, simply put a backslash in
    front of them.

    $dbh->do ("update test set (mudid, date_of_last_entrym
    number_of_entries) = (select mudid, max(thetime) as
    date_of_last_entry, count(*) as number_of_entries from log\@test l,
    user\@test u
    where l.user = u.user
    and mudid = '$a'
    and thetime > sysdate - 90
    group by mudid)");


    Alternatively, do not put the variable within the SQL directly. Use
    placeholders instead.

    $dbh->do ('update test set (mudid, date_of_last_entrym
    number_of_entries) = (select mudid, max(thetime) as
    date_of_last_entry, count(*) as number_of_entries from log@test l,
    user@test u
    where l.user = u.user
    and mudid = ?
    and thetime > sysdate - 90
    group by mudid)', {}, $a);

    See also:
    perldoc DBI
    perldoc perlsyn
    perldoc perldata

    Hope this helps,
    Paul Lalli
     
    Paul Lalli, May 21, 2007
    #3
  4. Thomas J. Guest

    On 21 Mai, 12:11, "" <>
    wrote:
    > If this is the wong board to post this question can someone guide me
    > to the correct board...
    >
    > I have an sql statement that is in single quotes
    >
    > or $dbh->do ('update test set (mudid, date_of_last_entrym
    > number_of_entries) = (select mudid, max(thetime) as
    > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > user@test u
    > where l.user = u.user
    > and mudid = '$a'
    > and thetime > sysdate - 90
    > group by mudid)')
    >
    > where $a is defined as the id.
    >


    If you would "print" your sql-statement you will probably see the
    mistake.

    Try sting-concat via "." ...
    eg. 'bla'.$a.'bla'

    hth, Thomas
     
    Thomas J., May 21, 2007
    #4
  5. Mumia W. Guest

    On 05/21/2007 05:11 AM, wrote:
    > If this is the wong board to post this question can someone guide me
    > to the correct board...
    >
    > I have an sql statement that is in single quotes
    >
    >
    > or $dbh->do ('update test set (mudid, date_of_last_entrym
    > number_of_entries) = (select mudid, max(thetime) as
    > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > user@test u
    > where l.user = u.user
    > and mudid = '$a'
    > and thetime > sysdate - 90
    > group by mudid)')
    >
    > where $a is defined as the id.
    >
    > however i don't think it likes the 2 sets of single quotes. I have to
    > use single quotes as otherwise the @ symbols aren't read correctly.
    > [...]


    Use double-quotes and put backslashes before the @ symbols to prevent
    them (and the characters that follow) from being interpreted as array names.
     
    Mumia W., May 21, 2007
    #5
  6. Guest

    "" <> wrote:
    > If this is the wong board to post this question can someone guide me
    > to the correct board...
    >
    > I have an sql statement that is in single quotes


    Use place holders. That is, use a ? in the SQL string, and then pass in
    the value of $a separately.

    $dbh->do('whatever where mudid=?', undef, $a);

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , May 21, 2007
    #6
  7. Xicheng Jia Guest

    On May 21, 11:23 pm, Dennis Roesler <> wrote:
    > Paul Lalli wrote:
    > > On May 21, 6:11 am, ""
    > > <> wrote:
    > >> If this is the wong board to post this question can someone guide me
    > >> to the correct board...

    >
    > >> I have an sql statement that is in single quotes

    >
    > >> or $dbh->do ('update test set (mudid, date_of_last_entrym
    > >> number_of_entries) = (select mudid, max(thetime) as
    > >> date_of_last_entry, count(*) as number_of_entries from log@test l,
    > >> user@test u
    > >> where l.user = u.user
    > >> and mudid = '$a'
    > >> and thetime > sysdate - 90
    > >> group by mudid)')

    >
    > >> where $a is defined as the id.

    >
    > >> however i don't think it likes the 2 sets of single quotes. I have to
    > >> use single quotes as otherwise the @ symbols aren't read correctly.

    >
    > >> The below sql statement works fine directly in my sql program (TOAD)
    > >> and this is why i think it is a perl problem:

    >
    > >> select id, max(thetime) as date_of_last_entry, count(*) as
    > >> number_of_entries from log@test l, user@test u
    > >> where l.user = u.user
    > >> and id = 'MATT'
    > >> and thetime > sysdate - 90
    > >> group by id

    >
    > >> any ideas?

    >
    > > Because you need interpolation to occur in this string, you NEED to
    > > use double quotes to surround the string. To prevent Perl from
    > > thinking the @ characters start an array, simply put a backslash in
    > > front of them.

    >
    > > $dbh->do ("update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log\@test l,
    > > user\@test u
    > > where l.user = u.user
    > > and mudid = '$a'
    > > and thetime > sysdate - 90
    > > group by mudid)");

    >
    > > Alternatively, do not put the variable within the SQL directly. Use
    > > placeholders instead.

    >
    > > $dbh->do ('update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > > user@test u
    > > where l.user = u.user
    > > and mudid = ?
    > > and thetime > sysdate - 90
    > > group by mudid)', {}, $a);

    >
    > > See also:
    > > perldoc DBI
    > > perldoc perlsyn
    > > perldoc perldata

    >
    > I don't think place holders will work with $dbh->do because that does a
    > prepare and execute in one go.


    why not, I use placeholder with $dbh->do(...) on my website, and it
    works pretty well so far. :)

    Regards,
    Xicheng
     
    Xicheng Jia, May 22, 2007
    #7
  8. Paul Lalli Guest

    On May 21, 11:23 pm, Dennis Roesler <> wrote:
    > Paul Lalli wrote:
    > > On May 21, 6:11 am, ""
    > > <> wrote:
    > >> If this is the wong board to post this question can someone guide me
    > >> to the correct board...

    >
    > >> I have an sql statement that is in single quotes

    >
    > >> or $dbh->do ('update test set (mudid, date_of_last_entrym
    > >> number_of_entries) = (select mudid, max(thetime) as
    > >> date_of_last_entry, count(*) as number_of_entries from log@test l,
    > >> user@test u
    > >> where l.user = u.user
    > >> and mudid = '$a'
    > >> and thetime > sysdate - 90
    > >> group by mudid)')

    >
    > >> where $a is defined as the id.

    >
    > >> however i don't think it likes the 2 sets of single quotes. I have to
    > >> use single quotes as otherwise the @ symbols aren't read correctly.

    >
    > >> The below sql statement works fine directly in my sql program (TOAD)
    > >> and this is why i think it is a perl problem:

    >
    > >> select id, max(thetime) as date_of_last_entry, count(*) as
    > >> number_of_entries from log@test l, user@test u
    > >> where l.user = u.user
    > >> and id = 'MATT'
    > >> and thetime > sysdate - 90
    > >> group by id

    >
    > >> any ideas?

    >
    > > Because you need interpolation to occur in this string, you NEED to
    > > use double quotes to surround the string. To prevent Perl from
    > > thinking the @ characters start an array, simply put a backslash in
    > > front of them.

    >
    > > $dbh->do ("update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log\@test l,
    > > user\@test u
    > > where l.user = u.user
    > > and mudid = '$a'
    > > and thetime > sysdate - 90
    > > group by mudid)");

    >
    > > Alternatively, do not put the variable within the SQL directly. Use
    > > placeholders instead.

    >
    > > $dbh->do ('update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > > user@test u
    > > where l.user = u.user
    > > and mudid = ?
    > > and thetime > sysdate - 90
    > > group by mudid)', {}, $a);

    >
    > > See also:
    > > perldoc DBI
    > > perldoc perlsyn
    > > perldoc perldata

    >
    > I don't think place holders will work with $dbh->do because that does a
    > prepare and execute in one go.
    >
    > http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do


    What the hell? Did you bother reading this URL? Why would you post
    it if you didn't read it?

    Yes, placeholders work. The syntax is:
    $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
    which that URL that *you* posted tells us is "logically similar" to:
    sub do {
    my($dbh, $statement, $attr, @bind_values) = @_;
    my $sth = $dbh->prepare($statement, $attr) or return undef;
    $sth->execute(@bind_values) or return undef;
    my $rows = $sth->rows;
    ($rows == 0) ? "0E0" : $rows; # always return true if no error
    }

    Placeholders work just fine.

    Paul Lalli
     
    Paul Lalli, May 22, 2007
    #8
  9. Paul Lalli Guest

    On May 21, 11:23 pm, Dennis Roesler <> wrote:
    > Paul Lalli wrote:
    > > On May 21, 6:11 am, ""
    > > <> wrote:
    > >> If this is the wong board to post this question can someone guide me
    > >> to the correct board...

    >
    > >> I have an sql statement that is in single quotes

    >
    > >> or $dbh->do ('update test set (mudid, date_of_last_entrym
    > >> number_of_entries) = (select mudid, max(thetime) as
    > >> date_of_last_entry, count(*) as number_of_entries from log@test l,
    > >> user@test u
    > >> where l.user = u.user
    > >> and mudid = '$a'
    > >> and thetime > sysdate - 90
    > >> group by mudid)')

    >
    > >> where $a is defined as the id.

    >
    > >> however i don't think it likes the 2 sets of single quotes. I have to
    > >> use single quotes as otherwise the @ symbols aren't read correctly.

    >
    > >> The below sql statement works fine directly in my sql program (TOAD)
    > >> and this is why i think it is a perl problem:

    >
    > >> select id, max(thetime) as date_of_last_entry, count(*) as
    > >> number_of_entries from log@test l, user@test u
    > >> where l.user = u.user
    > >> and id = 'MATT'
    > >> and thetime > sysdate - 90
    > >> group by id

    >
    > >> any ideas?

    >
    > > Because you need interpolation to occur in this string, you NEED to
    > > use double quotes to surround the string. To prevent Perl from
    > > thinking the @ characters start an array, simply put a backslash in
    > > front of them.

    >
    > > $dbh->do ("update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log\@test l,
    > > user\@test u
    > > where l.user = u.user
    > > and mudid = '$a'
    > > and thetime > sysdate - 90
    > > group by mudid)");

    >
    > > Alternatively, do not put the variable within the SQL directly. Use
    > > placeholders instead.

    >
    > > $dbh->do ('update test set (mudid, date_of_last_entrym
    > > number_of_entries) = (select mudid, max(thetime) as
    > > date_of_last_entry, count(*) as number_of_entries from log@test l,
    > > user@test u
    > > where l.user = u.user
    > > and mudid = ?
    > > and thetime > sysdate - 90
    > > group by mudid)', {}, $a);

    >
    > > See also:
    > > perldoc DBI
    > > perldoc perlsyn
    > > perldoc perldata

    >
    > I don't think place holders will work with $dbh->do because that does a
    > prepare and execute in one go.
    >
    > http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do


    Did you bother reading this URL? Yes, placeholders work. The syntax
    is:
    $rows = $dbh->do($statement, \%attr, @bind_values) or die ...
    which that URL that *you* posted tells us is "logically similar" to:
    sub do {
    my($dbh, $statement, $attr, @bind_values) = @_;
    my $sth = $dbh->prepare($statement, $attr) or return undef;
    $sth->execute(@bind_values) or return undef;
    my $rows = $sth->rows;
    ($rows == 0) ? "0E0" : $rows; # always return true if no error
    }

    Placeholders work just fine.

    Paul Lalli
     
    Paul Lalli, May 22, 2007
    #9
  10. Guest

    Dennis Roesler <> wrote:

    >
    > I don't think place holders will work with $dbh->do because that does a
    > prepare and execute in one go.
    >
    > http://search.cpan.org/~timb/DBI-1.56/DBI.pm#do


    Look at the third line of the initial examples in the link you just posted:

    $rows = $dbh->do($statement, \%attr, @bind_values) or die ...


    (As a hint, if you have no \%attr to pass, just use undef instead.)

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , May 22, 2007
    #10
  11. Dr.Ruud Guest

    Dennis Roesler schreef:

    > I don't think place holders will work with $dbh->do because
    > that does a prepare and execute in one go.


    Why prepare without placeholders?

    (Your one go has at least two steps.)

    --
    Affijn, Ruud

    "Gewoon is een tijger."
     
    Dr.Ruud, May 22, 2007
    #11
  12. Dr.Ruud Guest

    schreef:

    > (As a hint, if you have no \%attr to pass, just use undef instead.)


    And some people prefer to pass {}.

    --
    Affijn, Ruud (I don't)

    "Gewoon is een tijger."
     
    Dr.Ruud, May 22, 2007
    #12
    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. dna
    Replies:
    1
    Views:
    1,278
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    448
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    461
    David Browne
    Aug 21, 2003
  4. mahesh
    Replies:
    3
    Views:
    4,696
    cb_1987
    Apr 6, 2010
  5. naveenduttvyas
    Replies:
    0
    Views:
    950
    naveenduttvyas
    Nov 26, 2008
Loading...

Share This Page