Integration Connector with Web Service

XOne has a connector to perform an integration between a database and a Web Service.

We have to indicate that is complicated having a single connector for all the Web Services there are, since each one of them is different and they do not have anything generic, so therefore for almost all the Web Services integrations, the connector will have to be modified for its proper functioning.


Its configuration is done in the same way to the database connector, so they will have a config file to tell where is the XML file that has the integration itself.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <!--
     **** VALORES GENERALES
    -->
    <!-- Nombre de la aplicación usado para mostrar mensajes en el visor de sucesos -->
    <add key="Application Name" value="Xone Integration Service" />
    <!-- Código devuelto por el webservice para determinar que los datos han sido recibidos correctamente -->
    <add key="Result Code Ok" value="00" />
    <!-- Código devuelto por el webservice para determinar que los datos han sido recibidos correctamente, pero que hay una advertencia -->
    <add key="Result Code Warning" value="50" />
    <!-- Nombre del campo ROWID de la base de datos, en ORACLE es palabra reservada y suele usarse CGSROWID -->
    <add key="ROWID Field Name" value="ROWID" />
    <!-- Nombre del campo SQL de la base de datos, en MYSQL es palabra reservada y suele usarse CGSSQL -->
    <add key="SQL Field Name" value="CGSSQL" />
    <!-- Numero de MID que se quiere usar para insertar las operaciones en el master_replica_queue -->
    <add key="MID Number" value="9999" />
    <!-- Ruta del intercliente de donde vamos a coger todas las transformaciones -->
    <add key="XML Path" value="C:\loquesea\interclient.xml" />
    <!-- Intervalo entre ejecucion y ejecucion de la interface, expresado en segundos -->
    <add key="Timer" value="60" />
    <!--
     **** VALORES PARA AUTOREGISTRO (GENERATE ADM DEVICE)
    -->
    <!-- IP en la que esta instalado el servidor de replica -->
    <add key="Replicator Server Address" value="IP del server de replica" />
    <!-- Puerto de escucha del servidor de replica -->
    <add key="Replicator Server Port" value="Puerto del server de replica" />
    <!-- DBLIC de la base de datos en la que queremos generar la licencia -->
    <add key="Replicator DataBase Number" value="10000026" />
    <!-- Formato de fecha de replica del dispositivo que vamos a firmar -->
    <add key="Replicator Client DLDATE" value="ymd" />
    <!-- Formato de fecha de replica del dispositivo que vamos a firmar -->
    <add key="Replicator Client RPDATE" value="ymd" />
    <!--
     **** OTROS VALORES
    -->
    <!-- Indica si queremos generar una DLL a partir de un fichero dado -->
    <add key="Generate wrapper" value="false" />
    <!-- Nombre de la tabla donde se guardan los errores generados por la interface -->
    <add key="Interclient Error Log Table" value="interclient_error_log" />
    <!-- Consulta de creacion de la tabla de sincronizacion, necesarias para la ejecucion de la interface -->
    <add key="Create Sync Table Stmt" value="CREATE TABLE interclient_sync_tables(&#xD;&#xA;    [ID] [int] IDENTITY(1,1) NOT NULL,&#xD;&#xA;    [OBJECTNAME] [nvarchar](50),&#xD;&#xA;    [LASTMODIFIED] [datetime] NULL,&#xD;&#xA;    [LASTCHARID] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL,&#xD;&#xA;    [LASTNUMBERID] [int] NULL,&#xD;&#xA;    CONSTRAINT [PK__interclient_sync_tables__01234A04] PRIMARY KEY CLUSTERED&#xD;&#xA;    (&#xD;&#xA;    [ID] ASC&#xD;&#xA;    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]&#xD;&#xA;    ) ON [PRIMARY]" />
    <!-- Consulta de creacion de la tabla de errores, necesarias para la ejecucion de la interface -->
    <add key="Create Sync Error Stmt" value="CREATE TABLE interclient_error_log(&#xD;&#xA;    [ID] [int] IDENTITY(1,1) NOT NULL,&#xD;&#xA;    [SOURCE] [nvarchar](150),&#xD;&#xA;    [ERRORCODE] [nvarchar](50),&#xD;&#xA;    [MESSAGE] [nvarchar](255),&#xD;&#xA;    [TEXT] [ntext],&#xD;&#xA;    CONSTRAINT [PK__interclient_error_log__01234B04] PRIMARY KEY CLUSTERED&#xD;&#xA;    (&#xD;&#xA;    [ID] ASC&#xD;&#xA;    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]&#xD;&#xA;    ) ON [PRIMARY]" />
 
    <!-- .NET VALUES -->
    <add key="ClientSettingsProvider.ServiceUri" value="" />
  </appSettings>
