How esProc Assists Writing SQL Queries

esProc can simplify complex SQL-style queries using ordered sets, object-style access and stepwise computation. Usually there are two ways in which esProc is used: an independent one and one requiring integration with Java. Now let’s look at the first one through an example.  

The sales table stores several years of order data, based on which you need to calculate the link relative ratio of each month’s sales amount in a specified time period. Below is a selection of source data: 

Step 1: Connect to the database in esProc IDE

esProc allows connecting to multiple data sources at one time to perform hybrid computation. In this example MySQL is used to configure JDBC, as shown below:

Step 2: Implement the algorithm in esProc IDE

First retrieve data from the database according to a time period, in which begin and end are query parameters; then group data by years and months, and aggregate Amount; finally add a new field Irr (the link relative ratio by month) to A2. In the new field expression, mAmount represents sales amount of the current month and mAmount[-1] represents that of the previous month.

Step 3: Execute the script

The script execution requires assigning values to the parameters. The interface is as follows:

Click “OK” to complete the computation. Then you can see result of each step by clicking the corresponding cell. For instance, below is A2’s result:

A3 stores the final result, as shown below:

Note: Apart from executing all cells sequentially as the above shows, esProc provides a shortcut key for executing the current cell.

For a script that is already finished and stored, you can execute it using the command line or batch processing, without the need of running it via IDE every time.

Using the command line to execute scripts
You can use esprocx.exe to execute scripts, in a form like esprocx -R d:\Query1.dfx 2012-06-01 2014-06-30. -R option can display the computing result, the last cell by default. This is shown below:

See esProc Integration & Application: Command Line for details.

Besides being used alone, sometimes the result of an esProc script requires invocation by Java.

Java invocation

The Java main program can invoke the esProc script via JDBC, using the following code:

    Class.forName(“com.esproc.jdbc.InternalDriver”);

    con= DriverManager.getConnection(“jdbc:esproc:local://”);

    // call esProc script (which is similar to the stored procedure); Query1 is the name of the dfx file

    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call Query1 (?,?)”);

    java.util.Date dateBegin = new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-01-01”);

    java.sql.Date sqlDateBegin = new java.sql.Date(dateBegin.getTime());

    java.util.Date dateEnd = new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-12-31”);

    java.sql.Date sqlDateEnd = new java.sql.Date(dateEnd .getTime());

    st.setDate(1, sqlDateBegin);

    st.setDate(2, sqlDateEnd );

    // Execute the script

    st.execute();

    // Get the result set

    ResultSet rs = st.getResultSet();

    ……

esProc will return the last calculation cell by default, or it will return a certain cell using the return statement. The returned result is a ResultSet object in accordance with JDBC standards. The method of calling an esProc script is the same as that of accessing a database. Programmers who are familiar with JDBC can master it quickly.

The reporting tools that support JDBC interface can invoke an esProc script as well. For details, see esProc’s Assisting in Report Development: Integration Methods.

The above is the normal way of integrating an esProc script by Java. Now let’s move on to look at the special cases.

Simple script without a file

A simple esProc script can be written directly in the Java application, without the need of creating a script file. Note that when SQL is used, prepareStatementment uses the quotation mark as the parameter placeholder. But as the quotation mark is specially reserved for use in esProc expressions, you need to use the form of “arg1,arg2,arg3” to hold places sequentially. Here’s the code:

    ResultSet rs1 =(com. esproc.jdbc.InternalCStatement)con.prepareStatement(“$select * from sales3 where OrderDate>=? and OrderDate<=?;arg1,arg2\n =A1.groups(year(OrderDate):y,month(OrderDate):m;sum(Amount):mAmount)\n =A2.derive(mAmount/mAmount[-1]:lrr)”);

    java.util.Date  dateBegin  =  new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-01-01”);    

    java.sql.Date  sqlDateBegin  =  new java.sql.Date(dateBegin.getTime());

    java.util.Date  dateEnd  =  new SimpleDateFormat(“yyyy-MM-dd”).parse(“2010-12-31”);    

    java.sql.Date  sqlDateEnd  =  new java.sql.Date(dateEnd  .getTime());

    st.setDate(1, sqlDateBegin); 

    st.setDate(2, sqlDateEnd ); 

    st.execute();

Writing data to another database

It is not necessarily that a result set needs to be returned. For instance, if you want to update the Oracle database with A3, here’s the code:

myDB1 and orDB1 are different data sources. But with update function, you can update the summaryTable table in orDB1 with A3’s table sequence. In the table, the fields Year and Month correspond A3’s y and m respectively; and mAmount and Irr fields have counterparts of the same names in A3.

In this case, the code of integration by Java doesn’t need st.getResultSet() to get the result.

Big result set

Sometimes the computing result is too big to be held in the memory. In that case you can use an esProc cursor function to return it, and use JDBC flow to access Java. For example, tb1 is the big table in MySQL database, and tb2 is another big one in Oracle database. Both have the same structure but they store different data. Now you need to find records from them with the same IDs.

esProc code:

The cursor function can execute a SQL statement and return a cursor. The merge function can merge ordered data; its @x means merging cursors and @i means returning an intersection of the cursors.

A2 also returns a cursor, from which the Java main program cannot fetch data at one time. So you need to perform a batch fetching with the following code:

           st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call bigMerge ()”);

           st.setFetchSize(1000);//Set the number of records retrieved in each batch

           st.execute();

           ResultSet rs = st.getResultSet();

           while (rs.next()) {

                                     ……

                            }