SQL Templates

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  SQL との作業 >

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.  例えば、 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). 例えば、 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.

 

以下の点に注意してください:

 

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

デフォルトでは、 parameters are treated as type text or varchar, which makes parameterized queries fail with certain database drivers (例えば、 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],
      [col2]
FROM   [mytable] WHERE col1 = :p1 /*# varchar, size:max */

 

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],
      [col2]
FROM   [mytable] WHERE col1 = :p1 /*# decimal, precision:23, scale:4 */

 

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 */
 
/*# decimal, precision:23, scale:4 */
 
/*# decimal, prec:23, scale:4 */
 
/*# varchar, size:max */
 
/*# nvarchar, size:32 */
 
/*# varchar, length:255 */
 
/*# number, precision:38, scale:0 */

 

以下の点に注意してください:

 

The character # explicitly instructs DatabaseSpy to handle the corresponding parameter with the declared type and size.
The データ型 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 エディター, and make sure that it contains at least one parameter. 例えば、 the INSERT statement below contains three parameters:

dbs_sql_template1

2.Click the 実行 ic_execute-sql or データの編集のための実行 ic_edit-data ボタン in the SQL エディター's ツールバー.
3.Fill in the required parameter values, for example:

dbs_sql_template2

You can also supply multiple parameter rows to the query. これを行うには、 Append New ds_ic_append_param または Insert New ds_ic_insert_param ボタンをクリックします。 The Append New ボタン adds a parameter row after all existing rows, while Insert New inserts a parameter at the current position (例えば、 between two existing rows).

dbs_sql_template3

4.Click 「OK」. DatabaseSpy executes the query with supplied parameters and displays the execution result in the Result tab.

 

以下の点に注意してください:

 

If the SQL エディター contains multiple SQL statements, the "Parameter values" ダイアログボックス 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" ダイアログボックス. To use a previous value, select it from the ドロップダウンリスト 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 エディター as SQL template, 次のいずれかを実行してください:

 

In the File メニューで「保存」(または、 名前を付けて保存をクリックします。 ).
「保存」 ツールバー ボタンをクリックします。
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".

dbs_sql_template4

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 ボタン.

 

 

Prerequisites

1.Create a SQL Template by typing it in the SQL エディターウィンドウ. 例えば、 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 オンラインブラウザー内で、 generate the INSERT statement, and modify it to add the required parameters, see SQL ステートメントの生成.

 

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 実行 ic_execute-sql, 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 (例えば、 right-click a table オンラインブラウザー内で、 and select データの編集 from コンテキストメニュー).
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 an INSERT template with three parameters like the one illustrated above, the following selection is valid:

dbs_sql_template5

A selection of multiple rows which are not necessarily consecutive is also valid, for example:

dbs_sql_template6

3.Right-click the selection and select 実行 template with values | <name of the template> をコンテキストメニューから選択します。 All generated statements are displayed in the SQL エディター and executed.

(C) 2019 Altova GmbH