cursor()

dfx

Cellset file name

…

dfx’s parameter

@c

The dfx can be represented by a cell that corresponds to a subroutine defined by func() function

A

B

1

for arg1

2

=connect("demo").query("select * from GYMNASTICSWOMEN where ID=?",A1)

3

=B2.derive(avg(VAULT,UNEVENBARS,BALANCEBEAM,FLOOR):Average)

4

return B3

Perform a loop computation and return the result sets

A

1

=[5,10,20,25]

2

=cursor("test.dfx",A1)

Cll test.dfx, pass A1’s sequence as the value of parameter arg1, and return result as a cursor

3

=A2.fetch()

Fetch records from the cursor; the result is the union of returned result sets by the loop computation of test.dfx

4

=call("test.dfx",A1)

Use call function to call test.dfx but only the result set of the first loop is returned 

A

B

1

func

2

return A1.sum()

3

=cursor@c(A1,[2,5,7])

Call the code block whose master cell is A1

4

=A3.fetch()

14

cs

A cursor

…

A code block

A

B

1

=to(100000).new(rand(1000):f1,rand(1000):f2).cursor()

Generate a cursor

2

cursor A1

=A2.groups(f1:count(1))

Traverse cursor A1, calculate A2’s channel in B2’s code block and write the result to A2

3

cursor

=A3.groups(f2:count(1))

Same as the above

A

A sequence

k

Segment number

n

The number of data segments; retrieve all data out when both k and n are omitted

@m

With this option:

A.  cursor@m(n) returns a multicursor; n is the number of segments; use the max number of parallel tasks defined in the designer as the number of segments if n is absent

B.  cursor@m(mcs,K:K’,...) divides an ordered sequence into several segments according to segmenting key K:K’ in sync with ordered multicursor mcs and returns them as a multicursor, too.

A

1

=demo.query("select * from SCORES")

2

=A1.cursor(1:3)

Divide the cursor generated from A1 into 3 segments, and retrieve the first one to return

3

=A1.cursor@m(3)

Return a multicursor

4

=demo.query("select * from STUDENTS").sort(ID)

Sort the table sequence by ID

5

=demo.query("select * from STUDENTS1").sort(ID).cursor@m()

Sort the table sequence by ID and return it as a multicursor

6

=A4.cursor@m(A5,ID:ID)

Divide A4’s table sequence into multiple segments by ID and return them as a multicursor

db. cursor(sql {,args …})

db.cursor(call dfx(){,args …})

Connect to esProc-ODBC data source db to call the specified dfx file, and return result as a cursor

db

Database connection

sql

A SQL statement, like select * from table.

args

If there are parameters in the SQL, they must get assigned; their values can be actual ones or args specified in query statement

Note: Parameters shall be separated by commas

dfx

A cellset file; can be specified using a relative path, which is relative to the search path, or an absolute path

@i

If the result set has only one column, the content of the returned cursor is a sequence

@d

Convert the numeric data type to the double data type, instead of the decimal data type

@x

Disconnect from the database automatically when the cursor is closed. This option applies to the database connection with connect only

A

B

C

1

=connect("demo")

2

=A1.cursor("select * from SCORES")

Return a cursor for retrieving data

3

=create(CLASS,STUDENTID,SUBJECT,SCORE)

Create a table sequence

4

for

5

if A4==1

=A2.skip(5)

When the loop number is 1, jump five rows forward

6

=A2.fetch(3)

Fetch three records from cursor A2 at once

7

if B6==null

If B6 is empty, break the loop

8

Break

9

else

10

>A3.insert(0:B6,CLASS,STUDENTID,SUBJECT,SCORE)

Insert the records in B6 into A3

11

=A1.cursor@ix("select NAME from STUDENTS")

Use @x option to automatically disconnect from the database when the cursor is closed

12

=A11.fetch()

The content of the cursor is a sequence

13

=A1.cursor@i("select NAME from STUDENTS")

