Persistant chat configuration with postgres DB

yogeshshanbhag — Fri, 01 Mar 2019 10:44:21 GMT

Hi Team,

I was able to complete the setup of Postgres DB and configure it to CUPS (for Persistant chat) as per the steps mentioned in the link given below.

Database Setup Guide for Cisco Unified Presence Release 8.0, 8.5 and 8.6 - Configuring an External Database on Cisco Uni…

I see that the checks 'Ping External DB' and ' Connectivity to External DB' both pass. I also see the tables created in the postgres database schema.

The issue I am facing is that the insert statements that persist the chat messages are failing (invalid syntax as per the postgres DB logs).

The sql statement looks like:

INSERT INTO tc_msgarchive(msg_id, to_jid, from_jid, nick_jid, sent_date, msg_type, body_len, message_len, body_string, message_string, body_text, MESSAGE_TEXT, subject)

VALUES('1',

'testconf@conf.abc.local',

'user@abc.local',

'testconf@conf.abc.local/user1@abc.local/jabber_1111',

'2014-05-12 08:32:13.839975',

'g',

469,

'test',

'<message from=\'user1@abc.local/jabber_1111\' id=\'uid:5359230a:000049ec:00000679\' to=\'testconf@conf.abc.local\' type=\'groupchat\' xml:lang=\'en\'><body>test</body><html xmlns=\'http://jabber.org/protocol/xhtml-im\'>..........</message>'

'',

'');

ERROR: syntax error at or near "user1" at character 417

The error is because of the '\' (backslash) which is escaping the " ' " single quote in the <message> tag.

<message from=\'user1@abc.local/jabber_1111\'

If I change the '\' backslash to " ' " , the insert query works fine.

<message from=''user1@abc.local/jabber_1111''

Is anyone facing this issue with postgres DB for chat persistance setup? Is there a way to tell CUPS to use " ' " (single quotes) instead of '/' (backslash).

Regards,

Re: Persistant chat configuration with postgres DB

tinghche — Wed, 14 May 2014 15:36:37 GMT

Hi Yogesh,

You need to edit the postgressql.conf for the following attribute

escape_string_warning = off

http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#GUC-ESCAPE-STRING-WARNING

And maybe this as well

backslash_quote = on/off

http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#GUC-BACKSLASH-QUOTE

Regards,

Howard

Re: Persistant chat configuration with postgres DB

yogeshshanbhag — Mon, 19 May 2014 06:08:21 GMT

Yes I found the information in the link, forgot to update the message.

Instant Messaging Compliance Guide for Cisco Unified Presence Release 8.0, 8.5 and 8.6 - Planning for IM Compliance [Cis…

Thanks Howard.

topic Re: Persistant chat configuration with postgres DB in DevNet General Discussions

Persistant chat configuration with postgres DB

Re: Persistant chat configuration with postgres DB

Re: Persistant chat configuration with postgres DB