When analyzing embedded SQL, it’s important to understand that simply having dynamic SQL doesn’t necessarily mean it’s hard to convert. The key factor is structural complexity - how the SQL statement is formed in the code:

• how many variables are involved,
• whether conditional logic is used,
• whether the structure of the query changes at runtime,
• and whether the SQL is built in one place or across multiple program units.

Below is a practical classification of embedded SQL by complexity level and the typical ability of Ispirer toolkit to handle them.

Description:
The SQL statement is static - defined as a single string, either directly or in a variable. No concatenation, no conditional logic.

Example:

 String sql = "SELECT id, name FROM employees WHERE status = 'ACTIVE' and ROWNUM < 1000";

Conversion:
Fully automatic. The tool correctly converts SQL syntax (functions, types, literals, etc.) without issues.

Comment:
These are the simplest cases. Conversion is straightforward and typically achieves 100% success.

Description:
The SQL statement is divided into multiple strings or variables, but without any conditions or branching logic. Concatenation is used only for formatting or readability.

Example:

 StringBuilder insertSql = new StringBuilder();insertSql.append("INSERT INTO orders ");
 insertSql.append("(order_id, order_date, created_by, total_amount) ");
 insertSql.append("VALUES ( ");
 insertSql.append("101, SYSDATE, 'SCOTT', 250.75");
 insertSql.append(")");

Conversion:
Mostly automatic. The tool can merge fragments, reconstruct the full SQL statement, and correctly convert SQL syntax.

Comment:
Even though the tool is capable of combining all the fragments into a single SQL statement, during conversion the structure of the query may change, e.g. system functions into a CASE expression expanding, or an Oracle-style join using (+) may be converted to an ANSI JOIN, or other clause reordering may occur. As a result, it may become impossible to split the converted SQL back into the same fragments as in the original code. In such cases, the tool may decide to leave the SQL in its original form rather than inject an incomplete or syntactically inconsistent version back into the code.

Description:
SQL is built depending on certain conditions, but its overall structure remains predictable. There is a base query and optional fragments appended based on parameters.

Example:

 String sql = "SELECT id, name, NVL(type, 'NONE') FROM employees WHERE 1=1";
 if (isActive) {
    sql += " AND status = 'ACTIVE'";
 }
 if (deptId != null) {
    sql += " AND department_id = " + deptId;
 }

 PreparedStatement ps = conn.prepareStatement(sql);

Conversion:
Partially automatic. The tool can correctly convert individual SQL fragments, but the logic of how those fragments are combined (conditions, order, nesting) may require manual review or adjustments.

Comment:
Success rate depends on:

• how consistent the SQL pattern is,
• whether nested conditions exist,
• and how readable the structure is.

Description:
The SQL is assembled from multiple parts across different methods or classes. Fragments depend on nested conditions, loops, or runtime parameters. Concatenation may include subqueries, ORDER BY, GROUP BY, etc.

Example:

 String selectPart = "SELECT " + (useDistinct ? "DISTINCT " : "") + "emp_id, emp_name ";
 String fromPart = "FROM " + tableName;
 String wherePart = " WHERE 1=1";
 if (region != null) {
     wherePart += " AND region = '" + region + "'";
 }
 String sql = selectPart + fromPart + wherePart;
 if (sortBy != null) {
     sql += " ORDER BY " + sortBy;
 }

Conversion:
Mostly manual. The tool may handle individual SQL fragments, but often struggles to reconstruct the full query structure. Manual intervention is usually required to restore logic and context.

Comment:
At this level, analyzing the SQL-building logic becomes essential. Sometimes it’s easier to rewrite the code rather than rely on full automation.

Description:
The SQL is constructed across multiple modules, passed between methods, stored in global variables, or assembled dynamically at runtime. Parts of the query may reside in arrays, collections, or configuration files.

Example:

 String base = QueryParts.getSelectClause(userType);
 String filter = QueryBuilder.buildFilters(params);
 String sql = base + filter;
 execute(sql);

Conversion:
Not automatically convertible. The tool cannot reliably reconstruct the final SQL query. Manual analysis, logic reconstruction, or even rewriting may be required to achieve a maintainable, predictable structure.

Comment:
These cases typically represent the lower boundary of what can be handled automatically.

Summary: Automation Applicability by Complexity Level

If you have any questions or difficulties, please contact us at support@ispirer.com.