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: 21-Sep-12 09:30 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 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 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
