The following issue has been RESOLVED. ====================================================================== http://www.dbmail.org/mantis/view.php?id=985 ====================================================================== Reported By: vampyre Assigned To: ====================================================================== Project: DBMail Issue ID: 985 Category: Database layer Reproducibility: always Severity: major Priority: normal Status: resolved target: Resolution: fixed Fixed in Version: ====================================================================== Date Submitted: 20-Sep-12 14:54 CEST Last Modified: 24-Jun-13 11:12 CEST ====================================================================== Summary: SQLException in case of imapsync Description: Hello Paul,
I've found incorrect SQL in mailbox_search in Oracle. It looks like you are trying to convert "datefield" to date (TO_DATE) but the field has type datetime so must be converted to datetime (TO_DATETIME). I'll send you a patch shortly, but the fix looks simple: ==== - field = g_strdup_printf(db_get_sql(SQL_TO_DATE), s->hdrfld); .... - date = g_strdup_printf(db_get_sql(SQL_TO_DATE), qs); ==== + field = g_strdup_printf(db_get_sql(SQL_TO_DATETIME), s->hdrfld); .... + date = g_strdup_printf(db_get_sql(SQL_TO_DATETIME), qs); ==== ====================================================================== ---------------------------------------------------------------------- (0003488) paul (administrator) - 20-Sep-12 16:48 http://www.dbmail.org/mantis/view.php?id=985#c3488 ---------------------------------------------------------------------- Looks valid, but please make sure that the search granularity must be on date, not on datetime. ---------------------------------------------------------------------- (0003489) vampyre (reporter) - 21-Sep-12 09:30 http://www.dbmail.org/mantis/view.php?id=985#c3489 ---------------------------------------------------------------------- Ok. I see, in such a case the fix should be a bit different, here is a results from oracle: DBMAIL@SERVICE>select TO_TIMESTAMP(datefield, 'YYYY:MM:DD HH24:MI:SS'), TO_DATE(TRUNC(datefield), 'YYYY:MM:DD') from dbmail_headervalue where datefield is not null; TO_TIMESTAMP(DATEFIELD,'YYYY:MM:DDHH24:MI:SS') TO_DATE(TR --------------------------------------------------------------------------- ---------- 2012-06-18 00:00:00 2012-06-18 ---------------------------------------------------------------------- (0003498) vampyre (reporter) - 02-Oct-12 10:51 http://www.dbmail.org/mantis/view.php?id=985#c3498 ---------------------------------------------------------------------- Hi Paul, Could you please post your vision here. Btw, when are you going to issue release 3.0.3, I see lots of useful and major changes. Thank you in advance. ---------------------------------------------------------------------- (0003504) vampyre (reporter) - 09-Oct-12 08:59 http://www.dbmail.org/mantis/view.php?id=985#c3504 ---------------------------------------------------------------------- Hello, Sorry, it looks like the patch didn't cover all possible cases so I have updated it accordingly. So now, the date, dateTime or string with correct format (nls_date_format, nls_timestamp_format) will be converted to dateTime and then to date. Enclosing patch here. Thank you. ---------------------------------------------------------------------- (0003529) paul (administrator) - 24-Jun-13 11:12 http://www.dbmail.org/mantis/view.php?id=985#c3529 ---------------------------------------------------------------------- patch accepted. Thanks. Issue History Date Modified Username Field Change ====================================================================== 20-Sep-12 14:54 vampyre New Issue 20-Sep-12 16:48 paul Note Added: 0003488 21-Sep-12 09:30 vampyre Note Added: 0003489 21-Sep-12 09:30 vampyre File Added: 0001-TT-0000985-Correct-Oracle-SQL-for-date-and-datetime-.patch 02-Oct-12 10:51 vampyre Note Added: 0003498 09-Oct-12 08:59 vampyre Note Added: 0003504 09-Oct-12 09:00 vampyre File Added: 0001-TT-0000985-Correct-Oracle-SQL-for-date-and-datetime.patch 24-Jun-13 11:12 paul Note Added: 0003529 24-Jun-13 11:12 paul Status new => resolved 24-Jun-13 11:12 paul Resolution open => fixed ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
