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

Ok, I didn't have a very good response on my last question because it related to SQL. My problem appears to be that I need to send my dates with '' around them.

Can anyone help me out with my perl code?

Here's how my code was written.

#get mileage
my $sth = $dbh->prepare('SELECT number, date, miles, purpose
FROM mileage
WHERE date BETWEEN ? and ?
ORDER BY date ?')
or print "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute($from_date, $to_date, $sort_method) # Execute the query or print "Couldn't execute statement: " . $sth->errstr;

I need to have single quotes around the dates. If I try "BETWEEN '?' and '?'" it doesn't work. I tried putting a backslash before the quotes, but that didn't work either. Does anyone know how to do this?

Thanks

Jeremy

edited: Mon Sep 23 00:31:59 2002 by jeffa - (title change was: This one is perl related)

Replies are listed 'Best First'.
Re: This one is perl related
by Zaxo (Archbishop) on Sep 22, 2002 at 21:31 UTC

    'date' remains an illegal column name.

    The placeholder mechanism provides the quotes. You probably should not have a placeholder after the order clause. In what format do you provide the dates? They should be 'yyyy-mm-dd' formatted strings. &POSIX::strftime can provide those if you are starting from a localtime generated timestruct array.

    After Compline,
    Zaxo

[reply]
      Thanks for the help. When I read your last posting, I missed the part about the date column being invalid. You probably wondered why I asked if date was valid right after that.

      I changed the name of this column to mileage_date and it seems to be running now. Thanks a lot Zaxo. I really appreciate it.

      Jeremy

[reply]
Re: This one is perl related
by valdez (Monsignor) on Sep 22, 2002 at 21:54 UTC
[reply]
      He is using the placeholders correctly there though - to bind values. (Of course, that doesn't mean he shouldn't read that node. :-) )

      Makeshifts last the longest.

[reply]

        May be my explanation was not clear, but our brother glickjd is trying to insert ASC or DESC in his query with a placeholder, right? This method of building queries can be dangerous, as well described by dws in his post, am I right?

        Ciao, Valerio

        Update: thanks to Aristotle for explaining me why I was wrong.

[reply]
Re: Quoted dates in SQL with placeholders
by diotalevi (Canon) on Sep 23, 2002 at 13:37 UTC

    About the only thing I can add here is that you should really revisit your database schema and see that you've not used other troublesome constructs. I don't know what database you are using but I strongly suggest you join a SQL list related to that database. Just for an example - this is something that I got caught up on when I started with PostgreSQL: 

    SELECT blah FROM blah WHERE name = ?

    [download]

    Since "name" is actually a datatype it's now a reserved word so I would have write that query as: 

    SELECT blah FROM blah WHERE "name" = ?

    [download]

    At that point you can generally put whatever you want inside of the double quotes (mind you - single quotes are for data, double are for identifiers) including "BiCaps" and "White Space". It gets ugly fast since the use of a feature inside of double-quotes now requires you use it *all* the time. Ugh.

    So - re-read your database's documentation and this time look for reserved identifiers. Also join the right mailing list since they'll be able to help so much more than we can.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom