(RADIATOR) query during insert data
Hugh Irvine
hugh at open.com.au
Fri Jun 13 03:38:29 CDT 2003
Hello Budi -
Can you tell me exactly what you want to do? I am not clear on what you
want to have happen.
The simplest way to carry information between AuthBy clauses is to add
it to the incoming request packet which can be used as a scratch-pad
area for this purpose.
regards
Hugh
On Friday, Jun 13, 2003, at 18:32 Australia/Melbourne, budi wibowo
wrote:
> i have made other AuthBy SQL
> <AuthBy SQL>
> DBSource dbi:mysql:bill
> DBUsername xxx
> DBAuth xxxx
> AccountingTable accounting
> AcctSQLStatement update set
> time_bill=ceiling(acctsessiontime/60)
> </AuthBy>
>
> i'm confused about one thing
> at second AuthBy SQL which paramater should i take
> Acct-Session-Time (from cisco)or acctsessiontime (from
> mysql table)
> after first AuthBy SQL is executed is the value
> inserted to mysql immediately? so when second
> AuthBySQL executed i can get the data from mysql table
>
> rgds
>
> budi
>
>
> --- Hugh Irvine <hugh at open.com.au> wrote:
>>
>> 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.
>>
>> ===
>>
> === message truncated ===
>
>
> __________________________________
> 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.
More information about the radiator
mailing list