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)
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.
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.
No comments:
Post a Comment