The data query in VFP

The Visual FoxPro language system consists of three parts: one is the traditional Xbase language system, the other is Structured Query Language (SQL), and the third is object-oriented (OOP) programming language. The author thinks, Visual FoxPro these three parts of language have the function of data query. There is no objection to the fact that the Xbase language and SQL language have data query capabilities, but the OOP language in Visual FoxPro should not have data query capabilities because it is generally believed that data processing in Visual FoxPro is not object-oriented, and this can be derived from visual The OOP language in FoxPro should not have data query capabilities. In fact, when we import the current RDO or ADO data-processing model through OOP language in Visual FoxPro, we get different answers.

This article wants to focus on the data query methods in visual FoxPro about Xbase languages and SQL languages. Before we begin, let's distinguish between the different roles that xbase and SQL languages play in data queries. In a word: SQL language focuses on bulk data queries and complex relational operations.

It is impossible to detail all the contents of the Select-sql statement here because of the space relationship. Readers are interested to see the introduction of the website of the "Structured Query Language (SQL)."

As a good data Processing language (DML), Xbase many commands, functions have data query function, we can talk about browse, list such commands can also find data, but this in the database system development how much role? So we don't talk about them.

First, we build a demo environment with the following command. Requirement: Find the value of COL2 when col1=9023.

Close all
CREATE TABLE ABC (COL1 i, COL2 i)
For I=1 to 10000?
INSERT into ABC (COL1, COL2) VALUES (I, RAND () *100000)
ENDfor
INDEX on COL1 TAG COL1
Use

Using the Locate command

Use ABC
LOCATE for col1=9023
IIF (FOUND (), MESSAGEBOX ("col2=" +str (COL2)), MESSAGEBOX ("Not Found")
Use

I think: Locate command speed is very slow, for example, the record pointer movement 9,022 times, if I have 100 million records? In order to solve this kind of problem, FoxPro proposed the Rushmore technology for the optimization for clause, but its essence still is indexing technology. To tell the truth. At first, intermediate users generally do not have the ability to optimize for clauses, we recommend that you use some of the high-speed query statements, such as: Seek command, Seek (), LOOKUP (), Indexseek () function. Before we discuss these high-speed query statements, I would also like to talk about the joint use of the Continue command and the locate command. In the Select-sql has not been introduced into the Xbase language system, we can only use Locate+continue for batch data query, I think now we must all use Select-sql for batch data retrieval, If so you will find that the previous multiline code has been replaced by a select-sql command.

Using the Seek command

Use ABC
Seek 9023 order TAG COL1
IIF (FOUND (), MESSAGEBOX ("col2=" +str (COL2)), MESSAGEBOX ("Not Found")
Use

Using the Seek () function

Use ABC
IIF (Seek (9023, "ABC", "COL1"), MESSAGEBOX ("col2=" +str (COL2)),;
MESSAGEBOX ("Not Found")
Use

Using the lookup () function

Use ABC
IIF (EMPTY (col2,9023,col1,col1)), MESSAGEBOX ("not Found"),;
MESSAGEBOX ("col2=" +str (COL2))
Use

Using the Indexseek () function

Use ABC
IIF (Indexseek 9023,. T., "ABC", "COL1"), MESSAGEBOX ("col2=" +str (COL2)),;
MESSAGEBOX ("Not Found")
Use

The above High speed query statement has the common characteristic, is must use the index, the high speed query can call the high speed the reason is uses the indexing technology. When we look at the command manual, we find the words "Cannot adopt Rushmore optimization technology". You may want to ask: Rushmore technology can improve query speed, these statements can not enjoy it, how fast? We have already pointed out in the previous article that the essence of Rushmore Technology is the index, which is to optimize the conditional statements that do not enjoy indexing technology, so that it can take advantage of the efficiency of indexing technology. Since high speed query statements are inherently based on indexing techniques, they do not need to be Rushmore optimized.

On the specific use of the above statement due to the length of the relationship can not be expanded, here is a brief introduction to the Indexseek () function bar!

Indexseek () is a newly added function in Visual FoxPro 6.0, which belongs to the Xbase language category. I think that with good it, you can forget the other high-speed query statements.

Indexseek (Eexpression,1movepointer,ctablealias,ctagname)

1. The parameter eexpression indicates that you want to search for an index keyword expression.

2. When the parameter is 1movepointer=.t, the pointer moves to a matching record after the record is found, and no pointer is found, which is similar to the Seek () function. 1movepointer=.f. When, means that even if you find the record and do not move the record pointer, this can greatly improve the efficiency of the query, of course, if you want to get the target row about the field information, this benefit will not be enjoyed.

3. The parameter Ctablealias represents the alias of the table.

4. The parameter ctagname represents an index mark.

Using the Sql-select statement

SELECT COL2 from ABC WHERE col1=19023 into ARRAY SZ
Use in ABC
IIF (_tally<>0,messagebox ("col2=" +str (SZ)), MESSAGEBOX ("Not Found")