Error message saying Data Source demo is shutdown or wrong setup suggests that the data source hasn’t been enabled appears. This is because database connection is automatically closed when A11’s cursor is closed

14

=mysql.cursor@d("select * from ta")

15

=A14.fetch()

16

=myproc.cursor("call hsql(?)",10).fetch()

hsql.dfx is in the search path. Below is the file:

myproc is the esProc-ODBC data source name; the parameter value is 10. Below is the result:

17

=demo.cursor("select * from dept  deptid<? and father=?",arg1,arg2)

arg1 and arg2 are cellset parameters, whose default values are set as 10 and 1

18

=A17.fetch()

f.cursor()

f.cursor(F_i:type,…; k:n,s)

f

File object, only supporting the textual file object

F_i

Fields to be retrieved; all fields will be retrieved by default. The to-be-retrieved field(s) can be represented by their sequence numbers headed by the sign #

type

Field types, including bool, int, long, float, decimal, string, date, time and datetime. Data type of the first row will be used by default

s

User-defined separator. The default is tab. When the parameter is omitted, the comma preceding it can be omitted, too

k

The segment number

n

The number of segments. Retrieve the whole file when both k and n are omitted

@t

Use the first row of f as the field names. If omitted, use_1,_2,… as the default field names

@b

Retrieve data from the exported binary file, with the support for parameters F_i, k and n, and with no support for parameters type and s. Ignore options @t, @s, @i, @q and @m. The segmental retrieval could result in empty segment in cases when a file has only a very small number of records

@e

Make the function return null when F_i doesn’t exist in the file; raise an error when the option is absent 

@x

Delete the source file automatically on closing the cursor

@s

Not split the to-be-retrieved field when it is imported as a cursor whose content is a table sequence consisting of strings of a single field; in this case the parameters will be ignored

@i

If the result set has only one column, the content of the returned cursor will be a sequence

@q

Remove the quotation marks, if any, from the field strings, including the field names, in the first place, and handle escape sequences

@c

Use comma as the separator when the parameter s is absent, but the user-defined separator s should take priority when there is one

@m

With the option, the f.cursor@m(Fi:type,…;n,s) function returns a multicursor;  parameter n is the number of segments; use the max number of parallel tasks defined in the designer as the number of segments if the option is absent

@o

Use quotation marks as the escape character

@k

Retain the white space on both sides of the data item; without it a trim operation will be automatically performed

@d

Delete a record if it contains unmatching data types or data formats and start examining data by type, or if the parentheses and the quotation marks in it do not match when @p option and @q option respectively are present

@n

Ignore and discard rows whose number of columns don’t match the first row

@v

@w

Verify data type matching, and, if error reports, throw an exception, terminate the execution and output the content of the problem record

Read each row, including the column headers row, as a sequence and return a cursor of sequence made up of sequences

@a

Treat single quotes as quotes, including scenarios where the option cooperates with @q option; single quotes are left not handled when both options are absent

@p

Enable handling the matching of parentheses (not including the separators within the parentheses) and quotes, as well as the escape sequences outside of the quotes

@f

Split the file content into a string by the separator without parsing

@l

Allow line continuation if there is an escape character \ at the end of the line

A

B

C

1

=file("D://Student.txt").cursor@tx()

Return the cursor for retrieving data, take the record in the first row as field names, and delete the file automatically when closing the cursor.

2

=create(CLASS,STUDENTID,SUBJECT,SCORE)

Construct a new table sequence

3

for

4

if A3==1

=A1.skip(5)

If the number of loop is 1, then skip 5 consecutive rows.

5

=A1.fetch(3)

Retrieve data from cursorA1, 3 rows each time

6

if B5==null

Jump out from the loop when B5 is null

7

Break

8

else

9

>A2.insert(0:B5,CLASS,STUDENTID,SUBJECT,SCORE)

Insert records in B5 into A2

10

=file("D://Department.txt").cursor@t(Dept,Manager;,"/")

=A10.fetch()

Below is Department.txt:

