SELECT not creating array 6

[mmerlinn]

[ ]
Can anybody tell me what I am missing here? I don't see any reason why the second array is not being created just like the first one was.

[tt][blue]
zdestpath = qBPathDest + zdosdaddy + '_f.TXT'
= G_CATFRM(zdestpath, zhtmdaddy, zwebtit)
[green]
SELECT DISTINCT tm_sname ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tname) ;
HAVING vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO ARRAY temp ; <==== This creates an array
ORDER BY tm_sname
[/green]
zfamlist = ''
FOR z = 1 TO ALEN(temp, 1)
zfamlist = zfamlist + ', ' + ALLTRIM(temp[z])
ENDFOR

zfamlist = ATXRIGHT(zfamlist, ', ')
[red]
SELECT DISTINCT tm_tkind ;
FROM tranmodl ;
WHERE NOT EMPTY(tm_tkind) ;
HAVING vfamname = (qFamName) ;
AND DELETED("Tranmodl") <> .T. ;
INTO ARRAY a_Temp <==== This does NOT create an array
[/red][red]
*
* Throws 'variable not found' error here
* V
* V [/red]
FOR z = 1 TO ALEN([red]a_Temp[/red], 1)
a_Temp[z] = 'trans' + PICKLIST(ALLTRIM(a_Temp[z]), 'R', 'missions', '4', 'missions', 'T', 'axles', 'A', 'axles', 'X', 'fer cases')
ENDFOR
[red]
*
* Throws 'variable not found' error here
* V
* V [/red]
FOR z = 1 TO ALEN([red]a_Temp[/red], 1)
a_Temp[1] = a_Temp[1] + IIF(a_Temp[z] $ a_Temp[1], '', ' & ' + a_Temp[z])
ENDFOR
[red]
DIMENSION a_Temp[6] <==== creates array instead of redimming it[/red]
a_Temp[1] = a_Temp[1]
a_Temp[2] = zfamlist
a_Temp[3] = zmaker
a_Temp[4] = qFamname
a_Temp[5] = qBPathDest + zdosdaddy + '_m.TXT'
a_Temp[6] = zwebtit

