SQL Templates
With DatabaseSpy Enterprise Edition, you can create and run SQL statements which contain parameters (subsequently referred to as "SQL Templates"). The only difference between standard SQL queries and SQL templates is that the latter contain parameter placeholders. For example, the SELECT statement below is a template which defines two parameters, :color and :size.
SELECT id FROM products WHERE color = :color AND size > :size; |
You can define parameters not only in SELECT statements but also in other statement types (such as INSERT or UPDATE). For example, an INSERT statement with parameters could look like:
INSERT INTO users (first_name,last_name,email,created_at,updated_at) VALUES (:fname,:lname,:email,CURRENT_TIMESTAMP,NULL); |
INSERT templates such as the one above can greatly simplify the transfer of large amounts of data from one table or view to another (even across various databases). Specifically, when you need to populate a table, multiple INSERT statements can be generated in a few simple steps from the given template, as further described below.
Note the following:
•In order to be recognized by DatabaseSpy as such, parameters within SQL statements must be preceded by a colon ( : ) character.
•Parameters may substitute values inside statements; however, in order to prevent SQL injections, they may not be used to substitute SQL reserved keywords or identifiers (such as column or table names).
•Parameters are by default treated as text, and parameterized queries will fail with certain database connections unless you explicitly change the parameter type (see the next section below, "Changing the parameter type").
Changing the parameter type
By default, parameters are treated as type text or varchar, which makes parameterized queries fail with certain database drivers (for example, with ADO.NET or JDBC connections to SQL Server). In such cases, you can explicitly specify the type, and, if applicable, the precision, scale, or the size of the parameter by adding a comment immediately after the parameter.
For string types, consider the following example:
SELECT [col1], |
In the code listing above, the parameter type is explicitly set as varchar and its size is set to max. The modifier length can also be used instead of size.
For numeric types, consider the following example:
SELECT [col1], |
In the code listing above, the type of parameter p1 is explicitly set as decimal, the precision (total number of digits) is set to 23, and the scale (number of digits right of the decimal point) is set to 4. You can also use the short name prec for precision. Both precision and scale require integer values.
Below are a few other examples of attributes that may follow a parameter (make sure to adjust the details as required by your database):
/*# float */ |
Note the following:
•The character # explicitly instructs DatabaseSpy to handle the corresponding parameter with the declared type and size.
•The data type and the attributes following it must be separated by a comma. Each attribute must also be separated by a comma.
•Attributes other than prec or precision, scale, size or length will not be recognized. All, some, or none of these attributes may be required in order to run the parameterized query successfully, depending on the database driver and how much information it is able to determine automatically when the statement is prepared.
Running a query with parameters
1.Enter or generate a SQL statement in SQL Editor, and make sure that it contains at least one parameter. For example, the INSERT statement below contains three parameters:
2.Click the Execute 
or Execute for Data Editing 
button in the SQL Editor's toolbar.
3.Fill in the required parameter values, for example:
You can also supply multiple parameter rows to the query. To do this, click either the Append New 
or Insert New 
button. The Append New button adds a parameter row after all existing rows, while Insert New inserts a parameter at the current position (for example, between two existing rows).
4.Click OK. DatabaseSpy executes the query with supplied parameters and displays the execution result in the Result tab.
Note the following:
•If the SQL Editor contains multiple SQL statements, the "Parameter values" dialog box contains a tab for each SQL statement where parameters exist. Also, multiple Result tabs are displayed after you run the query (one Result tab for each statement containing parameters).
•DatabaseSpy remembers the last 25 parameter values entered in the "Parameter Values" dialog box. To use a previous value, select it from the drop-down list available for each parameter.
Saving parameterized queries as SQL Template
A query which contains parameters can be saved for later use, similar to how this works for standard SQL files. To save the contents of the SQL Editor as SQL template, do one of the following:
•In the File menu, click Save (or Save As).
•Click the Save toolbar button.
•Press Ctrl + S.
SQL template files are saved with .sql extension, like other SQL scripts. To help you distinguish between SQL templates and other SQL scripts, the DatabaseSpy project contains a specially designated folder called "SQL Templates".
All SQL templates created for the project must be in the "SQL Templates" folder. Only when a SQL template is inside the "SQL Template" folder can DatabaseSpy execute it with values, as further described below. |
Executing SQL templates
You can execute SQL templates against sets (rows) of dynamically supplied values. Executing a SQL template means that DatabaseSpy replaces all parameter placeholders found in the template with the values you supply, creates a new SQL statement for each value (or row of values, depending on the case), and runs it against the database. As a result, it is possible to generate and run a very large number of SQL statements with a click of a button.
Prerequisites
1.Create a SQL Template by typing it in the SQL Editor window. For example, the SQL template below takes three parameters: :fname, :lname, and :email.
INSERT INTO users (first_name,last_name,email,created_at,updated_at) VALUES (:fname,:lname,:email,CURRENT_TIMESTAMP,NULL); |
As an alternative to typing the statement, you can also right-click a table in the Online Browser, generate the INSERT statement, and modify it to add the required parameters, see Generating SQL Statements.
2.Save the SQL Template and add it to your DatabaseSpy project. The SQL template must be inside the "SQL Templates" folder of the project.
At this stage, you can also test whether the new SQL Template executes successfully, by running it with some manually-entered parameters. To test the SQL template, click Execute , and enter the required parameters when prompted, see Running a query with parameters.
To execute a template with values:
1.Connect to a data source and retrieve data from the table which contains the values you want to supply as input to the SQL template (for example, right-click a table in the Online Browser, and select Edit Data from the context menu).
2.Hold the Ctrl key pressed and select one or more cells from the Results grid. To select multiple columns, click on their header while holding the Ctrl key pressed. The SQL template will be executed as many times as there are rows in the selection. Note that the number of selected columns must correspond to the number of parameters that the SQL template accepts, therefore:
oIf the template accepts only one parameter, all the selected cells (one or more rows) must be in the same column.
oIf the template accepts multiple parameters, there must be as many selected columns as there are parameters.
For example, for an INSERT template with three parameters like the one illustrated above, the following selection is valid:
A selection of multiple rows which are not necessarily consecutive is also valid, for example:
3.Right-click the selection and select Execute template with values | <name of the template> from the context menu. All generated statements are displayed in the SQL Editor and executed.