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

If.... $year=1992...perl interpretes it as a string...so when I try to compare it with year table in MS access ....it doesnt return any results. Can someone help me...by telling me how to type cast in perl or tell perl tht $year is an integer variable.....???

Replies are listed 'Best First'.
Re: Type casting
by monkeygirl (Pilgrim) on Jul 18, 2001 at 05:03 UTC

    MS Access is really funny. They store their dates in yyyy-mm-dd hh:mm:ss format, no matter how you think it looks in the table. And you can't simply write that format into the date field. :(

    I ran into a similar issue a while back when trying to write a date to an MS Access database. I ended up having to use {ts '2001-01-01 23:59:59'} to pull it off. Granted, your issue may be unrelated, but if you're looking for dates, you may have to write them too.

    So, anyway, back to your question: I tried this out earlier, and it should suit your needs, but is most likely a quick hack:

    WHERE field BETWEEN #1/1/$year# AND #12/31/$year#

    Update: While sifting through the Cheetah Book (Programming the Perl DBI), I noticed that on page 293 it says this:

    Similar escape sequences are defined for other date/time types. Here's the full set:
    {d 'YYYY-MM-DD'} - date
{t 'HH:MM:SS'} - time
{ts 'YYYY-MM-DD HH:MM:SS'} - timestamp
{ts 'YYYY-MM-DD HH:MM:SS.FFFFFFF'} - timestamp

    [download]

    Further research at that dratted MSDN does indeed indicated that that is the full set

    Sarah
    If Bill Gates can name a company after his "bedroom" problems, I can have a stupid sig that points it out.
[reply]
[d/l]
[select]
Re: Type casting
by HyperZonk (Friar) on Jul 18, 2001 at 03:26 UTC
    Well, there's always
    $year=int($year);

    [download]

    But Perl is not a strongly-typed language. You can also use
    $year+=0;

    [download]

    to ensure that $year is interpreted as a number in following statements (as long as the following statements don't do anything to the variable).

    However, the code you show should make year a number in any case. Perhaps the code actually is
    $year = '1992';

    [download]

    or is otherwise assigned a value interpreted as a string.
[reply]
[d/l]
[select]
      Hey, Thanks but it isnt working....can look at my code.. 
      
use Win32::ODBC;

           my @rows;
           my $make1="Acura";
           $year=int($year);
        #   $year+=0;
           $year=1992;
           my $DSN = "carcov";

           if (!($db = new Win32::ODBC($DSN))){
               print "error connecting to $DSN\n";
               print "error: " . Win32::ODBC::Error() . "\n";
               exit;
           }

           die qq(SQL failed: ), $db->Error(), qq(\n) if ($db->Sql("SE
+LECT DISTINCT modelname,startyear,endyear
                                                            FROM model
+,detail,make 
                                        WHERE detail.modelid=model.mod
+elid 
                                         AND detail.makeid=make.makeid
                                         AND make.makename='$make1'
                                         AND startyear <= '$year'
                                         AND endyear >= '$year'"));

      [download]
[reply]
[d/l]

        First, print you SQL statement to check it looks OK.

        Then try it with less requirements to see whether you're asking for an impossible dataset.

        Then ummm... I'll get back to you...

        clive ;-)

[reply]
        Although you want $year to be treated by the database as a number, you've put single quotes around it in the SQL, which tells the database the value is a string.

        Try removing the single quotes around $year in the SQL, and maybe even using placeholders instead, if MS Access supports them.

[reply]
        Well, there are some notes above that may make this irrelevant, but you are doing the assignement after the $year+=0;. When trying to force evaluation as a number, you should do the +=0 trick after, not before the assignment.
[reply]
[d/l]
        From this, it looks like your SQL string contains carrige returns. I don't belive you can do that.

        If that's not the case, what happens if you just replace all occurances of $year with a hard coded int?

        Rich

        Update: Thanks to crazyinsomniac for correcting me. SQL is not delimited by a CR so I was completely off base.

[reply]
        Type really shouldn't make a difference here, since, when it's passed to the db, it's all a string anyway. But to make sure, you can always just drop $year and add 1992 in the code and see what the return is. I'm betting it will be the same result, in which case you will have to look elsewhere for the flaw.
        -Syn0

        update: damn, rchiav beat me :) .. another thing, when forcing the type, you dont' want to do it before you declare what's in $year, you want to do it during or after.
        for example: $year = 1992; $year = int($year); or $year = 1992+0;
[reply]
[d/l]
[select]
Re: Type casting
by voyager (Friar) on Jul 18, 2001 at 08:11 UTC
    Check your WHERE clause again. Your complaint is that Perl interprets the year as a string. In fact you are forcing the issue by putting the value in quotes.

    If it is a number in the db, proper sql requires that the value be unquoted, although some RDBMS implementations can handle this type of conversion.

[reply]
Re: Type casting
by cLive ;-) (Prior) on Jul 18, 2001 at 03:39 UTC
    How are you comparing? There are two ways you can compare: 
    # this will be false
('1992' eq '1992.0')

# whereas this is true
('1992' == '1992.0')

    [download]

    As always, golden rule - post your code snippet!

    cLive ;-)

[reply]
[d/l]

Back to Seekers of Perl Wisdom