= G_CATSPL(@a_Temp)
[/blue][/tt]


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Tamar, thanks for clearing up my misconceptions about FP 2.6 (after all, I used it for a short time only years ago, and don't have it any more to run my own tests).

But why the code in question works correctly after packing the table? How can it be? Seems dangerous - you can never know for sure when your program works correctly and when it doesn't.

Was it corrupted or why else packing (or moving the cursor, for that matter) helped?

 

[Stella740pl]

Hi Mike,

Are you saying that alias parameter should not be used in DELETED() (or similar functions) when called from SELECT, and to be on the safe side one should put it as

SELECT something FROM tranmodl WHERE something else AND DELETED() ?

Well, I (almost) never use DELETED("alias") anyway, but it is good to know if it is what you are saying.

 

[Stella740pl]

Thanks. Work as usual.

That's exactly what I am saying.

That's helpful information.
It's a little too late in the thread to say that, but I didn't like this parameter from the start - but thought of it as redundant, not dangerous.

So it appears that both, moving the pointer or packing a table with no deleted records were both unnecessary and both ultimately served the same purpose of "shaking" the original table a little.

Mike, if this is the case, how would you suggest to handle a query performed on more than one table if DELETED() needs to be checked?

mmerlinn, would you mind removing the parameter from DELETED() function and testing your code without it?

 

[r0gerc]

Dear all

I have been testing this today because I use SELECT-SQL a fair bit, and because of this thread I have discovered issues that I do not know enough about. I have found that if an alias is used in the deleted() function, the results are different depending on whether the table is already open before the query is run or whether the query opens it.

1. If the table is already open in a workarea (say 1), when the query runs, it leaves the record pointer where it was prior to the query being run.

2. If the query opens the table, it leaves the pointer at the last record in the table. This means that if the last record happens to be a deleted one, the pointer stays there and a subsequent run of the query returns no results because the query is only looking at the last (deleted) record.

The above 2 points bear out what Mike Yearwood said because if no alias is used in the deleted() function, the expected results are returned regardless of whether the table is open before the query or not.

Please forgive my lengthy intrusion into your thread but this has worried me greatly, and I wanted to throw some light (or more confusion!) onto the problem. I now have to find out what happens when querying 2 or more tables...

Roger

 

[Stella740pl]

Thanks for the tests, Roger.

I don't think you added confusion to the thread.
Just the opposite, you confirmed what Mike Yearwood said and that explains what happened in the mmerlinn's program.

Mike, a star for you.

 

[r0gerc]

From FPW 2.6 Help...

"Be careful when using functions (DELETED( ), RECNO( ), EOF( ), FOUND( ), RECCOUNT( ), and so on) that support an optional alias or work area in join conditions.
Including an alias or work area in these functions may yield unexpected results. SELECT doesn't use your work areas; it performs the equivalent of USE ... AGAIN.
Single-table queries that use these functions without an optional alias or work area will return proper results. However, multiple-table queries that use these functions
even without an optional alias or work areamay return unexpected results."

So single-table queries are OK as long as no alias is included, but multiple-table queries can't be relied upon. Oh well, there must be a workaround. Include a logical "deleted" field in all tables for use in queries?

Still worried though - it's not reliable enough.

Roger

 

[Stella740pl]

Oh well, there must be a workaround. Include a logical "deleted" field in all tables for use in queries?

Simpler than that. If you really need to keep those deleted records in your table, first SELECT or COPY everything that is not deleted into a cursor, then use it in subsequent SELECTs.

And I found that my VFP6 also has this fragment in Help. Probably even read it more than once - and forgot. Not good.

 

[r0gerc]

It's even simpler than that, Stella. With SET DELETED ON, it appears that deleted records are not included in the result anyway. I didn't realise that, I always thought SELECT-SQL ignored that setting. Many thanks, and a star for you and one for mmerlin for bringing this up in the first place.

Roger

 

[Stella740pl]

Well, yes, it does honor SET DELETED.

But SET DELETED is set for the whole data session.

The problem would be when you need to use deleted records from one of the multiple tables participating in SELECT and avoid them in another - and you can't use DELETED("alias") in SELECT.

Thanks for the star.

 

[mmerlinn]

[&nbsp;]
Here are the results of some extensive testing that I did before I discovered that I needed to pack the table.

No matter where or under what conditions that I test EOF(), it is ALWAYS .F.

When the table is not open

The first SELECT opens it and leaves the record pointer [RED]at the END of the table[/red]. This is confirmed by Roger's tests. At NO time in any test that I did, did the SELECT move the record pointer of the underlying table.

The second SELECT apparently only uses last table record in the SELECT. This means that if that record is not a valid result, the array is not created. All subsequent SELECTs on that table follow this pattern. Note that the record pointer REMAINS on the last record of the table.

When GO TOP is used, the record pointer is moved to the top of the table, and the SELECT works as expected. SELECT does NOT move the record pointer off of record #1. If GO TOP is used before every SELECT, then all subsequent SELECTs work as expected. If GO TOP is NOT issued for the third SELECT, then the SELECT reverts to the results in the previous paragraph even though the record pointer REMAINS at the top of the underlying table. Note that the record pointer in the original table REMAINS at the TOP regardless whether a second GO TOP is issued or not. The SELECTs are NOT moving the table record pointer!!!


When the table is open in some work area and the record pointer is at the top

All SELECTs work as expected without the use of GO TOP.

When the table is open in some work area and the record pointer is at the last record

All SELECTs fail unless GO TOP is issued before the first SELECT, in which case all SELECTs work correctly.

************

Based on what Mike is saying, my testing, and the content in this thread, this seems to be what is happening.

Since the SELECTs are using a DIFFERENT SELECT-generated work area than the original table, subsequent SELECTs are using the pointers and filters in the SELECT-generated work area without reference back to the original table. But when the original table is changed using GO TOP or PACK, the SELECT starts over with NEW pointers and filters from the original table. So, by using GO TOP, the SELECTs were forced to start over with current up-to-date information.

When the original table was already open and at the top, all SELECTs worked because the SELECTs "knew" about the full content of the original table. Otherwise the SELECTs only "knew" about the last record in the table, except when the SELECT opened the table itself.

************

I tested the SELECTs with DELETED() instead of DELETED("tablename"). They work fine that way. I also removed the DELETED() from the SELECT and used SET DELETED ON. That also worked fine.

It appears to me that the only way to be safe is to use SET DELETED ON with no DELETED() in the SELECT, to use DELETED() in the SELECT with ONLY single tables, or to use DELETED("tablename") in SELECTs with multiple tables if and only if GO TOP is issued for EVERY table in the SELECT immediately before calling the SELECT.

As for me, I am going to remove DELETED() from all of my SELECTs and use SET DELETED ON instead. That seems to be the simplest, most consistant, and most reliable way to solve this problem.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

In fact, you can't used DELETED("<alias>") safely in any query. I knew this, but somehow missed that until reading your message today in response to my testing suggestion.

In fact, the general rule is that you can't use any function that accepts an alias in a query involving more than one table. In a query involving a single table, you can use those functions, but most _not_ pass the parameter.

What's embarrassing about having missed this is that I wrote about this exact issue in my book "Taming Visual FoxPro's SQL" and have talked about a dozen or so times in a session I give based on that book. Ouch.

Tamar

 

[Stella740pl]

...or to use DELETED("tablename") in SELECTs with multiple tables if and only if GO TOP is issued for EVERY table in the SELECT immediately before calling the SELECT.

And, according to Help, not even then, as with multiple tables it "may return unexpected results" - and you never know when.

 

[mmerlinn]

The problem would be when you need to use deleted records from one of the multiple tables participating in SELECT and avoid them in another - and you can't use DELETED("alias") in SELECT.

Based on my testing noted in my last post, the solution seems to be using DELETED("alias") but ensuring that you explicitly GO TOP for EVERY table in your SELECT immediately before calling the SELECT.

But SET DELETED is set for the whole data session.

I would use just before the SELECT

zolddeleted = SET('DELETED')
SET DELETED ON

then immediately after the SELECT i would use

SET DELETED &zolddeleted

to restore DELETED to whatever setting you are using for your data session.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

And, according to Help, not even then, as with multiple tables it "may return unexpected results" - and you never know when.

Then the only solution I see is to SELECT from those tables where you need to keep the DELETED entries into a CURSOR/TABLE, then issue your main SELECT using those results with your other tables without using DELETED() in your SELECT at all.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Stella740pl]

