Pulling data based on date time returns error

Discussion in 'Perl Misc' started by xhoster@gmail.com, Aug 16, 2006.

  1. Guest

    wrote:
    > Data is stored in MySQL as '2006-07-14 13:44:13'
    >
    > Select Result from DataListing where
    > InputDate = "2006-07-14 13:44:13"
    > having count(*) > 1;
    >
    > DBD::mysql::st execute failed: You have an error in your SQL syntax;
    > check the manual that corresponds to your MySQL server version for the
    > right syntax to use near '"Select Result from DataListing where
    > InputDate = "2006-07-14 13:' at line 1 at
    > copiesinDATA.pl line 29.


    It would seem that your Perl quoting and your mysql quoting are somehow
    interfering with each other. As you haven't shown us any Perl, there
    isn't much we can do for you.

    BTW, using placeholders/bind variables often minimizes these quoting
    problems.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Aug 16, 2006
    #1
    1. Advertising

  2. Guest

    Data is stored in MySQL as '2006-07-14 13:44:13'

    Select Result from DataListing where
    InputDate = "2006-07-14 13:44:13"
    having count(*) > 1;

    DBD::mysql::st execute failed: You have an error in your SQL syntax;
    check the manual that corresponds to your MySQL server version for the
    right syntax to use near '"Select Result from DataListing where
    InputDate = "2006-07-14 13:' at line 1 at
    copiesinDATA.pl line 29.
     
    , Aug 16, 2006
    #2
    1. Advertising

  3. Paul Lalli Guest

    wrote:
    > Data is stored in MySQL as '2006-07-14 13:44:13'
    >
    > Select Result from DataListing where
    > InputDate = "2006-07-14 13:44:13"
    > having count(*) > 1;
    >
    > DBD::mysql::st execute failed: You have an error in your SQL syntax;
    > check the manual that corresponds to your MySQL server version for the
    > right syntax to use near '"Select Result from DataListing where
    > InputDate = "2006-07-14 13:' at line 1 at
    > copiesinDATA.pl line 29.


    What exactly is your Perl question? You have an SQL syntax error.
    Take that SQL statement and use it in MySQL's native interphase. What
    results do you get?

    Paul Lalli
     
    Paul Lalli, Aug 16, 2006
    #3
  4. wrote:

    > Data is stored in MySQL as '2006-07-14 13:44:13'
    >
    > Select Result from DataListing where
    > InputDate = "2006-07-14 13:44:13"
    > having count(*) > 1;
    >
    > DBD::mysql::st execute failed: You have an error in your SQL syntax;
    > check the manual that corresponds to your MySQL server version for the
    > right syntax to use near '"Select Result from DataListing where
    > InputDate = "2006-07-14 13:' at line 1 at
    > copiesinDATA.pl line 29.


    I would guess that you have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax.

    BTW, this has nothing to do with Perl.

    Random short in the dark: standard SQL uses single not double quotes.

    Random short in the dark: Your "having" clause looks odd. There's no
    "group by".
     
    Brian McCauley, Aug 16, 2006
    #4
  5. Guest

    wrote:
    > wrote:
    > > Data is stored in MySQL as '2006-07-14 13:44:13'
    > >
    > > Select Result from DataListing where
    > > InputDate = "2006-07-14 13:44:13"
    > > having count(*) > 1;
    > >

    This should have been:
    Select Result,count(*) from DataListing where
    InputDate = "2006-07-14 13:44:13"
    group by Result
    having count(*) > 1;
    I finally fixed the problem
    by putting
    $SQL = "Select Result,count(*) from DataListing where
    InputDate = "2006-07-14 13:44:13"
    group by Result
    having count(*) > 1";

    my $sth = $dbh->prepare(qq{$SQL});
    $sth->execute ();

    instead of

    my $sth = $dbh->prepare(qq{"Select Result,count(*) from DataListing
    where
    InputDate = "2006-07-14 13:44:13"
    group by Result
    having count(*) > 1"});
    $sth->execute ();


    > > DBD::mysql::st execute failed: You have an error in your SQL syntax;
    > > check the manual that corresponds to your MySQL server version for the
    > > right syntax to use near '"Select Result from DataListing where
    > > InputDate = "2006-07-14 13:' at line 1 at
    > > copiesinDATA.pl line 29.

    >
    > It would seem that your Perl quoting and your mysql quoting are somehow
    > interfering with each other. As you haven't shown us any Perl, there
    > isn't much we can do for you.
    >
    > BTW, using placeholders/bind variables often minimizes these quoting
    > problems.
    >
    > Xho
    >
    > --
    > -------------------- http://NewsReader.Com/ --------------------
    > Usenet Newsgroup Service $9.95/Month 30GB
     
    , Aug 16, 2006
    #5
  6. J. Gleixner Guest

    wrote:
    > wrote:
    >> wrote:
    >>> Data is stored in MySQL as '2006-07-14 13:44:13'
    >>>
    >>> Select Result from DataListing where
    >>> InputDate = "2006-07-14 13:44:13"
    >>> having count(*) > 1;
    >>>

    > This should have been:
    > Select Result,count(*) from DataListing where
    > InputDate = "2006-07-14 13:44:13"
    > group by Result
    > having count(*) > 1;
    > I finally fixed the problem
    > by putting
    > $SQL = "Select Result,count(*) from DataListing where
    > InputDate = "2006-07-14 13:44:13"
    > group by Result
    > having count(*) > 1";



    Really?? That doesn't even compile.

    my $SQL = q {
    Select Result,count(*)
    from DataListing
    where InputDate = "2006-07-14 13:44:13"
    group by Result
    having count(*) > 1
    };

    You'd be much better off using placeholders. e.g.

    my $date = '2006-07-14 13:44:13';
    my $SQL = q {
    Select Result,count(*)
    from DataListing
    where InputDate = ?
    group by Result
    having count(*) > 1
    };

    my $sth = $dbh->prepare( $SQL );
    $sth->execute( $date );
     
    J. Gleixner, Aug 17, 2006
    #6
    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. Chris Berg
    Replies:
    0
    Views:
    829
    Chris Berg
    Oct 27, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,332
    Michael Borgwardt
    May 30, 2004
  3. Jim in Arizona
    Replies:
    3
    Views:
    576
    Jim in Arizona
    Sep 5, 2006
  4. dch3
    Replies:
    2
    Views:
    525
  5. Mudcat
    Replies:
    2
    Views:
    1,027
    Mudcat
    Dec 14, 2008
Loading...

Share This Page