Contents of Department. txt are separated with the slashes and read out according to the specified fields of DEPT and MANAGER

11

=file("D://Department5.txt").cursor@t(;1:2)

=A11.fetch()

With F_i and s omitted, the function separates the cursor into 2 segments and retrieves the first one

12

=file("D:// EMPLOYEE. btx").cursor@b(GENDER;1:2)

=A12.fetch()

Retrieve the GENDER field of the bin file, EMPLOYEE.btx(a segmented binary file) exported through f.export@z(); by default the exported binary file includes field names .

13

=file("D://EMPLOYEE.btx").cursor@b(;1:2)

=A13.fetch()

Retrieve the segmented binary file, EMPLOYEE1.btx, exported by f.export@b(), divide the file contents into 2 parts, and get the first part

14

=file("D://Department.txt").cursor@ts()

= A14.fetch()

Won’t split fields, the cursor contains a table sequence consisting of a single string field

15

=file("D://StuName.txt").cursor@i()

=A15.fetch()

StuName.txt is a file containing only one field, so the content of the cursor is a sequence

16

=file("D://EMPLOYEE1.txt").cursor@tc()

=A16.fetch()

Retrieve the first segment of GENDER field of comma-seperated EMPLOYEE1.txt

17

=file("D://Department3.txt").cursor@e(EID)

=A17.fetch()

Return null since EID field can’t be found in Department3.txt; without @e option, error will be reported, saying EID: field is not found

18

=file("D://Department2.txt").cursor@tq(;,"|")

=A18.fetch()

Below is Department2.txt:  

B19’s result:

19

=file("D://Department.txt").cursor@tm(DEPT:string,MANAGER:int;3,"/")

The cursor is devided into 3 segments and the result of A19 is returned as a multicursor

20

