<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<tt>Hi <tt>Frank,<br>
<br>
Usuall<tt>y we look for th<tt>e most e<tt>xpected types (<tt>according</tt>
to the DB schema we use)- there is not rule to try to
cover all the types (maybe some <tt>of them have no
meaning in <tt><tt>conjunction</tt><tt> with OpenSIPS).<br>
<br>
<tt>Give it a try and let me know. If it s<tt>olves
the problem<tt>, I can do a backport to 3.0 and
2.4 .</tt></tt></tt><br>
</tt></tt></tt></tt></tt></tt><br>
<tt>Regards,</tt><br>
</tt></tt>
<pre class="moz-signature" cols="72">Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
<a class="moz-txt-link-freetext" href="https://www.opensips-solutions.com">https://www.opensips-solutions.com</a>
OpenSIPS Summit 2019
<a class="moz-txt-link-freetext" href="https://www.opensips.org/events/Summit-2019Amsterdam/">https://www.opensips.org/events/Summit-2019Amsterdam/</a>
</pre>
<div class="moz-cite-prefix">On 06/05/2019 04:22 PM, Frank Lee
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:007401d51ba1$c3471d80$49d55880$@wtild.com">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Times New Roman \,serif";
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
tt
{mso-style-priority:99;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Hello Bogdan,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Thank you very
much for your help. I will try out and see about the “int”
type and my driver. So mostly in the code, when this error
happens, are you guys looking for “int” type or do you check
all type of all fields (e.g. Char has to be the right length
or bit field)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Thank you!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:windowtext">From:</span></b><span
style="color:windowtext"> Bogdan-Andrei Iancu
[<a class="moz-txt-link-freetext" href="mailto:bogdan@opensips.org">mailto:bogdan@opensips.org</a>] <br>
<b>Sent:</b> Tuesday, June 4, 2019 9:42 AM<br>
<b>To:</b> Frank Lee <a class="moz-txt-link-rfc2396E" href="mailto:Frank@wtild.com"><Frank@wtild.com></a>; 'OpenSIPS
users mailling list' <a class="moz-txt-link-rfc2396E" href="mailto:users@lists.opensips.org"><users@lists.opensips.org></a><br>
<b>Subject:</b> Re: [OpenSIPS-Users] Opensip 2.4.5
Subscriber module on ODBC (MSSQL 2016 )<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><tt><span style="font-size:10.0pt">Hi
Frank,</span></tt><span
style="font-size:10.0pt;font-family:"Courier New""><br>
<br>
<tt>The official DB schema is here :</tt><br>
<tt> <a
href="https://github.com/OpenSIPS/opensips/tree/master/db/schema"
moz-do-not-send="true">https://github.com/OpenSIPS/opensips/tree/master/db/schema</a></tt><br>
<br>
<tt>We use that in order to generate the DB engine specific
schema, see:</tt><br>
<tt> <a
href="https://github.com/OpenSIPS/opensips/tree/master/scripts"
moz-do-not-send="true">https://github.com/OpenSIPS/opensips/tree/master/scripts</a></tt><br>
<br>
<tt>But as it is not native, there is nothing for MS SQL .</tt><br>
<br>
<tt>Are you able to pull the latest master and check my fix
(<a
href="https://github.com/OpenSIPS/opensips/commit/dafa8f6a50958e1c6905621cceb2457f94163cc5"
moz-do-not-send="true">https://github.com/OpenSIPS/opensips/commit/dafa8f6a50958e1c6905621cceb2457f94163cc5</a>
) ?</tt><br>
<br>
<tt>As I said, nothing changed in the OpenSIPS unixodbc
support - but maybe something changed in the unixodbc lib
or in the MS SQL driver.</tt><br>
<br>
<tt>Regards,</tt><br>
<br>
<br>
</span><span style="font-size:12.0pt"><o:p></o:p></span></p>
<pre>Bogdan-Andrei Iancu<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>OpenSIPS Founder and Developer<o:p></o:p></pre>
<pre> <a href="https://www.opensips-solutions.com" moz-do-not-send="true">https://www.opensips-solutions.com</a><o:p></o:p></pre>
<pre>OpenSIPS Summit 2019<o:p></o:p></pre>
<pre> <a href="https://www.opensips.org/events/Summit-2019Amsterdam/" moz-do-not-send="true">https://www.opensips.org/events/Summit-2019Amsterdam/</a><o:p></o:p></pre>
<div>
<p class="MsoNormal">On 06/03/2019 04:55 PM, Frank Lee wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="color:#1F497D">Hello Bogdan,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Thank you
very much for looking into this for me. So you guys
actually check the data type of each field before query is
done? Do you know of anyone who has a TSQL script that
generate all the tables for Opensips 2.4.5? I do see you
guys have a link that has the table structure for opensips
tables:</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><a
href="https://opensips.org/html/docs/db/db-schema-devel.html#AEN9331"
moz-do-not-send="true">https://opensips.org/html/docs/db/db-schema-devel.html#AEN9331</a><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Is this information “complete” and is the
data field type “correct” for 2.4.5? Because it seesm the
table field type was used for 1.11 is “different” from the
2.4.5? And is that why my Microsoft sql server table that
worked for 1.11 no longer works for 2.4.5?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Frank<o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="color:windowtext">From:</span></b><span
style="color:windowtext"> Bogdan-Andrei Iancu [<a
href="mailto:bogdan@opensips.org"
moz-do-not-send="true">mailto:bogdan@opensips.org</a>]
<br>
<b>Sent:</b> Monday, June 3, 2019 12:27 AM<br>
<b>To:</b> OpenSIPS users mailling list <a
href="mailto:users@lists.opensips.org"
moz-do-not-send="true"><users@lists.opensips.org></a>;
Frank Lee <a href="mailto:frank@wtild.com"
moz-do-not-send="true"><frank@wtild.com></a><br>
<b>Subject:</b> Re: [OpenSIPS-Users] Opensip 2.4.5
Subscriber module on ODBC (MSSQL 2016 )</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><tt><span style="font-size:10.0pt">Hi
Frank,</span></tt><span
style="font-size:10.0pt;font-family:"Courier
New""><br>
<br>
<tt>There were no changes in the db_unixodbc driver in
OpenSIPS for some time. Maybe something changed in the
unixodbc driver for MS SQL.</tt><br>
<br>
<tt>Based on the this log:</tt><br>
<tt> core:db_table_version: invalid type (1) or nul (0)</tt><br>
<br>
<tt>I see that that the "table_version" from the version
table is returned as BIGINT, while OpenSIPS expects INT.
Of course, this is something we can quickly adjust.</tt><br>
<br>
<tt>In regards to the 'avp_db_query', could you post the
level 3 logs corresponding to the query ? Maybe it will
give us a clue.</tt><br>
<br>
<tt>Regards,</tt><br>
<br>
<br>
<br>
</span><o:p></o:p></p>
<pre>Bogdan-Andrei Iancu<o:p></o:p></pre>
<pre> <o:p></o:p></pre>
<pre>OpenSIPS Founder and Developer<o:p></o:p></pre>
<pre> <a href="https://www.opensips-solutions.com" moz-do-not-send="true">https://www.opensips-solutions.com</a><o:p></o:p></pre>
<pre>OpenSIPS Summit 2019<o:p></o:p></pre>
<pre> <a href="https://www.opensips.org/events/Summit-2019Amsterdam/" moz-do-not-send="true">https://www.opensips.org/events/Summit-2019Amsterdam/</a><o:p></o:p></pre>
<div>
<p class="MsoNormal">On 05/25/2019 07:33 AM, Frank Lee
wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span style="color:#1F497D">Hello, </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Was there
some major change between version 1.11 and 2.4.5 on
supporting of the ODBC connection of Microsoft SQL
Server? It seems I had 1.11 running fine on SQL server
(or at least partial table on sql server such as
location table). But now I use 2.4.5 it seems all the
module that uses odbc connector instead of the mysql
connector it will have the error below</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">core:db_table_version:
invalid type (1) or nul (0)</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Thus, I
changed all the module to use mysql connector (except
the AVPOPS Module because I have custom query in the
script which need to query the Microsoft sql server)
which made Opensips start out fine. But as soon as it
uses the </span><span style="font-family:Consolas">avp_db_query
in the script it seems it would not work. Alhtough I
don’t see any error in the syslog file (although I had
log_level set to 3, do I need to set it higher?)</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">Anyway,
it just seems all the query/support to Microsoft SQL
server doesn’t run right in 2.4.5 vs 1.11.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">Is
there some dependency that I am suppose to install which
is different? </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">I
had my 1.11 run on Debian 7. Now we are moving to Azure
cloud, I had run Ubuntu 18.04 since Debian 7 is not
support on Azure.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">I
had install odbc version 2.3.4 and also tdsodbc 1.00</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">Are
these not to correct version to interface between 2.4.5
and Microsoft sql server (I tried both version msssql
2008 and 2016).</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">Anyway,
any pointer or help will be greatly appreciated!</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas">Thank
you!</span><o:p></o:p></p>
<p class="MsoNormal"><span style="font-family:Consolas"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Frank Lee [<a
href="mailto:frank@wtild.com" moz-do-not-send="true">mailto:frank@wtild.com</a>]
<br>
<b>Sent:</b> Friday, May 24, 2019 10:58 AM<br>
<b>To:</b> '<a href="mailto:users@lists.opensips.org"
moz-do-not-send="true">users@lists.opensips.org</a>'
<a href="mailto:users@lists.opensips.org"
moz-do-not-send="true"><users@lists.opensips.org></a><br>
<b>Subject:</b> Opensip 2.4.5 Subscriber module on
ODBC (MSSQL 2016 )<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Hello, <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">This is the first time I use this mail
message, so let me know if this is not the correct way of
posting question.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Anyway, I am running Opensips 2.4.5 and
when I use MYSQL database on all module, opensips runs
fine. But as soon as I switch over the database source to
use ODBC driver (which is linked to Microsoft SQL server
2016), I get this error. What is It mean?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-family:Consolas">May 24 17:36:48
osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:core:db_table_version: invalid type (1) or nul (0)
version columns for subscriber</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-family:Consolas">May 24 17:36:48
osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:core:db_check_table_version: querying version for
table subscriber</span><o:p></o:p></p>
<p class="MsoNormal" style="text-autospace:none"><span
style="font-family:Consolas">May 24 17:36:48
osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:uri:mod_init: Invalid table version.</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I first created the MySQL table usinmg
the command:<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">opensipsdbctl create<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">And then I transfer the tables to MSSQL
using the:<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"
style="background:white;vertical-align:baseline"><span
style="font-size:17.0pt">Microsoft SQL Server Migration
Assistant 8.1 for MySQL</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">All the table named corrected and all
the index was created too.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Thank you very much!<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Frank<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman ,serif",serif"><br>
<br>
<br>
<br>
</span><o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Users mailing list<o:p></o:p></pre>
<pre><a href="mailto:Users@lists.opensips.org" moz-do-not-send="true">Users@lists.opensips.org</a><o:p></o:p></pre>
<pre><a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" moz-do-not-send="true">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><o:p></o:p></pre>
</blockquote>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New Roman
,serif",serif"> </span><o:p></o:p></p>
</blockquote>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman",serif"><o:p> </o:p></span></p>
</div>
</blockquote>
<br>
</body>
</html>