Displaying text appears as hex data

On 8 February 2011 10:39, Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.

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.

ASCII text? You mean you wish to translate the binary into ASCII?
You can use this:

SELECT id, time_stamp, callid, traced_user, convert_from(msg,
'SQL_ASCII'), method
FROM sip_trace;

The reason why it doesn't automatically do this is because since it's
binary data, it's up to you to define what its content format is.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Tue, Feb 08, 2011 at 11:39:04AM +0100, Michael wrote:

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB

I take it you mean BYTEA.

and PostgreSQL is displaying it in hex format like so:

$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.

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.

You might attempt to apply decode(column, 'hex') to the
relevant column.

Better be sure the result really is printable text.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Hello Karsten,

On Tues., Feb 08, 2011, Karsten Hilbert wrote:

On 8 February 2011 10:39, Michael wrote:

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB

I take it you mean BYTEA.

That's probably correct, yes.

and PostgreSQL is displaying it in hex format like so:

$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.

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.

You might attempt to apply decode(column, 'hex') to the
relevant column.

I had tried that before, and here's the result:

opensips=> SELECT id, time_stamp, callid, traced_user,
decode(msg, 'hex'), method FROM sip_trace;
ERROR: invalid hexadecimal digit: "\"

I don't understand this. Isn't it PostgreSQL that stores the BYTEA
values and then displays them in hex, indicating this by prepending
the '\x' backslash ex? Or if the '\x' is actually stored, then why
and who is doing that?

Any idea?

Thanks,
Michael

Hello Thom,

I sent this accidentally to you directly, here's a copy for the
list as well.

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.

ASCII text? You mean you wish to translate the binary into ASCII?
You can use this:

SELECT id, time_stamp, callid, traced_user, convert_from(msg,
'SQL_ASCII'), method FROM sip_trace;

The reason why it doesn't automatically do this is because since it's
binary data, it's up to you to define what its content format is.

That's understandable and PostgreSQL is doing the right thing, but...

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.

Is it because I've not build something in:

src/backend/utils/mb/conversion_procs

I think I built a standard PostgreSQL installation, or?

Regards,
Michael

On 8 February 2011 12:45, Michael <postgresql@encambio.com> wrote:

Hello Thom,

I sent this accidentally to you directly, here's a copy for the
list as well.

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.

ASCII text?  You mean you wish to translate the binary into ASCII?
You can use this:

SELECT id, time_stamp, callid, traced_user, convert_from(msg,
'SQL_ASCII'), method FROM sip_trace;

The reason why it doesn't automatically do this is because since it's
binary data, it's up to you to define what its content format is.

That's understandable and PostgreSQL is doing the right thing, but...

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?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:

On 8 February 2011 12:45, Michael <postgresql@encambio.com> 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.'

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?

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i bytea {} \; -print
...nothing

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i hex {} \; -print
...nothing

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i binary {} \; -print
static unsigned short BinarySearchRange
./euc_tw_and_big5/big5.c

...nope, not there either.

Regards,
Michael

On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote:

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,

That is not really possible since PostgreSQL does not have a
"BLOB" data type as such.

It would either be a BYTEA column or a Large Object (which
would live elsewhere and not inside the table you work on).

and I'm nearly sure that bytea is used in this case.

Very likely.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 8 February 2011 13:19, Michael <postgresql@encambio.com> wrote:

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:

On 8 February 2011 12:45, Michael <postgresql@encambio.com> 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.

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;

Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 8 February 2011 13:43, Thom Brown <thom@linux.com> wrote:

On 8 February 2011 13:19, Michael <postgresql@encambio.com> wrote:

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:

On 8 February 2011 12:45, Michael <postgresql@encambio.com> 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.

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;

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;

But then that's less efficient than storing it as bytea and slower to query.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Hello Karsten,

On Tues., Feb 08, 2011, Karsten Hilbert wrote:

On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote:

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,

That is not really possible since PostgreSQL does not have a
"BLOB" data type as such.

It would either be a BYTEA column or a Large Object (which
would live elsewhere and not inside the table you work on).

and I'm nearly sure that bytea is used in this case.

Very likely.

Okay, after looking at the sources I can confirm that BLOB maps
indeed to the 'bytea' PostgreSQL data type.

Regards,
Michael

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

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.

SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
'SQL_ASCII'::name), method
FROM sip_trace;

