[Freeswitch-users] Nibblebill database update

Wed Feb 13 23:19:15 MSK 2013

[STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1
Driver][mysqld-5.5.24-0ubuntu0.12.04.1]You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'usage=usage-0.476000 WHERE
user='1001'' at line 1
]

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.

The error message starts at the first character that failed the SQL
parsing. That's "usage".

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).
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

You either need to change your DB structure to avoid using the reserved
words (best) or quote the column name in backticks (`).

Try setting:
<param name="db_column_cash" value="`usage`"/>

-Steve

On 13 February 2013 19:59, David Villasmil
<david.villasmil.work at gmail.com>wrote:

> Hello,
>
> I was having the same problem, the solution is to use:
>
> <param name="odbc-dsn" value="odbc://dsn_name" />
>
> where "dsn_name" is obviously the dsn inside the /etc/odbc.ini file
> previously tested with "isql"
>
>
> good luck!
>
> David
>
>
> On Fri, Nov 2, 2012 at 11:25 AM, Sergey Okhapkin <sos at sokhapkin.dyndns.org
> > wrote:
>
>>  What is the structure of "accounts" table?
>>
>> On Friday 02 November 2012 14:42:41 William Alianto wrote:
>> > Thanks for pointing that out. I think I missed that part when I edited
>> > the config.
>> >
>> > On 11/02/2012 02:22 PM, Evgeniy Movlyan wrote:
>> > > Seems you need to uncomment custom SQL-queries and rewrite it to
>> > > according to your needs.
>> > >
>> > > 02.11.2012 05:54, William Alianto пишет:
>> > >> Hi,
>> > >>
>> > >> This is my nibblebill.conf.xml
>> > >>
>> > >> <configuration name="nibblebill.conf" description="Nibble Billing">
>> > >> <settings>
>> > >> <!-- See http://wiki.freeswitch.org/wiki/Mod_nibblebill for help
>> with
>> > >> these options -->
>> > >>
>> > >> <!-- Information for connecting to your database -->
>> > >> <param name="db_username" value="root"/>
>> > >> <param name="db_password" value="xxxxxxxx"/>
>> > >> <param name="db_dsn" value="billing"/>
>> > >>
>> > >> <!-- The database table where your CASH column is located -->
>> > >> <param name="db_table" value="accounts"/>
>> > >>
>> > >> <!-- The column name where we store the value of the account -->
>> > >> <param name="db_column_cash" value="usage"/>
>> > >>
>> > >> <!-- The column name for the unique ID identifying the account -->
>> > >> <param name="db_column_account" value="user"/>
>> > >>
>> > >> <!-- Custom SQL for loading current balance - overrides column names
>> > >> channel vars are interpreted.
>> > >> field nibble_balance is used for balance info
>> > >>
>> > >> <param name="custom_sql_lookup" value="SELECT cash AS nibble_balance
>> > >> FROM accounts WHERE account_code='${nibble_account}'"/>
>> > >> -->
>> > >>
>> > >> <!-- Custom SQL for loading current balance - overrides column names
>> > >> channel vars are interpreted.
>> > >> nibble_increment is the amount to update
>> > >>
>> > >> <param name="custom_sql_save" value="UPDATE accounts SET
>> > >> cash=cash-${nibble_increment} WHERE
>> account_code='${nibble_account}'"/>
>> > >> -->
>> > >>
>> > >>
>> > >> <!-- Default heartbeat interval. Set to 'off' for no heartbeat (i.e.
>> > >> bill only at end of call) -->
>> > >> <param name="global_heartbeat" value="1"/>
>> > >>
>> > >> <!-- By default, warn a caller when their balance is at $5.00. You
>> can
>> > >> set this to a negative number. -->
>> > >> <!--<param name="lowbal_amt" value="5"/>-->
>> > >> <!--<param name="lowbal_action" value="play ding"/>-->
>> > >>
>> > >> <!-- By default, terminate a caller when their balance hits $0.00.
>> You
>> > >> can set this to a negative number. -->
>> > >> <!--<param name="nobal_amt" value="0"/>
>> > >> <param name="nobal_action" value="hangup"/>-->
>> > >>
>> > >> <!-- If a call goes beyond a certain dollar amount, flag or
>> terminate it
>> > >> -->
>> > >> <!--<param name="percall_max_amt" value="100"/>
>> > >> <param name="percall_action" value="hangup"/>-->
>> > >>
>> > >> </settings>
>> > >> </configuration>
>> > >>
>> > >> On 11/01/2012 02:56 PM, Evgeniy Movlyan wrote:
>> > >>> Hello.
>> > >>> Show please your nibblebill.conf.xml.
>> > >>>
>> > >>> 01.11.2012 05:57, William Alianto пишет:
>> > >>>> Hi,
>> > >>>>
>> > >>>> I was trying to use nibblebill as billing control of my FS. I have
>> > >>>> configured the odbc and the nibblebill configuration. When I tried
>> to
>> > >>>> call, the query looks ok. But when I hanged up the call, it seems
>> like
>> > >>>> there is something wrong with the database connection, since it's
>> not
>> > >>>> updating the database at all. I got error message on CLI
>> > >>>>
>> > >>>> 2012-11-01 10:51:00.559472 [ERR] switch_odbc.c:494 ERR: [UPDATE
>> > >>>> accounts
>> > >>>> SET usage=age-0.476000 WHERE user='1001']
>> > >>>> [STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1
>> > >>>> Driver][mysqld-5.5.24-0ubuntu0.12.04.1]You have an error in your
>> SQL
>> > >>>> syntax; check the manual that corresponds to your MySQL server
>> version
>> > >>>> for the right syntax to use near 'usage=age-0.476000 WHERE
>> > >>>> user=001'' at line 1
>> > >>>> ]
>> > >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:343 ERR: [UPDATE
>> > >>>> accounts SET usage=age-0.476000 WHERE user='1001']
>> > >>>> []
>> > >>>> 2012-11-01 10:51:00.559472 [CRIT] mod_nibblebill.c:542 Failed to
>> > >>>> log to
>> > >>>> database!
>> > >>>> 2012-11-01 10:51:00.559472 [DEBUG] mod_nibblebill.c:383 Doing
>> lookup
>> > >>>> query
>> > >>>> [SELECT usage AS nibble_balance FROM accounts WHERE user=001']
>> > >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:385 ERR: [SELECT
>> > >>>> usage
>> > >>>> AS nibble_balance FROM accounts WHERE user=001']
>> > >>>> [STATE: 42000 CODE 1064 ERROR: [unixODBC][MySQL][ODBC 5.1
>> > >>>> Driver][mysqld-5.5.24-0ubuntu0.12.04.1]You have an error in your
>> SQL
>> > >>>> syntax; check the manual that corresponds to your MySQL server
>> version
>> > >>>> for the right syntax to use near 'usage AS nibble_balance FROM
>> > >>>> accounts
>> > >>>> WHERE user=001'' at line 1
>> > >>>> ]
>> > >>>> 2012-11-01 10:51:00.559472 [ERR] mod_nibblebill.c:386 Error running
>> > >>>> this
>> > >>>> query: [SELECT usage AS nibble_balance FROM accounts WHERE
>> user=001']
>> > >>>>
>> > >>>> Could anybody help me solve this issue?
>> > >>>>
>> > >>>> Regards
>> > >>>>
>> > >>>>
>> _______________________________________________________________________
>> > >>>> __
>> > >>>>
>> > >>>>
>> > >>>> Professional FreeSWITCH Consulting Services:
>> > >>>> consulting at freeswitch.org
>> > >>>> http://www.freeswitchsolutions.com
>> > >>>>
>> > >>>> 
>> > >>>> 
>> > >>>>
>> > >>>> Official FreeSWITCH Sites
>> > >>>> http://www.freeswitch.org
>> > >>>> http://wiki.freeswitch.org
>> > >>>> http://www.cluecon.com
>> > >>>>
>> > >>>> FreeSWITCH-users mailing list
>> > >>>> FreeSWITCH-users at lists.freeswitch.org
>> > >>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> > >>>> UNSUBSCRIBE:
>> http://lists.freeswitch.org/mailman/options/freeswitch-user
>> > >>>> s
>> > >>>>
>> > >>>> http://www.freeswitch.org
>> >
>> >
>> _________________________________________________________________________
>> > Professional FreeSWITCH Consulting Services:
>> > consulting at freeswitch.org
>> > http://www.freeswitchsolutions.com
>> >
>> > 
>> > 
>> >
>> > Official FreeSWITCH Sites
>> > http://www.freeswitch.org
>> > http://wiki.freeswitch.org
>> > http://www.cluecon.com
>> >
>> > FreeSWITCH-users mailing list
>> > FreeSWITCH-users at lists.freeswitch.org
>> > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> > UNSUBSCRIBE:
>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>> > http://www.freeswitch.org
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130213/e9cd35f4/attachment.html 