FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

shaun,

well, I don't really know where to start...
you're in the UK, right? would you like me to give you a call ?

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
Sent: 10 March 2005 14:36
To: FreeTDS Development Group
Subject: Re: [freetds] dbnextrow() question


ok i did an insert query after the query which had 'return NO_MORE_ROWS'

and i had no problems with it, so i think i may be in the clear with the

code i have!

craigs wrote:

> rite here goes!
>
> i declare dbconn, login in a header file so any program/functions can 
> use it.  I call all my freetds querys and inserts from functions, an 
> example of a function being:
>
> InsertRecord() // nearly same structure for a query
> {
>    dbinit()
>    dblogin()
>    DBSETLUSER(login, UID)
>    DBSETLPWD(login, PWD)
>    dbopen(login, DBSERVER)
>    dbuse(dbconn, DBNAME)
>    dbfcmd(dbconn, DBSQLQuery)
>    dbsqlexec(dbconn)
>    dbresults(dbconn)
>    dbbind(variables...)
>    dbnextrow(dbconn)
>    dbfreebuf(dbconn);
>    dbclose(dbconn);
>    dbexit();
> }
>
> each function goes through this process and frees dbconn at dbexit so 
> next function can use it, so thats whats worring me, will dbconn be 
> freed after 'return NO_MORE_ROWS' for the next function to use.  This 
> particular function, (one below) is called in main() in between other 
> functions so the program has a bit more querying and inserting to do 
> before it exits. eg.
>
> main()
> {
>       Query1()
>       Query2()
>       Query3() contains 'return NO_MORE_ROWS'
>       Query4()
>       InsertRecord()
>       return 0; //exit
> }
>
> How complex is the calculation in LatLonDistance ?
> where have prev_lat, prev_lon and SweepRadius come from ?
>
> Dont worry bout this as these variables are globally set from previous

> querys, LatLonDistance is just a function to tell the distance between

> two latitude, two longitude variables, this isnt important to the 
> problem.
>
> Im going to test the connection to dbconn now by doing another insert 
> after i call the function that contains the 'return NO_MORE_ROWS' and 
> see what happens, that may shed some more light on the situation!
>
> Shaun.
>
>
> Thompson, Bill D (London) wrote:
>
>> Shaun,
>>
>>  
>>
>>> what i mean is i dont get a chance to call the following functions 
>>> after the 'return NO_MORE_ROWS'
>>> dbfreebuf(dbconn);
>>> dbclose(dbconn);
>>> dbexit();
>>>   
>>
>>
>> So. this implies that you are all done after finding your single
match.
>> does that mean your program terminates ? or does it keep going,
leaving
>> the connection bound to "dbconn" hanging around ?
>>
>> If the program is really finished at this point, then the server
would
>> clean up the connection from it's point of view.
>> After all, that would be just like a process abending mid-stream.
>> Inelegant, but I can't see any real issues with it.
>>
>> If your program isn't finished at this point, then  you can't
possibly
>> be doing anything else on the connection bound to "dbconn", unless
you
>> run a dbcancel() or dbcanquery() first. So I guess it would just hang
>> around until the program eventually terminates.
>> Again, from the server's point of view, this would just look like an
>> unexpected process termination, and it should clean up.
>>
>> I take it that your "InsertRecord" function uses a separate
connection
>> to the one bound to "dbconn".
>> You would definitely run into the problems previously discussed
>> otherwise.
>>
>>  
>>
>>> I just want to be sure that for some reason all the records in my 
>>> table aren't being searched when i exit as this would slow my 
>>> programs down.
>>>   
>>
>>
>> Well, I suppose a dbcanquery() *would* read through all the records, 
>> so in the context of your design it would be a bad thing to call it.
>> That doesn't mean I think this is a good program design, though...
>> Do you potentially return 2.4 million records in this resultset ? If 
>> so, you should really think again about how to achieve your ends.
>>
>> How complex is the calculation in LatLonDistance ?
>> where have prev_lat, prev_lon and SweepRadius come from ?
>>
>> HTH,
>>
>> Bill
>>
>>
>>
>> -----Original Message-----
>> From: freetds-bounces AT lists.ibiblio.org
>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
>> Sent: 10 March 2005 13:10
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] dbnextrow() question
>>
>>
>> Bill,
>>
>> 'return NO_MORE_ROWS' does take me out of the code segment which is 
>> exactly what i want, but is it safe to do so.  I know that the insert

>> just before i call the return statement also works.  I just want to 
>> be sure that for some reason all the records in my table aren't being

>> searched when i exit as this would slow my programs down, what i mean
is
>>
>> i dont get a chance to call the following functions after the 'return

