Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Debian DBD::ODBC select row by timestamp

by alexx_sh (Novice)
on Jul 23, 2012 at 15:37 UTC ( #983194=perlquestion: print w/ replies, xml ) Need Help??
alexx_sh has asked for the wisdom of the Perl Monks concerning the following question:

Hello

I have a following question.

How to use the DBD :: ODBC referring to MS Access to write a query that as one of the parameters where used timestamp.

I tried to use sql

my $ sth = $ dbh-> prepare ( SELECT val from table where DD_MM_YYYY = '2012-07-22 00:00:00 ');
my $ sth = $ dbh-> prepare ( q{SELECT val from table where DD_MM_YYYY =}.q{/ts 2012-07-22 00:00:00 +/});
and get an error DBD :: ODBC does not yet support binding a named parameter more than once.

Help me please write the query correctly.

And it will be cool if query use placeholders.

Comment on Debian DBD::ODBC select row by timestamp
Select or Download Code
Re: Debian DBD::ODBC select row by timestamp
by mje (Deacon) on Jul 23, 2012 at 15:51 UTC

    This happens because your SQL contains colons and ':' is a placeholder introducer. There are 2 ways to fix it. 1) replace '2012-07-22 00:00:00 ' (BTW, notice trailing space) with ? and call execute passing '2012-07-22 00:00:00' as an argument (read about placeholders in DBI). 2) set odbc_ignore_named_placeholders. The first is best.

      Can you help me with code? I try read, but i can't write it by my self.
        my $sth = $dbh-> prepare (SELECT val from table where DD_MM_YYYY = ?); $sth->execute('2012-07-22 00:00:00');
        ? is the placeholder. It serves great use and it protects you against SQL injection attacks, by automatically quoting (and thereby neutralising) any "dangerous" characters.

        Of course you should add the usual or die ... checks to make sure no error happened. Also you have to verify the format of the DD_MM_YYY field to make sure your query sends in the timestamp in the right format.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

        My blog: Imperial Deltronics
Re: Debian DBD::ODBC select row by timestamp
by Anonymous Monk on Jul 23, 2012 at 16:02 UTC
    Especially in the case of date/time values, you should pass the parameter as a placeholder. (Never send anything to an SQL statement by direct modification of text!) Consult the ODBC driver to see how the date/time value can be provided.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://983194]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (10)
As of 2014-08-23 14:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (174 votes), past polls