db.query()

db.query( sql {,args …})

db.query(A,sql{,args …})

Perform sql based on A and return a table sequence composed of query results. Here, args can be an expression evaluated based on each member of A. This type of syntax can work with @idx options

db

Data source connection, including esProc-JDBC source

sql

A sql query or a call dfx() statement;

When parameter is “call dfx()”, the syntax means calling the esProc cellset file through esProc-ODBC data source db – the file format is dfx and file path can be a relative one, which is relative to the search path or an absolute one, and return result set of the cellset file 

args

 The parameter passed to the SQL query; it can be a parameter value or the name of a defined parameter; separate multiple parameters by comma

A

A sequence; sql is executed on each member of A; Generally, args is computed against each member of A and the value is passed to sql for execution

@1(sql {,args …})

Return only the first-found record satisfying the condition, which is a single value or a sequence

@i

Return a sequence if the result set has only one column

@d

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

@x

Close the database connection automatically when the statement finishes execution. Only valid with the database connection established through the connect function

@v

Return a pure sequence or a pure table sequence

A

1

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

Parameter db is data source name. This requires that demo is already connected successfully. The statement queries all data in SCORES table.

2

=connect("demo")

3

=A2.query@x("select * from SCORES")

Use connect connection mode; in this case, @x option can be used.

4

=A2.query("select * from STUDENTS")

Under normal circumstance, error is reported Data Source demo is shutdown or wrong setup., which promps you that the data source isn’t started. The error appears because database connecction is automatically closed when A3’s computation finishes.

A

1

>arg2="R&D"

Define parameter name as arg2 and parameter value as "R&D".

2

=demo.query("select EID,NAME,DEPT,GENDER from employee  where  EID<? and DEPT=?  and  GENDER=?",arg1,arg2,"M")

Query employee table; arg1 is a cellset parameter whose value is 100; arg2 is the parameter defined in A1; parameter value “M” is directly passed to the 3^rd parameter.

A

1

[1,2,3,4]

2

=demo.query(A1,"select EID,NAME from EMPLOYEE where EID=?",~*~)

Compute parameter value according to sequence A1, which is [1,4,9,16], pass the value to SQL statement and return result:

A

1

=power(a,b)

a and b are cellset parameters in t1.dfx.

A

1

=connect("MyOdbc").query("call D:/t1(?,?)",2,3)

Call t1.dfx and pass parameters to it – value of parameter a is 2 and that of parameter b is 3, and return result 8;

MyOdbc is esProc-ODBC data source name.

A

1

=connect().query("select * from D:/cities.txt")

When data source name is absent, parameter sql is simple SQL that queries cities.txt data file.

A

1

=demo.query("select NAME from  STUDENTS")

Query NAME field of STUDENTS table from demo data source and return result as follows:

2

=demo.query@i("select  NAME from  STUDENTS")

As result set contains only one column, use @i option to return a sequence:

3

=demo.query@v("select * from  STUDENTS")

Return a pure table sequence.

4

=demo.query@iv("select  NAME from  STUDENTS")

Return a pure sequence.

5

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

Use @d option to convert numeric type data to double type.

6

=demo.query@1("select NAME from  STUDENTS")

Return single value Emily.

k

Row number

T

A table sequence/in-memory table

F

A field of T; can be omitted

A

1

=demo.query("select EID,NAME from employee")

2

=1.r(A1,NAME)

Get the Name value in the first row of A1’s table sequence.

3

=1.r(A1)

Since parameter F is absent, list all field values of the first row.