</configuration>

Configuration file where the integration is going to be performed between the source system, the client system and the XOne system.

Once Once it has been decided in which order the data are transferred, each of the transform nodes are defined, which are the ones that carry out the transfer of data from one table to another.

Node which the data of that transformation are defined in.

The attributes of the node are:

<transform name="modificados">
     <coll dest-table="gen_tmp" source-table="##WEBMETHOD##" not-replicate="true">
	<function name="getRegCambios" class="XOneWrapper.expService">
	    <param name="fecha" value="##NOW##" type="D" />
	    <param name="fechaSpecified" value="true" type="NC" />
	</function>
	<coll dest-table="gen_tmp" source-table="##PARENT##.id" not-replicate="true">
	     <prop source-name="" dest-name="ID" type="N" />
             <prop source-name="##PARENT##.##PARENT##.fechaCambio" dest-name="FECHA" type="D">
		<oper type="copy"/>
		<format name="dest-name" value="yyyy/MM/dd"/>
             </prop>
	     <prop source-name="explo" dest-name="EXPLOTACION" type="T" key="true">
		<oper type="copy"/>
	     </prop>
	     <prop source-name="coEspecie" dest-name="CO_ESPECIE" type="T" key="true">
		<oper type="copy"/>
	     </prop>
	     <prop source-name="BAJADO" dest-name="BAJADO" type="N">
		<oper type="setval" value="0"/>
	     </prop>
	</coll>
     </coll>
</transform>

<transform name="datos" >
	<coll dest-table="gen_explotacion" source-table="##WEBMETHOD##">
		<function name="getDatos" class="XOneWrapper.Service">
			<param name="rega" value="COD2266" type="T"/>
			<param name="co_especie" value="COD344JK" type="T"/>
		</function>
 
		<prop source-name="" dest-name="ID" type="N" />
		<prop source-name="ads" dest-name="ADSG_TEX" type="T">
			<oper type="copy"/>
		</prop>
		<prop source-name="Menores6Meses" dest-name="ANM_6MES" type="N2">
			<oper type="copy"/>
		</prop>
		<prop source-name="censoActual" dest-name="CENSO" type="N2">
			<oper type="copy"/>
		</prop>
		<prop source-name="cp" dest-name="CP" type="T">
			<oper type="copy"/>
		</prop>
		<prop source-name="dir" dest-name="ENDEREZO" type="T">
			<oper type="copy"/>
		</prop>
 
		<coll dest-table="gen_anim" source-table="##PARENT##.maxAnimales">
			<prop source-name="" dest-name="ID" type="N" />
			<prop source-name="ESPECIE" dest-name="IDEXPLOTACION" type="N" key="true">
				<oper type="mapval" source="gen_explotacion" targetfld="ID">
					<prop mapfld="ESPECIE" mapvalue="##SELF##"/>
					<prop mapfld="REGA" mapvalue="REGA"/>
				</oper>
			</prop>
			<prop source-name="categoria" dest-name="CATEGORIA" type="T">
				<oper type="copy"/>
			</prop>
			<prop source-name="especie" dest-name="ESPECIE" type="T">
				<oper type="copy"/>
			</prop>
			<prop source-name="fcenso" dest-name="FCENSO" type="D">
				<oper type="copy"/>
				<format name="dest-name" value="yyyy/MM/dd"/>
			</prop>
		</coll>
 
		<after-action>
			<action name="execute-query">
				<param name="source" with-replica="false" value="UPDATE GEN_TMPREGA SET BAJADO=1 WHERE EXPLOTACION='COD2266' AND CO_ESPECIE='COD344JK'"/>
			</action>
		</after-action>
	</coll>
</transform>

Node in which the origin and destination tables of the data to be integrated are taken.

The attributes of the node are:

