Application closes my temporary table Automatically.

Discussion:

(too old to reply)

Juan Alonso

2004-08-17 20:42:29 UTC

Hello.

I have an application I am building in VFP8.

The problem I am having is that I use two tables. One is just for
information, the other is to put the information into.

The first table is created with a SQL - VIEW command. The second is created
from a blank stucture.

The second table seems to be re-created automatically when ever I run the
SQL - VIEW to get more info to put in.

I use a variable called: "LISINVOICE" and "!USED('INVENT') to make sure the
I am creating an invoice and that the table does not exsist.

Well in my Debug window I typed in the WATCH window:

used('invent') = .t.
!used('invent') = .f.
reccount('invent') = 0

The above display is what I start with. Then "reccount" turns to 1, but
when I run the SQL - VIEW again and select an item, instead of adding it to
the new temporary table it re-creates the table from scratch and puts only
the last record I selected in the table.

Any ideas why?

I have searched everywhere for possible forgotten codes and I have nothing
that even calls the Work Environment nor the Table Name or Alias.

I am tired of tampering with this. Can anyone help me here.????

Thank you in advance,

John

Lee Mitchell

2004-08-17 21:41:31 UTC

Permalink

Hi John:

This is behaving as expected. You are correct in your observation that the
table is overwritten with the SQL-VIEW command. The table is recreated and
populated with fresh data. The CREATE SQL VIEW does not have an 'additive'
clause.

I would send the results of the view to a cursor instead of a table and
then copy the results to your table.

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

Post by Juan Alonso
Hello.
I have an application I am building in VFP8.
The problem I am having is that I use two tables. One is just for
information, the other is to put the information into.
The first table is created with a SQL - VIEW command. The second is created
from a blank stucture.
The second table seems to be re-created automatically when ever I run the
SQL - VIEW to get more info to put in.
I use a variable called: "LISINVOICE" and "!USED('INVENT') to make sure the
I am creating an invoice and that the table does not exsist.
used('invent') = .t.
!used('invent') = .f.
reccount('invent') = 0
The above display is what I start with. Then "reccount" turns to 1, but
when I run the SQL - VIEW again and select an item, instead of adding it to
the new temporary table it re-creates the table from scratch and puts only
the last record I selected in the table.
Any ideas why?
I have searched everywhere for possible forgotten codes and I have nothing
that even calls the Work Environment nor the Table Name or Alias.
I am tired of tampering with this. Can anyone help me here.????
Thank you in advance,
John

Juan Alonso

2004-08-18 18:18:05 UTC

Permalink

Thank you Lee. But I don't think I explaned myself well.

Let me take this on steps so that I don't lose the real problem in the
shuffle.

Here is my situation.

Say I use the CREATE SQL VIEW to create the remote view.

Ok. now after I have the SQL-VIEW say the cursor is called CURSOR-X, I do
the following.

*** Example of what I am doing in code ***
Select CURSOR-X
COPY STRUCTURE TO TEMPTABLE
USE TEMPTABLE IN SELECT(1) SHARED
*** End of Example

Now, I RE-RUN my CREATE SQL VIEW and create CURSOR-X "AGAIN" with NEW DATA.

I do not RE-DO THE ABOVE EXAMPLE. I browse CURSOR-X exit the browse which
leaves the record pointer on the record I want to capture.

Now, I do this:

*** Example of what I am doing in code ***
Scatter Memvar && Creates Memory Variables with the same Names as the Table
Fields.
Select TEMPTABLE
APPEND BLANK
Gather Memvar && Fills the blank record with the data captured by the
SCATTER COMMAND.
*** End of Example

Now, I repeat the same thing, but here comes the problem.
The TEMPTABLE only holds the last appended data I selected from the CREATE
SQL VIEW data.

I do understand that CREATE SQL VIEW will create a new table each time I run
but what does my TEMPTABLE has anything
to do with CURSOR-X???

I first did the COPY STRUCTURE COMMAND inside forms I was using so I thought
that had issues so I took it out and do it programatically now.

I still get the same problem.

Sorry if my last explanation was not clear I sometimes get ahead of my
thinking...LOL :-)

Well, hope anyone can help cuz this is new to me....

