Hello,

True, i didn't catch that

David

---

David Villasmil


On Feb 13, 2013, at 21:19, Steven Ayre <steveayre@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@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@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@freeswitch.org
> >>>> http://www.freeswitchsolutions.com
> >>>>
> >>>> FreeSWITCH-powered IP PBX: The CudaTel Communication Server
> >>>> http://www.cudatel.com
> >>>>
> >>>> Official FreeSWITCH Sites
> >>>> http://www.freeswitch.org
> >>>> http://wiki.freeswitch.org
> >>>> http://www.cluecon.com
> >>>>
> >>>> FreeSWITCH-users mailing list
> >>>> FreeSWITCH-users@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@freeswitch.org
> http://www.freeswitchsolutions.com
>
> FreeSWITCH-powered IP PBX: The CudaTel Communication Server
> http://www.cudatel.com
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users@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@freeswitch.org
http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server
http://www.cudatel.com

Official FreeSWITCH Sites
http://www.freeswitch.org
http://wiki.freeswitch.org
http://www.cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@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@freeswitch.org
http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server
http://www.cudatel.com

Official FreeSWITCH Sites
http://www.freeswitch.org
http://wiki.freeswitch.org
http://www.cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@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@freeswitch.org
http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server
http://www.cudatel.com

Official FreeSWITCH Sites
http://www.freeswitch.org
http://wiki.freeswitch.org
http://www.cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org