Using SQL in esProc (I)

In esProc, we can use not only the SQL to retrieve data from databases, but also the preliminary database query results to perform further analyses and operations to solve some complicated problems which are difficult to deal with only with SQL. 

1. Database connection and disconnection
When using SQL to access databases, it should first connect to the designated database. Usually, there are two ways to connect to the database: direct connection in the data source manager or calling functions to connect in cellsets. 

In practice, the database accessed in the data source manager can be called by directly using data source name and are valid while the connection is on; for database accessed through functions, the connection objects it created will be stored as cell values,and it can be called by using cell names and are valid before the called connection objects close.

Similarly, there are two ways for database disconnection as well. Except for calling db.close() function shown in the above example,another way is to close the selected data source in data source manager:

2. Use of simple SQL

Using db.query() function can execute SQL orders in designated database.SQL statements may contain various query clauses and database functions.

A2 executes query on states whose abbreviations begin with N, and sort them by population in descending order. Results are as follows:

SQL statements can also use other data in cellsets as parameters:

A4 executes query on states whose abbreviations fall into designated sequences and sort them by area in ascending order. Results are as follows:

3. SQL that returns no results

If SQL that returns no results is used to access a database, like the use of SQL statements:create, update, delete and so on, then db.execute() function is needed in execution. Meanwhile, since it is not necessary to assign value to cells, expressions begin with ">" instead of "=". For example:

After statements in A2 modifies records in database table STATES, query results of A3 are as follows：

SQL that returns no results can use parameters, too: 

Statements in A4 restore modified records in database STATES to their original values, and query results of A5 are as follows:

4. Use of query results of SQL

Query results of SQL can be used in esProc to perform operations, like filtering, sorting and combination, etc.in order to increase query efficiency or deal with some complicated problems.

In the following examples, executions are performed by connecting to data source demo in data source manager and based on query results of cell A1:

For example, filtering data and searching data of states of designated abbreviations:

Also, aggregation computations can be performed on data. For example, count up the number of states whose abbreviations begin with C:

More significantly, we can group data in databases according to certain requirements, for example, group according to initials of abbreviations:

A2 executes grouping according to the initial of each state's abbreviation. Double-click and see details of each group’s data.

It is thus clear that, different from the SQL grouping method that doesn't provide real grouping and summarizing function, grouping with esProc is the real one, on which further computations can be performed. For example, select groups that contain three or more states, compute the total number and population of the states in these groups:

The final results of A4 are: