Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: Code flow not going to while loop

by chacham (Prior)
on Aug 02, 2017 at 19:07 UTC ( #1196581=note: print w/replies, xml ) Need Help??


in reply to Code flow not going to while loop

I would like to comment on the SQL here. As has been suggested already and you replied, you should use placeholders to avoid SQL Injection, and to allows the database to reuse the same statement.

It looks as if you are looking for year and month, if so, unless you are required to pass those dates, you should create them on the fly, especially because of leap years adding an extra day to February.

Anyway, the issue here is likely that the values are not being changed into DATEs. Let me explain, because a lot of people misunderstand how dates work in databases. A database stores dates in an internal format which is not available to the user. Instead, every time a date is input or output, it is formatted from or to text (or any other type you specify). That is, you input text and ask the rdbms to turn it into a database-date for you via a function, and when you output a date, you ask the rdbms to turn it into text for you. Both of these can always be done explicitly, though at times it is done implicitly. There are usually default date formats and rdbms-specific functions to do these actions for you. OTOH, you can usually use the ANSI date, by predicating the date string with DATE, as long as you only specify the year, month, and date. Also, while the the date can be turned into a string for comparison, this is redundant, and can lead to calculation errors, so it is best to leave it as a date.

As i would rewrite the statement as follows, using something like TIMESTAMP:

SELECT DISTINCT Fid_Cust FROM Session WHERE Dat_End BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)";

Replies are listed 'Best First'.
Re^2: Code flow not going to while loop
by dipit (Sexton) on Aug 02, 2017 at 20:09 UTC
    Hi, This is really useful information and from the starting i knew there is something wrong with parsing dates. I have read the doc, thanks for that. But now it is giving some ambiguity error and i do not know why it is coming.
    DBD::DB2::db prepare failed: [IBM][CLI Driver][DB2/AIX64] SQL0245N Th +e invocation of routine "TIMESTAMP" is ambiguous. The argument in pos +ition "1" does not have a best fit. SQLSTATE=428F5 DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/AIX64] SQL0245N Th +e invocation of routine "TIMESTAMP" is ambiguous. The argument in pos +ition "1" does not have a best fit. SQLSTATE=428F5 [IBM][CLI Driver][DB2/AIX64] SQL0245N The invocation of routine "TIME +STAMP" is ambiguous. The argument in position "1" does not have a bes +t fit. SQLSTATE=428F5

      Seems like TIMESTAMP can take a 2nd argument for microsecond precision so try

      SELECT DISTINCT Fid_Cust FROM Session WHERE Dat_End BETWEEN TIMESTAMP(?,0) AND TIMESTAMP(?,0)
      

      You might need to change your values to match those expected by TIMESTAMP.

        These are the same strings used in database. I cannot change them. You can see the code and i have run my test server also.
        db2 "select distinct fid_cust from session where DAT_END between TIMES +TAMP('2017-01-01 00:00:00') and TIMESTAMP('2017-01-31 00:00:00')" FID_CUST -------------------------------------------------- Lena2 TEST

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1196581]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (8)
As of 2021-01-27 11:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?