[OpenSIPS-Users] Problem with sip_trace storage, maybe a bug?

[Brian]

Hello Bogdan,

I figured it out, and here's the answer.

On Fri., Feb 04, 2011, Bogdan-Andrei Iancu wrote:
> opensipslist at encambio.com wrote:
>> I'm using:
>>
>>   Solaris 11 x86 (nv-b91)
>>   OpenSIPS 1.6.4 with TLS
>>
>> ...and am logging dialogs with the siptrace module.
>>
>> The problem is that every time I want to look at the traced messages
>> in the sip_trace table, I do:
>>
>>   $ psql opensips opensips
>>   opensips=> select * from sip_trace where fromip = 'tcp:1.2.3.4:5678';
>>   id | time_stamp | callid | traced_user | msg | method | ...
>>   1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
>>
>> As you can see, the 'msg' column ASCII text is stored as hex values.
>> The next thing I do is use my mouse to cut and paste the message
>> (extremely long and tedious) to the window in:
>>
>>   http://www.dolcevie.com/js/converter.html
>>
>> ...which translates '\x494e56495445207369703a' to 'INVITE sip:'.
>>
>> This way of debugging is not acceptable at all, and I want to
>> see the ASCII text of the SIP message directly (without converting.)
>>
>> How can I read the siptrace logs in this way?
>>
>> Is it a bug that OpenSIPS stores the ASCII message text in hex?
>>
> opensips siptrace module inserts the message as text blob. My guess
> is that your issue is psql related (as in mysql you do not have this
> problem) - maybe the "select" function prints the BLOBs in hexa... I
> do not think it is a hexa storage, but a hexa display.
>
PostgreSQL stores the text indeed, but has no way of knowing its
encoding (BIG5, EBCDIC, ASCII, UTF-8, ISO-8859...) so that's why
the data is printed in hexadecimal. Rather smart if you ask me.

The 'convert_from' PostgreSQL function works on the bytea datatype,
so the solution is to simply cast the text to that like this:

  $ psql opensips opensips -c "SELECT time_stamp, method, \
    convert_from(msg::bytea, 'SQL_ASCII'::name) FROM sip_trace;"

It might be nice for the one maintaining the db modules to indicate
that the text is stored UTF-8 (or ASCII) so that the user is not
forced to type the conversions.

Regards,
Brian