Help with func_odbc delete in MySQL

Hi, I have this in my func_odbc.conf

[DELCDR]
dsn=asterisk
writesql=DELETE FROM asterisk.cdr WHERE date = ${curdate} - INTERVAL 1 MONTH

My question is, Am I correct to use ‘writesql’ ? and how do I apply this in my dialplan?
exten => h,1,SET(ODBC_DELCDR()) <<<< I know this is wrong, anybody knows the correct way for the dialplan?

yes that’s correct.

Assuming you need to delete records for the same date of last month, I think you need something like this.
DELETE FROM asterisk.cdr WHERE DATE(YOUR_DATETIME_FIELD_NAME_HERE) = DATE( NOW() - INTERVAL 1 MONTH)

You can go through this to understand the basics of func_odbc

Hi @satish4asterisk
Thank you for your reply. I know for a fact that the MySQL query work already tested it. My problem is the dial plan. I only have exten => h,1,SET(ODBC_DELCDR()) which does not work

Is this from your func_odbc? I don’t understand how ${curdate} will work in your query. Do you try to pass something from your dialplan to func_odbc? Do you have a field named date in your cdr table?

I think you need = symbol on the right side of this function call.
exten => h,1,SET(ODBC_DELCDR()=)

${curdate} is a variable i set in the dialplan. And yes I have a column ‘date’ on my cdr table. Already tried putting an ‘=’ sign on the dialplan. Still doesnt work.

Dial-plan variables are not automatically available in func odbc. You need to pass that variable and collect it in func_odbc. so something like this

exten => h,1,SET(ODBC_DELCDR()=${YOUR_VARIABLE})

writesql=DELETE FROM asterisk.cdr WHERE date = ‘${VAL1}’ - INTERVAL 1 MONTH

or

writesql=delete from asterisk.cdr where date = (current_date - interval 1 month)

Any chance date could contain time?

Would ‘date < (current_date - interval 1 month)’ be more appropriate/reliable?

Also, probably a bad idea to name a column the same name as a datatype.

Hi @satish4asterisk and @sedwards
I have a custom field in my CDR which is ‘date’ only. And I set this on my dialplan by
exten => _X.,n,SET(CDR(date)=${ODBC_CURDATE()})

My corresponding func_odbc for $${ODBC_CURDATE()}) is
[CURDATE]
dsn=asterisk
readsql=SELECT (CURDATE()) as curdate

it just gets the current date and inserts it on cdr table after each call. Now what I want to do is filter out 30 day old CDR entries and delete the them using
[DELCDR]
dsn=asterisk
writesql=DELETE FROM asterisk.cdr WHERE date = ${curdate} - INTERVAL 1 MONTH

You don’t need to pass ${curdate} in that case.
exten => h,1,SET(ODBC_DELCDR()=)
and
DELETE FROM asterisk.cdr WHERE date <= DATE( NOW() - INTERVAL 1 MONTH)

should work. If it doesn’t then provide Asterisk CLI output

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.