Skip to Content.
Sympa Menu

freetds - RE: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] dbnextrow() question
  • Date: Thu, 10 Mar 2005 15:05:05 -0000

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/
--------------------------------------------------------





Archive powered by MHonArc 2.6.24.

Top of Page