List:General Discussion« Previous MessageNext Message »
From:John Gruber Date:August 29 2002 4:08pm
Subject:Stored Procedureish Advise needed
View as plain text  
Hello gang,

I have an application (radius) that is logging network activity to MySQL.
When a users completes a network session an accounting insert query is made
for that session showing the session length.  The application has the
functionality to SUM all session times for a user and determine if it should
let them connect the next time.  The SUM query will eat us up as the number
of session in the table is very large.  If I had stored procedures (and
triggers would make this so nice!) I would simply add the seconds every time
I got an accounting update. This would reduce my overhead 1000 fold. I can
alter the INSERT query for the application, but the application will only
let me issue ONE query.

What are my options with MySQL 3.x.  I have looked at the User Defined
Functions, but that requires me to write a n exit to C that just calls
embedded SQL to populate another table.  There must be an SQL solution to
this.

I.O.W turn the following into one query for me!

INSERT INTO SOMETABLE (val1,val2,val3) VALUES ('val1','val2','val3')

and (psuedo code.. which makes now sense!)

UPDATE SOMEOTHERTABLE SET (SECONDS = SUM('val1' + SECONDS))

This is trivial to do in a stored procedure for other databases. You would
simply store the value of the current seconds in a variable, add the input
argument to it, and then update the table.

Tell me how to do this in MySQL. Have your application do it or, call a
script doesn't help me much.  This has to be a database thing.  Am I stuck
with UDF or native functions?  The generic examples aren't much help.  Does
anyone have the C/C++ code to define a UDF that can execute an input command
script (i.e. execv?) so I can write this in perl and be able to change it on
the fly?

Thanks in advance...

John Gruber

Thread
Stored Procedureish Advise neededJohn Gruber29 Aug
  • Re: Stored Procedureish Advise neededDan Nelson29 Aug
  • Re: Stored Procedureish Advise neededGelu Gogancea29 Aug