I'll check this. Thanks for the report!
Which sqlbill version have you tested?
----- Original Message -----
From: "Farid Adigamov" <farid.adigamov@xxxxxxxxx>
Sent: Saturday, November 12, 2005 7:32 PM
1. The SQL constraint ( callid foreign key ).
I have to drop the constraint for table VOIPCALLTERMTARIFF, cause the
sqltrace.log of RADIUS have these rows:
Sun Nov 13 00:35:06 2005 : Error: rlm_sql (sql): failed after re-connect
Sun Nov 13 00:35:07 2005 : Error: rlm_sql (sql): Couldn't insert SQL
accounting START record - ERROR: insert or update on table
"voipcalltermtariff" violates foreign key constraint
"voipcalltermtariff_call_exists" DETAIL: Key (callid)=(381) is not
present in table "voipcall". CONTEXT: SQL statement "INSERT INTO
voipcalltermtariff (callid, accountid, h323id, terminatingip, cost,
price, currencysym, initialincrement, regularincrement, graceperiod,
tariffdesc) VALUES ( $1 , $2 , $3 , $4 , 0, $5 , $6 , $7 , $8 ,
$9 , $10 )" PL/pgSQL function "voipcall_match_tariff" line 52 at SQL
statement
Sun Nov 13 00:35:07 2005 : Error: rlm_sql (sql): failed after re-connect
Sun Nov 13 00:35:07 2005 : Error: rlm_sql (sql): Couldn't update SQL
accounting START record - ERROR: function throwex() does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Sun Nov 13 00:35:10 2005 : Error: rlm_sql: Stop packet with zero
session length. (user 'voip1', nas 'Y.Y.Y.Y')
Sun Nov 13 00:35:11 2005 : Error: rlm_sql (sql): failed after re-connect
Sun Nov 13 00:35:11 2005 : Error: rlm_sql (sql): Couldn't insert SQL
accounting STOP record - ERROR: insert or update on table
"voipcalltermtariff" violates foreign key constraint
"voipcalltermtariff_call_exists" DETAIL: Key (callid)=(383) is not
present in table "voipcall". CONTEXT: SQL statement "INSERT INTO
voipcalltermtariff (callid, accountid, h323id, terminatingip, cost,
price, currencysym, initialincrement, regularincrement, graceperiod,
tariffdesc) VALUES ( $1 , $2 , $3 , $4 , 0, $5 , $6 , $7 , $8 ,
$9 , $10 )" PL/pgSQL function "voipcall_match_tariff" line 52 at SQL
statement
the INSERT to voipcalltermtariff is executed by voipcall's trigger
from function voipcall_match_tariff(). At the other hand we have
constraint on table voipcalltermtariff, which prevents inserting rows
with value CALLID not existed in table voipcall(foreign key).
So, the INSERT is failed, cause the trigger fires up BEFORE INSERT.
I simply drop it - and all is ok, i have data in voipcall and
voipcalltermtariff tables.
May the best way to solve this is moving INSERT to other trigger which
will be fired AFTER INSERT?
2. The Framed-IP-Address.
A spent a lot of time trying to set up the billing. The billing system
didn't recognized the calling or called IP addresses in any cases.
Than i look up to postgresql.conf ( for RADIUS ), and delete the colon
in %{Framed-IP-Address:}:
accounting_start_query = "INSERT INTO ${acct_table1} \
(id, h323id, acctsessionid, h323confid, gkip, gkid, \
callingstationip, callingstationid, calledstationip, calledstationid, \
setuptime, \
acctstarttime, acctstartdelay, acctupdatetime \
) \
VALUES \
(DEFAULT, '%{SQL-User-Name}', '%{Acct-Session-Id}', '%{h323-conf-id}', \
'%{NAS-IP-Address}', '%{NAS-Identifier}', \
COALESCE(NULLIF('%{Framed-IP-Address}', ''),
radius_get_ciscoavpair('%Z', 'h323-gw-address'), '127.0.0.1')::INET, \
'%{Calling-Station-Id}', \
'%{h323-remote-address:-127.0.0.1}', '%{Called-Station-Id}', \
NULLIF('%{h323-setup-time}', '')::TIMESTAMP(0) WITH TIME ZONE, \
(now() - '%{Acct-Delay-Time:-0}'::INTERVAL), '%{Acct-Delay-Time:-0}', \
(now() - '%{Acct-Delay-Time:-0}'::INTERVAL) \
)"
So the recognizing is well - all IP's are set right in voipcall's records.
Sorry for my english.
/Farid
--
,,, ^ .. ^ ,,,
-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________________
Posting: mailto:Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549
Unsubscribe: http://lists.sourceforge.net/lists/listinfo/openh323gk-users
Homepage: http://www.gnugk.org/
