db.query()

Read(471) Label: data source, sql, query, table sequence,

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.

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

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

Note:

The function performs the specified SQL query sql on the data source db and returns a table sequence composed of query results. db is the database connection object.

When there’s only one table following FROM in the SQL statement sql, the key of the result table sequence will be automatically set.

Parameters:

db

Database connection object.

sql

A SQL statement, like select * from table.

args

An argument value passed into the specified SQL command sql. It can be a constant or an expression. The comma is used to separate multiple arguments.

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.

dfx

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

Options:

@1

Return only the first-found record satisfying the condition, which is a single value if only one field is involved or a sequence if multiple fields are involved.

@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.

Return value:

A table sequence composed of results of executing sql.

Example:

 

A

 

1

=demo.query("select * from EMPLOYEE where DEPT=? ","Sales")

 

2

=demo.query@1("select * from EMPLOYEE where DEPT=? ","Sales")

[3,"Rachel","Johnson","F","New Mexico",1970-12-17,2010-12-01,"Sales",9000]; with @1 option, a sequence composed of field values of the first record is returned.

3

[1,2,3,4]

 

4

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

5

=demo.query@i(A3,"select SURNAME from EMPLOYEE where EID=?",~)

Return the result as a sequence

6

=demo.query@i("select DEPT from DEPARTMENT")

Return the result as a sequence

7

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

8

=connect("demo")

 

9

=A8.query@x("select DEPT from DEPARTMENT")

Close the database connection automatically when the execution is over.

10

=myproc.query("call hsql(?)",10)

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:

11

=myproc.query("call G://hsql(?)",10)

hsql.dfx is in the root directoy on drive G; same content and same result as shown in A10

Related functions:

db.execute()

db.proc()