SQL Queries with Parameters: Support for SQL Templates in DatabaseSpy

David McGahey
/in , /by

SQL queries with parameters, also known as SQL templates, are a flexible and efficient solution for repetitive data reporting requirements, for instance allowing users to easily execute complex join statements with multiple sets of values. DatabaseSpy, the multi-database query, design, and editing tool from Altova, includes robust support for developing, executing, and refining complex SQL queries with parameters, also known as SQL templates.

Prototyping SQL queries with parameters in DatabaseSpy can even accelerate development of queries required in other projects such as database mappings in MapForce or database data sources for MobileTogether cross-platform mobile apps.

Big Data Support

The DatabaseSpy SQL Editor and Result windows here show a query to report all orders of a specific product by a specific customer:

A SQL query that joins data from several tables without parameters

This query can be made much more comprehensive by replacing the product name ‘sprockets’ and the customer number 525 with parameters, as seen in the SQL Editor window below where the parameters :pname and :custnum are substituted for the product and customer on lines 6 and 7 of the query.

SQL queries with parameters are flexible and efficient

When the query with parameters is executed via the yellow Execute button at the top left of the SQL Editor window, DatabaseSpy opens a dialog allowing the user to enter values for execution, and the result shown at the lower right is generated.

Supplying values for SQL queries with parameters

SQL templates for DatabaseSpy can be saved in a special projects folder and executed dynamically. The example of direct execution shown above is fine if the user already knows the desired parameter values. Often, however, a user needs to create queries that generate test results to explore unfamiliar data. After a test query is executed, the user can select values from the result table and supply them as parameters to a SQL template stored in the project templates folder.

A new SQL Editor window opens with an automatically-generated query based on the template with selected values assigned to the parameters, the generated query is executed, and the result table is displayed.

Supplying values by example and executing a SQL query with parameters

Apply SQL Queries with Parameters to Other Projects

After any SQL query with parameters is created in DatabaseSpy, it can easily be applied in another project, such as a mobile app developed in MobileTogether, Altova’s tool for cross-platform Rapid Mobile App Development (RMAD).

For instance, our sample query might be just one small part of a complex sales reporting app. A mobile developer can simply paste the query into the SQL Statement Editor window in the MobileTogether Designer window and define the parameter values as seen here.

SQL queries with parameters can be applied in many other projects

Using the drag-and-drop page layout tools in The MobileTogether Designer, the developer can create an edit field to allow the user to enter a customer number and a combo box to select the product name for setting the SQL query parameter values when the app runs.

Our example query embedded in the mobile app would look like this on an Android device:

An Android mobile app executing a SQL query with parameters

The MapForce data mapping, conversion, and ETL tool includes a SQL Editor dialog similar to the DatabaseSpy dialog shown above. Parameters for a MapForce data mapping can be supplied at runtime or generated internally in the mapping.

All the DatabaseSpy functionality described here for creating SQL queries with parameters– and much more – is supported for the databases listed below. DatabaseSpy even lets users simultaneously open connections to multiple databases of different types and even compare structures and content between them.

Supported Databases:

  • Firebird
  • IBM DB2 for iSeries®
  • IBM DB2®
  • Informix®
  • Microsoft Access™
  • Microsoft® Azure SQL
  • Microsoft® SQL Server®
  • MySQL®
  • Oracle®
  • PostgreSQL
  • Progress OpenEdge
  • SQLite
  • Sybase®

(Click here for the current detailed list of supported versions for each database.) You can try out DatabaseSpy, MobileTogether, or any other Altova product free for 30 days.

Tags: , ,