Thanks for answering!
Your solution would be right if I can modify the database (and I have no control on software that populate tables).
Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date).
An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-|
So the only possible ways are:
* do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (<Send Queries> + <Receive Results>) * N (where N >> 1 in general);
* do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: <Send Query One Time> + <Receive Result>
When I tried UDF I'm said .... WOW this could be the solution to my problems!!!!
Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.:
SELECT foobar( ... ) FROM ...;
I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object.
I this is true ... what a pity! .... I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see.
-- Marco
You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;)
a way to extend udf, is ... stored procedure ?
-- Philippe Poelvoorde COS Trading Ltd.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
