Closing tables after sql cmd select .. Into table.. 1

[EzLogic]

I have a loop (for/endfor loop) that gather information from 1 table and distributes small dbf files from each record.

for example:
for nCounter = 102 to 213
cAgent = ltrim(str(nCounter))
cFile = cAgent +'-Balance.dbf'

sele balance,storeId from xyz where xyz.StoreId = cAgent into table (cFile)

I need to close the (cFile) after it has been created on the computer.

I cannot issue a "USE" after, as it is closing other tables. And the alias names of the created table is not cFile as it is starting with a number, something like "E", "A","B", etc ...

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[jimstarr]

Try:

USE IN (cfile)

Jim

 

[GriffMG]

Ali,

I'm not sure but if you make a note of the workarea BEFORE you do the 'select... into...' you should be able to get back to it:

for nCounter = 102 to 213
   cAgent = ltrim(str(nCounter))
   cFile = cAgent +'-Balance.dbf'
   m.oldarea = select()
   select 0
  sele balance,storeId from xyz where xyz.StoreId = cAgent  into table (cFile)
  select(m.oldarea)
  use

Regards

Griff
Keep [Smile]ing

 

[EzLogic]

Jimstarr
I will try it.

Grif, wouldn't that close the work area and not the created table?

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[GriffMG]

I was assuming that the 'select... into...' would open the specified tablename in the currently selected work area.

You are right though, I did a quick test and it opens a new work area, and selects it so...

for nCounter = 102 to 213
   cAgent = ltrim(str(nCounter))
   cFile = cAgent +'-Balance.dbf'
   select 0
  sele balance,storeId from xyz where xyz.StoreId = cAgent  into table (cFile)
   m.wrkarea = select()
   ...
   do something
   ...

  select(m.wrkarea)
  use

Regards

Griff
Keep [Smile]ing

 

[EzLogic]

Thanks Guys!

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[DoctorNix]

SELECT balance,storeId from xyz where xyz.StoreId = cAgent  into table (cFile)
cAlias = ALIAS()
... do something 
USE IN (cAlias)

Or (preferably):
cFile = 'Bala' + cAgent
In that case, the alias will not change into 1 letter.

 

[StewartUK]

Ali,

I may not have quite understood what you are trying to do, but when you run a SQL SELECT, VFP puts you into the work area of the table created by the SQL. If you look in the status bar after the SQL has run, what alias and table name do you see? It should be something like A (c:\102-balance.dbf).

So in fact you should just be able to issue the USE command straight after the SQL and it will just close the created table.

What other tables are getting closed if you do issue the USE command straightaway?


Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[DoctorNix]

In response to StewartUK: the key to this problem is :
If you have a tablename that starts with a number, the alias will NOT be 123-etcetera, but it will change into a one-letter name, for instance 'H', or something else.
The reason for this rather unexpected behaviour is, that if you would name a table '11.dbf', and it would live in workarea 3, and if you then would issue the command 'SELECT 11', then FoxPro would not know if you mean workstation 11, or alias '11'.

See my solution above, which will cure everything..

Greetz, DoctorNix

 

[EzLogic]

StewartUK, that's what I thought, but, it didn't work like that. Here is a snippet of my code. (a simplified example).
****************
SELECT Cust_num as Agent,balance,crLimit FROM Customer ORDER ;
BY Cust_num INTO CURSOR TempCustBalance

SELECT TempCustBalance

SCAN
xuAgent = Agent
WAIT WINDOW 'Uploading Balance File for Store: ' + xuAgent NOWAIT
IF SUBSTR(xuAgent,1,2) = 'WT' OR SUBSTR(xuAgent,1,2) = 'CR' OR ;
SUBSTR(xuAgent,1,3) = 'SUB' && we will do so
IF 'CR' $ xuAgent
cInvName = "\\toyzcorp\ftp\OrderForm\"+TRIM(xuAgent)+"-Balance"
ELSE
IF 'WT' $ xuAgent
cInvName = "\\toyzcorp\ftp\OrderForm\"+TRIM(SUBSTR(xuAgent,3,7))+"-Balance"
ELSE
cInvName = "\\toyzcorp\ftp\OrderForm\"+TRIM(SUBSTR(xuAgent,4,7))+"-Balance"
ENDIF
ENDIF
SET SAFETY OFF
SELECT Balance,crLimit FROM TempCustBalance WHERE Agent = xuAgent ;
INTO TABLE (cInvName)
USE
SET SAFETY ON
ENDIF
SELECT TempCustBalance
ENDSCAN
SELECT TempCustBalance
USE


TempCustBalance was being closed somehow

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[StewartUK]

Ali,

I guess the first question is - do you now have a solution that does what you want? If so, good, otherwise...

Have you tried running the SCAN...ENDSCAN in the debugger, stepping through the code line by line?

Another option is to set a breakpoint using the breakpoint dialogue (Ctrl+B or Tools\Breakpoints). If you want to do this, set the Type to "Break when expression is true" and in the expression box type:

NOT USED([TempCustBalance])

Click the Add button and then the OK button. Run the scan with the debugger open and it should stop on the line that closes TempCustBalance.

By the way, you shouldn't need the SELECT TempCustBalance before the ENDSCAN. SCAN...ENDSCAN should reselect the original work area each time it loops around.

DoctorNix - oh yes, I see what you mean about the alias name - hadn't realised that. I agree that your solution would also work in Ali's situation, however as he is just closing table immediately after its creation by the SQL, USE should also work.


Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[baltman]

Don't forget the useful 'DROP TABLE TableName', which will close and delete an open (or closed) table. Great for when you have a temporary table and are done with it.

Brian