Put table fields values into array 2

[Paco75]

Hi,

is there a command in visual foxpro that can return all fields into a array?

thanks

 

[Paco75]

I know this wont work but it should be something like

SELECT nRowNumber * FROM DBF() INTO ARRAY aTest ORDER BY 1

 

[Mike Lewis]

You can do this:

SELECT MyTable
COPY TO laSomeArray FIELDS Name, Address, Phone, ....

Or this:

SELECT * FROM MyTable INTO ARRAY laSomeArray

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

i was not clear on my post. I need only one row not entire table.

 

[JRB-Bldr]

"I need only one row not entire table."

Well what you have been provided above will still work. You just need to put the specifying criteria into the SQL SELECT statement to limit the results.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

I need only one row not entire table.

So, how do you define the row you want?

If it is defined by some condition, then you add that condition to the code I showed you. You need a FOR clause if you are using COPY TO, or a WHERE clause for the SQL SELECT.

If you want any row, then the following would be as good as any:

SELECT TOP 1 * FROM MyTable ORDER BY 1 ;
INTO ARRAY laSomeArray

If neither of those meets your needs, perhaps you could clarify what you want to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

let say, as example, i want to return the row 3 into a array from any table (without knowing the structure of table)

 

[Mike Lewis]

Then do exactly as I showed you, but change TOP 1 to TOP 3.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Paco75]

Top 3 returns row 1 to 3 but i think that would be fine i will keep only last array row that should do the thing.

thanks

 

[Dan Freeman]

GUYS! HE NEEDS ONE ROW!

SCATTER TO laArray

 

[Mike Lewis]

HE NEEDS ONE ROW!

Or three?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

i was not clear on my post. I need only one row not entire table.

It doesn't get much clearer.

 

[Paco75]

Yes one row

 

[JRB-Bldr]

And you obviously somehow 'know' which row you want!

And I'd also guess that it is not always the 3rd row.

SELECT * ;
   FROM MyTable;
   WHERE ThisFldVal = 25;
   ORDER BY 1 ;
   INTO ARRAY laSomeArray

Just add the definition of the specific criteria (rough example shown above) of the individual record that you want.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Oh, I see. You want the third row, not three rows. Yes, that's clear now.

Well, if you mean the third row in the original table, leave out the ORDER BY clause, and use WHERE RECNO() = 3.

But, more likely, you have a criterion in mind for selecting a specific row. In that case, do what I and JRB-Bldr said earlier, that is, add the criterion to the WHERE clause.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

I'll go out on a limb here and say "third row" was just a ploy to get you guys away from set thinking and focus on JUST ONE RECORD.

If it's the current record (which is USUALLY the case when this question comes up), then this is what you want:

SCATTER TO laArray

 

[Olaf Doschke]

The "ORDER BY 1" in the second post surely is not suggesting one row, you don't need to order one row, you just need to navigate to the one row.

The Nth physical record is navigated to in the workarea/table named by "tablealias" sorted via some tag named "tagname" in one of the following manners:

* physical order, no tagname used:
SELECT tablealias
GOTO N

lnSkip = N-1
SELECT tablealias
SET ORDER TO tagname && optionally ASCENDING/DESCENDING
GO TOP
SKIP lnSkip

If you have a condition and want the Nth record fullfilling that condition, then you can also do

lnSkip = N-1
SELECT tablealias
SET ORDER TO tagname &&optional for physical record order
LOCATE FOR somecondition
Do While FOUND() And lnSkip>0
   CONTINUE
   lnSkip = lnSkip -1
EndDo
IF FOUND()
   * nth record found
ENDIF

Especially in the last case, you better do a SQL select to retreive all records, though. Then you afterwards can navigate between them.

I hope that'll give you some more ideas in that direction regarding how to get the Nth record.

Typically though, you should consider to retrieve the primary key of the record (it's ID) you want and don't care what number in any order this record is. As you typically will have a primary key index tag on that primary key field, you then can SEEK to that record, eg.

IF SEEK(primary key value,"tablealias","tagname")
   *found record
ENDIF

Afterwards, you can get the record into an array or an object by SCATTER, dan is very right about the point, that it's actually just a matter of making the record you want the current record.

The SEEK and GOTO are the only options guaranteed to be fast, and should be preferred.

Why do you need the record in an array, though? What do you want to do with that array? If you want to bind controls to that record you bind them to tablealias.fieldname and don't need anything else than to navigate to the record, controls will then display and update that record without any SCATTER/GATHER. This SCATTER/GATHER was a mechanism often used in FP2.6, it's obsolete and you don't do that any more. You use a view or cursoradapter cursor and buffering instead of copying records to memvars and back to the DBF. A Cursor IS already quasi a memvar. It's fast to handle records in a local or remote view cursor/cursoradapter cursor/sql passthrough cursor, any cursor.

Bye, Olaf.

 

[Paco75]

Thanks Danfreeman that was exactly what i was looking for

SCATTER TO laArray

The method i write is to be used with any table (actual table) so i cant go with a select because i dont know table structure. I need this to move one or more records up or down in tables. So yes its exactly as Dan was saying. Sorry if i have not being clear enough at begining

thanks all for the help!

 

[Olaf Doschke]

OK,

But then you better use SCATTER NAME loRecord and GATHER NAME loRecord instead of working with arrays.

eg to swap record 3 with record 5 do

Select tablealias
GOTO 3
SCATTER NAME loRecord1 MEMO
GOTO 5
SCATTER NAME loRecord2 MEMO
GATHER NAME loRecord1 MEMO
GOTO 3
GATHER NAME loRecord2 MEMO

This won't work for tables with Autoinc, but otherwise you don't need to know the structure of the table.

Nevertheless it's quite abnormal to sort records in tables physically. You normally keep them in physical order, no matter how that is and define indexes. If all else fails create a sortorder field and populate it with a number in sort order. Then index it and SET ORDER TO that index.

Eg to sort orderitems I'd introduce a field sortid and populate it with 1..n for each orderid, an INDEX ON orderid*1000+sortid will then help me to sort orderitems per order, but I keep orderitems physically in the order they were inserted into the table.

Bye, Olaf.

 

[Olaf Doschke]

You should also know RECNO() gives you the record number of the current record, which you can store into a memvar and use that in a GOTO to go back to that record. Be aware though, that in buffered tables all new not yet written records will have negative recnos, eg first new record will have recno()=-1, the real recno is only availabe if it's a real record. As DBFs are used shared this can't be foreseen in multi user environments, unless a record really is added into the DBF file.

Bye, Olaf.