How to simplify nested SQL in applications with esProc

In data analysis or reporting applications, various complex SQL statements are sometimes encountered: nested in multiple layers, self-join, dynamically transposing, …

Here are some exmaples:

From SQL to SPL：Create columns from distinct values of a column

From SQL to SPL: Statistics by time window

From SQL to SPL: Align the existing data to the corresponding position and fill in any missing data with 0

For the same task, the code of esProc SPL is simpler and easier to understand than SQL.

Next, let's try how to integrate esProc in applications and simplify these complex SQL statements.

Download esProc first, recommend standard edition: https://www.esproc.com/download-esproc/

After installing esProc, try to see if the esProc IDE can access the database normally. First, place the JDBC driver of the database in the directory "[installation directory] \ common \ jdbc", which is one of the class paths of esProc. For example, JDBC for PostgreSQL:

Start esProc IDE, find the menu "Tool ->Connect to Data Source", create a new JDBC data source, fill in the specific database connection information, and note that the data source name will be used in future code.

Return to the data source dialog and connect to the data source that was just configured. If the data source name turns pink, it indicates successful configuration.

Create a new script in the IDE, write SPL statements, connect to the PostgreSQL database, and load the data from the first example using simple SQL:

=connect("pg16").query@x("select * from ventas")

Press ctrl-F9 to execute, and you can see the execution result on the right side of the IDE, presented in the form of a data table, which is very convenient for debugging SPL code.

After simply loading data from the database, SPL can be used to simplify complex SQL. For example, the complete code for the first example is:

=connect("pg16").query@x("select * from ventas where month>=? and month<=?",arg1,arg2).pivot@s(month;product,sum(amount))

First filter with parameters, then transpose the grouped summary, and the result is on the right:

The first example has less code and can be written entirely in one cell. Some operations have more code and are suitable for step-by-step writing in multiple cells for easy debugging. For example, the second example: Statistics by time window.

	A
1	=connect("pg16").query@x("select * from main where time>? and time<=?",arg1,arg2)
2	=A1.run(time=time@m(time))
3	=list=periods@s(A2.min(time),A2.max(time),60)
4	=A2.align@a(list,time)
5	=A4.new(list(#):start, elapse@s(start,60):end, sv=ifn(end_value[-1],.value):start_value, ifn(.m(-1).value, sv):end_value, ifn(.min(value),sv):min, ifn(.max(value),sv):max)

Save the above script as a file, such as D:\data\mainProc.splx. After running it, you can see the result:

After debugging in the IDE, configure the Java application environment. Find the esProc JDBC related jar files from the directory "[Installation directory]\esProc\lib": esproc-bin-xxxx.jar, icu4j_60.3.jar.

Deploy these two jars to the class path of the Java development environment. Find the esProc configuration file raqsoftConfig. xml from the directory "[Installation directory]\esProc\config" and deploy it to the Java development environment's class path.

The configuration item to be changed in the configuration file is mainPath, which represents the default path for scripts and other files. Note that the information of the data source is also in the configuration file. Next, you can write Java code to execute SPL through esProc JDBC. Let's try Example 2 first:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call mainProc(?,?)");
st.setTime(1,Time.valueOf("10:00:00"));
st.setTime(2,Time.valueOf("11:00:00"));
ResultSet rs = st.executeQuery();

After running, you can see the result:

As can be seen, the process of esProc JDBC calling SPL scripts is the same as that of database JDBC calling stored procedures. Example 1 has relatively short code, so you can directly embed SPL code into Java without saving the code in a script file.

PreparedStatement st = con.prepareStatement("=connect(\"pg16\").query@x(\"select * from ventas where month>=? and month<=?\",?,?).pivot@s(month;product,sum(amount))");
st.setInt(1,2);
st.setInt(2,4);
ResultSet rs = st.executeQuery();

After execution, you can see the calculation result:

As can be seen, the process of embedding SPL into Java is the same as embedding SQL into Java. SPL scripts can be converted into SPL code, for example, in Example 2, first open the script file in the IDE, select cells A1-A5 with code, and then click the menu "Edit ->Copy ->Code copy". This converts the grid code of multiple rows and columns into single line SPL code, which is temporarily stored in the clipboard.

Copy the converted SPL code into Java code, replace parameter names with question marks, and execute. The result is as follows:

There are many examples of simplified SQL on the esProc official website that database programmers should not miss.