<p>If I remember correctly you should do CDR(flavor)="cherry" and it should work. I have added custom fields in my CDR table in the past and didn't need triggers.</p>
<p><blockquote type="cite">On 2010-03-29 3:40 AM, "Robert Price" <<a href="mailto:robert@proxims.com">robert@proxims.com</a>> wrote:<br><br>Hello Alex,<br>
<br>
I'm struggling with the same problem and, not wanting to modify the CDR<br>
backend, I just put in a workaround in the form of a MySQL trigger.<br>
I'll describe what I did in case it helps someone, though I'm very<br>
inexperienced at making compound procedures in MySQL.<br>
<br>
In my extensions.conf, I can do something like this:<br>
<br>
exten => s,1,Set(CDR(userfield)="flavor=cherry|color=maroon"<br>
<br>
The result is that my CDR(userfield) is a pipe-delimited list of<br>
key=value pairs. In my MySQL 5.0.45 database, I have altered my cdr<br>
table with a column called 'flavor' and another called 'color'. I have<br>
also created a trigger thus:<br>
<br>
DELIMITER //<br>
<br>
CREATE TRIGGER cdr_insert BEFORE INSERT ON cdr<br>
FOR EACH ROW<br>
BEGIN<br>
SET @numidx = LENGTH(NEW.userfield)<br>
- LENGTH(REPLACE(NEW.userfield, '|', '')) + 1;<br>
SET @idx = 0;<br>
WHILE @idx + 1 <= @numidx DO<br>
SET @idx = @idx + 1;<br>
SET @param = SUBSTRING_INDEX(<br>
SUBSTRING_INDEX(NEW.userfield, '|', @idx), '|', -1);<br>
SET @pos = LOCATE('=', @param);<br>
IF @pos > 0 THEN<br>
SET @key = SUBSTRING(@param, 1, @pos - 1);<br>
SET @value = SUBSTRING(@param, @pos + 1);<br>
<br>
CASE @key<br>
WHEN 'flavor' THEN SET NEW.flavor = @value;<br>
WHEN 'color' THEN SET NEW.color = @value;<br>
END CASE;<br>
<br>
END IF;<br>
END WHILE;<br>
SET NEW.userfield = '';<br>
END<br>
//<br>
<br>
DELIMITER ;<br>
<br>
You can omit SET NEW.userfield = '' if you want to retain the userfield<br>
to prevent data loss. Expand the CASE statement as necessary to<br>
enumerate all the fields you want to be able to specify via userfield,<br>
and make sure you've created the appropriate columns beforehand. You<br>
can even specify things like 'dst' and 'dcontext', which you wouldn't<br>
normally be able to control. The loop silently ignores elements of the<br>
pipe-delimited list that it does not recognize. In particular, if the<br>
value of CDR(userfield) begins with a pipe, that doesn't create a problem.<br>
<br>
So far, it appears to work.<br>
<br>
Cheers,<br>
Robert<br>
<br>
> Hi all,<br>
><br>
> I've been trying to add a custom mysql field to my CDR's, but I<br>
> must be doing something wrong.<br>
><br>
> I am using asterisk 1.4 and asterisk 1.6, in extensions.conf I<br>
> add:<br>
><br>
> exten => h,1,Set(CDR(q931)=${HANGUPCAUSE})<br>
><br>
> This extension is executed, I can see it in the asterisk console.<br>
><br>
> I have added a new column in my MySQL database called q931.<br>
> However,<br>
> the new field does not show up in my database or in the Master.csv<br>
> file.<br>
><br>
> Any help would be greatly appreciated.<br>
> Regards,<br>
><br>
> Alex<br>
<br>
--<br>
_____________________________________________________________________<br>
-- Bandwidth and Colocation Provided by <a href="http://www.api-digital.com" target="_blank">http://www.api-digital.com</a> --<br>
New to Asterisk? Join us for a live introductory webinar every Thurs:<br>
<a href="http://www.asterisk.org/hello" target="_blank">http://www.asterisk.org/hello</a><br>
<br>
asterisk-users mailing list<br>
To UNSUBSCRIBE or update options visit:<br>
<a href="http://lists.digium.com/mailman/listinfo/asterisk-users" target="_blank">http://lists.digium.com/mailman/listinfo/asterisk-users</a><br>
</blockquote></p>