Select from open view using DBF()

Discussion:

(too old to reply)

Ron Weldy

2005-01-28 19:52:26 UTC

I am trying to SELECT from an open view using the DBF() function and it
errors saying 'table does not exist'. Interestingly enough, when I look for
the tmp file name that DBF() returns, I can't find it either. Anyone know
what's going on or how to do this?

Josh Assing

2005-01-28 21:00:56 UTC

Permalink

what is your statement?

Post by Ron Weldy
I am trying to SELECT from an open view using the DBF() function and it
errors saying 'table does not exist'. Interestingly enough, when I look for
the tmp file name that DBF() returns, I can't find it either. Anyone know
what's going on or how to do this?

---
Remove x's to send.

Ron Weldy

2005-01-28 21:24:00 UTC

Permalink

I simplified it while testing with the view open and executed it in the
command window using:

SELECT * from DBF("v_myview")

A straight DBF("v_myview") will return a file name of c:\temp\J2GO003U.TMP,
but this file does not exist when looking at the temp folder. I also did a
search using windows explorer but could not find the file.

Post by Josh Assing
what is your statement?

---
Remove x's to send.

Josh Assing

2005-01-29 05:46:42 UTC

Permalink

Can you provide code that replicates it?

I just created an alias -- and did a select from dbf("MyAlias")

the file it refers to does not exist (no big suprise here) -- and the select
worked fine...

What VFP? an d code to replicate it would be handy... (Create cursor)
becuase my steps worked w/o error.

Post by Ron Weldy
I simplified it while testing with the view open and executed it in the
SELECT * from DBF("v_myview")
A straight DBF("v_myview") will return a file name of c:\temp\J2GO003U.TMP,
but this file does not exist when looking at the temp folder. I also did a
search using windows explorer but could not find the file.

Post by Josh Assing
what is your statement?

---
Remove x's to send.

Ron Weldy

2005-01-29 06:40:41 UTC

Permalink

Hmmm... but that's an alias. You are not using a view out of a database.
Maybe that's the difference. Or it could be that I have set the TMPFILES
term in my config.fpw like this:
TMPFILES=C:\TEMP

So you created an alias like this?

USE mytable ALIAS myalias

If so, dbf() should have returned your table name...

At any rate as I mentioned I'm using a parameterized view in a database. For
example let's call it myview. I suppose I could write some code to create
one but if you have a database with a parameterized view, set your variable
and use it like this:

lctheparameter = "parameter"
USE mydatabase!v_myview
SELECT * from DBF("v_myview")

This will bring up the open dialog when I execute this code.

Now, you mentioned Create Cursor and that inspired me to write this, which
does replicate the error:

CREATE CURSOR mytable (somefield c(10))
INSERT INTO mytable (somefield) VALUES ("test1")
SELECT * from DBF("mytable")

So you may be asking "Why does this dude want to select from the temp file,
why not just write

SELECT * from v_myview

and call it good?"

Let's say you buffer the view, and changes get made but they are not updated
using TABLEUPDATE. If you SELECT from the view name like I have above, you
will not see any un-saved changes as VFP appears to do a USE AGAIN and
selects from a new instance of the view. In fact, I think this is documented
in the KB and this method is the suggested workaround. I'll be darned if I
can find that article though...

I have worked around it by using copy to array and other commands, but it
would be easier and much more powerful if you could SELECT from a open,
buffered view. I suppose I could always report it if I go to the trouble of
coding it... unless they fixed this in 9.0 or there is some command or
method I don't know about. I'm using 8.0, btw.

Thanks for the chat!

- Ron

Post by Josh Assing
Can you provide code that replicates it?
I just created an alias -- and did a select from dbf("MyAlias")
the file it refers to does not exist (no big suprise here) -- and the select
worked fine...
What VFP? an d code to replicate it would be handy... (Create cursor)
becuase my steps worked w/o error.

Post by Ron Weldy
I simplified it while testing with the view open and executed it in the
SELECT * from DBF("v_myview")
A straight DBF("v_myview") will return a file name of
c:\temp\J2GO003U.TMP,
but this file does not exist when looking at the temp folder. I also did a
search using windows explorer but could not find the file.

Post by Josh Assing
what is your statement?

---
Remove x's to send.

Josh Assing

2005-01-29 17:14:51 UTC

Permalink

Sorry I missed the p-view stuff.

anyway; here's what I did:

