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!"

 

[mmerlinn]

[&nbsp;]

Obviously it works. There is no "bug" about SET DELETED and SELECT-SQL, nor it is by accident.

Thanks Mike. I don't see any point then in creating a DELETED field in any of my tables and writing the code to implement it.

If, at some time in the future (very highly unlikely), this database is migrated to some other system, it is extremely easy to add the DELETED field. Just PACK, add DELETED field, set all DELETED fields to .F., and incorporate into new system.

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.

SELECT-SQL

Syntax SELECT [[RED]ALL[/RED]|DISTINCT] blah blah blah

The scope ALL is respected by SELECT-SQL according to this and as stated before is expected to work correctly with SET DELETED.

Unless you can make Foxpro last forever, sooner or later you'll need to migrate to some other system.

For all practical purposes Foxpro will last forever for me. I just can't see me migrating to another system that I could never master as long as Foxpro does the job for me.

SQL Server is a database server from Microsoft. It's very well-known.

I have heard of it, but know very little about it and will never use it. The reason I asked in the first place is that when I read your original reply I was not clear on exactly what you were saying based on previous replies.



mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[mmerlinn]

[&nbsp;]

Mike:

Unless you are God, you have NO way of knowing who knows what about anything. And there is no way you can help anyone if you spend all of your time covering all of the bases before you answer.

And I really don't know anything about SQL Server beyond the fact that it is another Foreign Computer Language to me.

Regardless, the apology is accepted even though I don't believe that it was necessary.

mmerlinn

http://mmerlinn.com

"Political correctness is the BADGE of a COWARD!"

 

[TamarGranor]

(FoxPro Language Reference):

SELECT-SQL

Syntax SELECT [ALL|DISTINCT] blah blah blah


The scope ALL is respected by SELECT-SQL according to this and as stated before is expected to work correctly with SET DELETED.

This use of the ALL keyword is totally independent of the ALL scope, just another of the ways in which the VFP language is ambiguous. SELECT-SQL doesn't use scope.

Nonetheless, it absolutely respects SET DELETED.

Tamar

 

[golf212]

I just stumbled on this thread and finally worked my way down to the end and even though problem was resolved I thought everyone be interested the “official” explanation. I encountered a similar problem back in my early days of FPW2.6 in 1998. In my case the code worked in the compiled EXE but failed in debug testing. My EXE had SET DELETE ON as part of the mainline code setup.

I had documented this problem in my personally compiled “Foxpro Book of hard Knocks & Incantations” from a Microsoft Technet release, PSS ID Number: Q103573, “PRB: Simple SELECT to a Cursor Not Creating a Cursor”. Last modified 6-27-1995.

The basic premise is if you create a cursor and then try to create a second cursor from the result of the first query it will fail if SET DELETED is OFF. Basic example was:

SET DELETE OFF
SELECT * FROM customer INTO CURSOR cust1
?DBF()
SELECT * FROM cust1 INTO CURSOR cust2 fails, generates and error

If the above test is repeated with SET DELETE ON cust2 will be created.

The Technet explanation for this is: “The SET DELETE ON command forces FoxPro to create a .TMP file rather than just point to the original file even if there are no fields in CUSTOMER that are marked for deletion. (This behavior would also occur in the SELECT command creating cust1 were more restrictive.) The simple SELECT command in the above code fragment just causes FoxPro to use CUSTOMER again; since the SELECT * CUSTOMER command represents the entire CUSTOMER table, FoxPro saves time by not really performing the SELECT command.” “Affects FoxPro for MS-DOS vers 2, 2.5x, 2.6, 2.6a, for Windows vers 2.5x, 2.6, 2.6a”. The Technote states that "This behavior is by design".

I tried to relocate this TechNet note for a link, but apparently NOT everything is archived for ever on the net, so the above is the “gist” of the two page tech note (paper copy). I had no luck finding it on the MS site or with Google.

John