June 30, 2015

Segmented Data Exports to Text Files of Specified Format in esProc



Below is a selection from a database table, TB1:

Based on this data, you need to generate text files of the specified format and with the following names (yyyyMMddHH) by the hour.
2011101106.txt
1|2011-10-11 6:24:21|aaa
2|2011-10-11 6:30:45|dddd

2011101107.txt
3|2011/10/11 7:10:12|dsf
4|2011/10/11 7:50:38|dffew
5|2011/10/11 7:59:59|dfae

2011101108.txt
6|2011/10/11 8:00:00|edfae

After grouping data by the hour, you export every group to a text file according to the specified format (|) and name it after the hour: 

A1: Use a SQL to create a database cursor sorted by CreateTime. db represents the connection to a database.
A2-B2: Group data by CreateTime (yyyyMMddHH), and fetch data by loop, one group each time, to export, through B2, to text files named after the create time. And specifiy format for the text data at the same time.

Text files are as follows: 

June 28, 2015

esProc Exports Data by Groups


A selection from the original table (TB1)

MONTH  ID   COMPANY    DEPARTMENT     JOINTIME     PROGROUP
201305    1009       1     A     A1   201108    DG
201305    1009       1     B     B1   201207    PG
201305    1009       1     C     C1   201301    SG
201305    1009       1     D     D1   201109    SEG
201305    1013       2     C     C2   201302    SG
201305    1027       2     A     A3   201007    DG

Note: The table has a great amount of data volume with 4 million rows of data.
You need to export data into several TXTfiles according to different values in PROGROUP column. Based on the above data, the expected result is as follows:

DG.txt
MONTH  ID   COMPANY    DEPARTMENT     JOINTIME     PROGROUP
201305    1009       1     A     A1   201108    DG
201305    1027       2     A     A3   201007    DG

PG.txt
MONTH  ID   COMPANY    DEPARTMENT     JOINTIME     PROGROUP
201305    1009       1     B     B1   201207    PG

SEG.txt
MONTH  ID   COMPANY    DEPARTMENT     JOINTIME     PROGROUP
201305    1009       1     D     D1   201109    SEG

SG.txt
MONTH  ID   COMPANY    DEPARTMENT     JOINTIME     PROGROUP
201305    1009       1     C     C1   201301    SG
201305    1013       2     C     C2   201302    SG

You can not store the grouped set in SQL (aggregation is required after grouping), which makes the coding really complicated. Besides, it is annoying to export the big data involved in the problem to TXT files in batches using cursors. esProc can handle the operation elegantly with its group cursor through a three-line script:

A1: Use the SQL statement to create and return a database cursor sorted by PROGROUP.

A2-B2: Group data by PROGROUP. Run a loop to retrieve a group of records each time and then export them in B2 to the corresponding TXT file named after a type of value in PROGROUP field.

June 26, 2015

Generate a Result Set with Dynamic Columns with esProc


Below is a selection from the original table (tb1):
Prjno      Subtask   Ddate      Num
P9996     P9996-sub002 2015-01-01     123
P9996     P9996-sub002 2015-01-02     134
P9996     P9996-sub002 2015-01-03     345
P9996     P9996-sub002 2015-01-04     55
T0071     T-007-01 2015-01-01     3333
T0071     T-007-01 2015-01-02     356
T0071     T-007-01 2015-01-03     178

According to a specified date, you need to get all projects before this date in the same month. Suppose the input date is 2015-01-03, you’ll get this:
Prjno     Subtask  2015-01-01     2015-01-02     2015-01-03
P9996     P9996-sub002 123  134  345
T0071     T-007-01 3333       356  178


esProc code for doing this:

A1: Query data from the beginning of the month to the specified date. d_date is an input date parameter, like 2015-01-03. pdate@m(d_date) calculates the first date of the current month.

A2: Create an empty result table sequence with dynamic columns according to the sequence of dates from the first date of the month to the specified date: 

