Browse Prior Art Database

Using DBMS-specific SQL syntax in client applications

IP.com Disclosure Number: IPCOM000181042D
Original Publication Date: 2009-Mar-24
Included in the Prior Art Database: 2009-Mar-24
Document File: 3 page(s) / 121K

Publishing Venue

IBM

Abstract

Using DBMS-specific SQL syntax in client applications

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 41% of the total text.

Page 1 of 3

Using DBMS-specific SQL syntax in client applications

1. Motivation

State of the Art

Tools operating with database systems have a strong dependency on the specific database management system (DBMS) for which they are written. More complicated, it is not only the DBMS but a specific version of it. Each new version of the DBMS requires some adjustments in the tooling products to adjust to the changes and enhancements made. Sometimes, such changes are even necessary for fixpaks (aka service packs or PTFs). This increases development costs for the tools and/or is bound to show incompatibilities because some features are not yet supported by a tool, or the tool supports a feature that was removed. Typical scenarios where such dependencies exist are SQL editors. In the following, we use such an SQL editor as example.

An SQL editor can be used to type in SQL statements that are to be executed against the database system. Syntax highlighting is a typical feature of such an SQL editor. The highlighting can be used to show errors in the statement like missing keywords or syntactically incorrect expressions or predicates. For example, mismatches in parenthesis become quickly apparent. In order to provide the syntax highlighting, the SQL editor has the grammar built-in that defines exactly how a SQL statement has to be structured syntactically to be understood by the target database system. However, as it is used today, this has several short-comings:

The SQL grammar built into the SQL editor may not be in sync with the SQL

1.

grammar used by the DBMS for the very same SQL statements. Thus, a SQL statement indicated as syntactically correct by the SQL editor may still be rejected with a syntax error by the DBMS. Likewise, the SQL editor may flag a statement as syntactically incorrect but it is correct in terms of the DBMS' grammar.

Increased implementation effort is needed in the SQL editor. At least the SQL

2.

grammar has to be re-implemented. While the actual coding is already complicated due to the feature-richness of SQL itself, it is also a significant effort to test this.

An alternative to the built-in grammar is that the SQL editor sends each SQL statement to the DBMS to compile it and, thus, verify the syntax is accepted by the DBMS. This approach is also sub-optimal because:

Only complete SQL statements can be sent to the server. Partial statements (for

1.

example statements consisting of only a SELECT clause so far) will always raise syntax errors.

The DBMS typically does not give an indication where exactly in the statement a

2.

syntax error was found.

Only a single syntax error is flagged, namely the first one found.

3.

Additional network traffic is incurred because the SQL statement is sent back and

4.

forth between the SQL editor and the DBMS to validate its syntax. This becomes even more troublesome if the sy...