A NOTE has been added to this issue. ====================================================================== 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: new target: ====================================================================== Date Submitted: 20-Sep-12 14:54 CEST Last Modified: 02-Oct-12 10:51 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. 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 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