Thank you,

John.

Post by Lee Mitchell
This is behaving as expected. You are correct in your observation that the
table is overwritten with the SQL-VIEW command. The table is recreated and
populated with fresh data. The CREATE SQL VIEW does not have an 'additive'
clause.
I would send the results of the view to a cursor instead of a table and
then copy the results to your table.
I hope this helps.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell
*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/
*-- VFP8 HAS ARRIVED!! --*
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

created

Post by Juan Alonso
from a blank stucture.
The second table seems to be re-created automatically when ever I run the
SQL - VIEW to get more info to put in.
I use a variable called: "LISINVOICE" and "!USED('INVENT') to make sure the
I am creating an invoice and that the table does not exsist.
used('invent') = .t.
!used('invent') = .f.
reccount('invent') = 0
The above display is what I start with. Then "reccount" turns to 1, but
when I run the SQL - VIEW again and select an item, instead of adding it to
the new temporary table it re-creates the table from scratch and puts only
the last record I selected in the table.
Any ideas why?
I have searched everywhere for possible forgotten codes and I have nothing
that even calls the Work Environment nor the Table Name or Alias.
I am tired of tampering with this. Can anyone help me here.????
Thank you in advance,
John

Lee Mitchell

2004-08-18 19:33:05 UTC

Permalink

Hi John:

I think I understand. The TEMPTABLE has two records in it and both have
the data from the original CREATE SQL VIEW command. If this is the case,
try selecting the view, issuing a ReQuery() function, and then the append
routine in to TEMPTABLE.

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

Post by Juan Alonso
Thank you Lee. But I don't think I explaned myself well.
Let me take this on steps so that I don't lose the real problem in the
shuffle.
Here is my situation.
Say I use the CREATE SQL VIEW to create the remote view.
Ok. now after I have the SQL-VIEW say the cursor is called CURSOR-X, I do
the following.
*** Example of what I am doing in code ***
Select CURSOR-X
COPY STRUCTURE TO TEMPTABLE
USE TEMPTABLE IN SELECT(1) SHARED
*** End of Example
Now, I RE-RUN my CREATE SQL VIEW and create CURSOR-X "AGAIN" with NEW DATA.
I do not RE-DO THE ABOVE EXAMPLE. I browse CURSOR-X exit the browse which
leaves the record pointer on the record I want to capture.
*** Example of what I am doing in code ***
Scatter Memvar && Creates Memory Variables with the same Names as the Table
Fields.
Select TEMPTABLE
APPEND BLANK
Gather Memvar && Fills the blank record with the data captured by the
SCATTER COMMAND.
*** End of Example
Now, I repeat the same thing, but here comes the problem.
The TEMPTABLE only holds the last appended data I selected from the CREATE
SQL VIEW data.
I do understand that CREATE SQL VIEW will create a new table each time I run
but what does my TEMPTABLE has anything
to do with CURSOR-X???
I first did the COPY STRUCTURE COMMAND inside forms I was using so I thought
that had issues so I took it out and do it programatically now.
I still get the same problem.
Sorry if my last explanation was not clear I sometimes get ahead of my
thinking...LOL :-)
Well, hope anyone can help cuz this is new to me....
Thank you,
John.
This is behaving as expected. You are correct in your observation that the
table is overwritten with the SQL-VIEW command. The table is recreated and
populated with fresh data. The CREATE SQL VIEW does not have an 'additive'
clause.
I would send the results of the view to a cursor instead of a table and
then copy the results to your table.
I hope this helps.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell
*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/
*-- VFP8 HAS ARRIVED!! --*
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

created

Juan Alonso

2004-08-18 20:34:52 UTC

Permalink

Hey Lee.

Thanks again for your help. The ReQuery() works fine, but my TEMPTABLE is
closed for some reson when I run the CREATE SQL VIEW on another file. I
don't understand. Here is a question.

Does VFP8 FORMS close any open tables that are not in a DBC automatically
when you open the form, or does this happen only when you run "ANY" CREATE
SQL VIEW ???

It seems when I do the CREATE SQL VIEW for my customer records, it closes
out the table I opened before I ran the CREATE SQL VIEW.

Thanks,

John.

