[OpenSIPS-Users] OpenSips 1.11.5 MSILO - MS SQL integration via UnixODBC + FreeTDS

[Milagros González]

Hello Everyone,

I have some questions regarding msilo module usage for MS SQL DB
integration using db_unixodbc module.

Following instructions found in OpenSips documentation, I've installed
UnixODBC (v2.2.14) and FreeTDS (v0.82) and configured it properly. I'm
currently using some other modules which store information persistently in
DB as dispatcher, usrloc or subscriber. Those modules are working properly,
but I'm having some troubles with msilo module.
Since sql scripts are not included for MS MSQL DB, I've created tables
manually. The problem I'm facing with msilo module is related to body
column in msilo table. Since this column must be binary, I've defined it as
'varbinary(500)' MS SQL data type.

The following error is shown in OpenSips log file when msilo tries to store
messages to DB:

Feb 29 16:06:05 deservoz2 /opt/opensips-1.11.5/sbin/opensips[21623]:
ERROR:db_unixodbc:db_unixodbc_submit_query: rv=-1. Query= insert into silo
(username,domain,dst_addr,src_addr,body,ctype,exp_time,inc_time,snd_time )
values ('300100','mediafusion.es','sip:300100 at mediafusion.es','
sip:300900 at mediafusion.es','test message for offline
user','text/plain;charset=UTF-8',1456758965,1456758365,0)
Feb 29 16:06:05 deservoz2 /opt/opensips-1.11.5/sbin/opensips[21623]:
ERROR:db_unixodbc:db_unixodbc_extract_error:
*unixodbc:SQLExecDirect=22018:1:257:[FreeTDS][SQL
Server]Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query.*
Feb 29 16:06:05 deservoz2 /opt/opensips-1.11.5/sbin/opensips[21623]:
ERROR:core:db_do_insert: error while submitting query
Feb 29 16:06:05 deservoz2 /opt/opensips-1.11.5/sbin/opensips[21623]:
ERROR:msilo:m_store: failed to store message



Same error occurs when the query is executed using osql tool provided by
FreeTDS. As error suggests, I've tried to execute insert query using
CONVERT function for a explicit varchar to varbinary conversion and it
works:

insert into silo
(username,domain,dst_addr,src_addr,body,ctype,exp_time,inc_time,snd_time )
values ('300100','mediafusion.es','sip:300100 at mediafusion.es','
sip:300900 at mediafusion.es', CONVERT(varbinary(500),'test message for
offline  user','text/plain;charset=UTF-8',1456758965,1456758365,0)


Although data is correctly inserted using CONVERT function, when OpenSips
retrieves it for message dumping, the body of the message is being sent as
binary so it is not readable. For the body to be readable, a CONVERT
function must be used for explicit conversion from varbinary to varchar in
select statement.
Not sure whether this issue is on Opensips side or UnixODBC + FreeTDS. I
can provide entire debug traces, network traces, OpenSips script,
UnixODBC/FreeTDS configuration if it helps.

Using MySQL instead of MS SQL, both data storage and retrieval are working
properly.
Is it an OpenSips limitation or a UnixODBC + FreeTDS misbehavior? Does
anyone suffer this problem before?
Any help will be appreciated.

Thanks & Regards,
Mila
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20160229/a6a3b4c8/attachment.htm>