By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

DLookup uses twice the SEQUENCE numbers

P: n/a
DFS
FYI,

Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers
each time it's called.

Anyone know why?

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
DFS wrote:
FYI,

Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE numbers
each time it's called.

Anyone know why?


Without seeing what "pass-thru query" contains, no, but I would suspect
it's something in there since DLookup is as it's name suggests just
something to look up, it doesn't write to tables.

(comp.databases.oracle dropped as my ISP doesn't carry the group)

--
This sig left intentionally blank
Nov 13 '05 #2

P: n/a
DFS
Trevor Best wrote:
DFS wrote:
FYI,

Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE
numbers each time it's called.

Anyone know why?
Without seeing what "pass-thru query" contains, no, but I would
suspect it's something in there since DLookup is as it's name
suggests just something to look up, it doesn't write to tables.

"Pass-thru query" contains a SQL call to an Oracle nextVal function that
increments a SEQUENCE: SELECT SEQuenceName.nextVal AS ResultsField FROM
DUAL;

If I use a recordset with the same SELECT statement, it uses only one
SEQUENCE number.

It's just a little strange. There's no need to waste half the ID numbers,
so I'm going with the recordset method.

Thanks
(comp.databases.oracle dropped as my ISP doesn't carry the group)

Nov 13 '05 #3

P: n/a
DFS wrote:
Trevor Best wrote:
DFS wrote:
FYI,

Using DLookup("ResultsField","Pass-thru query") consumes 2 SEQUENCE
numbers each time it's called.

Anyone know why?
Without seeing what "pass-thru query" contains, no, but I would
suspect it's something in there since DLookup is as it's name
suggests just something to look up, it doesn't write to tables.


"Pass-thru query" contains a SQL call to an Oracle nextVal function that
increments a SEQUENCE: SELECT SEQuenceName.nextVal AS ResultsField FROM
DUAL;


I know almost nothing about Oracle itself (apart from the bits that are
common to all db engines) so the above is a bit foreign to me and I
don't know how it works.
If I use a recordset with the same SELECT statement, it uses only one
SEQUENCE number.
That's interesting.
It's just a little strange. There's no need to waste half the ID numbers,
so I'm going with the recordset method.


Very interesting (I sound like Captain Jack Sparrow). I just tested
this, did a DLookup (Dlookup("FileName","dbo_tblFile","FileID=5")) on a
SQL Server table while running a trace in Profiler, I got one hit in the
trace.

For my next trick I made a pass-through query, a simple "select Filename
from tblFile where FileId=5" then did a DLookup
(DLookup("FileName","xxx")) (I named the query xxx), having restarted
the trace just to make sure I got two selects on that table (and I'm the
only one logged in). If I open the query there is one select in the
trace so it would appear that DLookup() on a passthrough query will
execute the query twice.

I also tested this with my own tLookup() (see
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) and this
gets just the one select so you could revert back to your original code
and use tLookup() instead :-)

--
This sig left intentionally blank
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.