469,331 Members | 4,501 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

DLookup uses twice the SEQUENCE numbers

DFS
FYI,

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

Anyone know why?

Nov 13 '05 #1
3 2283
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
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
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.

Similar topics

4 posts views Thread by Joe | last post: by
5 posts views Thread by Eric E | last post: by
13 posts views Thread by nzyui | last post: by
2 posts views Thread by John | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.