>> NO_MORE_ROWS'
>>
>> dbfreebuf(dbconn);
>> dbclose(dbconn);
>> dbexit();
>>
>> Would this have any effect on my number of licences seen as its not 
>> closing the connection, any other side-effects?
>>
>> Shaun.
>>
>> Thompson, Bill D (London) wrote:
>>
>>  
>>
>>> Shaun,
>>>
>>> that "return NO_MORE_ROWS" is going to leap right out of this entire
>>> code segment, n'est-ce pas ?
>>> ..in which case you'll only call the dbcanquery() after dbnextrow()
has
>>> returned NO_MORE_ROWS
>>> ..at which point there's no point in calling dbcanquery()
>>>
>>> did you mean to code a "break" instead of the "return NO_MORE_ROWS"
?
>>>
>>> Also, your table has 2.4 million rows.
>>> does that mean the query behind this dbresults loop is returning 2.4
>>> million rows ?
>>> if so, you should try and cut that number down at source, via your
>>> query.
>>> ..unless you have a nice fat network :-)
>>>
>>> Bill
>>>
>>> -----Original Message-----
>>> From: freetds-bounces AT lists.ibiblio.org
>>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
>>> Sent: 10 March 2005 12:03
>>> To: FreeTDS Development Group
>>> Subject: Re: [freetds] dbnextrow() question
>>>
>>>
>>> Right having problems with this, i want to exit query when i get
what i
>>>   
>>
>>
>>  
>>
>>> want, theres over 2.4 millions records in my table so really need to
>>>   
>>
>> get
>>  
>>
>>> this right, can someone advise me if the following code would work.
>>>
>>> while ((return_code = dbresults(dbconn)) != NO_MORE_RESULTS)
>>> {
>>>            if (return_code == SUCCEED)
>>>            {
>>>                       /* Bind results to program variables. */
>>>                       dbbind(dbconn, 1, NTBSTRINGBIND, 0, (BYTE *)& 
>>> PlaceName);
>>>                       dbbind(dbconn, 2, FLT8BIND, 0,
>>> (BYTE*)&Longitude);
>>>                       dbbind(dbconn, 3, FLT8BIND, 0,
>>> (BYTE*)&Latitude);
>>>                       dbbind(dbconn, 4, NTBSTRINGBIND, 0, (BYTE *)& 
>>> Country);
>>>                       dbbind(dbconn, 5, NTBSTRINGBIND, 0, (BYTE *)&
>>> Town);
>>>                       dbbind(dbconn, 6, INTBIND, (DBINT)0, (BYTE *)&

>>> SweepRadius);
>>>                                /* Retrieve and print the result 
>>> rows. */
>>>                       while (dbnextrow(dbconn) != NO_MORE_ROWS)
>>>                       {
>>>                                   tempDist = LatLonDistance(
>>>   
>>
>> prev_lat,
>>  
>>
>>> prev_lon, Latitude, Longitude );
>>>                                                tempDist = tempDist *

>>> 1000; //convert kilometers to meters
>>>                                                if ( tempDist <= 
>>> SweepRadius )
>>>                                   {
>>>                                           /* Find what i'm looking
>>>   
>>
>> for
>>  
>>
>>> so i call a function to Insert into a table using variables above.
*/
>>>                                           InsertRecord(roadsquery);
>>>                                                           return 
>>> NO_MORE_ROWS;
>>>                                   }
>>>                       }
>>>                       dbcanquery(dbconn);
>>>            }
>>> }
>>>
>>> Any help would be much appreciated, thanks.
>>>
>>> Shaun.
>>>
>>> craigs wrote:
>>>
>>>
>>>
>>>   
>>>
>>>> can i do the following using dbcanquery()
>>>>
>>>> while (dbnextrow(dbconn) != NO_MORE_ROWS)
>>>> {
>>>>   bind results to varialbes
>>>>
>>>>        if ( find what i want )
>>>>        {
>>>>             do something here
>>>>              now exit doing the following
>>>>
>>>>           dbcanquery(dbconn);
>>>>        }
>>>> }
>>>>
>>>> will this let me exit the loop correctly with the variables still
set?
>>>>
>>>>
>>>>
>>>> Thompson, Bill D (London) wrote:
>>>>
>>>>  
>>>>     
>>>>
>>>>> Easy, cowboy.
>>>>>
>>>>> well, *might* is right , if you'll excuse the pun - as there is
one
>>>>> circumstance when it wouldn't go wrong.
>>>>>
>>>>> On the other hand, I *might* not have had the time to go into all
the
>>>>> gory details :-)
>>>>>
>>>>> Yours non-specifically,
>>>>>
>>>>> Bill
>>>>>
>>>>> -----Original Message-----
>>>>> From: freetds-bounces AT lists.ibiblio.org
>>>>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Lowden,
James
>>>>>    
>>>>>       
>>>>
>>> K
>>>
>>>
>>>   
>>>
>>>>> Sent: 08 March 2005 17:57
>>>>> To: ml AT freetds.org
>>>>> Subject: RE: [freetds] dbnextrow() question
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>    
>>>>>       
>>>>>
>>>>>> From: Thompson, Bill D (London)
>>>>>> Sent: Tuesday, March 08, 2005 6:01 AM
>>>>>>
>>>>>> your next call to dblibrary *might* fail with a "results pending"
>>>>>>      
>>>>>>         
>>>>>
>>> type
>>>
>>>
>>>   
>>>
>>>>>> error though, if you tray and do another operation on the same
>>>>>>      
>>>>>>         
>>>>>
>>> channel
>>>
>>>
>>>   
>>>
>>>>>>
>>>>>>      
>>>>>>         
>>>>>
>>>>> What "might"?  *Will* fail, period. The code below doesn't tell 
>>>>> the server to stop sending rows, and
>>>>>    
>>>>>       
>>>>
>>> doesn't
>>>
>>>
>>>   
>>>
>>>>> tell the library the that the result set was exhausted/cancelled.
>>>>> db-lib maintains information about the state of the connection,
and
>>>>>    
>>>>>       
>>>>
>>> will
>>>
>>>
>>>   
>>>
>>>>> refuse to send another query.  Anything else is a bug. The only 
>>>>> exception would be if the last row fetched happened also to
>>>>>    
>>>>>       
>>>>
>>> be
>>>
>>>
>>>   
>>>
>>>>> the last row sent, i.e., the last row of the result set.  Some
>>>>> implementations of db-lib (not FreeTDS, afaik), will note the
>>>>> end-of-rowset packet and update the internal connection status
>>>>> automatically.  Others will not do so until dbnextrow returns
>>>>> NO_MORE_ROWS. Am I missing something? --jkl
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>    
>>>>>       
>>>>>
>>>>>> -----Original Message-----
>>>>>> From: freetds-bounces AT lists.ibiblio.org
>>>>>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
>>>>>> Sent: 08 March 2005 10:58
>>>>>> To: FreeTDS Development Group
>>>>>> Subject: Re: [freetds] dbnextrow() question
>>>>>>
>>>>>>
>>>>>> found an easier way of doin it.
>>>>>>
>>>>>> while (dbnextrow(dbconn) != NO_MORE_ROWS)
>>>>>> {
>>>>>>    if ( find what i want )
>>>>>>    {
>>>>>>          do something here
>>>>>>          now exit doing the following
>>>>>>
>>>>>>       return NO_MORE_ROWS; // this will let you exit
>>>>>>    }
>>>>>> }
>>>>>>
>>>>>>
>>>>>> Thompson, Bill D (London) wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>      
>>>>>>         
>>>>>>
>>>>>>> Shaun
>>>>>>>
>>>>>>> see dbcanquery() or dbcancel() . dbcanquery looks like exactly
what
>>>>>>>          
>>>>>>>           
>>>>>>
>>>>> you
>>>>>
>>>>>
>>>>>    
>>>>>       
>>>>>
>>>>>>> want.
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: freetds-bounces AT lists.ibiblio.org
>>>>>>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
>>>>>>> Sent: 04 March 2005 12:19
>>>>>>> To: FreeTDS Development Group
>>>>>>> Subject: [freetds] dbnextrow() question
>>>>>>>
>>>>>>>
>>>>>>> Could someone tell me how to exit this loop?  I dont want to run
>>>>>>>          
>>>>>>>           
>>>>>>
>>>>>> through
>>>>>>
>>>>>>
>>>>>>      
>>>>>>         
>>>>>>
>>>>>>> thousands of unnecessary records when i find the one i want.
>>>>>>>
>>>>>>> while (dbnextrow(dbconn) != NO_MORE_ROWS)
>>>>>>> {
>>>>>>>   if ( find what i want )
>>>>>>>   {
>>>>>>>         do something here
>>>>>>>         now i want to exit out off this loop
>>>>>>>   }
>>>>>>> }
>>>>>>>
>>>>>>> Shaun
>>>>>>>          
>>>>>>>           
>>>>>>
>>>>> -----------------------------------------
>>>>> 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 
>>>>> --------------------------------------------------------
>>>>>
>>>>> 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 
>> --------------------------------------------------------
>>
>> 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/ 
--------------------------------------------------------