sirius98 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have a strange problem, when i ran my .cgi script on my machine it works flawlessly (athlon 64 3200+) but when i run it on another machine (pentium 4 2.4ghz) with exactly the same database (Access 2000) and exactly the same version of perl (Activestate Activeperl 5.8)and the same operating system (Windows XP Home)i get a weird problem. The problem is in the sql string. This is the code that produces an empty dataset. This format works on my machine and it took me a while to get the parenthises right. 
my $sql="SELECT idNum, (First(price)-Last(price)) AS difference ,Min(d
+ateCreated) AS minDate, Max(dateCreated) AS maxdate, Last(mobile) AS 
+lastMobile, Last(contents) AS lastContents,(( (First(price)-Last(pric
+e)) /First(price) )*100) AS percentChange , Last(price) AS lastPrice,
+ Last(state) AS lastState, Last(contact), Last(year) FROM carAdds GRO
+UP BY idNum HAVING (( Last([year]))>=$yearmodel) ORDER BY (( (First(p
+rice)-Last(price)) /First(price) )*100) DESC";

[download]
When i change (( Last(year))>=$yearmodel) to (( Last(year))=$yearmodel)it works on the pentium machine but obviously its not what i want. In fact in my other searches not presented here i get the same problem with any numerical conditions. Im using dbi and an access 2000 database. Any help would be really appreciated Thanks

Replies are listed 'Best First'.
Re: Strange error when .cgi deployed on a different machine
by davido (Cardinal) on Mar 10, 2005 at 06:59 UTC

    Is Last([year]) or $yearmodel a floating point number, by any chance?

    If so, maybe you're getting bitten by differences in internal representation of base ten floating point numbers under the constraints of base-2 mathematics.


    Dave

[reply]
[d/l]
[select]
      Some are integers, some are floating points, happens with both.
[reply]
Re: Strange error when .cgi deployed on a different machine
by Jasper (Chaplain) on Mar 10, 2005 at 08:50 UTC
    All I can say is no wonder it took you a while to get the parenthesizing right when you've formatted (or haven't formatted) the query like that. What's wrong with some return characters in there?
    my $sql="
SELECT
        idNum,
        (First(price)-Last(price)) AS difference,
        Min(dateCreated) AS minDate,
        Max(dateCreated) AS maxdate,
        Last(mobile) AS lastMobile,
        Last(contents) AS lastContents,
        (( (First(price)-Last(price)) /First(price) )*100) AS percentC
+hange,
        Last(price) AS lastPrice,
        Last(state) AS lastState,
        Last(contact),
        Last(year)
FROM carAdds
GROUP BY idNum
HAVING (( Last([year]))>=$yearmodel)
ORDER BY (( (First(price)-Last(price)) /First(price) )*100) DESC
";

    [download]

    Doesn't that look better? It'll be much easier to debug and change, and understand for the next guy, too.
[reply]
[d/l]
      ok thanks jasper
[reply]
Re: Strange error when .cgi deployed on a different machine
by jhourcle (Prior) on Mar 10, 2005 at 13:41 UTC

    I would suggest looking at the database. The SQL string should be passed through. Print out the value of $sql, then go into your access database, make a new query, switch to sql mode, and paste it in. Make sure the database is returning the correct values.

    Oh -- and when switching machines, the version of perl is a good start, but you may need to make sure you're running the same versions of all of your modules that you'll be needing. (I got stung once when a machine change resulted in a different version of Net::LDAP, and everything blew up)

[reply]
[d/l]
      The problem was somehow 0 valued records got into my database and i was dividing by them so i got an overflow error and the recordset returned was empty, thanks all for your help.
[reply]
Re: Strange error when .cgi deployed on a different machine
by derby (Abbot) on Mar 10, 2005 at 12:47 UTC
    with exactly the same database (Access 2000)

    well ... dumb question ... but is it exactly the same data?

    -derby

[reply]
      The data is different but of the same form and the database has the same structure.
[reply]

Back to Seekers of Perl Wisdom