Mendix OQL bulk delete with date parameter

0
Hi,I want to use the oql execute statement to delete records in de database but I get:Parameter 'cutoff' has an unsupported type. at Administration.CleanUpData_DML (JavaAction : 'Execute DML statement')The statement:'DELETE FROM Logging.Message WHERE Timestamp < $cutoff'When I replace the date parameter with an integer and set it to 1 and change the query to:'DELETE FROM Logging.Message WHERE 1 = $cutoff'All records will be deleted, so the bulk delete works, only not with a date as parameter.Any ideas?Thanks
asked
2 answers
0

Hi Wouter knoops,


Mendix OQL does not support DateTime parameters in Execute DML. That’s why you get the unsupported type error.

Workaround:

  • Convert the DateTime to milliseconds / Unix timestamp (Long) and compare that, or
  • Build the date value directly into the query string (formatted DB‑specific), or
  • Use a Retrieve + Delete in batches instead of OQL for date‑based deletes.

Date parameters work for retrieve OQL, but not for bulk delete DML.


answered
0

Hi,
Yes, if you are using the OQL Module’s ExecuteDMLStatement action, this is expected.

The official Mx documentation for the OQL Module states that AddDateTimeParameter cannot be used together with ExecuteDMLStatement, so a DateTime parameter like $cutoff is not supported there.

That is why your integer test works, while the DateTime parameter fails.

See this: https://docs.mendix.com/appstore/modules/oql-module/

answered