SQL

Info

L’abonnement SQL est un peu particulier dans le sens où la configuration standard de listes externes produit automatiquement ce type d’abonnements. Il est donc très simple de récupérer une syntaxe pour un cas spécifique.

Il s’agit de rapatrier des valeurs provenant d’une requête SQL :

Dans des champs : valeur unique dans un résultat de SELECT
Dans le contrôle graphique de rendu d’une liste : liste de valeurs (cas standard depuis l’interface)

La classe d’abonnement à utiliser est com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension.

Here is a subscription definition example based on external data:

<subscription name="onLoad" subscription-class="com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension">
        <sql reference="SQLReference_1" order="SELECT PRODUCTNAME FROM VDOC_PRODUCTS">
        </sql>
        <action field-id="Field_1" update-mode="both" sql-type="VARCHAR" field-list="PRODUCTNAME"/>
</subscription>

When the document is loaded, the “onLoad” type subscription will be processed. It will fill the “Field_1” field list with the product name of each returned row by running the SQL request.

<subscription field-id="Field_1" name="onChange" subscription-class="com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension">
        <on-stage name="Stage_1"/>  
        <on-stage name="Stage_2"/>
                <sql reference="SQLReference_1" order="SELECT * FROM Table_1 WHERE Column_1 =?">
                        <bind field-id="Field_1" prop-id="" sql-type="VARCHAR"/>
                </sql>
        <action field-id="Field_2" prop-id="" update-mode="both" sql-type="FLOAT" field-list="column_2"/>
        <action field-id="Field_3" prop-id="" update-mode="both" sql-type="FLOAT" field-value="column_3"/>
</subscription>

The second SQL subscription example will run on altering the value of the “Field_1” field. The “Field_2” and “Field_3” fields will take the value of the “column_2” and “column_3” columns respectively.

The on-stage tags indicate that this subscription will only be acknowledged in steps “Stage_1” and “Stage_2”. If you want them to be processed each time that the value of “Field_1” field is altered, delete these lines.

The bind tag is the means to enter constraints on the SQL order dependent on the values of other fields.

Tags

Subscription

The “subscription” element defines the subscription input in XML of the resource template.

Element	Description
field-id	Modified document field and trigger of the “onChange” event.
name	Name of occurred event
prop-id	Not available
subscription-class	Extension class: `com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension`

On-stage

The “on-stage” element defines the input of a step on which the subscription will be run.

Element	Description
name	System name of the concerned step. The keyword sys_CreationWizard enables to indicate that the treatment will be processed only on the action of process document creation.

Sql

Ce type d’abonnement intègre une configuration spécifique permettant de spécifier :

La référence externe
La requête SQL
Les éventuels champs bindés dans la requête

<sql reference="externalReference" order="SELECT CHAMP1 FROM MYTABLE WHERE CHAMP2 > ?">  
     <bind field-id="ProcessField" sql-type="FLOAT" />  
</sql>

Ce noeud XML “sql” s’intégre dans le noeud de “subscription”.

The “sql” element defines the SQL request to the external database which will be run:

Element	Description
reference	Name of the external reference defined on the affiliated organization and allowing connection to an external database.
order	SQL order to be run.

Bind

The “bind” element associates an SQL request parameter to a document field. You should have as many as « ? » in the request as declared « bind » . The order of “bind” is important.

Element	Description
field-id	System name of the document field whose value will be taken as the request parameter.
prop-id	Name of the “get” type property or method that will allow introspection of the bind object.
sql-type	SQL type for storage of parameter field.

Action

The “action” element defines the input of an action to be performed after an event is triggered. Used in XML of the resource template.

Element	Description
field-id	System name of the subscribed field
prop-id	Not available
update-mode
action-key	Property of the DirectoryElement that we want to retrieve
field-list / field-value	Request field that will be used to update the document field based on whether it is of list type (field-list) or not (field-value).
sql-type	SQL type for storage of field to be updated. (VARCHAR or FLOAT)

The actions