Within the coll node, each and every one of the fields that have to be transferred from one table to another will be placed. For this purpose, different nodes prop are set, which will be those with the name of the source field and the name of the destination field. Subsequently several attributes will be defined defining the type or what type of action has to be performed.

Node in which you define which field is transferred and what action is performed in the destination table.
The attributes of this node are:

Once we define which field is origin and the destination field, with its individual characteristics, we have to define the action to be performed, which will be defined within a oper node within the prop node.

Types of operations that can be performed with the fields that we want to transfer from one table to another, in the prop node.

Operation to put a given value in the destination field. That's why the prop node, only has the type of the type field, and the name of the field that is the destination, dest-name .
In the example we can see that in the IDPADRE field of the destination table, which is of numeric type, N, the value 1 will be saved.

<prop dest-name="IDPADRE" type="N">
	<oper type="setval" value="1"/>
</prop>

In this other example we put the “xone” value in the NOMBRE destination field, that is text type.

<prop dest-name="NOMBRE" type="T">
	<oper type="setval" value="xone"/>
</prop>

Operation to copy the value of a source field into a destination field.

Example where it is seen how the value of the “delegation” field will be copied to the “LABEL” field. This field is text type. We can also see how it is the primary key, so to find if there is such a record in the destination, it will made by searching for the value of this field. It is also being told to save the value of the source in the macro ## SOURCEKEYVALUES ## , and to save the value of the destination in the macro ## DESTKEYVALUES ## .

<prop source-name="delegación" dest-name="ETIQUETA" key="true" type="T" saved-source="true" saved-dest="true">
		<oper type="copy"/>
</prop>

Node that transfers the value of the “PASSWORD_E” field into the PWD field. As we can see, it has type X, so it is being indicated that it is a password field, and its value keeps it encrypted. Encryption is supported by the XOne Platform, so that users can subsequently enter the application.

<prop source-name="PASSWORD_E" dest-name="PWD" type="X">
           <oper type="copy"/>
</prop>	

Node that transfers the value of the “DESCPTE” field into the “INCIDENCE” field. It is text type. We also see that it has the “always-update” attribute, so we are forcing it into the replication queue, “master_replica_queue”, to insert an update even if its value does not change.

<prop source-name="DESCPTE" dest-name="INCIDENCIA" key="false" type="T" always-update="true">
	<oper type="copy"/>
</prop>	

Node that transfers the value of the “FECCARGA” field into the “DATE” field. The field is date type. You can also see a new node, format , node that is used within the prop nodes, and that is used to format the value in the destination with the characteristics that are indicated in the value attribute. In the example, when we put it, we are telling it that in the destination the date has to be inserted in day / month / year mode.

<prop source-name="FECCARGA" dest-name="FECHA" type="D">				
  <oper type="copy"/>
  <format name="dest-name" value="dd/mm/yyyy"/>
</prop>	

Node that transfers the data from the “CODAVISO” field into the “TIPOPARTE” field, and we see that it is the text type. We can also see that there is the format node. Here we can see that it has a new attribute, trim . This attribute does or does not eliminate the blank spaces that come from the origin in the destination. The attribute if it is not defined is “true”, that is, it eliminates the blank spaces. For it to work, it is necessary to put the value “NULL” in the “value” attribute.

<prop source-name="CODAVISO" dest-name="TIPOPARTE" type="T">
	<oper type="copy"/>
	<format value="NULL" trim="false"/>
</prop>	

Node that transfers the data from the “PRETAR” field into the “PRICE” field. It is numeric, and with 5 decimals specifically. In order for the copy to be done well, the format node has been set, placing exactly the number of decimals that will be put to it.

<prop source-name="PRETAR" dest-name="PRECIO" type="N5">
	<oper type="copy"/>
	<format name="dest-name" value="0.00000"/>
</prop>

Operation to transfer data from fields related to values ​​that are in other tables. What would be in the mappings fields of type IDEMPRESA or IDUSUARIO. For this, what is done is to take the value of the source field, and look for it in a table, when it finds the value, it puts the value of a field that we indicate it of that table in the destination field of the beginning.