A3: The first part A1.group(Prjno,Subtask) groups A1’s data by Prjno and Subtask (esProc data grouping will keep the detail data of each group), then ~.groups(Ddate;sum(Num):Num), one by by, groups each group of data by the date and aggregate Num valules; finally, A2.record() writes each group name and the aggregate value into A2’s result table sequence. The following is the final result: 

In a similar way any database is called, esProc can be called by the reporting tool or a JAVA program. The computed result in the form of ResultSet can be returned to JAVA main program via esProc JDBC. You can see related documents for detailed method.

June 25, 2015

Aggregate Data by Specified Numbers of Rows with esProc


Below is data from SL field of table tb1:
SL
5
7
7
8
8
5
7
8
2
3
4
2

You need to calculate the sum of values every five rows. The expected aggregate result is as follows:

1-5  35
6-10 25
11-12      6

That is, the first column is the interval of rows and the second one is the sums of SL field values.


To do this in SQL, you need to use subqueries to generate sequence numbers. Whereas esProc uses sorted sets to make the computation intuitive. esProc code is as follows:

A1: Execute SQL to query SL data.

A2: Group and aggregate data according to member positions (sequence numbers). Result is as follows:

A3: Modify values in GN column that correspond to A2’ group numbers. Then you get the final computed result:

In a similar way any database is called, esProc can be called by the reporting tool or a JAVA program. The computed result in the form of ResultSet can be returned to JAVA main program via esProc JDBC. You can see related documents for detailed method.

June 23, 2015

esProc Helps to Compose Dynamic SQL

It is not uncommon in actual business to tackle complex computational problems by composing dynamic SQL. The skill has generated a lot of discussion across online IT forums. Here are links to some of the questions around it:
             
The usual methods of composing dynamic SQL include:

1. Using dynamic statements
Many databases provide syntax for handling dynamic SQL, such as Oracle’s EXECUTE IMMEDIATE statement, MSSQL’s EXEC and SP_EXECUTESQL statement and MySQL’s prepared statements. They make the handling of dynamic queries on the database side extremely convenient. Yet they are only suitable for comparatively simple dynamic queries. To do complicated queries, generally you can employ these methods: 

2. Using stored procedures
You can dynamically compose SQL statements for complicated queries in the stored procedure. This is comparatively flexible but the coding is too complicated and sometimes the efficiency can be compromised.

3.Using other (like JAVA) applications
This alternative is to compose the dynamic statement with external high-level languages (such as JAVA) and then hand it over to the database to execute. The method offers even higher flexibility. However, JAVA lacks support for set operations, so it is not easy for it to do the preparing job.

Use esProc to help with computations requiring dynamic SQL if it is the JAVA main program that needs to execute the dynamic SQL. esProc script is written for dynamic interpretation and execution. It is convenient for it to compose dynamic SQL and execute it. esProc provides JDBC interface that acts as the middleware between Java application and the database to let Java application execute esProc script as it accesses a database, without changing the application structure.

The following examples explain how esProc works to handle problems involving dynamic SQL and is integrated into JAVA application.

Composing dynamic SQL

To get the query result, first compose dynamic SQL query with esProc and then hand it over to the database to execute. esProc is only responsible for constructing dynamic SQL during the computation, and does not hit the computational target directly. For example:


Parameters source and target represent two tables of the same structure but containing different data. The table structure is unknown. You are required to update target with source according to their primary keys. Assume both Table1 and Table2 use A and B as the primary key. Source data is as follows:

To use table2 to update table1, write MERGE statement as follows:

MERGE INTO table1 as t
USING table2 as s
ON t.A=s.A and t.B=s.B
WHEN MATCHED
THEN UPDATE SET t.C=s.C,t.D=s.D
WHEN NOT MATCHED
THEN INSERT VALUES(s.A,s.B,s.C,s.D)

esProc script for composing dynamic SQL:

