Parameterized query

 Print Previous page Top page Next page

ici_9121 Parameterized query represents a data set, which records are formed as a result of SQL-query execution. The code of the Parameterized query forms or varies at the moment of its activation. Such queries are usually applied in that case, when it is necessary to provide for operations of the user.

Properties of a component are the followings: Active, Left, Name, Top, Database path, SQL.

Active - defines whether the query is activated or not, possible values - True, False. False value is assigned by default. If property has True value the query execution is realized.

Database path - contains a name of the directory where DB tables, or DB alias are disposed. Is selected by using the dialogue of choice of the concrete directory or alias of path to a place where the DB tables participating in a query are arranged.

Query code (SQL)- contains a query text, on account of which the records are selected into the data set. At creation of a query it is possible to use the Query builder - editor permitting to enter and to execute SQL parameterized queries (the main information about SQL language are given in item 2.6.1. of documentation). The principle of operations in the given editor is similar to SQL - queries builder (main information about it are given in item 2.5.4 of documentation).

For setting of the Parametrized query during the application's execution in its text it is possible to use parameters - variables, which names are made in characters $ or # in the text of a query. The characters $ and # are the service one and between them the name of form's component is indicated which contains the given parameter's value. At the moment of query activation the concrete value from an appropriate component of the form is substituted instead of a name of the parameter - variable. The form's component is defined according to a name of the parameter - variable.  For definition of values the components "Value edit" and "Operator combobox" are used.  

 If the value has the numeric type, the name of a component  "Value edit" is enclosed in characters $ (for example, $Value edit $).

If the value has a character type, the name of a component "Value edit" is enclosed in characters $ and apostrophe (for example, $ 'Value edit 2' $).

For the logical operator a component's name "Operator combobox" should be enclosed in characters # (for example, #Operator combobox1#).

If Parameterized query component is located onto Data Module, it is necessary to actuate the user's form containing components "Value edit" and "Operator combobox". Thus Queries builder will contain lists with names of components "Value edit" and "Operator combobox" of the given form, that will allow to simplify query building.  

In the text of Parameterized query, in conditions of selection, usage of data sets arranged on Data Module is possible. For example, let's consider the query text:

SELECT * From AIR_MAP WHERE AIR_MAP.NAME = ~ 'Data Module1.Table1.AIRPORT' ~

As execution result of SELECT operator it is the data set, which contents to criterion of selection:

AIR_MAP.NAME= ~'Data Module1.Table1.AIRPORT'С'~

The field name of a data set arranged on the Data Module is enclosed in ~ character.

 

Creation example of the user's form with usage of a component "Parametrized query".

Let's consider creation of the user form “Example of parameterized query”. This form displays the information about map objects by the layer and the name of object that are specified by the user (data are formed from the table anygeonk.dbf.

For creation of the form it is necessary to execute following operations:

1. Press Add button in a bookmark "User form" of Base Administrator. Form constructor will be actuated.

2. To create new form (the command Form/New, or by selecting the button Create new form)

        3. To place the component "Value edit1" on the form. By use of this component the demanded layer of a map is set.

        4. To place the component "Value edit2" on the form. By use of this component the required object name is set.

       5. To place the component "Operator combobox1" on the form. The search logical operator is set by using of this component. Let's select value AND (logical AND).

       6. To place the component Parametrized query - "Param-Query1" on the form.

We shall assign the value 'C:\Panorama11\Data\Noginsk\Training project Noginsk\Base' to Database path property. Let's form the Query text:

 

SELECT  *  FROM  anygeonk  WHERE (anygeonk.NAME = $'Value_edit2'$)

# Operator_combobox11#(anygeonk.LAYER = $'Value_edit1'$)

 

7. To place the component Action_list1 on the form. To add new macros Form_macros1. Set the Create param.query value to Macros type property. Set the Param-Query1 value to Param.query property. Set the False value to Auto execution property. If the property has True value the automatic execution of a query is carried out, the user does not see the text of a query.

8. To place the component Button1 on the form. Set the Form_macros1 value to Macros name property.

  1. To save the created form.

 

For example, shall form the list of objects from the layer HYDROGRAPHY with a name CONSTANT LAKES.

  1. Enter value of a layer - HYDROGRAPHY;
  2. Enter value of object name - CONSTANT LAKES;
  3. Choose the logic operator - AND;

4.  At pressing ОК button the Parametrized query is formed (as at form's designing the False value was set to Auto execution property of the component Macros_list1):

 

SELECT  *  FROM  anygeonk  WHERE (anygeonk.NAME='CONSTANT LAKES') AND (anygeonk.LAYER = 'HYDROGRAPHY')