Then the only solution I see is to SELECT from those tables where you need to keep the DELETED entries into a CURSOR/TABLE, then issue your main SELECT using those results with your other tables without using DELETED() in your SELECT at all.

Yes, I agree. It's exactly what I suggested in my reply to r0gerc (the one marked 6-Feb-08 13:33)

 

[severach]

The correct solution is to stop using DELETED() and make your own field that does the same thing which will always work with SELECT. SELECT does not handle DELETED() or RECNO() properly, particularly when GROUP BY is present. Try this code on any table to see what I mean:

SELECT RECNO() AS RNO,COUNT(*) as CT FROM ANYTABLE GROUP BY RNO

I get "348,1" 348 times. 348 is the record count of the random table I chose.

What may be happening is that sometimes DELETED() applies to the source table and sometimes it applies to the result table and it might even apply to a table not even in the query or like RECNO() above Fox may decide that it is a constant and only read it at the end of the table scan. Placed in a HAVING clause it should apply to a result table. A CURSOR or TABLE result always produces non deleted records. DELETED() ceases to have meaning with an ARRAY result. Because neither DELETED() nor RECNO() are part of the SQL standard there is no requirement that they should work all the time. If they work you got lucky.

I use DELETED() only with Fox primitives. I only use SELECT RECNO() when there's no GROUP BY and I've checked the result to ensure that it is working correctly. You might find that a CLOSE DATA between the two selects fixes your DELETED() problem though that's not usually acceptable for a general solution.

I recommend avoiding SET DELETED. Fox specifically states that SELECT uses USE AGAIN to avoid such programmer shenanigans. Murphey's law says that the probability that SET DELETED will fail increases as your deadlines approach.

>I never used to write SELECTs this way - I used NOT DELETED() instead

It's too bad the query generator makes such bad code. The 2048 byte command limit is painful enough without the generator consuming an extra byte because it's too lazy to do it right.

 

[mmerlinn]

[&nbsp;]

I recommend avoiding SET DELETED. Fox specifically states that SELECT uses USE AGAIN to avoid such programmer shenanigans.