A1, A2: Retrieve values of source’s primary key from the system table and store them in the variable pks. A2’s result is set ["A","B"]. The ways of getting primary key values vary according to different databases. Here MSSQL is used as an example.

A3,A4: Retrieve all fields from source. The result of columns is ["A","B","C","D"].

A5: Build MERGE statement dynamically. pks.(…) is a loop function for handling members of a set (including a result set) sequentially. You can use ~ to reference a loop variable and # to reference the loop number.

A6: Execute the MERGE statement.

The structure of the tables is unknown, but it is very inconvenient to get the table structure through the stored procedure or JAVA and then construct dynamic SQL. With esProc that supports set operations well, you can write a universal, maintainable script with simple code.

The result of esProc script can be used as the data source of the report, or be called by JAVA application via JDBC. Below is the JAVA code for calling the esProc script:
  Class.forName("com.esproc.jdbc.InternalDriver");
    con= DriverManager.getConnection("jdbc:esproc:local://");
    //Call esProc script (which is similar to the stored procedure); the script file name is p1.
    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call p1()");
st.setObject(1,"table1");
    st.setObject(2," table2");
     //Execute the script
    st.execute();
       ……
The way of calling esProc script is the same as that of accessing a database. Programmers who are familiar with JDBC can master it fast.

About deploying esProc JDBC and calling esProc script in it, see esProc Integration & Application: Java Invocation.

Dynamic table joins

As opposed to static table joins, dynamic table joins join tables based on dynamic values in fields from different tables. The following data query is such an example:
Table A

Table B

Table C

Get Num values corresponding to Table A’s ID from Table B or Table C according to Table A’s TableName.

The targeted result:

esProc script for joining tables:

A1: Execute SQL to retrieve data from Table A.
A2: Group data by TableName and then loop through groups to compose queries dynamically, and finally sort query result by ID.

esProc’s ability of handling set operations (i.e. the function of retaining members of each group for potential use) makes it simple to compose dynamic SQL.

Dynamic UPDATEs with data of unconventional formats

In addition to dynamic data queries, sometimes you need to handle dynamic data updates. The data used for update often comes from a third-party application and may be of various formats, like JSON and XML. In some particular business situations, the data of unconventional formats (as opposed to the conventional two-dimensional table) needs to be updated into a (relational) database. To do this, a third-party application is needed. But the absence of class library from JAVA and other high-level languages and, as a result, the difficulty of hardcoding put them at a disadvantage. In view of this, you can use esProc to do their job. Below is an example of parsing a JSON file to update the database with esProc. The source data is:
{
    "SUCCESS": [
        {
            "MESSAGE": "IMEI Service List",
            "LIST": {
                "MOVISTAR SPAIN": {
                    "GROUPNAME": "MOVISTAR SPAIN",
                    "SERVICES": {
                        "3": {
                            "SERVICEID": 32,
                            "SERVICENAME": "MOVISTAR NOKIA INSTANTE",
                            "CREDIT": 4,
                            "TIME": "1-30 Minutes",
                            "INFO": "<p style=\"text-align: center;\"> </p>",
                            "Requires.Network": "None",
                            "Requires.Mobile": "None",
                            "Requires.Provider": "None",
                            "Requires.PIN": "None",
                            "Requires.KBH": "None",
                            "Requires.MEP": "None",
                            "Requires.PRD": "None",
                            "Requires.Type": "None",
                            "Requires.Locks": "None",
                            "Requires.Reference": "None"
                        },
                       ……
                    }
                }
            }
        }
    ],
    "apiversion": "2.0.0"
}
Target: Update database tables – groups and Services – with specified sections, mainly the Service list under IMEI.

These JSON strings contain multiple levels among which many have dynamic values (for instance, the numbers and names of sections under LIST and SERVICES are unfixed), making them very difficult to be parsed. Moreover, the spaces (like that in MOVISTAR SPAIN) and the dot (like that in Requires.Network) in attribute names greatly increase the difficulty of their parsing with JAVA.

esProc script for data update: