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:

http://bbs.csdn.net/topics/390876591

http://bbs.csdn.net/topics/390981627

https://www.linkedin.com/groups/SQL-Query-Help-needed-137774.S.5948812806903119877?trk=groups_items_see_more-0-b-ttl

http://bbs.csdn.net/topics/390073758

http://bbs.csdn.net/topics/390611005

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:

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:

A1: Import the JSON file. Data in the result set has levels.

A2-A3: Create two empty table sequences for storing updated data.

A4-D10: Loop through A1 to parse data dynamically and output the parsing result to A2 and A3, the targeted resulting table sequences.

A11-A12: Execute data update to update the groups and services table with table sequences in A2 and A3.