=file("D://Sale1.txt").cursor(#1,#3)

=A20.fetch()

Below is the Sale1.txt file:

 Below is the result of B20:

21

=file("D:/Dep3.txt").cursor@qo()

=A21.fetch()

Here is the Dep3.txt file:

With @o option, two double quotation marks are treated as one and return the result as follows:

22

=file("D:/Dep1.txt").cursor@k()

=A22.fetch()

Retain the whitespace characters on both sides of the data item

23

=file("D:/Department1.txt").cursor@t(id:int,name;,"|")

=A23.fetch()

Return id and name fields of Department1.txt

24

=file("D:/Department1.txt").cursor@td(id:int,name;,"|")

=A24.fetch()

Delete a record containing unmatching data types; rows where id value is a are deleted

25

=file("D:/Department1.txt").cursor@tv(id:int,name;,"|")

=A25.fetch()

Verify data type matching, and, and if error reports, throw an exception, terminate the execution and output the content of the problem record; data type doesn’t match with rows where id value is a

26

=file("D:/Dep2.txt").cursor@tdn(id:int,name,surname;,"|")

=A26.fetch()

Here’s the file Dep2.txt:

Ignore row 6 and row 8 because the number of columns don’t match that in row 1

27

=file("D:/Desktop/DemoData/txt/City.txt").cursor@w()

=A27.fetch()

Use @w option to read each row as a sequence and return a cursor of sequence whose members are sequences

28

=file("D://t1.txt").cursor@c()

=A28.fetch()

Below is t1.txt:

With @c option, use the comma as the default separator and return result as follows:

29

=file("D://t1.txt").cursor@cp()

=A29.fetch()

With @p option, parentheses and quotation marks matching will be handled during parsing

30

=file("D://t1.txt").cursor@cpa()

=A30.fetch()

With @a option, single quotes are treated as quotes

31

=file("D://t2.txt").cursor@l()

=A31.fetch()

Below is t2.txt:

With @1 option, allow line continuation when there is an escape character at the end of the line

32

=file("D://t3.txt").cursor@f()

=A32.fetch()

With @f option, just splt the file as a string using the separator

@m

The T.cursor@m(x:C…;wi,...;n) function with this option generates a multicursor segmented into n parts. n is an integer; the function returns an ordinary cursor if n<2; use the max number of segments set in【Tool】-【Options】if n is absent

T

An entity table or an in-memory table

x

Expression

C

Column alias; can be omitted

wi

Filtering condition; multiple conditions are separated by comma(s) and their relationships are AND.

You can use the following three types of syntax in a filtering condition:

1. K:Ti  K is a field in the entity table; Ti is a table sequence whose primary key is K. The expression filters away records where K=Ti.find(K) is false. When K is not a selected field to be output, just do the filtering as usuall.

2.K:Ti:null  Filter away all records that can be found.

3.K:Ti:#  K=Ti(K); locate corresponding records using sequence numbers and delete those out-of-range ones and those where Ti(K) is false or null.

k

A positive integer (k≤n) representing the k^th segment

n

A positive integer representing the number of segments

A

1

for 100

2

=to(10000).new(#:k1,rand():c1).sort@o(k1)

Return a table sequence

3

=to(10000).new(#:k1,rand(10000):c2,rand()*1000:c3).sort@o(k1)

Return a table sequence

4

=A2.cursor()

Return a cursor

5

=A3.cursor()

Return a cursor

6

=file("D:\\tb4.ctx")

Generate a composite table file

7

=A6.create(#k1,c1)

Create A6’s base table whose key is k1

8

=A7.append(A4)

Append data of A4’s cursor to A7’s base table

9

=A7.attach(table4,c2,c3)

Create an attached table table4 for the base table

10

=A9.append(A5)

Append data of A5’s cursor to attached table table4

11

=A9.cursor(;c2<1000;2:3)

Divide records meeting c2<1000 in the attached table into 3 segments and return a cursor of all columns of the second segment

12

=A11.fetch()

Fetch data from A11’s cursor

13

=A7.cursor(;c1>0.99)

Get records meeting c1>0.99 from A7’s base table

14

=A13.fetch()

Fetch data from A13’s cursor

b

15

=A9.cursor(k1,c1:b,c3;c3>999)

Get the base table’s k1 field and c1 field from attached table table4, as well as c3 field of the attached table, according to condition c3>999, and rename c1 b

16

=A15.fetch()

Fetch data from A15’s cursor

17

=A9.cursor@m(;;3)

Use @m option to generate a multicursor from attached table table4

18

=create(k1,k2).record([1,"aa",2,"bb",3,"uh",6,"kkj",7,"fdf",10,"wwwww"]).keys(k1)

Return a table sequence whose primary key is k1

19

=A6.open()

Open composite table tb4.ctx

20

=A19.cursor(;k1:A18;1:3).fetch()

Get k1 field and c1 field from the base table according to condition that k1 is contained in corresponding k1 field in A18’s table sequence

21

=A19.cursor(;k1:A18:null;1:3).fetch()

Get k1 field and c1 field from the base table according to condition that k1 isn’t contained in corresponding k1 field in A18’s table sequence

22

=A19.cursor(;k1:A18:#;1:3).fetch()

Get k1 field and c1 field from the base table according to condition k1=A18(k1)

@m

The function with this option generates a cluster multicursor synchronously segmented into n parts and splits a duplicate table on two levels. n is a positive integer representing the number of segments to be divided; the function returns an ordinary cursor if n<2; use the max number of segments set in【Tool】-【Options】if n is absent.

T

A cluster entity table/cluster memory table

x

Expression

wi

Filtering condition; multiple conditions are separated by comma(s) and their relationships are AND.

You can use the following three types of syntax in a filtering condition:

1. K:Ti  K is a field in the entity table; Ti is a table sequence whose primary key is K. The expression filters away records where K=Ti.find(K) is false. When K is not a selected field to be output, just do the filtering as usuall.

2.K:Ti:null  Filter away all records that can be found.

3.K:Ti:#  K=Ti(K); locate corresponding records using sequence numbers and delete those out-of-range ones and those where Ti(K) is false or null.

A

1

=file("D:/tb4.ctx":[1],"169.254.121.62:8281")

Open a cluster table file

2

=A1.open()

Return a cluster composite table

3

=A2.attach(table3)

Retrieve cluster entity table table3

4

=A2.cursor()

Return a cluster cursor

5

=A3.cursor(NAME,GENDER;EID<6)

 Return NAME field and GENER field where EID<6 as a cluster cursor

6

=file("tb5.ctx":[2],"192.168.31.72:8281")

7

=A6.open()

8

=A7.cursor@m(;;3)

Generate a cluster multicursor that is segmented into 3 parts

9

=A7.cursor()

10

=A9.memory()

Generate a cluser memory table

11

=A10.cursor()

Return a cluster cursor

T

An entity table

x

Expression

C

Column alias

wi

Filtering condition; multiple conditions are separated by comma(s) and their relationships are AND.

You can use the following three types of syntax in a filtering condition:

1. K:Ti  K is a field in the entity table; Ti is a table sequence whose primary key is K. The expression filters away records where K=Ti.find(K) is false. When K is not a selected field to be output, just do the filtering as usuall.

2.K:Ti:null  Filter away all records that can be found.

3.K:Ti:#  K=Ti(K); locate corresponding records using sequence numbers and delete those out-of-range ones and those where Ti(K) is false or null.

mcs

A multicursor generated from an entity table

@k

Perform matching using the multicursor’s first field of the key

A

1

for 100

2

=to(10000).new(#:k1,rand():c1).sort@o(k1)

Return a table sequence

3

=to(10000).new(#:k1,rand(10000):c2).sort@o(k1)

Return a table sequence

4

=to(10000).new(#:k1,rand()*1000:c3).sort@o(k1)

Return a table sequence

5

=A2.cursor()

Return a cursor

6

=A3.cursor()

Return a cursor

7

=A4.cursor()

Return a cursor

8

=file("D:\\cs1.ctx")

Generate a composite table file

9

=A8.create(#k1,c1)

Create the composite table’s base table

10

=A9.append(A5)

Append data in A5’s cursor to the base table

11

=A9.attach(table1,c2)

Create attached table table1 on base table

12

=A11.append(A6)

Append data in A6’s cursor to table1

13

=A11.cursor@m(;;2)

Segment the attached table and return a multicursor

14

=A9.attach(table2,c3)

Create attached table table2 on base table

15

=A14.append(A7)

Append data in A7’s cursor to table2

16

=A14.cursor(;;A13)

Segment table2 according to A13’s multicursor

T

A cluster entity table

x

Expression

wi

Filtering condition; multiple conditions are separated by comma(s) and their relationships are AND.

You can use the following three types of syntax in a filtering condition:

1. K:Ti  K is a field in the entity table; Ti is a table sequence whose primary key is K. The expression filters away records where K=Ti.find(K) is false. When K is not a selected field to be output, just do the filtering as usuall.

2.K:Ti:null  Filter away all records that can be found.

3.K:Ti:#  K=Ti(K); locate corresponding records using sequence numbers and delete those out-of-range ones and those where Ti(K) is false or null.

mcs

A cluster multicursor generated from a cluster entity table

A

1

=file("cs1.ctx":[1],"169.254.121.62:8281")

2

=A1.open()

Open a cluster composite table

3

=A2.attach(table1)

Retrieve cluster entity table table1

4

=A3.cursor@m(;;2)

Return a cluster multicursor

5

=A2.attach(table2)

Retrieve cluster entity table table2

6

=A5.cursor(;;A4)

Segment cluster entity table table2 according to table1’s multicursor; the two tables should use same segmenting field

mcx

A memory table/a cluster memory table

n

Subcursor column name

A

1

=file("D:\\dept2.txt").cursor@m()

Retrieve dept2.txt as a multicursor

2

=A1.fetch()

Fetch records from A1’s multicursor

3

=A2.cursor().groups(#3:FATHER;count(#1):TOTAL)

Merge the multicursor into a common cursor and perform grouping & aggregation