fetch()

Read(1743) Label: fetch,

Here’s how to use fetch() function.

cs .fetch()

Description:

Open a cursor or a cluster cursor and fetch records from it.

Syntax:

cs.fetch(n;x)

Note:

The function opens cursor/cluster cursor cs and retrieves records from it.

 

When parameter n is present, the function reads n records; when parameter x is present, fetch records continuously until the value of expression x is changed (in this case x isn’t a logical value) or expression x becomes true (in this case x is a logical value);

 

Only one of the parameters n and x is valid. When both n and x are omitted, the function fetches all existing records in cs and then closes the cursor.

 

The function returns the fetching result as a sequence/record sequence/table sequence or returns null if the cursor moves to the end.

The function is often used to retrieve a large amount of data in batches.

Parameter:

cs

A cursor/cluster cursor

n

A positive integer

x

Grouping expression, according to which cs is sorted. With x, n will be ignored

Option:

@0

Won’t actually fetched out the returned data from the cursor. The option enables an action functionally equivalent to copying the data; it doesn’t support parameter x.

@x

Close the cursor after data is fetched.

Return value:

   A sequence/record sequence/table sequence

Example:

cs is a cursor and when parameter n is present:

 

A

 

1

=connect("demo").cursor("select top 10 EID,NAME,DEPT,SALARY from employee")

There are 10 records in the returned cursor.

2

=A1.fetch(3)

As parameter n is 3, the function fetches 3 records from cursor A1 (Fetch data in batches when there is a huge amount of data in the cursor in real-world businesses):

3

=A1.fetch(5)

Continue to fetch data from cursor A1; as A2 already fetches 3 records, here the fetch function fetches 5 records beginning from the 4th one:

3

>A1.close()

Close the cursor.

 

cs is a cursor, and when parameter x is present and x isn’t a logical value:

 

A

 

1

=connect("demo").cursor("select top 20 EID,NAME,DEPT,SALARY from employee").sortx(DEPT)

Data in the returned cursor is ordered by DEPT field.

2

=A1.fetch(;DEPT)

Parameter x is DEPT and the fetch function fetches records until DEPT value is changed; below is the returned result:

3

=A1.fetch()

As no parameters are present, the fetch function fetches all existing records in the cursor and closes the cursor:

4

=A1.fetch()

As A3 already fetches all records out of the cursor and closes the cursor, A4 returns null.

 

cs is a cursor, and when parameter x is present and x is a logical value:

 

A

 

1

=connect("demo").cursor("select top 15 EID,NAME,DEPT,SALARY from employee").sortx(-SALARY)

Data in the returned cursor is ordered by SALARY field in descending order.

2

=A1.fetch(2;SALARY<10000)

As parameter x is present, ignore parameter n and the fetch function fetches records from the cursor until the result of SALARY<10000 is true; below is the returned result:

3

=A1.fetch()

Fetch the rest of records in the cursor and close the cursor:

 

When cs is a cluster cursor:

 

A

 

1

[192.168.0.110:8281,192.168.18.143:8281]

A sequence of nodes.

2

=file("emp.ctx":[1,2], A1)

1.emp.ctx on node 192.168.0.110 contains records where GENDER field values are F; 2.emp.ctx on node 192.168.18.143 contains records GENDER where GENDER field values are M.

3

=A2.open()

Open A2’s cluster homo-name file group.

4

=A3.cursor()

Return a cluster cursor.

5

=A4.fetch()

Open A4’s cluster cursor, fetch all records, and close the cursor.

 

With @0 option, the returned data won’t be fetched from the cursor:

 

A

 

1

=connect("demo").cursor("select top 10 EID,NAME,DEPT,SALARY from employee")

Return a cursor where there are 10 records.

2

=A1.fetch@0(3)

With @0 option, fetch function reads 3 records in cursor A1 but does not fetch the out:

3

=A1.fetch()

Read the rest of records from cursor A1 and all records are fetched out now; fetch function returns 10 records:

 

With @x option, close the cursor after data is fetched out:

 

A

 

1

=connect("demo").cursor("select top 10 EID,NAME,DEPT,SALARY from employee")

Return a cursor where there are 10 records.

2

=A1.skip(5)

Skip 5 records in cursor A1.

3

=A1.fetch@x(3)

Fetch 3 recrods form cusor A1; as A2 skips 5 records, here fetch function fetches 3 records beginning from the 6th one:

With @x option, fetch function closes the cursor after it finishes fetching data out.

4

=A1.fetch()

As A3 closes the cursor, fetch function returns null.

Related function:

db.cursor()

cs.skip()

ch .fetch()

Description:

Fetch and store the existing data in a channel.

Syntax:

ch.fetch(f)

Note:

The function fetches and stores the existing data in channel ch; write result of data retrieval to bin file f when parameter f is present. It is a result set generation function.

Parameter:

ch

Channel

f

A bin file

Return value:

Channel

Example:

 

A

 

1

=demo.cursor("select * from SALES")

 

2

=channel()

Create a channel.

3

=channel()

 

4

=A2.select(ORDERID>100)

Attach ch.select() operation to the channel.

5

=A3.select(ORDERID>100)

 

6

=A2.fetch()

Attach ch.fetch() function that returns the final result to A2’s channel while retaining the exisitng data in the channel.

7

=A3.fetch("sales.btx")

Attach ch.fetch() function that gets the final result set to A3’s channel, and define writing result to bin file sales.btx in the main directory.

8

=A1.push(A2,A3)

Push A1’s data in into the channel.

9

=A1.fetch()

 

10

=A2.result()

Get the result of performing the operation from the channel.

mcs .fetch()

Description:

Get records from a multicursor.

Syntax:

mcs.fetch()

Note:

The function gets all records from a multicursor. The order of the resulting records could be different from their orginal order.

Parameter:

mcs

Multicursor

Return value:

Record sequence

Example:

 

A

 

1

=file("D:/txt_files/orders.txt").cursor@m()

Return a multicursor.

2

=A1.fetch()

Fetch records from a multicursor.

3

=file("D://tb1.txt").import()

tb1.txt contains 10w rows.

4

=A3.cursor()

Generate an ordinary cursor.

5

=A4.fetch(15000)

Fetch the first 15000 rows.

6

=A3.cursor@m(10)

Generate a multicursor.

7

=A6.fetch(15000)

Fetch the first 15000 rows.

Here the fetched records are different from those in A5. With a multicursor, data is fetched from each cursor and then unioned; while with an ordinary cursor, data is sequentially fetched.