In the example that is seen, the value of the “delegation” field is taken, and compared with the “LABEL” field, which is put in the mapfld attribute, which exists in the “Gen_Business” table, defined in the source attribute. If there is a record that has that value, there only has to be one, since the field by which the search is made in the “Gen_Business” table, must be the primary key of the same, take the value of the field that is define in the attribute targetfld , “ID” for this case, and put it in the field dest-name , field “IDEMPRESA”.

<prop source-name="delegación" dest-name="IDEMPRESA" type=”N”>
	<oper type="mapval" source="Gen_Empresa" targetfld="ID">
		<prop mapfld="ETIQUETA" mapvalue="##SELF##"/>
	</oper>
</prop>	

In this case, several props can be used within the same oper, which is for those cases in which the first key of the record we are looking for is defined by several fields, in this case “COMPANY_ID” and “CAMPO2”.

Example:

<oper type="mapval" source="gen_usuarios" targetfld="ID">
     <prop mapfld="COMPANY_ID" mapvalue="##SELF##"/>
     <prop mapfld="CAMPO2" mapvalue="##FLD_PROPIEDAD##"/>
</oper>  

Node that is executed when we finishing to perform the data transfer from one table to another.

It executes an SQL. This is quite useful for putting a field in the source table with a value, to indicate that this value has already been transferred to the destination table.

In the given example, we see that it has two nodes param , in which it is told that SQL will be executed, in the attribute value . Also in that database, the name attribute, is given “dest”, which is the destination database, and “source”, which is the source database.

We can also see that it uses the ## SOURCEKEYVALUES ## and ## DESTKEYVALUES ## macros, which have been filled in while transferring the data from one database to another.

<action name="execute-query">
<param name="source" value="Update Gen_DetDocumento SET ACTUALIZADO=1 WHERE ##SOURCEKEYVALUES##"/>				<param name="dest" value="UPDATE Gen_Documentos  SET ACTUALIZADO=1  WHERE ID IN (SELECT IDDOCUMENTO FROM GEN_DETDOCUMENTO WHERE ##DESTKEYVALUES##)"/>	
</action>

It is a sample code about how it would be an interclient.xml file.

