Hi Hugh,
Thank you for your reply.
For the first problem, after searching for the logfile, I can find that there are
at most three null values: ACCTSESSIONID, FRAMEDIPADDRESS, and SERVICETYPE. I
changed the config to be the following:
AddQuery insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, TIME_STAMP,
NASPORTTYPE) values ('%n', '%N', %{NAS-Port}, %{Timestamp}, '%{NAS-Port-Type}')
CountQuery select NASIDENTIFIER, NASPORT from RADONLINE where USERNAME='%n'
Then, I run some testing. From the logfile, I can see the following message:
Fri Aug 26 16:58:54 1999: DEBUG: do query is: insert into RADONLINE (USERNAME,
NASIDENTIFIER, NASPORT, TIME_STAMP, NASPORTTYPE) values ('c200005', '203.63.154.1',
1234, 935667533, 'Async')
Fri Aug 26 17:01:02 1999: DEBUG: do query is: select NASIDENTIFIER, NASPORT from
RADONLINE where USERNAME='c200005'
Is that it would avoid null values to be inserted into the session database.
For the second problem, I try to use store procedure for the Oracle session
database. I create a procedure using the following sql:
CREATE OR REPLACE PROCEDURE radius.sessiondb (NAME IN VARCHAR2 , NASID IN VARCHAR2,
PORT IN NUMBER, TIME IN NUMBER, PORTTYPE IN VARCHAR2)
AS BEGIN
insert into RADONLINE (USERNAME, NASIDENTIFIER, NASPORT, TIME_STAMP,
FRAMEDIPADDRESS, NASPORTTYPE) values (NAME, NASID, PORT, TIME, PORTTYPE);
END;
I use sqlplus to create the above procedure. Then, I exeute the procedure, at the
sql prompt:
EXEC radius.sessiondb ('c200030', '203.63.154.1', 1234, 935668514, 'Async');
The execute is success. Then, I put the following config into the Radiator config
file:
AddQuery exec radius.sessiondb ('%n', '%N', %{NAS-Port}, %{Timestamp},
'%{NAS-Port-Type}')
However, I get the following error message:
Thu Aug 26 19:55:15 1999: ERR: do failed for 'EXECUTE radius.sessiondb ('c200030',
'203.63.154.1', 1234, 935668514, 'Async')': ORA-00900: invalid SQL statement (DBD
ERROR: OCIStmtExecute)
I don't find any further descriptions inside the Radiator reference manual about
it. Can you give me more details about using the store procedures?
Hugh Irvine wrote:
> > I encountered two problems of using Oracle 8. The first one is Oracle
> > core dump and the second one is about the Oracle efficiency.
> >
> > For the first problem, after I use the Oracle as session database, the
> > Oracle server always core dump. From the reply of Oracle support, there
> > is a bug in Oracle 8 that the core dump is due to the existence of null
> > variables inside the SQL statements. However, they don't have a patch
> > for our server. So, we have to avoid null variables. From the logfile, I
> > found that there is always a null variable, servicetype, inside the
> > insert sql statement, i.e. insert into RADONLINE (USERNAME,
> > NASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS,
> > NASPORTTYPE,SERVICETYPE) values ('lawkei', '207.176.112.154', 20109,
> > '295908866', 935519065,
> > '208.151.66.123', 'Async', '').
> >
> > The workaround for this problem is to replace all null variables using a
> > space. Can you do me a favour to tell me how to change the source code
> > for the make it work?
> >
>
> You don't need to change the source code - all you need to do is change the
> AddQuery definition in the SessionDatabase declaration:
>
> <SessionDatabase SQL>
> DBSource ....
> DBUsername ....
> DBAuth ....
> AddQuery .......
> .....
> </SessionDatabase>
>
> See Section 6.6 in the Radiator 2.14.1 reference manual for further details.
>
> > The second problem is that, from the DBA of the Oracle, the Radiator is
> > now using hardcored SQL statement to the session database. It would
> > decrease the efficiency of the Oracle. If the frequency of delete and
> > insert statements are very frequent, it would cause memory leak or even
> > ora error: 4031. The dba suggest to use variable binding of the SQL
> > statement.
> >
> > For the variable binding of SQL statements, it would consist of
> > ora_login, ora_open, & ora_bind. I checked the source code, SessSQL.pm
> > and SqlDb.pm, that they don't exist. How's your suggestion about it? Is
> > that possible you can provide us with the variable binding SQL
> > statements?
> >
>
> Because we use the DBI/DBD libraries, you will not see the Oracle-specific
> routines in the Radiator source code. For the above problem you might be better
> served with a Stored-Procedure inside Oracle, and use the SessionDatabase
> AddQuery statement above to call it.
>
>
--
Cheers,
Anthony Chan
Sun Professional Services Hong Kong
22/F Shui On Centre,
8 Harbour Road, Wanchai,
Hong Kong
Phone: (852) 2820-0674
Fax : (852) 2802-8655
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
