query()
Here’s how to use query() functions.
d.query( dql,… )
Description:
Execute DQL query statement.
Syntax:
d.query(dql,…)
Parameter:
|
d |
DQL connection |
|
dql |
DQL query statement |
Return value:
Table sequence
Example:
|
|
A |
|
|
1 |
=dql("demo.glmd") |
Create DQL connection |
|
2 |
=A1.query("SELECT OrderID,ShipDate#Month,Amount FROM Orders") |
Execute DQL query statement |
db.query()
Description:
Execute a SQL statement on a data source and return a table sequence composed of the query results.
Syntax:
|
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 |
Note:
The function performs the specified SQL query sql on the data source db and returns a table sequence composed of query results.
When there’s only one table following FROM in the SQL statement sql, the key of the result table sequence will be automatically set.
The function supports simple SQL.
Parameter:
|
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 |
Option:
|
@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 |
Return value:
Single value, table sequence, pure sequence or pure table sequence
Example:
With a simple SQL query statement
|
|
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 |
When the SQL statement contains parameters
|
|
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 3rd parameter
|
Perform SQL query according to sequence A
|
|
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:
|
Use call splx() syntax in the SQL query statement
t1.dfx is as follows:
|
|
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 |
When parameter sql is simple SQL
|
|
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 |
Return result of other types
|
|
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 a single value Emily |
Related function:
Description:
Find a certain field of a specified row in a table sequence/in-memory table.
Syntax:
k.r(T,F)
Note:
The function finds F field of row k in table sequence/in-memory table T, which is equivalent to T(k).F. Won’t check whether k is within T’s length.
Parameter:
|
k |
Row number |
|
T |
A table sequence/in-memory table |
|
F |
A field of T; can be omitted |
Return value:
A record
Example:
|
|
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 |
