(RADIATOR) <SessionDatabase SQL>

Tue May 15 09:42:32 CDT 2001

El 15 May 2001, a las 10:50, Hugh Irvine escribió:

> 
> Hello Mariano -
> 
> Why don't you use this:
> 
> 	%d%H%M%S

Right, now it's:
    AddQuery INSERT INTO USUARIOS_EN_LINEA \
        (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, \
        USUA_IP_NAS, POOL_NAME, USUA_PORT, USUA_BYTES, USUA_TIEMPO, \
        USUA_HORA_CONEXION, USUA_CALL_ID, USUA_DNIS, USUA_IP_ASIGNADA) \
        VALUES \
        ('%U', '%R', '%{Acct-Session-Id}', \
        '%N', 'nombre del pool', %{NAS-Port}, 0, 0, \
        TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS'), \
        '%{Calling-Station-Id}', '%{Called-Station-Id}', '%{Framed-IP-Address}')
and it works but... where am I supposed to get things from?

Where does the timestamp in this sql statement coming from?

In the manual: 

http://www.open.com.au/radiator/ref.html#25705 (TABLE 2. DateFormat 
special characters)

is giving me characters to format an (arbitrary?) epoch and

http://www.open.com.au/radiator/ref.html#22600 (TABLE 1. Special string 
formatting characters)

is giving me characters to insert either the current time or the current 
packet timestamp (being the later what I wanted, but would settle for the 
other)

As I had it configured before
TO_DATE('%f-%g-%i %j:%k:%p', 'YYYY-MM-DD HH24:MI:SS')
I understood I was directly taking the current packet timestamp and 
generating 'YYYY-MM-DD HH:mm:SS' where (from the manual's Table 1)
YYYY: The Timestamp year (4 digits)
MM: The Timestamp month number (2 digits)
DD: The Timestamp day of the month (2 digits)
HH: The Timestamp hour (0-23)
mm: The Timestamp minute (0-59)
SS: the Timestamp second (0-59)
    ^BTW, the lowcase "t" in the manual is inconsistent :-)

The problem here was that at least %p doesn't add a leading "0".

