Browse Prior Art Database

Method And System For Generating Complex SQL Statements

IP.com Disclosure Number: IPCOM000195900D
Publication Date: 2010-May-21
Document File: 5 page(s) / 27K

Publishing Venue

The IP.com Prior Art Database

Abstract

A method and system for generating Structured Query Language (SQL) statements by using a language based on the Extensible Markup Language (XML) syntax is disclosed. The method and system enables efficient generation of dynamic SQL statements for on-demand reporting applications to maintain data within and retrieve the data from relational databases.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 38% of the total text.

Page 1 of 5

Method And System For Generating Complex SQL Statements

Disclosed is a method and system for generating Structured Query Language (SQL) statements by using a language based on the Extensible Markup Language (XML) syntax. The method involves defining language files containing static SQL text and a set of dynamic control statements by utilizing the language. These static SQL statements and the set of control statements are used for generating the dynamic SQL statements.

The disclosed method and system involves utilizing an engine for enabling generation of dynamic SQL statements. The engine is capable of evaluating the language files containing the static SQL text and the set of dynamic control statements. The set of dynamic control statements include expressions which represent content to be generated at runtime. Such expressions may be used to inject business logic into generation of the SQL statements. The engine configures these expressions with appropriate parameters and invokes the expressions.

Accordingly, the engine

evaluates the language files and generates text of syntactically-correct SQL statements. Further, the engine generates a potential set of parameter values to be bound to the SQL statements at execution time.

A basic outline of a dynamic SQL template may be

as follows:

In the given dynamic SQL template, a root element uses a

tag and may contain two child elements which are stated by

tag and

tag. Here, the

tag is used to define custom tags and fully-qualified class name of JAVA* objects represented by that tag. It is expected that the JAVA object defined here follows the JavaBean* specification with regard to having a zero-argument constructor and appropriate accessor and mutator methods to accommodate any attributes or child elements nested within custom tag. Further, the

tag contains template of SQL statements to be generated by the engine. Content of this tag is comprised of a mix of static SQL text and nested XML elements.

The

tag includes nested elements which may be custom tags declared by the

1

Page 2 of 5

tag or may be 'reserved' or 'built-in' tags. Examples of some reserved or built-in tags are given below as:

- This element corresponds to a simple implementation of a SQL tag in which SQL text generated is a single place-holder character and a corresponding value resulting from the evaluation of a specified expression. The specified expression to be evaluated is declared via a 'value' attribute. For example,

.

- This element corresponds to a simple implementation of a SQL tag. The element creates a string-representation of an evaluated expression. The element may be used in place of the

element in scenarios where bind variables are not permitted or not desired.

An expression to be evaluated is declared via the 'value'

attribute. For example,

.

- This element is used to include content of another evaluated dynamic SQL template into a template that is being processed. This element may be useful in scenarios...