Re: Displaying text appears as hex data

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:
>On 8 February 2011 13:43, Thom Brown wrote:
>> On 8 February 2011 13:19, Michael wrote:
>>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>>On 8 February 2011 12:45, Michael wrote:
>>>>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>>>>On 8 February 2011 10:39, Michael wrote:
>>>>>>> opensips=> select * from sip_trace;
>>>>>>>  id | time_stamp | callid | traced_user | msg | method | ...
>>>>>>>  1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
>>>>>>>
>>>>>>> Others have said that when they use MySQL, the exact SQL command
>>>>>>> as above results in ASCII text rather than hexadecimal, and this
>>>>>>> is my goal as well.
>>>>>>>
>>>>> opensips=> SELECT id, time_stamp, callid, traced_user,
>>>>>           convert_from(msg, 'SQL_ASCII'), method FROM sip_trace;
>>>>> ERROR:  function convert_from(text, unknown) does not exist
>>>>> LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg...
>>>>>                                                    ^
>>>>>
>>>>> The arrow in the last line indicates that 'convert_from' is not
>>>>> correctly parsed.
>>>>>
>>>>My understanding was that your msg column was of type bytea.  Is this
>>>>not the case?  Or is it a different column which needs converting?
>>>>
>>> The main developer has confirmed that the msg column is stored
>>> as a BLOB, and I'm nearly sure that bytea is used in this case.
>>> Yes, the correct column that is appearing in hex is called 'msg.'
>>>
>> Well the function seems to think it's a text field.
>>
Okay, maybe I missed something and thought it was a bytea.

>>> Is the function 'convert_from' that you mentioned compiled into
>>> the PostgreSQL server binary, or is it part of the template1 when
>>> first created, or something else? It seems I'm missing it, right?
>>>
>> No, you're probably not missing it.  It's complaining that a function
>> with the given signature (text, unknown), doesn't exist.  It will work
>> if it matches (bytea, name).  The error message being returned is
>> saying that the msg field is actually a text field.
>>
>> Try:
>>
>> SELECT pg_typeof(msg) FROM sip_trace LIMIT 1;
>>
opensips=> SELECT pg_typeof(msg) FROM sip_trace LIMIT 1;
pg_typeof
-----------
text
(1 row)

...so you are right. Its not a bytea after all.

>Or if it really is text format:
>
>SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
>'SQL_ASCII'::name), method
>FROM sip_trace;
>
That worked very well, thanks. Now that I can read the text, I see
that it is very poorly formatted. I'll write about that problem in
another email.

>But then that's less efficient than storing it as bytea and slower
>to query.
>
I'll pass that on to the OpenSIPS database developer, thanks.

Regards,
Michael