FoxPro database connector - Select Count and Unique Array Name VFP 9

Author

Select Count and Unique Array Name VFP 9

bradwiseathome@hotmail.com

2005-11-03, 8:30 pm

I am trying to assign a count into a temporary array. I want a unique
name because the function is actually a VFP9 DBC stored procedure, and
I want to be sure that the return value is unique to a given caller
(this is a heavy multiuser web app doing read-only against the DBC). I
am having a problem with the SQL-SELECT statement, can anyone help?

Function TestThis
LPARAMETERS param , paramparam

LOCAL count as Integer
LOCAL tmpStore as String

tmpStore = SYS(2015)

SELECT COUNT(ColumnA) as uCount FROM TestTable ;
INTO ARRAY &tmpStore

count = &tmpStore(0)

RETURN count

ENDFUNC

Olaf Doschke

2005-11-03, 8:30 pm

Hi Brad,

I'd say your procedure would only return Reccount("TestTable").
Waht do you really want to count?

You could use the same array name over and over again,
as that array is created local. VFP is no sql server, so stored
procedures are run locally, not on the server. So you act in
the local/client memory.

And arrays start with element 1, not 0, accessing element 0
causes an error...

Bye, Olaf.

Dan Freeman

2005-11-03, 8:30 pm

Your requirements aren't clear at all but there are two things wrong here:

> count = &tmpStore(0)

1) VFP's arrays are 1-based, not 0-based
2) You can't directly assign an array to a new variable. For that, you use
ACOPY().

Dan

bradwiseathome@hotma
il.com wrote:
> I am trying to assign a count into a temporary array. I want a unique
> name because the function is actually a VFP9 DBC stored procedure, and
> I want to be sure that the return value is unique to a given caller
> (this is a heavy multiuser web app doing read-only against the DBC). I
> am having a problem with the SQL-SELECT statement, can anyone help?
>
>
>
>
> Function TestThis
> LPARAMETERS param , paramparam
>
> LOCAL count as Integer
> LOCAL tmpStore as String
>
> tmpStore = SYS(2015)
>
> SELECT COUNT(ColumnA) as uCount FROM TestTable ;
> INTO ARRAY &tmpStore
>
> count = &tmpStore(0)
>
>
> RETURN count
>
> ENDFUNC

Jack Jackson

2005-11-03, 8:30 pm

On 2 Nov 2005 10:50:47 -0800, bradwiseathome@hotma
il.com wrote:

>
>I am trying to assign a count into a temporary array. I want a unique
>name because the function is actually a VFP9 DBC stored procedure, and
>I want to be sure that the return value is unique to a given caller
>(this is a heavy multiuser web app doing read-only against the DBC). I
>am having a problem with the SQL-SELECT statement, can anyone help?
>
>
>
>
>Function TestThis
> LPARAMETERS param , paramparam
>
> LOCAL count as Integer
> LOCAL tmpStore as String
>
> tmpStore = SYS(2015)
>
> SELECT COUNT(ColumnA) as uCount FROM TestTable ;
> INTO ARRAY &tmpStore
>
> count = &tmpStore(0)
>
>
> RETURN count
>
>ENDFUNC

1. Why do you need the array name to be different every time? It's
just a local variable in this method. If it is important that the
array have a different name each time, why does 'count' not have to be
different?

2. If you are going to name the array at runtime like this, you
should also make it be LOCAL in the possible but unlikely event that
there is an existing PRIVATE or PUBLIC variable of the same name:
LOCAL ARRAY &tmpStore.[1]

3. The SELECT will not create or touch the array if no records are
returned. To be safe you should check for that case.

4. 'count' is not needed - you could return the array value itself
without copying it first.

5. VFP arrays are 1 based, so you should be referencing [1] not [0].

6. You say you are having a problem with the SELECT statement - what
problem?

Kurt Grassl

2005-11-03, 8:30 pm

....
> 3. The SELECT will not create or touch the array if no records are
> returned. To be safe you should check for that case.

AFAIK, a Select with COUNT will always return 1 record / write into the
array (as long as there are no other functions like max, min ...)

Since VFP 9 (and SET ENGINEBEHAVIOR 90) there will be always a record
return. Conforming to ANSI (I believe)

regards
Kurt

Olaf Doschke

2005-11-03, 8:30 pm

Jack Jackson

2005-11-03, 8:30 pm

On Thu, 3 Nov 2005 09:13:18 +0100, "Kurt Grassl" <ng@kurtgrassl.de>
wrote:

>
>...
>
>AFAIK, a Select with COUNT will always return 1 record / write into the
>array (as long as there are no other functions like max, min ...)
>
>Since VFP 9 (and SET ENGINEBEHAVIOR 90) there will be always a record
>return. Conforming to ANSI (I believe)

Yes, I'm sure you are correct.

Sponsored Links