In the current configuration, I am using the fields from Table 2 
formatting an unknown date-time (I guess it's the current one).

TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS')

As the info in the manual wasn't all that clear about it, I changed the 
date in the machine and confirmed that %m and %d also use a leading "0" 
(THAT isn't written in the manual).

So now I have it working, now for the "theoretical" part of the question, 
if I had a radius attribute with an arbitrary date-time in it (say, the 
birthday -and time- of the nas manufacturer's mother) and I would like to 
put it in a column in my on-line users database, what would be the idiom 
to do it so?. That is I understand (I think) how to put it in an 
accounting database by means of a

AcctColumnDef   DB_COLUMN,Radius-Attribute-Name,integer-date, \
                TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS')	

but in the AddQuery (or AnythingQuery) I don't know how to use Radius-
Attribute-Name here.

> 
> regards
> 
> Hugh
> 
> 
> On Tuesday 15 May 2001 07:56, Mariano Absatz wrote:
> > Hi,
> >
> > I'm having problems with a <SessionDatabase SQL>... I want to use the
> > timestamp in the AddQuery (with Oracle), but '%p' is yelding a 1 digit
> > second if the seconds in the timestamp is <10.
> >
> > Following is the corresponding part in the config file and afterwards, a
> > trace 4.
> >
> > Note, in the trace, that it says
> > TO_DATE('2001-05-14 18:38:2', 'YYYY-MM-DD HH24:MI:SS')
> > instead of
> > TO_DATE('2001-05-14 18:38:02', 'YYYY-MM-DD HH24:MI:SS')
> >
> > <SessionDatabase SQL>
> >         Identifier SessDBUsers
> >
> >         include %{GlobalVar:ConfigDir}/DBUseData.cfg
> >
> >
> >         AddQuery INSERT INTO USUARIOS_EN_LINEA \
> >                 (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, \
> >                 USUA_IP_NAS, POOL_NAME, USUA_PORT, USUA_BYTES,
> > USUA_TIEMPO, \
> >                 USUA_HORA_CONEXION, USUA_CALL_ID, USUA_DNIS,
> > USUA_IP_ASIGNADA) \
> >                 VALUES \
> >                 ('%U', '%R', '%{Acct-Session-Id}', \
> >                 '%N', 'nombre del pool', %{NAS-Port}, 0, 0, \
> >                 TO_DATE('%f-%g-%i %j:%k:%p', 'YYYY-MM-DD HH24:MI:SS'), \
> >                 '%{Calling-Station-Id}', '%{Called-Station-Id}',
> > '%{Framed-IP-Address}')
> >
> >         DeleteQuery DELETE FROM USUARIOS_EN_LINEA \
> >                 WHERE USU_CODIGO='%U' AND VISP_CODIGO='%R' AND \
> >                         USUA_IP_NAS='%N' AND USUA_PORT='%{NAS-Port}'
> >
> >         ClearNasQuery DELETE FROM USUARIOS_EN_LINEA \
> >                 WHERE USUA_IP_NAS='%N'
> >
> >         CountQuery SELECT USUA_IP_NAS, USUA_PORT, USUA_SESION_ID \
> >                 FROM USUARIOS_EN_LINEA \
> >                 WHERE USU_CODIGO='%U' AND VISP_CODIGO='%R'
> >
> >
> > </SessionDatabase>
> >
> >
> >
> >
> >
> >
> > Mon May 14 18:38:02 2001: INFO: Server started: Radiator 2.18.1 on mr-visp
> > Mon May 14 18:38:02 2001: DEBUG: Packet dump:
> > *** Received from 127.0.0.1 port 41858 ....
> > Code:       Accounting-Request
> > Identifier: 198
> > Authentic:  Gr<16><25>3<197>+<215><2><219><223>`eSUK
> > Attributes:
> >         User-Name = "yaNi at pert"
> >         Service-Type = Framed-User
> >         NAS-IP-Address = 200.59.130.83
> >         NAS-Port = 1234
> >         NAS-Port-Type = Async
> >         Acct-Session-Id = "hola001"
> >         Acct-Status-Type = Start
> >         Called-Station-Id = "123456789"
> >         Calling-Station-Id = "987654321"
> >
> > Mon May 14 18:38:02 2001: DEBUG: Rewrote user name to yaNi at pert
> > Mon May 14 18:38:02 2001: DEBUG: Rewrote user name to yani at pert
> > Mon May 14 18:38:02 2001: DEBUG: Check if Handler  should be used to
> > handle this request
> > Mon May 14 18:38:02 2001: DEBUG: Handling request with Handler ''
> > Mon May 14 18:38:02 2001: DEBUG: SessDBUsers Adding session for
> > yaNi at pert, 200.59.130.83, 1234
> > Mon May 14 18:38:02 2001: DEBUG: do query is: DELETE FROM
> > USUARIOS_EN_LINEA WHERE USU_CODIGO='yani' AND VISP_CODIGO='pert' AND
> > USUA_IP_NAS='200.59.130.83' AND USUA_PORT='1234'
> >
> > Mon May 14 18:38:02 2001: DEBUG: do query is: INSERT INTO
> > USUARIOS_EN_LINEA (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, USUA_IP_NAS,
> > POOL_NAME, USUA_PORT, USUA_BYTES, USUA_TIEMPO, USUA_HORA_CONEXION,
> > USUA_CALL_ID, USUA_DNIS, USUA_IP_ASIGNADA) VALUES ('yani', 'pert',
> > 'hola001', '200.59.130.83', 'nombre del pool', 1234, 0, 0, TO_DATE('2001-
> > 05-14 18:38:2', 'YYYY-MM-DD HH24:MI:SS'), '987654321', '123456789', '0')
> >
> > Mon May 14 18:38:03 2001: ERR: do failed for 'INSERT INTO
> > USUARIOS_EN_LINEA (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, USUA_IP_NAS,
> > POOL_NAME, USUA_PORT, USUA_BYTES, USUA_TIEMPO, USUA_HORA_CONEXION,
> > USUA_CALL_ID, USUA_DNIS, USUA_IP_ASIGNADA) VALUES ('yani', 'pert',
> > 'hola001', '200.59.130.83', 'nombre del pool', 1234, 0, 0, TO_DATE('2001-
> > 05-14 18:38:2', 'YYYY-MM-DD HH24:MI:SS'), '987654321', '123456789',
> > '0')': ORA-01722: invalid number (DBD ERROR: OCIStmtExecute)
> > Mon May 14 18:38:03 2001: ERR: do failed for 'INSERT INTO
> > USUARIOS_EN_LINEA (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, USUA_IP_NAS,
> > POOL_NAME, USUA_PORT, USUA_BYTES, USUA_TIEMPO, USUA_HORA_CONEXION,
> > USUA_CALL_ID, USUA_DNIS, USUA_IP_ASIGNADA) VALUES ('yani', 'pert',
> > 'hola001', '200.59.130.83', 'nombre del pool', 1234, 0, 0, TO_DATE('2001-
> > 05-14 18:38:2', 'YYYY-MM-DD HH24:MI:SS'), '987654321', '123456789',
> > '0')': ORA-01722: invalid number (DBD ERROR: OCIStmtExecute)
> > Mon May 14 18:38:03 2001: DEBUG: Handling with Radius::AuthSQL
> > Mon May 14 18:38:03 2001: DEBUG: Handling accounting with Radius::AuthSQL
> > Mon May 14 18:38:03 2001: DEBUG: Accounting accepted
> > Mon May 14 18:38:03 2001: DEBUG: Packet dump:
> > *** Sending to 127.0.0.1 port 41858 ....
> > Code:       Accounting-Response
> > Identifier: 198
> > Authentic:  Gr<16><25>3<197>+<215><2><219><223>`eSUK
> > Attributes:
> >
> >
> >
> > TIA.
> >

===
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.