[Freeswitch-users] Nibblebill database update

Thu Feb 14 01:13:21 MSK 2013

Hello,

True, i didn't catch that

David

---

David Villasmil

On Feb 13, 2013, at 21:19, Steven Ayre <steveayre at gmail.com> wrote:

> [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
> 
> _________________________________________________________________________
> 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/d75d7d1f/attachment-0001.html 