[freetds] dbnextrow() question

craigs s.craig at andronics.com
Mon Mar 21 09:36:24 EST 2005


unfortunately i cant, using 7.0 which doesnt support UDF's

ZIGLIO, Frediano, VF-IT wrote:

>Or use functions instead...
>
>
>  
>
>>Alas, no.
>>you have to put the result in a  variable first...
>>Bill
>>
>>-----Original Message-----
>>From: freetds-bounces at lists.ibiblio.org
>>[mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of craigs
>>Sent: 21 March 2005 14:18
>>To: FreeTDS Development Group
>>Subject: Re: [freetds] dbnextrow() question
>>
>>
>>Does anyone know if i can call a math function while 
>>executing a stored 
>>procedure
>>
>>for example can i do this
>>
>>EXEC GetMin @x = @my_lon / 2, @y = sqrt( @my_lat ) @ResultOut 
>>= @my_min 
>>OUTPUT
>>
>>Shaun.
>>
>>craigs wrote:
>>
>>    
>>
>>>Cheers Bill,
>>>
>>>thats what i was looking for, probably going to be testing 
>>>      
>>>
>>today so it
>>
>>    
>>
>>>should be interesting!
>>>
>>>Thompson, Bill D (London) wrote:
>>>
>>>      
>>>
>>>>Shaun,
>>>>
>>>>DECLARE @my_x float,
>>>>       @my_y float
>>>>DECLARE @my_min float
>>>>
>>>>SELECT @my_x = ..., @my_y = ...
>>>>
>>>>EXEC GetMin @x = @my_x, @y = @my_y @ResultOut = @my_min OUTPUT
>>>>
>>>>
>>>>Bill
>>>>
>>>>-----Original Message-----
>>>>From: freetds-bounces at lists.ibiblio.org
>>>>[mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of craigs
>>>>Sent: 21 March 2005 13:19
>>>>To: FreeTDS Development Group
>>>>Subject: Re: [freetds] dbnextrow() question
>>>>
>>>>
>>>>Guys, just got back to working on this problem.
>>>>
>>>>Can someone show me how to call a stored procedure(nested) 
>>>>        
>>>>
>>inside my 
>>    
>>
>>>>main stored procedure, searched damn web looking for an 
>>>>        
>>>>
>>example and 
>>    
>>
>>>>having no luck, for example here is the function i want to call 
>>>>inside my main stored procedure.
>>>>
>>>>CREATE  procedure GetMin
>>>>(
>>>>   @x double,
>>>>   @y double,
>>>>   @ResultOut double OUTPUT
>>>>)
>>>>as
>>>>   if ( @x <= @y )
>>>>       Set @ResultOut = @x
>>>>   else
>>>>       Set @ResultOut = @y
>>>>
>>>>Return
>>>>
>>>>can i do the following:
>>>>
>>>>declare @GetMinResult        double
>>>>@GetMinResult = GetMin( 1, sqrt( @intermed_a ))
>>>>
>>>>or do i have to do something like this:
>>>>
>>>>exec GetMin( 1, sqrt( @intermed_a )), @GetMinResult OUTPUT
>>>>
>>>>where @GetMinResult would hold the result of the procedure?
>>>>
>>>>Any help would be much appreciated!
>>>>
>>>>Shaun.
>>>>
>>>>Lowden, James K wrote:
>>>>
>>>> 
>>>>
>>>>        
>>>>
>>>>>>From: craigs
>>>>>>Sent: Monday, March 14, 2005 10:13 AM
>>>>>>
>>>>>>Bill,
>>>>>>
>>>>>>Could you have a look at my first attempt, first time writing 
>>>>>>stored procedures/functions and im sure i've got something wrong.
>>>>>>
>>>>>>CREATE PROC GetPostcodeRecord ( @my_lat float, @my_long float )
>>>>>>AS
>>>>>>BEGIN
>>>>>>    SELECT TOP 1 PlaceName, Town, Country, Latitude, Longitude
>>>>>>    FROM  PostCodes PC
>>>>>>    WHERE LatLonDistance( @my_lat, @my_lon, PC.Latitude,
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>PC.Longitude
>>>>>
>>>>>  
>>>>>
>>>>>          
>>>>>
>>>>>>) < 1
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>"Top 1": Do you want just any row?  You might want an ORDER BY
>>>>>          
>>>>>
>>clause,
>>    
>>
>>>>>but ISTM you're looking for the PlaceName with the minimum
>>>>>          
>>>>>
>>distance....
>>    
>>
>>>>>To answer your UDF question: yes, it's a feature of SQL 
>>>>>          
>>>>>
>>Server 2000.
>>    
>>
>>>>>You can nest stored procedures, and you can get almost the same
>>>>>modularity using OUTPUT parameters.  Just remember to use 
>>>>>          
>>>>>
>>the OUTPUT
>>    
>>
>>>>>  
>>>>>          
>>>>>
>>>>tag
>>>> 
>>>>
>>>>        
>>>>
>>>>>both in the procedure body and when you call it. 
>>>>>IMHO, you're better off with a single procedure, because (lacking
>>>>>functions) the code will be easier to follow than with OUTPUT
>>>>>parameters.  I've attached one that might do the job. 
>>>>>
>>>>>
>>>>>  
>>>>>
>>>>>          
>>>>>
>>>>>>CREATE FUNCTION AsRadians( @x float )
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>There is a built-in PI() function, no? 
>>>>>
>>>>>
>>>>>  
>>>>>
>>>>>          
>>>>>
>>>>>>CREATE FUNCTION Arcsin( @x float )
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>Does the function asin() do what you want? 
>>>>>
>>>>>
>>>>>  
>>>>>
>>>>>          
>>>>>
>>>>>>CREATE FUNCTION LatLonDistance(@latitude float, 
>>>>>>            
>>>>>>
>>@longitude float, 
>>    
>>
>>>>>>@prev_lat float, @prev_lon float)
>>>>>>RETURNS float AS
>>>>>>BEGIN
>>>>>>       declare @radiusEarth integer
>>>>>>  declare @delta_lat    float
>>>>>>  declare    @delta_lon    float
>>>>>>  declare @intermed_a    float
>>>>>>  declare @intermed_c    float
>>>>>>  declare @result        float
>>>>>> 
>>>>>>      set @radiusEarth = 6367
>>>>>> 
>>>>>>  @delta_lon = AsRadians(@prev_lon) - AsRadians(@longitude);
>>>>>>  @delta_lat = AsRadians(@prev_lat) - AsRadians(@latitude) ;
>>>>>> 
>>>>>>  @intermed_a = Sin2(@delta_lat / 2) + cos( AsRadians( 
>>>>>>            
>>>>>>
>>@latitude ))
>>    
>>
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>*
>>>>>
>>>>>  
>>>>>
>>>>>          
>>>>>
>>>>>>cos( AsRadians( @prev_lat )) * Sin2( @delta_lon / 2);
>>>>>>
>>>>>>  @intermed_c = 2 * Arcsin( GetMin( 1, sqrt( @intermed_a )));
>>>>>>
>>>>>>  @result = @radiusEarth * @intermed_c;
>>>>>>
>>>>>>  return @result;
>>>>>> 
>>>>>>END
>>>>>> 
>>>>>>    
>>>>>>            
>>>>>>
>>>>>So here goes.  I'm just trying to restate your query.  I'm sure it
>>>>>          
>>>>>
>>can
>>    
>>
>>>>>be simplified; it may even be wrong. 
>>>>>It may also be quite slow (although still faster than transmitting
>>>>>          
>>>>>
>>the
>>    
>>
>>>>>whole table to the client) because the server has no 
>>>>>          
>>>>>
>>useful index an
>>    
>>
>>>>>must examine every row.  To speed it up, the best thing 
>>>>>          
>>>>>
>>would be to
>>    
>>
>>>>>restrict the number of rows examined.  For example, would 
>>>>>          
>>>>>
>>it be the
>>    
>>
>>>>>  
>>>>>          
>>>>>
>>>>case
>>>> 
>>>>
>>>>        
>>>>
>>>>>that 90% of the time the PlaceName will be located at +/- 1 degree
>>>>>          
>>>>>
>>(or
>>    
>>
>>>>>some other threshold)?  If so, you could restrict the innermost
>>>>>          
>>>>>
>>query
>>    
>>
>>>>>that way, and, if it fails to find one, fall back to scanning the
>>>>>          
>>>>>
>>whole
>>    
>>
>>>>>table. 
>>>>>Another optimization would be to cache the results in a global
>>>>>  
>>>>>          
>>>>>
>>>>temporary
>>>> 
>>>>
>>>>        
>>>>
>>>>>table.  That way, once a PlaceName is determined as the 
>>>>>          
>>>>>
>>solution for
>>a
>>    
>>
>>>>>given latitude/longitude pair, it can be re-retrieved 
>>>>>          
>>>>>
>>without being
>>    
>>
>>>>>recalculated. 
>>>>>HTH. 
>>>>>--jkl
>>>>>
>>>>>
>>>>>CREATE PROC GetPostcodeRecord
>>>>>      @lat float
>>>>>    , @lon float
>>>>>as BEGIN
>>>>>    create table #distances (PlaceName varchar(30), 
>>>>>          
>>>>>
>>Distance float)
>>    
>>
>>>>>    insert #distances
>>>>>    select PlaceName, distance
>>>>>    from ( -- distances
>>>>>        select      PlaceName
>>>>>            , 6367 * 2             * asin(    case    when 1.0 < 
>>>>>sqrt(intermed_a)                         then 1.0 
>>>>>                        else sqrt(intermed_a)
>>>>>          
>>>>>
>>end
>>    
>>
>>>>>              ) as distance
>>>>>        from ( -- intermediate "a"
>>>>>            select    PlaceName
>>>>>                , ( (1 - cos(2 * ((lat_radians -
>>>>>Lat_radians) / 2))) / 2 )
>>>>>                --  |            |^^^^^^^^^^ 1/2 delta
>>>>>^^^^^^^^^^|     |
>>>>>                --  |^^^^^^^^^^^^^ Sin2
>>>>>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|
>>>>>                + cos(Lat_radians)                 * 
>>>>>cos(lat_radians)                 * ( (1 - cos(2 * ((lon_radians -
>>>>>Lon_radians) / 2))) / 2 )
>>>>>                as intermed_a
>>>>>            from (    -- radians
>>>>>                select    PlaceName
>>>>>                    , (PI() / 180) * @lat
>>>>>as lat_radians
>>>>>                    , (PI() / 180) * PC.Latitude
>>>>>as Lat_radians
>>>>>                    , (PI() / 180) * @lon
>>>>>as lon_radians
>>>>>                    , (PI() / 180) * PC.Longitude
>>>>>as Lon_radians
>>>>>                FROM PostCodes as PC
>>>>>            ) as Radians
>>>>>        ) as intermediates
>>>>>    ) as Distances
>>>>>    where distance < 1
>>>>>
>>>>>    SELECT PlaceName, Town, Country, Latitude, Longitude
>>>>>    FROM PostCodes     WHERE PlaceName in (
>>>>>        select min(PlaceName)
>>>>>        from #distances         where Distance = (
>>>>>            select min(Distance)             from #distances
>>>>>        )
>>>>>    )
>>>>>END
>>>>>
>>>>>-----------------------------------------
>>>>>The information contained in this transmission may contain
>>>>>          
>>>>>
>>privileged
>>    
>>
>>>>>  
>>>>>          
>>>>>
>>>>and confidential information and is intended only for the 
>>>>        
>>>>
>>use of the
>>    
>>
>>>>person(s) named above. If you are not the intended recipient, or an
>>>>employee or agent responsible for delivering this message to the
>>>>intended recipient, any review, dissemination, distribution or
>>>>duplication of this communication is strictly prohibited. 
>>>>        
>>>>
>>If you are
>>not
>>    
>>
>>>>the intended recipient, please contact the sender immediately by
>>>>        
>>>>
>>reply
>>    
>>
>>>>e-mail and destroy all copies of the original message. Please note
>>>>        
>>>>
>>that
>>    
>>
>>>>we do not accept account orders and/or instructions by e-mail, and
>>>>therefore will not be responsible for carrying out such 
>>>>        
>>>>
>>orders and/or
>>    
>>
>>>>instructions.
>>>> 
>>>>
>>>>        
>>>>
>>>>>If you, as the intended recipient of this message, the purpose of
>>>>>          
>>>>>
>>which
>>    
>>
>>>>>  
>>>>>          
>>>>>
>>>>is to inform and update our clients, prospects and consultants of
>>>>developments relating to our services and products, would 
>>>>        
>>>>
>>not like to
>>    
>>
>>>>receive further e-mail correspondence from the sender, 
>>>>        
>>>>
>>please "reply"
>>to
>>    
>>
>>>>the sender indicating your wishes.  In the U.S.: 1345 Avenue of the
>>>>Americas, New York, NY 10105.
>>>> 
>>>>
>>>>        
>>>>
>>>>>_______________________________________________
>>>>>FreeTDS mailing list
>>>>>FreeTDS at lists.ibiblio.org
>>>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>>
>>>>>
>>>>>  
>>>>>          
>>>>>
>>>>_______________________________________________
>>>>FreeTDS mailing list
>>>>FreeTDS at lists.ibiblio.org
>>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>--------------------------------------------------------
>>>>
>>>>If you are not an intended recipient of this e-mail, please notify 
>>>>the sender, delete it and do not read, act upon, print, disclose, 
>>>>copy, retain or redistribute it. Click here for important 
>>>>        
>>>>
>>additional 
>>    
>>
>>>>terms relating to this e-mail.     http://www.ml.com/email_terms/
>>>>--------------------------------------------------------
>>>>_______________________________________________
>>>>FreeTDS mailing list
>>>>FreeTDS at lists.ibiblio.org
>>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>> 
>>>>
>>>>        
>>>>
>>>_______________________________________________
>>>FreeTDS mailing list
>>>FreeTDS at lists.ibiblio.org
>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>      
>>>
>>_______________________________________________
>>FreeTDS mailing list
>>FreeTDS at lists.ibiblio.org
>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>--------------------------------------------------------
>>
>>If you are not an intended recipient of this e-mail, please 
>>notify the sender, delete it and do not read, act upon, 
>>print, disclose, copy, retain or redistribute it. Click here 
>>for important additional terms relating to this e-mail.     
>>http://www.ml.com/email_terms/
>>--------------------------------------------------------
>>_______________________________________________
>>FreeTDS mailing list
>>FreeTDS at lists.ibiblio.org
>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>    
>>
>_______________________________________________
>FreeTDS mailing list
>FreeTDS at lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>  
>




More information about the FreeTDS mailing list