"sguazt sguazt" <[EMAIL PROTECTED]> wrote on 03/16/2005 11:54:26 AM:
> Hi!
>
> >From: Tom Crimmins <[EMAIL PROTECTED]>
> >To: sguazt sguazt <[EMAIL PROTECTED]>
> >CC: mysql@lists.mysql.com
> >Subject: RE: Queries inside UDF
> >Date: Wed, 16 Mar 2005 10:09:16 -0600
> >
> ...
> >
> >Can you explain exactly what you are using this for? What benefit does
this
> >provide over just executing the query?
> >
> >You can either execute SELECT foobar() or SELECT COUNT(*) FROM
tblfoobar,
> >and
> >you don't have to do anything to make the latter work. Creating the UDF
> >just
> >
> >seems like a lot of extra work. Maybe this is my ignorance, but I don't
see
> >much use for executing a query within a UDF esspecially if you are
using
> >4.1
> >
> >with subqueries.
>
> ok sorry if I submitted a stupid exampe :P
> but I wanted to keep the stuff simple.
> My real case is a little more complicated ...
>
> First of all, unfornately I've to use MySQL 4.0.21.
> The purpose of creating a UDF, in my case, is doing something that in my
> opinion cannot be done in a query.
>
> For example, if you have to implement in SQL the following pseudo-code:
>
> start = <a unix timestamp>;
> duration = 100;
> stop = null;
> increment = 0;
>
> while ( true ) {
> start2 = 0;
> stop2 = 0;
>
> stop = start + ( duration - increment );
>
> rs = doQuery( "SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
....
> WHERE t1.start>=" + start + " AND t1.stop<=" + stop );
> if ( rs == null ) {
> delta = 0;
>
> start2 = rs[0]['start'];
> stop2 = rs[0]['stop'];
>
> if ( start < start2 ) {
> delta += start2 - start;
> }
> increment += delta;
> start = stop2
> }
> else {
> break;
> }
> }
> return stop;
>
> what do you write?
>
> The main problem is that you can establish a-priori when you stop the
main
> while ... :(
> I thought to use a temporary table but the problem remains ...
>
> Furthermore that code produces an info that is to be used by another
(more
> complex) query.
>
> So the only solutions are:
> 1. use a high-level language (C,perl,Java,PHP) and implement the entire
> logic (not only the code above) in that language (but this seems to be
too
> slow ... I did it before exploring the UDF world ;) )
>
> 2. try to write a UDF that implement the code above; so you create a
UDF,
> e.g. named foobar, you can use inside other query: SELECT ...,foobar(
start,
> duration ) AS stop FROM <a very long JOIN> ...
>
> Any idea?
>
> bye!!!
>
> -- Marco
>
> >--
> >Tom Crimmins
> >Interface Specialist
> >Pottawattamie County, Iowa
> >
> >--
I can almost follow the logic of your pseudocode. Can you explain "what"
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
