Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Unanswered: Prepared Statement

    HI ALL

    I have a prepared statement which i used to find the minimum/maximum date values from a speciffic table in postgres 7.1.3 this worked fine and still does on 7.1.3 as I have this running on one machine and the same database running (Postgresql 7.3.2) on another

    my $sth = $dbh->prepare("SELECT EXTRACT(YEAR FROM TIMESTAMP(MIN(xact_date))) AS \"Year\", EXTRACT(MONTH FROM TIMESTAMP(MIN(xact_date))) AS \"Month\", EXTRACT(DAY FROM TIMESTAMP(MIN(xact_date))) AS \"Day\" FROM badge_history_temp");
    my $ret = $sth->execute;


    I NOW GET THIS ERROR MESSAGE FROM 7.3.2

    DBD::Pg::st execute failed: ERROR: parser: parse error at or near "MIN" at char
    acter 36 at ./convert_db.pl line 32.
    DBD::Pg::st execute failed: ERROR: parser: parse error at or near "MAX" at char
    acter 36 at ./convert_db.pl line 43.
    Date::Pcalc::Add_Delta_Days(): not a valid date at ./convert_db.pl line 56
    Database handle destroyed without explicit disconnect.
    DB ERROR: ERROR: parser: parse error at or near "MIN" at character 36DB ERROR: parser: parse error at or near "MAX" at character 43


    7.3.2 no longer seems to recognise the MIN/MAX SQL statement and when I remove these it also has problems extracting the date value from the field selected

    Can anyone please help

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80

    Re: Prepared Statement

    Originally posted by hughk
    my $sth = $dbh->prepare("SELECT EXTRACT(YEAR FROM TIMESTAMP(MIN(xact_date))) AS \"Year\", EXTRACT(MONTH FROM TIMESTAMP(MIN(xact_date))) AS \"Month\", EXTRACT(DAY FROM TIMESTAMP(MIN(xact_date))) AS \"Day\" FROM badge_history_temp");
    my $ret = $sth->execute;

    I NOW GET THIS ERROR MESSAGE FROM 7.3.2

    DBD::Pg::st execute failed: ERROR: parser: parse error at or near "MIN" at character 36 at ./convert_db.pl line 32.
    Is the date the same in both databases?
    Bradley

  3. #3
    Join Date
    Aug 2003
    Posts
    8

    Re: Prepared Statement

    Originally posted by bcrockett
    Is the date the same in both databases?
    Hi,

    Yes the date is the same in both databases, but I have now solved it, I was looking at problems to do with the aggregate functions min & max but it turns out that in newer versions of postgres your SQL language has to be exact althought the documentation says the statement will work. All I had to do was change each TIMESTAMP to DATE as I was only looking ate the date section of the field this works fine.

    Many thanks for you relply

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •