(RADIATOR) query during insert data

[Hugh Irvine]

Hello Budi -

You should use two AuthBy SQL clauses with the AcctSQLStatement(s) in  
the second one.

Alternatively you could possibly use a hook (there are some examples in  
"goodies/hooks.txt").

regards

Hugh


On Thursday, Jun 12, 2003, at 18:21 Australia/Melbourne, budi wibowo  
wrote:

> ok here we go
> from trace 4 result .. AcctSQLStatement is executed
> first time before i got the paramater from
> AcctColumnDef. that makes the value always 0
> any idea to make AcctSQLStatement executed after
> AcctColumnDef?
>
>
>
>      <AuthBy SQL>
>         DBSource                dbi:mysql:bill
>         DBUsername              xx
>         DBAuth                  yyy
>
>         AuthSelect
>         AccountingTable accounting
>         AcctColumnDef
> h323setuptime,%{cisco-h323-setup-time},formatted,substri
> ng_index(%s, '=',-1)
>         AcctColumnDef
> h323gwid,%{cisco-h323-gw-id},formatted,substring_index(%
> s, '=',-1)
>         AcctColumnDef
> h323confid,%{cisco-h323-conf-id},formatted,substring_ind
> ex(%s, '=',-1)
>         AcctColumnDef
> h323callorigin,%{cisco-h323-call-origin},formatted,subst
> ring_index(%s, '=',-1)
>         AcctColumnDef
> h323calltype,%{cisco-h323-call-type},formatted,substring
> _index(%s, '=',-1)
>         AcctColumnDef
> acctsessiontime,Acct-Session-Time,integer
>         AcctColumnDef
> h323connecttime,%{cisco-h323-connect-time},formatted,sub
> string_index(%s, '=',-1)
>         AcctColumnDef
> h323disconnecttime,%{cisco-h323-disconnect-time},formatt
> ed,substring_index(%s, '=',-1)
>         AcctColumnDef
> h323disconnectcause,%{cisco-h323-disconnect-cause},forma
> tted,substring_index(%s, '=',-1)
>         AcctColumnDef
> h323remoteaddress,%{cisco-h323-remote-address},formatted
> ,substring_index(%s, '=',-1)
>         AcctColumnDef   username,User-Name
>         AcctColumnDef
> acctstatustype,Acct-Status-Type
>         AcctColumnDef
> calledstationid,Called-Station-Id
>         AcctColumnDef   nasipaddress,NAS-IP-Address
>         AcctSQLStatement update set
> time_bill=ceiling(%{Acct-Session-Time}/60)
>
>     </AuthBy>
>
> trace 4 result:
>
> Thu Jun 12 08:16:42 2003: DEBUG: Handling with
> Radius::AuthSQL
> Thu Jun 12 08:16:42 2003: DEBUG: Handling accounting
> with Radius::AuthSQL
> Thu Jun 12 08:16:42 2003: DEBUG: do query is: update
> set time_bill=ceiling(0/60)
>
> Thu Jun 12 08:16:42 2003: ERR: do failed for 'update
> set time_bill=ceiling(0/60)': You have an error in
> your SQL syntax near 'set time_bill=ceiling(0/60)' at
> line 1
> Thu Jun 12 08:16:42 2003: ERR: do failed for 'update
> set time_bill=ceiling(0/60)': You have an error in
> your SQL syntax near 'set time_bill=ceiling(0/60)' at
> line 1
> Thu Jun 12 08:16:42 2003: DEBUG: do query is: insert
> into accounting
> (acctsessiontime,h323callorigin,h323connecttime,h323calltype,acctstatus 
> type,h323confid,calledstationid,username,h323gwid,nasipaddress,h323remo 
> teaddress,h323setuptime,h323disconnectcause,h323disconnecttime)
> values (0,substring_index('h323-call-origin=answer',
> '=',-1),substring_index('h323-connect-time=.08:16:19.226
> UTC Thu Jun 12 2003',
> '=',-1),substring_index('h323-call-type=VoIP',
> '=',-1),'Stop',substring_index('h323-conf-id=F2564005
> 6DED1810 95BD00E0 4C8CA47E',
> '=',-1),'37373762771315886','budi at dbku',substring_index('h323-gw- 
> id=ipipgw.',
> '=',-1),'211.152.187.134',substring_index('h323-remote- 
> address=211.152.187.133',
> '=',-1),substring_index('h323-setup-time=.08:15:43.031
> UTC Thu Jun 12 2003',
> '=',-1),substring_index('h323-disconnect-cause=10',
> '=',-1),substring_index('h323-disconnect-time=.08:16:19.226
> UTC Thu Jun 12 2003', '=',-1))
>
>
> --- Hugh Irvine <hugh at open.com.au> wrote:
>>
>> Hello Budi -
>>
>> Can you please send me a copy of your configuration
>> file (no secrets)
>> together with a trace 4 debug from Radiator showing
>> what is happening.
>> I will also need to see a copy of the relevant SQL
>> records.
>>
>> In general you should test your SQL queries
>> interactively with whatever
>> tools are available with your database before trying
>> to use them in the
>> Radiator configuration file.
>>
>> regards
>>
>> Hugh
>>
>>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> http://calendar.yahoo.com
>
>

NB: have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.

===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.