opensips=> SELECT id, time_stamp, callid, traced_user, \
convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace;
id | time_stamp | callid | traced_user | convert_from | method
-------+---------------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | | INVITE sip:num@name.host.tld;user=phone SIP/2.0\r +| INVITE
| | | | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r +|
| | | | From: "Username Person One" <sip:Proxyuser@name.host.tld>;tag=thefromtag12\r +|

There's a ton of blanks after '\r' the carriage returns. Is there
any way to construct a SELECT statement using filters or something
that will neatly format the msg field?

Is there some stream editor similar function?

SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name))

Sorry to be pesky, since you already answered the original question.
Thanks again for doing that.

Regards,
Michael

On 8 February 2011 14:30, Michael <postgresql@encambio.com> wrote:

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.

SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
'SQL_ASCII'::name), method
FROM sip_trace;

opensips=> SELECT id, time_stamp, callid, traced_user, \
 convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace;
 id   |     time_stamp      |          callid           | traced_user |                                                                                                                                    convert_from                                                                                                                                     | method
-------+---------------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
 30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c |             | INVITE sip:num@name.host.tld;user=phone SIP/2.0\r                                                                                                                                                                                                                              +| INVITE
      |                     |                           |             | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r                                                                                                                                                                                                          +|
      |                     |                           |             | From: "Username Person One" <sip:Proxyuser@name.host.tld>;tag=thefromtag12\r                                                                                                                                                                                  +|

There's a ton of blanks after '\r' the carriage returns. Is there
any way to construct a SELECT statement using filters or something
that will neatly format the msg field?

Is there some stream editor similar function?

 SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name))

Sorry to be pesky, since you already answered the original question.
Thanks again for doing that.

Well, you could always try:

SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', '');

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:

On 8 February 2011 14:30, Michael <postgresql@encambio.com> wrote:

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.

SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
'SQL_ASCII'::name), method
FROM sip_trace;

opensips=> SELECT id, time_stamp, callid, traced_user, \
�convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace;
�id � | � � time_stamp � � �| � � � � �callid � � � � � | traced_user | � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �convert_from � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � | method
-------+---------------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
�30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | � � � � � � | INVITE sip:num@name.host.tld;user=phone SIP/2.0\r � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �+| INVITE
� � � | � � � � � � � � � � | � � � � � � � � � � � � � | � � � � � � | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �+|
� � � | � � � � � � � � � � | � � � � � � � � � � � � � | � � � � � � | From: "Username Person One" <sip:Proxyuser@name.host.tld>;tag=thefromtag12\r � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �+|

There's a ton of blanks after '\r' the carriage returns. Is there
any way to construct a SELECT statement using filters or something
that will neatly format the msg field?

Is there some stream editor similar function?

�SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name))

Sorry to be pesky, since you already answered the original question.
Thanks again for doing that.

Well, you could always try:

SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', '');

Thanks alot that really helps, I'll figure the rest out from here.

Regards,
Michael

Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.

opensips=> select * from sip_trace;
id | time_stamp | callid | traced_user | msg | method | ...
1234 | 2011-02-03 | ... | | \x494e56495445207369703a...

set:

bytea_output = 'escape'

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.

opensips=> select * from sip_trace;
 id | time_stamp | callid | traced_user | msg | method | ...
 1234 | 2011-02-03 | ... | | \x494e56495445207369703a...

set:

bytea_output = 'escape'

That won't help as the msg column is actually text... for some reason.
And they want to see the converted ASCII text based on the
hex-represented binary data.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

^^^^

set:

bytea_output = 'escape'

That won't help as the msg column is actually text... for some reason.

Are you sure? I know that problem from DRUPAL with 9.0.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Thom Brown <thom@linux.com> wrote:

On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

       ^^^^

set:

bytea_output = 'escape'

That won't help as the msg column is actually text... for some reason.

Are you sure? I know that problem from DRUPAL with 9.0.

I asked Michael to confirm the column type of msg and it turned out to
be text. No mention of the lo contrib module, so I guess that may
possibly come into play, but I wouldn't know about that.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Thom Brown <thom@linux.com> wrote:

On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Michael <postgresql@encambio.com> wrote:

Hello list,

I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:

� � � �^^^^

set:

bytea_output = 'escape'

That won't help as the msg column is actually text... for some reason.

Are you sure? I know that problem from DRUPAL with 9.0.

I asked Michael to confirm the column type of msg and it turned out to
be text. No mention of the lo contrib module, so I guess that may
possibly come into play, but I wouldn't know about that.

Okay, you are right, i haven't read the other sub-thread, sorry.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