Actions define all the document fields that will be updated when subscription is run. Actions can only be defined at the XML document level of the resource template. It is possible to define 1 to n actions.

The filters

Filters on steps can be placed to restrict subscriptions running on some steps. If no step has been specified, the subscription will be valid at all steps of the document. Filters are defined at the XML document level of the resource template. The on-stage tags can be present from 0 to n times.

The constraints

The SQL request constraints are defined as binds at the XML document level. Each bind tag will therefore represent a request “?”. » de la requête. Warning, the order must be maintained.

In case of “OnLoad’ type subscription, the constraints can also be defined at the level of Lists Administration. Similarly, each added field constraint should be in the order of the request “?”. » .

Modifier un champ ou modifier le rendu d’une liste

Comme présenté plus haut, on peut à la fois modifier un champ ou plutôt simplement utiliser une requête SQL pour initialiser les valeurs possibles dans un champ liste (sans affecter la valeur sélectionnée dans ce champ liste).

On peut utiliser les attributs :

“field-list” pour affecter une liste
“field-value” pour stocker la valeur dans un champ

<action field-id="targetField" field-list="source" sql-type="FLOAT" />

<action field-id="targetField" field-value="source" sql-type="FLOAT" />

Formules disponibles

Pas de formules disponibles ici.

Exemples

Exemple 1 : alimentation des valeurs possibles dans une liste

On souhaite remplir simplement les valeurs possibles d’une liste via une requête SQL. On ajoute dans cette requête un “bind” afin de filtrer cette requête par rapport à la valeur d’un champ du document de processus.

<subscription name="onLoad"  subscription-class="com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension">  
 <sql reference="externalReference" order="SELECT CHAMP1 FROM MYTABLE WHERE CHAMP2 > ?">  
             <bind field-id="ProcessField" sql-type="FLOAT" />  
        </sql>  
 <action field-id="targetFieldList" field-list="CHAMP1" sql-type="FLOAT" />
</subscription>

Exemple 2 : alimentation d’un champ à partir d’une requête SQL

On souhaite réaliser une requête SQL ramenant une ligne de données. L’idée est qu’un champ CLIENTID renseigné est possédé par le document.

Il faut récupérer l’adresse et le numéro de téléphone du client à partir de cet identifiant. On ajoute dans cette requête un “bind” afin de filtrer cette requête par rapport à la valeur d’un champ du document de processus.

<subscription name="onLoad" subscription-class="com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension">
 <sql reference="externalReference" order="SELECT ADDRESS, PHONENUMBER FROM CLIENTS WHERE ID = ?">  
	 <bind field-id="IdClient" sql-type="VARCHAR" />  
 </sql>  
 <action field-id="FieldAddress" field-value="ADDRESS" sql-type="VARCHAR" />
 <action field-id="FieldPhoneNumber" field-value="PHONENUMBER" sql-type="VARCHAR" />
</subscription>

Exemple 3 : alimentation de champs à la sélection dans une liste

A la sélection dans la liste Client, on récupère dans la base Northwind les champs Nom du client, Fonction et Téléphone.

<subscription field-id="Client" name="onChange" prop-id="" subscription-class="com.axemble.vdp.mapping.extensions.SQLResourceMappingExtension">  
	<sql reference="Northwind"  order="SELECT ContactName, ContactTitle, Phone FROM CUSTOMERS WHERE CompanyName=?">  
		<bind field-id="Client" prop-id=""  sql-type="VARCHAR" />  
	</sql>  
	
	<action field-id="NomClient" prop-id="" throw-events="false" update-mode="both" sql-type="VARCHAR" field-value="ContactName"/>
	<action field-id="Fonction" prop-id="" throw-events="false" update-mode="both" sql-type="VARCHAR" field-value="ContactTitle"/>
	<action field-id="Telephone" prop-id="" throw-events="false" update-mode="both" sql-type="VARCHAR" field-value="Phone"/>
</subscription>

Source : https://wiki.myvdoc.net/xwiki/bin/view/Dev+Floor/ResourceSubscription