Post by Lee Mitchell
I think I understand. The TEMPTABLE has two records in it and both have
the data from the original CREATE SQL VIEW command. If this is the case,
try selecting the view, issuing a ReQuery() function, and then the append
routine in to TEMPTABLE.
I hope this helps.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell
*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/
*-- VFP8 HAS ARRIVED!! --*
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

Table

Post by Juan Alonso
Fields.
Select TEMPTABLE
APPEND BLANK
Gather Memvar && Fills the blank record with the data captured by the
SCATTER COMMAND.
*** End of Example
Now, I repeat the same thing, but here comes the problem.
The TEMPTABLE only holds the last appended data I selected from the CREATE
SQL VIEW data.
I do understand that CREATE SQL VIEW will create a new table each time I

run

Post by Juan Alonso
but what does my TEMPTABLE has anything
to do with CURSOR-X???
I first did the COPY STRUCTURE COMMAND inside forms I was using so I

thought

Post by Juan Alonso
that had issues so I took it out and do it programatically now.
I still get the same problem.
Sorry if my last explanation was not clear I sometimes get ahead of my
thinking...LOL :-)
Well, hope anyone can help cuz this is new to me....
Thank you,
John.
This is behaving as expected. You are correct in your observation that

the

Post by Juan Alonso
table is overwritten with the SQL-VIEW command. The table is recreated

and

Post by Juan Alonso
populated with fresh data. The CREATE SQL VIEW does not have an 'additive'
clause.
I would send the results of the view to a cursor instead of a table and
then copy the results to your table.
I hope this helps.
This posting is provided "AS IS" with no warranties, and confers no

rights.

Post by Juan Alonso
Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell
*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/
*-- VFP8 HAS ARRIVED!! --*
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

created

the

Post by Juan Alonso

Post by Juan Alonso
I am creating an invoice and that the table does not exsist.
used('invent') = .t.
!used('invent') = .f.
reccount('invent') = 0
The above display is what I start with. Then "reccount" turns to 1, but
when I run the SQL - VIEW again and select an item, instead of adding

Post by Lee Mitchell
to

Post by Juan Alonso

Post by Juan Alonso
the new temporary table it re-creates the table from scratch and puts

only

Post by Juan Alonso

Post by Juan Alonso
the last record I selected in the table.
Any ideas why?
I have searched everywhere for possible forgotten codes and I have

nothing

Post by Juan Alonso

Post by Juan Alonso
that even calls the Work Environment nor the Table Name or Alias.
I am tired of tampering with this. Can anyone help me here.????
Thank you in advance,
John

Dan Freeman

2004-08-18 22:26:27 UTC

Permalink

Post by Juan Alonso
Hey Lee.
Thanks again for your help. The ReQuery() works fine, but my
TEMPTABLE is closed for some reson when I run the CREATE SQL VIEW on
another file. I don't understand. Here is a question.
Does VFP8 FORMS close any open tables that are not in a DBC
automatically when you open the form, or does this happen only when
you run "ANY" CREATE SQL VIEW ???
It seems when I do the CREATE SQL VIEW for my customer records, it
closes out the table I opened before I ran the CREATE SQL VIEW.

Any time you open a cursor (table, view, whatever), it uses a "workarea". If
a table is already open in the current workarea and you open another, the
first automatically closes.

Before your CREATE SQL VIEW, issue SELECT 0 which will put you in the first
available unused workarea.

I really doubt, however, that you really want CREATE SQL VIEW. You know of
course that once created, you can simply USE TheView just as if it were a
dbf?

Dan

Juan Alonso

2004-08-19 12:42:51 UTC

Permalink

Thanks I will try the workarea stuff. I think you might have hit this one
on the nose.. sort of speak... LOL

I will post my result soon after..

Thank you again all for your help.

Jr.

Post by Dan Freeman

Any time you open a cursor (table, view, whatever), it uses a "workarea". If
a table is already open in the current workarea and you open another, the
first automatically closes.
Before your CREATE SQL VIEW, issue SELECT 0 which will put you in the first
available unused workarea.
I really doubt, however, that you really want CREATE SQL VIEW. You know of
course that once created, you can simply USE TheView just as if it were a
dbf?
Dan