Array not found if ALEN used

[Vasile1991]

Hello, I have a problem with arrays and determining the size of them.
I'm writing a complex select statement for determining only the the IDs I have in that table for those products.

I have simplified it to make it easier to understand... even simplified it will still not work...

1 SELECT product.name ;
2 FROM product;
3 WHERE alltrim(product.name) == alltrim(VVname) ;
4 INTO ARRAY arrayIDs
5
6 IF ALEN(arrayIDs) (Here I want to say that if it has no records show that message)
7 MESSAGEBOX("No results found.", 48, "Error")
8 RETURN
9 ENDIF

This kinda worked before... but I made some major changes to the project (this forms only) and now it seems it' not...

VVname is the control source I have from a textbox in a form... There are all links made with the database and everything is working fine. Only this problem :

The problem is that on row 6, when ALEN should be executed, it says the array doesn't not exist (arrayIDS not found). What am I missing? And No, there should be data found.

Please help me out..
Thank you, Vasile.

 

[Vasile1991]

sorry, should be this select :

1 SELECT product.id_prod;
2 FROM product;
3 WHERE alltrim(product.name) == alltrim(VVname) ;
4 INTO ARRAY arrayIDs
5
6 IF ALEN(arrayIDs) (Here I want to say that if it has no records show that message)
7 MESSAGEBOX("No results found.", 48, "Error")
8 RETURN
9 ENDIF

 

[Vasile1991]

I have found the problem and the caues was really ... strange...

It seems you cannot use ALEN(arrayName) in an IF
So :
1 IF ALEN(arrayName) ==0
2 Messagebox(""No results found")
3 ENDIF
is INCORRECT...

The Correct form would be something like this :

1 STORE ALEN(arrayName) to arrayNameLengthVariable
2 IF x==0
3 Messagebox("No results found")
4 ENDIF

 

[Olaf Doschke]

In contrast to the INTO CURSOR clause, which does create an empty cursor, if there is no result record, INTO ARRAY does not create an empty array, if the result is empty, it creates no array.

There also is no such thing as an array with 0 elements, therefore this wouldn't even work.

It may just have worked before, because VFP then leaves any exisitng array untouched, so if your first query results in an array, a query resulting in 0 records leves that as is and so ALEN() does not lead to an error.

The best thing you can do is to check _TALLY>0. To show why this is even better than TYPE(), whcih you could also use to determine the existance of the array:

SELECT * FROM foxcode WHERE .F. INTO ARRAY arrayFoxcode
? _Tally,Type("arrayFoxcode",1) 
* output: 0 U - 0 records, no array is created

SELECT * FROM foxcode INTO ARRAY arrayFoxcode
? _Tally,Type("arrayFoxcode",1), ALEN(arrayFoxcode), ALEN(arrayFoxcode,1)
* output: 1008 A 12096 1008 - 1008 records, array is created, has 12096 elements and 1008 rows

SELECT * FROM foxcode WHERE .F. INTO ARRAY arrayFoxcode
? _Tally,Type("arrayFoxcode",1), ALEN(arrayFoxcode), ALEN(arrayFoxcode,1)
* output: 0 A 12096 1008 - 0 records, array is unchanged, and does not reflect the result!

The last query again results in 0 records, but the array is a left over from the second query, it does not reflect the current result!

Bye, Olaf.

 

[Mike Lewis]

I agree with Olaf. I would always use INTO CURSOR rather than INTO ARRAY for this situation. You can easily test for an empty cursor by using RECCOUNT().

An alternative approach is to keep the array, but test _TALLY to see if any rows were returned.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Vasile1991]

Thank you for your replies, I understood the problem, but how to test if the array is empty??

 

[GriffMG]

You could test for it's TYPE:

If Type("arrayIDs") = "U"
Messagebox("No results found")
Else
** do something else
EndIf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Vasile, I already said the test for an array is misleading, as an array may exist after a query with an empty result, just because it existed before the query already. Please revisit my example and pay attention to the result of the third query in regard of _TALLY vs TYPE().

The only reliable thing is to check _TALLY.

Bye, Olaf.

 

[GriffMG]

Surely if he does a RELEASE arrayIDs before he does the query
that will be just as reliable...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[JRB-Bldr]

Vasile1991 - As you have been told above, your biggest problem is using an ARRAY at all when things would work MUCH BETTER if you were to be using a CURSOR for your SQL Query results.

I, like the others above, recommend that you quit struggling to make things work with the Array, and change over to using a CURSOR which you can then handle/process/etc. like a 'normal' data table
(especially if you use: INTO CURSOR Results READWRITE)

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Griff,

OK, that would also work, but why the extra effort? _TALLY tells it.

Bye, Olaf.

 

[GriffMG]

True, but he didn't seem to want to do that for some reason Olaf...

Personally, I've never used this approach anyway, I make cursors or temp files - never arrays like this
because I'm not confident I would not run out of memory... there is a max size for an array isn't there?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

There was a max size of 65000 elements. It's gone with VFP9. But actually memoery usage via arrays is bloated in comparison with the recsize() of cursors, which hold value side by side to the next value in the dbf structure.

Eg memoery usage of N integers in a cursor is Header()+5*Reccount(), while an array of N integers uses N*sizeof(Value) where value is the following C struct:

typedef struct {
  char        ev_type;
  char        ev_padding;
  short        ev_width;
  unsigned    ev_length;
  long        ev_long;
  double      ev_real;
  CCY         ev_currency;
  MHandle      ev_handle;
  unsigned long ev_object;
} Value;

Without looking up how large a CCY and an MHandle is this is at least 20 bytes per variable or also per array element. So your "natural fear" about arrays leads you to using the more efficient cursors, yes.

There might be operations done after the query, that work better with arrays. Of course that can only be told by Vasile.

Bye, Olaf.

 

[GriffMG]

Thank you Olaf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

I forgot to quote the source of information: [URL unfurl="true"]http://www.foxpert.com/docs/howfoxproworks.en.htm[/url]

Specifically the paragraph about "Variables, Arrays and Objects".

Bye, Olaf.