<div><span style="background-color:rgb(255,255,255);color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px">[STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1</span></div><div><span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">Driver][mysqld-5.5.24-</span><span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">0ubuntu0.12.04.1]You have an error in your SQL</span><br style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">syntax; check the manual that corresponds to your MySQL server version</span><br style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">for the right syntax to use near 'usage=usage-0.476000 WHERE</span><br style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">user='1001'' at line 1</span><br style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">]</span></div><div><font color="#222222" face="arial, sans-serif"><br></font></div><div><font color="#222222" face="arial, sans-serif">This error is coming from MySQL itself, which indicates it is actually using the ODBC connection. This isn't the same as David's issue.</font></div>
<div><font color="#222222" face="arial, sans-serif"><br></font></div><div><font color="#222222" face="arial, sans-serif">The error message starts at the first character that failed the SQL parsing. That's "usage".</font></div>
<div><font color="#222222" face="arial, sans-serif"><br></font></div><div><font color="#222222" face="arial, sans-serif">That happens to be on the list of MySQL reserved words which will give an error in 5.5 (which wouldn't have happened in 5.1 or earlier).</font></div>
<div><font color="#222222" face="arial, sans-serif"><a href="http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html">http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html</a></font></div><div><font color="#222222" face="arial, sans-serif"><br>
</font></div><div><font color="#222222" face="arial, sans-serif">You either need to change your DB structure to avoid using the reserved words (best) or quote the column name in backticks (`).</font></div><div><font color="#222222" face="arial, sans-serif"><br>
</font></div><div><font color="#222222" face="arial, sans-serif">Try setting:</font></div><div><font color="#222222" face="arial, sans-serif"><param name="db_column_cash" value="`usage`"/></font></div>
<div><font color="#222222" face="arial, sans-serif"><br></font></div><div><font color="#222222" face="arial, sans-serif">-Steve</font></div><div><font color="#222222" face="arial, sans-serif"><br></font></div><div><font color="#222222" face="arial, sans-serif"><br>
</font><br><div class="gmail_quote">On 13 February 2013 19:59, David Villasmil <span dir="ltr"><<a href="mailto:david.villasmil.work@gmail.com" target="_blank">david.villasmil.work@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Hello,<div><br></div><div>I was having the same problem, the solution is to use:</div><div><br></div><div><param name="odbc-dsn" value="odbc://dsn_name" /><br></div>
<div><br></div><div>where "dsn_name" is obviously the dsn inside the /etc/odbc.ini file previously tested with "isql"</div><div><br></div><div><br></div><div>good luck!</div><span class="HOEnZb"><font color="#888888">
<div><br></div><div>David</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Fri, Nov 2, 2012 at 11:25 AM, Sergey Okhapkin <span dir="ltr"><<a href="mailto:sos@sokhapkin.dyndns.org" target="_blank">sos@sokhapkin.dyndns.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> What is the structure of "accounts" table?<br>
<div><div><br>
On Friday 02 November 2012 14:42:41 William Alianto wrote:<br>
> Thanks for pointing that out. I think I missed that part when I edited<br>
> the config.<br>
><br>
> On 11/02/2012 02:22 PM, Evgeniy Movlyan wrote:<br>
> > Seems you need to uncomment custom SQL-queries and rewrite it to<br>
> > according to your needs.<br>
> ><br>
> > 02.11.2012 05:54, William Alianto пишет:<br>
> >> Hi,<br>
> >><br>
> >> This is my nibblebill.conf.xml<br>
> >><br>
> >> <configuration name="nibblebill.conf" description="Nibble Billing"><br>
> >> <settings><br>
> >> <!-- See <a href="http://wiki.freeswitch.org/wiki/Mod_nibblebill" target="_blank">http://wiki.freeswitch.org/wiki/Mod_nibblebill</a> for help with<br>
> >> these options --><br>
> >><br>
> >> <!-- Information for connecting to your database --><br>
> >> <param name="db_username" value="root"/><br>
> >> <param name="db_password" value="xxxxxxxx"/><br>
> >> <param name="db_dsn" value="billing"/><br>
> >><br>
> >> <!-- The database table where your CASH column is located --><br>
> >> <param name="db_table" value="accounts"/><br>
> >><br>
> >> <!-- The column name where we store the value of the account --><br>
> >> <param name="db_column_cash" value="usage"/><br>
> >><br>
> >> <!-- The column name for the unique ID identifying the account --><br>
> >> <param name="db_column_account" value="user"/><br>
> >><br>
> >> <!-- Custom SQL for loading current balance - overrides column names<br>
> >> channel vars are interpreted.<br>
> >> field nibble_balance is used for balance info<br>
> >><br>
> >> <param name="custom_sql_lookup" value="SELECT cash AS nibble_balance<br>
> >> FROM accounts WHERE account_code='${nibble_account}'"/><br>
> >> --><br>
> >><br>
> >> <!-- Custom SQL for loading current balance - overrides column names<br>
> >> channel vars are interpreted.<br>
> >> nibble_increment is the amount to update<br>
> >><br>
> >> <param name="custom_sql_save" value="UPDATE accounts SET<br>
> >> cash=cash-${nibble_increment} WHERE account_code='${nibble_account}'"/><br>
> >> --><br>
> >><br>
> >><br>
> >> <!-- Default heartbeat interval. Set to 'off' for no heartbeat (i.e.<br>
> >> bill only at end of call) --><br>
> >> <param name="global_heartbeat" value="1"/><br>
> >><br>
> >> <!-- By default, warn a caller when their balance is at $5.00. You can<br>
> >> set this to a negative number. --><br>
> >> <!--<param name="lowbal_amt" value="5"/>--><br>
> >> <!--<param name="lowbal_action" value="play ding"/>--><br>
> >><br>
> >> <!-- By default, terminate a caller when their balance hits $0.00. You<br>
> >> can set this to a negative number. --><br>
> >> <!--<param name="nobal_amt" value="0"/><br>
> >> <param name="nobal_action" value="hangup"/>--><br>
> >><br>
> >> <!-- If a call goes beyond a certain dollar amount, flag or terminate it<br>
> >> --><br>
> >> <!--<param name="percall_max_amt" value="100"/><br>
> >> <param name="percall_action" value="hangup"/>--><br>
> >><br>
> >> </settings><br>
> >> </configuration><br>
> >><br>
> >> On 11/01/2012 02:56 PM, Evgeniy Movlyan wrote:<br>
> >>> Hello.<br>
> >>> Show please your nibblebill.conf.xml.<br>
> >>><br>
> >>> 01.11.2012 05:57, William Alianto пишет:<br>
> >>>> Hi,<br>
> >>>><br>
> >>>> I was trying to use nibblebill as billing control of my FS. I have<br>
> >>>> configured the odbc and the nibblebill configuration. When I tried to<br>
> >>>> call, the query looks ok. But when I hanged up the call, it seems like<br>
> >>>> there is something wrong with the database connection, since it's not<br>
> >>>> updating the database at all. I got error message on CLI<br>
> >>>><br>
> >>>> 2012-11-01 10:51:00.559472 [ERR] switch_odbc.c:494 ERR: [UPDATE<br>
> >>>> accounts<br>
> >>>> SET usage=age-0.476000 WHERE user='1001']<br>
> >>>> [STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1<br>
> >>>> Driver][mysqld-5.5.24-0ubuntu0.12.04.1]You have an error in your SQL<br>
> >>>> syntax; check the manual that corresponds to your MySQL server version<br>
> >>>> for the right syntax to use near 'usage=age-0.476000 WHERE<br>
> >>>> user=001'' at line 1<br>
> >>>> ]<br>
> >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:343 ERR: [UPDATE<br>
> >>>> accounts SET usage=age-0.476000 WHERE user='1001']<br>
> >>>> []<br>
> >>>> 2012-11-01 10:51:00.559472 [CRIT] mod_nibblebill.c:542 Failed to<br>
> >>>> log to<br>
> >>>> database!<br>
> >>>> 2012-11-01 10:51:00.559472 [DEBUG] mod_nibblebill.c:383 Doing lookup<br>
> >>>> query<br>
> >>>> [SELECT usage AS nibble_balance FROM accounts WHERE user=001']<br>
> >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:385 ERR: [SELECT<br>
> >>>> usage<br>
> >>>> AS nibble_balance FROM accounts WHERE user=001']<br>
> >>>> [STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1<br>
> >>>> Driver][mysqld-5.5.24-0ubuntu0.12.04.1]You have an error in your SQL<br>
> >>>> syntax; check the manual that corresponds to your MySQL server version<br>
> >>>> for the right syntax to use near 'usage AS nibble_balance FROM<br>
> >>>> accounts<br>
> >>>> WHERE user=001'' at line 1<br>
> >>>> ]<br>
> >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:386 Error running<br>
> >>>> this<br>
> >>>> query: [SELECT usage AS nibble_balance FROM accounts WHERE user=001']<br>
> >>>><br>
> >>>> Could anybody help me solve this issue?<br>
> >>>><br>
> >>>> Regards<br>
> >>>><br>
> >>>> _______________________________________________________________________<br>
> >>>> __<br>
> >>>><br>
> >>>><br>
> >>>> Professional FreeSWITCH Consulting Services:<br>
> >>>> <a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
> >>>> <a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
> >>>><br>
> >>>> FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
> >>>> <a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
> >>>><br>
> >>>> Official FreeSWITCH Sites<br>
> >>>> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
> >>>> <a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
> >>>> <a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
> >>>><br>
> >>>> FreeSWITCH-users mailing list<br>
> >>>> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
> >>>> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> >>>> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-user" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-user</a><br>
> >>>> s<br>
> >>>><br>
> >>>> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
><br>
> _________________________________________________________________________<br>
> Professional FreeSWITCH Consulting Services:<br>
> <a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
> <a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
><br>
> FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
> <a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
><br>
> Official FreeSWITCH Sites<br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
> <a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
> <a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
><br>
> FreeSWITCH-users mailing list<br>
> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br>
_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
</div></div></blockquote></div><br></div>
</div></div><br>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br></div>