I took my (commercially available zipcode database -- available at
http://zipcode.jassing.com -- Shamless self plug).

I created a parameterized view for the zipcode portionof the data.
The parameter was called "pSTATE" and the parameterized view was based on
state="?pView" , I called this "pZIPCODE"

I then did a "use pZIPCODE" to validate the parameterized view....
VFP asked for the paremter -- I entered WA and I was presented with the list of
zipcodes for the state of Washington.

Now -- I have the view "pZIPCODE" open.

I did what you are doing, which I suspect would fail - it did.
select * from dbf("pZIPCODE")

This does not fail:

select * from pZIPCODE

I tested this in 6,7,8,9

in a nutshell -- dump the dbf() part of it.

Post by Ron Weldy
Hmmm... but that's an alias. You are not using a view out of a database.
Maybe that's the difference. Or it could be that I have set the TMPFILES
TMPFILES=C:\TEMP
So you created an alias like this?
USE mytable ALIAS myalias
If so, dbf() should have returned your table name...
At any rate as I mentioned I'm using a parameterized view in a database. For
example let's call it myview. I suppose I could write some code to create
one but if you have a database with a parameterized view, set your variable
lctheparameter = "parameter"
USE mydatabase!v_myview
SELECT * from DBF("v_myview")
This will bring up the open dialog when I execute this code.
Now, you mentioned Create Cursor and that inspired me to write this, which
CREATE CURSOR mytable (somefield c(10))
INSERT INTO mytable (somefield) VALUES ("test1")
SELECT * from DBF("mytable")
So you may be asking "Why does this dude want to select from the temp file,
why not just write
SELECT * from v_myview
and call it good?"
Let's say you buffer the view, and changes get made but they are not updated
using TABLEUPDATE. If you SELECT from the view name like I have above, you
will not see any un-saved changes as VFP appears to do a USE AGAIN and
selects from a new instance of the view. In fact, I think this is documented
in the KB and this method is the suggested workaround. I'll be darned if I
can find that article though...
I have worked around it by using copy to array and other commands, but it
would be easier and much more powerful if you could SELECT from a open,
buffered view. I suppose I could always report it if I go to the trouble of
coding it... unless they fixed this in 9.0 or there is some command or
method I don't know about. I'm using 8.0, btw.
Thanks for the chat!
- Ron

Post by Ron Weldy
I simplified it while testing with the view open and executed it in the
SELECT * from DBF("v_myview")
A straight DBF("v_myview") will return a file name of
c:\temp\J2GO003U.TMP,
but this file does not exist when looking at the temp folder. I also did a
search using windows explorer but could not find the file.

Post by Josh Assing
what is your statement?

---
Remove x's to send.

Ron Weldy

2005-01-30 02:29:34 UTC

Permalink

Yup. Doesn't work. But it would be nice if it did so that you could select
from the buffered data in the open view. If you do as you mention on an open
buffered view, it will not return any data that has been changed, and that's
the problem.

So dropping the dbf() will not solve the problem as we need to get at the
buffered data.

Post by Josh Assing
Sorry I missed the p-view stuff.
I took my (commercially available zipcode database -- available at
http://zipcode.jassing.com -- Shamless self plug).
I created a parameterized view for the zipcode portionof the data.
The parameter was called "pSTATE" and the parameterized view was based on
state="?pView" , I called this "pZIPCODE"
I then did a "use pZIPCODE" to validate the parameterized view....
VFP asked for the paremter -- I entered WA and I was presented with the list of
zipcodes for the state of Washington.
Now -- I have the view "pZIPCODE" open.
I did what you are doing, which I suspect would fail - it did.
select * from dbf("pZIPCODE")
select * from pZIPCODE
I tested this in 6,7,8,9
in a nutshell -- dump the dbf() part of it.

Anders Altberg

2005-01-31 13:44:06 UTC

Permalink

Hi Ron
VFP9 allows you to query for buffered data using the new clause: WITH
(buffering = lExpr), on a column by column basis.
-Anders

Post by Ron Weldy
Yup. Doesn't work. But it would be nice if it did so that you could select
from the buffered data in the open view. If you do as you mention on an open
buffered view, it will not return any data that has been changed, and that's
the problem.

Zahid Maqsood

2005-02-07 11:50:34 UTC

Permalink

Hi everybody,
try using alias('aliasname') rather to dbf()

sele * from alias('myview')

i hope it works

Post by Anders Altberg
Hi Ron
VFP9 allows you to query for buffered data using the new clause: WITH
(buffering = lExpr), on a column by column basis.
-Anders

Post by Ron Weldy
Yup. Doesn't work. But it would be nice if it did so that you could select
from the buffered data in the open view. If you do as you mention on an

open

Post by Ron Weldy
buffered view, it will not return any data that has been changed, and

that's

Post by Ron Weldy
the problem.

Michel Lévy

2005-02-07 12:28:49 UTC

Permalink

Ron,

this works for me :
select my_view.nom, my_view.prenom from ("my_view") where....
--
http://www.atoutfox.org

AtoutFoxement,
Michel Lévy
Communauté Francophone des Professionnels FoxPro
--

Post by Ron Weldy
Yup. Doesn't work. But it would be nice if it did so that you could select
from the buffered data in the open view. If you do as you mention on an
open buffered view, it will not return any data that has been changed, and
that's the problem.
So dropping the dbf() will not solve the problem as we need to get at the
buffered data.

Wolfgang Schmale

2005-02-15 17:06:29 UTC

Permalink

Hi Ron!
As long as VFP has enough RAM to access it will create the cursor in memory
instead creating a physically file.
If you select * from alreadyOpenDBF where some_condition VFP won't create a
cursor but use the AlreadyOpenDbf again and sets an appropiate filter
--
------------------------------

Mit freundlichen Güßen aus der Zigarrenstadt

Wolfgang Schmale

------------------------------------------------