<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Nodo xml obligatorio, puesto que es el formato del documento -->
<xml>
	<!-- Nodo interface, dentro del cual va todo lo necesario para nuestra integración -->
	<interface>
		<!-- Aqui van los datos a nivel de aplicación -->
		<app name="WEBSERVICE" timeaccess="false" logoper="true">
			<!-- Aquí vamos a tener las distintas conexiones -->
			<dsn>
				<!-- Esta es la conexión al webservice -->
				<client type="WEB" name="webservice-demo" persistent="true" url="https://localhost/services/WebservicesInHandler" endpoint="WebservicesInHandler" wsdl="" wsdl2="" wrapper="XOneWrapper" class="WebservicesInHandlerService"/>		
				<!-- Conexión normal a nuestra BD -->
				<source type="ODBC" name="connstr1" connstring="DSN=Localhost_DES;Database=database;UID=root;PWD=password" user="root" pwd="password" />
				<!-- Conexión a nuestra BD para actualizar registros lastmodified (Se usa para marcar por donde vamos en algunas iteraciones) -->
				<lastmodified type="ODBC" name="connstr2" connstring="DSN=Localhost_DES;Database=database;UID=root;PWD=password" user="root" pwd="password" />
				<!-- Conexión a nuestra BD para operaciones opcionales (Normalmente no se usa) -->
				<optional name="NOMBRE3" type="MYSQL" connstring="......."/>
			</dsn>
			<!-- Intervalo tras el cual se vuelve a ejecutar la interface, expresado en segundos -->
			<schedule type="continue" interval="5"/>
		</app>
		<!-- Estas son las transformaciones de BAJADA, es decir del CLIENTE a NOSOTROS -->
		<transforms type="client-to-source">
			<!-- TRANSFORMACIÓN DE USUARIOS -->
			<transform name="t_usuarios">
				<!-- En el nodo coll tenemos la tabla de destino y el origen, en este caso un ##WEBMETHOD## -->
				<coll dest-table="TABLA_DESTINO" source-table="##WEBMETHOD##">
					<!--
						Aqui tenemos el nodo function, el cual indica la función a la que tenemos que llamar y su Clase
						Si tuviéramos que enviar parámetros estos irían dentro del nodo, añadiendo nuevos nodos param
					-->
					<function name="GetUsuarios" class="XOneWrapper.ObtenerDatosClient" />
 
					<!--
						A continuación vamos pasando prop a prop, de sus campos a los nuestros.
						¡IMPORTANTE! Siempre debemos tener un prop al menos con key="true"
						sino nuestros datos se van a ver duplicados y vamos a generar miles de operaciones
						en el queue totalmente innecesarias.
					-->
					<prop source-name="IDNUMPERSO" dest-name="CAMPO_A_MAPEAR" type="N" key="true">
						<oper type="copy"/>
					</prop>
					<prop source-name="APEYNOM" dest-name="CAMPO_A_MAPEAR" type="T">
						<oper type="copy"/>
					</prop>
					<prop source-name="LOGIN" dest-name="CAMPO_A_MAPEAR" type="T">
						<oper type="copy"/>
					</prop>
					<prop source-name="FECHAMODIFICACION" dest-name="CAMPO_A_MAPEAR" type="D">
						<oper type="copy"/>
						<format name="dest-name" value="dd/MM/yyyy"/>
					</prop>					
				</coll>
			</transform><!-- FIN TRANSFORMACIÓN DE USUARIOS -->
		</transforms><!-- FIN TRANSFORMACIÓN DE USUARIOS -->
 
		<!-- Estas son las transformaciones de SUBIDA, es decir de NOSOTROS al CLIENTE -->
		<transforms type="source-to-client">
			<!-- TRANSFORMACIÓN DE ASOCIAR USUARIO PARTE -->
			<transform name="t_asociar_usuario_parte">
				<!-- 
					La coll principal, aqui seleccionamos los datos que queramos pasar, con los WHERE necesarios para filtrar
 
					source-table	es el SQL del que vamos a sacar las filas que queremos pasar (Aqui no hace falta sacar datos, tan solo el ID y ROWID)
					dest-table		en este caso ##WEBMETHOD## indica que es una llamada a un webservice
					desc			es el nombre del método que vamos a llamar
					name			es el nombre del método que vamos a llamar
 
					NOTA: empty-result="true" Usamos este atributo ya que la funcion del webservice no nos devuelve nada
				-->
				<coll source-table="SELECT t.ID,t.ROWID	FROM tabla1 t WHERE CAMPO6 IS NULL" 
				dest-table="##WEBMETHOD##" desc="AsociarUsuarioParte" name="AsociarUsuarioParte" empty-result="true" >
					<!-- Nodo function indicando la función que queremos invocar -->
					<function name="AsociarUsuarioParte" />
					<prop source-name="ID" dest-name="ID" type="N" key="true" saved-source="true" />
					<!--
						En esta coll indicamos la variable a la que van a ir los datos y su tipo
 
						source-table	es el SQL del que vamos a sacar nuestros datos filtrados por el ID que cogimos antes arriba.
						dest-table		Nombre de la variable a donde van los datos
						name			Nombre de la variable a donde van los datos
						desc			Tipo de la variable
					-->
					<coll dest-table="Parte" name="Parte" desc="XOneWrapper.Datos.PARTE_USUARIOS" 
					source-table="SELECT t.ID, t.CAMPO1, t.CAMPO2 FROM tabla1 t where t.ID=##ID##">
						<prop source-name="CAMPO1" dest-name="IDNUMPERSO" type="N">
							<oper type="copy"/>
						</prop>
						<prop source-name="CAMPO2" dest-name="IDPA" type="N2">
							<oper type="copy"/>
						</prop>
					</coll>
					<!--
						Indicamos en el nodo after-action que queremos que se ejecute para cada registro
					-->
					<after-action each-record="true">
						<!-- execute-query: Lo que queremos es ejecutar una consulta a una base de datos -->
						<action name="execute-query">
							<!-- aqui el SQL que queremos ejecutar filtrado por los ID's de nuestros registros -->
							<param name="source" value="update tabla1 set CAMPO6='4432' WHERE ##SOURCEKEYVALUES##"/>
						</action>
					</after-action>
				</coll> 
			</transform><!-- FIN TRANSFORMACIÓN DE ASOCIAR USUARIO PARTE -->
		</transforms><!--FIN de las transformaciones de SUBIDA -->
	</interface><!-- FIN Nodo interface -->
</xml>