Could you please expound on this for me. My documentation for SET DELETED says:

All commands that select records (including records in related tables) using a scope ignore records marked for deletion if SET DELETED is ON.

This indicates to me that SET DELETED is supposed to work correctly with SELECT-SQL. If that assumption is wrong, the next step for me is to add a DELETED column to all of my tables and go that route.

**********

As for me, I am going to remove DELETED() from all of my SELECTs and use SET DELETED ON instead.  That seems to be the simplest, most consistant, and most reliable way to solve this problem.

Ever since I wrote this on 6 Feb 07, I have been removing all DELETEDs from all of my SELECTs.

I am converting all of them to:

zolddeleted = SET('DELETED')
SET DELETED ON

SELECT whatever

SET DELETED &zolddeleted

This has been working quite well for me. No more problems like those that made me start this thread in the first place.

I have also noted a slight improvement in speed. Most SELECTs used to take 8-9 seconds to work. Now they take 2-3 seconds.

That is not much of an absolute improvement, although percentagewise it is VERY significant.

Even though each one runs only a few seconds faster, when I am doing heavy duty crunching, there is a noticeable improvement in speed. I notice it most when I am generating and uploading web pages. I used to be able to generate and upload only 650 pages per hour on dialup. Now I can do 700 pages per hour. That alone makes it well worth my while to remove DELETED() from all of my SELECTs.

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

This indicates to me that SET DELETED is supposed to work correctly with SELECT-SQL. If that assumption is wrong, the next step for me is to add a DELETED column to all of my tables and go that route.

As you've found with the changes you're making, SELECT-SQL does respect SET DELETED. Severach is referring to the exact problem we finally realized you had, that you can't use DELETED() with an alias in a multi-table query because tables may not have the aliases you think, and that you can't use DELETED() without an alias in a multi-table query because it won't know what table you're talking about.

Tamar

 

[mmerlinn]

SQL Server deletes a record. Period.

Not sure what you mean here.

Are you saying that SELECT-SQL in FP 2.6 will physically delete the records in my tables if I use DELETED() in the SELECT? If so, that is even more reason to never use it in my SELECTs.

Or are you talking about a totally different program that uses FP files? In that case, this is not an issue for me as the only program I am running is FP 2.6.

I only delete records after they've been "deactivated" for some time.

Records are never deleted in my case except for records created in error. Once a valid record is established, there is never any reason to delete it. Valid records can be updated when new or better information becomes available or when there is an error.

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[severach]

ON
All commands that select records (including records in related tables/.DBFs) using a scope ignore records marked for deletion if SET DELETED is ON.

This indicates to me that SET DELETED is supposed to work correctly with SELECT-SQL.

<scope>
The scope clauses are: ALL, NEXT <expN>, RECORD <expN>, and REST. These are explained in the Overview of the FoxPro Language chapter in the FoxPro Language Reference. Commands which include <scope> operate only on the table/.DBF in the active work area.

SELECT-SQL does not select records with a scope nor does it use relations the way you do so the SET DELETED command is not expected to work with SELECT-SQL. I tried to defeat SET DELETED the way that Fox would with this code:

USE FILE && Some deleted records here
SET DELETED ON && no longer visible
SELECT 0
USE FILE AGAIN
BROWSE && I should see deleted records

I expected to see the deleted records but they have disappeared. This must be another Fox bug relating to USE AGAIN, there are so many, which may be why it works for you. If it works use it though I'd migrate over to a deleted field at your convenience.

SELECT-SQL does respect SET DELETED

It looks like a bug to me so it's only supported by accident. I'd use it myself but I don't change Fox versions and I'm involved in the Fox project all the time so I'm there to observe failures and can fix as necessary. Tricking SELECT-SQL into working with deleted records using a USE AGAIN bug isn't such a good idea for a deployed app.

you can't use DELETED() with an alias in a multi-table query

You should minimize their use in single table queries too since they may on rare occasion fail.

SQL Server deletes a record. Period.

Not sure what you mean here.

Unless you can make Foxpro last forever, sooner or later you'll need to migrate to some other system. Other systems DELETE permanently. It's not a flag that you can RECALL at your convenience. Migrating away from DELETED() to a field will make your system more reliable now and a little easier to convert later. No hurry.