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:37:59 -0000

Shaun,

work - well yes, now I can see why it would - but...

you shouldn't repeatedly call dbinit(), dblogin() dbopen() ... dbclose()
dbexit()
each time you do, you open up a new connection to the database.
This explains why the original function we discussed works.
The connection used in that function is just left hanging (and open)
until your whole program terminates.
The next function opens a brand new connection.
It's an overhead to create/destroy all these connections...

If LatLonDistance is relatively straightforward as a function,
and prev_lat, prev_lon and SweepRadius are known before your "Query3"
query is executed,
then couldn't the functionality of "Query3", i.e.

select a bunch of records
process each record until one satifies the LatLonDistance test
for that matching record, insert a record into another table

be achieved within a stored procedure ?


Again, 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 15:15
To: FreeTDS Development Group
Subject: Re: [freetds] dbnextrow() question


Bill,

Its complicated understanding my code/logic but i think what i have may
work, if it doesnt i may take you up on that phone call! Im based in
Northern Ireland so not too far away from you.

Thompson, Bill D (London) wrote:

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