User Tools

Site Tools


en:wiki:3.-servidor:3.9.-bus-integracion:c.-conector-base-datos:start

Integration Connector between Databases

Configuration


The database connector is installed as a service, and for its proper functioning it is necessary to configure 2 files:

Xone ITF Advanced.exe.config

This file is the first one that reads the executable, and it is used to indicate which one is the configuration file of main data, as well as the name.
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="Application Name" value="XOne ITF Advanced" />
    <add key="Configuration Path" value="C:\Archivos de programa\XOne\XOne ITF Advanced\ITF Config.xml" />
    <!-- Activar Seguridad SSL en el envio de correo -->
    <add key="mailSettings.EnableSsl" value="true" />
    <!-- Tiempo estipulado en segundos para reenviar un mensaje que ya ha sido enviado -->
    <add key="mailSettings.RetrySend" value="300" />
  </appSettings>
  <system.net>
    <mailSettings>
      <smtp from="">
        <network host="" userName="" password="" />
      </smtp>
    </mailSettings>
  </system.net>
</configuration>



ITF Config.xml

File which we program our interface with. It should be noted as an important point, that the same executable allows you to have several systems running at the same time, so that we can have several integrations that run against different database with a single executable. It is also commonly used to separate the upload with the download of data to be mobilized.

<xml>
	<!-- IMPORTANTE MANTENER COMO ESTAN LAS MAYUSCULAS O MINUSCULAS PARA LOS SETTINGS DE LA APLICACION -->
        <!-- Descripción Atributos:
           name: nombre de la interface
           versión: versión de la interface para su mejor visión en el visor de sucesos
           disabled: con este parametro activaremos o desactivaremos la interface
 
        -->
	<itf name="ITF 1" version="1.0" disabled="false">
                <!-- Para controlar el intervalo con el que se ejecuta la aplicación, tenemos dos opciones: Interval o cron-expression.-->
                <!-- Solo se debe de quedar activo uno de los dos nodos. Si estan los dos, predomina el Interval. -->
                <!-- El Interval da el tiempo en segundos. Tiene la limitación de 21600 segundos -->
		<item name="Interval" value="1"/>	
                <!-- El cron es mucho mas amplio y es el que se debe utilizar, ya que tienes un control total del tiempo. -->
                <!-- Mirar este enlace para documentarse: 
                 http://www.xoneisp.com/xonewiki/doku.php?id=f.-herramientas:f02.-xonemonitor:start#anexo_i_expresiones_cron              
                -->
                <item name="cron-expression" value="0 0/30 7-20 ? * *"/>
		<!-- ruta donde se encuentra el xml de configuracion de aplicacion-->
		<item name="XML Path" value="C:\Archivos de Programa\XOne\itf\itf1.xml"/>		
		<!-- Valor maximo de valores cacheados. Los valores que cachea son los filtros a buscar en el mapval -->
	        <item name="MaxCacheValues" value="15000"/>    
		<!-- Valor del MID con el que escribiremos en la base de datos -->
		<item name="MID" value="999"/>		
		<!-- Cuenta de correo a donde queremos que reporte los errores y advertencias que aparezcan -->
		<item name="Mail To" value="correo1@xone.es"/>
		<item name="Mail To 2" value="correo2@hotmail.com"/>
		<item name="Mail To 3" value="correo3@gmail.com"/>
                <!-- Creacion por defecto de la tabla interclient_sync_tables en caso de no existir en la base de datos, para control de cambios -->
		<item name="Inter Sync Create" value="CREATE TABLE interclient_sync_tables (ID int(11) NOT NULL Identity(1,1),   OBJECTNAME varchar(150) NULL,LASTMODIFIED datetime NULL, PRIMARY KEY (ID))"/>
 
                <!-- Parametros de conexion de tabla de eventos. 
                     Si queremos pasar el log a Base de datos en lugar de hacerlo en el visor de sucesos.
                     Habría que poner el nodo itflog en cada itf que queramos guardar log en base de datos.
                     En la siguiente línea, name especifica el nombre de la tabla de log.
                -->
		<itflog name="ITFLOG" provider="System.Data.OracleClient" connstring="Data Source=BASEDATOS;User id=USUARIOQUESEA;Password=CLAVEQUESEA"/>
 
		<!--  Estructura de la tabla ITFLOG:
 
				Nombre       Nulo     Tipo           
				------------ -------- -------------- 
				ID           NOT NULL NUMBER(11)     
				APPNAME               VARCHAR2(50)   
				APPVERSION            VARCHAR2(50)   
				EVENTDATE             VARCHAR2(50)   
				EVENTTYPE             NUMBER(4)      
				TRANSFORM             VARCHAR2(50)   
				EVENTMESSAGE          VARCHAR2(4000)
 
		-->
	</itf>
 
	<itf name="ITF 2" version="1.5" disabled="false">
		<item name="Interval" value="1"/>
		<item name="XML Path" value="C:\archivos de programa\XOne\itf\itf2.xml"/>
		<item name="MaxCacheValues" value="1000"/>
		<item name="MID" value="777"/>
	</itf>
	<!--
	...
	<itf>
	...
	</itf>
	...
	-->	
</xml>


You can check the different CHRON configurations, to set the date and time you want to make the different tasks: Aquí.

Interclient


Configuration file where the integration itself will be carried out between the source system, the client's system, and the XOne system.


The basic functioning of the interface is the following one:

  1. In each transform there is a coll node with a source-table and dest-table attributes, which will indicate us the table or view of origin of the data and the table of destination of the same.
  2. There is an “SQL” attribute that defines us the query we are going to send over the source that is going to delimit us the data which we are going to try to transfer to the destination table. (It is advisable to limit this query so that if there are many records, those ones are transfered in groups of 1000 records for instance, by adding the LIMIT, TOP, ROWNUM clauses, or whatever corresponds to the DBMS we are using ).
  3. In the fields that we are going to transfer, we must put a key=“true” attribute in those fields that are going to define us a record as unique, which it could be a field that already have as unique key or it could be the combination of several fields that its conjunction make that the record may be located unequivocally. Besides with the key=“true” attribute in the different fields, there is an attribute to COLL level that enables to the machinery to keep the keys of the records that fall within the transformation. This attribute is: saved-source=“true” and saved-dest=“true” to keep the keys of the records of the source and destination respectively. These two attributes fulfill the value to the ##SOURCEKEYVALUES## and ##DESTKEYVALUES## macros.
  4. Once we have the key field or fields (The ones that have the key=“true” attribute defined), the interface looks in the destination to see if already there is a records with these values, in case it does not exist, an INSERT will be made, and if these values already exist in the destination, an UPDATE will be made from those fields that change value in the transformation.
  5. Finally, by general rule, we will want to mark the source of the records that we have processed, by putting them with some mark to know that this records we already have them “synchronized” in the destination. For this, we make use of the “after action” node in which we will put the UPDATE over the data source to put these “marked” records as processed ones.


Nodes
<?xml version >=“1.0” encoding=“ISO-8859-1”?> Node to put at the upper part of the file. It is useful to be able to use the ISO-8859-1 codification, and admits, among many other things, the accents. OPTIONAL.
<xml> Main node, it is the father of the all nodes, and every file must have this node, in which everything else will go. MANDATORY.
<interface> Node child of the xml node, within this will go the rest of the options. MANDATORY.


APP Node

APP

Node in which is define who are the source and destination databases to be able to make the interface. MANDATORY. This node, has several attributes, such as:

Attributes Description
NAME “INTERFASE”: application name
LOGOPER “true”: to enable the LOG, by showing everything the CGInterclientXone does, in the Windows Events Visor.
SQLFIELDNAME the application when transfers the data from a database toward another one, also, it writes in the replica queue. This table, has a SQL field, which the replica SQL are entered. So, that name of that field is a reserved word in some database systems such as the MYSQL 5.0. What we have to do is create the replica queue, and change the SQL name for any other, and then, this name is put in this attribute, in order the program knows the change there is.
TBL-QUEUE if we want to fulfill or we want not the value of the TBL field in the master_replica_queue table. It will take values true or false
SLASH attribute that we can indicate not to duplicate the “\” character. The program duplicates automatically, but there are some database systems that it is not necessary to duplicate them, by putting this attribute to false. Duplicate is because there are systems that this attribute is a reserved word, and to tell it that we do not want to do anything, we duplicate it, and then the system removes the duplicate.
ROWIDFIELDNAME attribute to change the value of the ROWID mandatory field. This is usually used when the replica database is in ORACLE, since the ROWID is a reserved word or that system. Only for the Server database.
ROWIDFIELDNAMEDEST attribute to change the value of the ROWID mandatory field. This is usually used when the replica database is in ORACLE, since the ROWID is a reserved word for that system. Only for the Devices database.
ORACLE-SESSION modifying the session in Oracle in which among other options we can change date format with which we want to work in the ITF, it is executed over each provider connection OracleClient, by default ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'


Connection

Node in which are the calls to the database with which the interface interacts. It consists of child nodes, with calls to the database.

Nodes Description
client Node that has the call to the source database, the database to mobility which always will be the database of the client.
source Node that has the call to the destination database, the XOne database.
optional It is a connection with an optional database (auxiliary) being able to be the same that the one that exists in the client or source nodes, or it may be a different connection. It can be put all the optional nodes we need, it is not limited to 1. They are called by making reference to its name (it is explained below in DATA-SOURCE, DATA-DEST).



Provider Connstring
MYSQL connstring=“Persist Security Info=False;database=salesdemo;server=IPSERVIDORBD;user id=user;Password=pass”
SQLSERVER connstring=“Data Source=IPSERVIDORBD;Initial Catalog=beltranprod;Persist Security Info=True;User ID=user;Password=pass”
ORACLE connstring=“Data Source=CGS;Persist Security Info=True;User ID=user;Password=pass;Unicode=True”
ODBC DSN=nombredsnsistema;uid=usuario;pwd=clave
OLeDB provider=ej.[SQLOLEDB.1,MSDAORA…];data source=DATASOURCE;user id=USER;password=PWD..



Sample code of the APP node. Each of the connections that can be identified is specified.

<app name="ITF_PRUEBA" logoper="false" rowidfieldname="CGSROWID"  sqlfieldname="SQL" oracle-session="ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'">
   <!--
     logoper - deja log de todas las operaciones que realiza la interface, por defecto es false
     rowidfieldname - nombre del campo ROWID, por defecto es ROWID pudiendo cambiarse para gestores como ORACLE
     rowidfieldnamedest - nombre que va a tomar el campo ROWID en el SQL del Queue, pudiendo ser diferente al rowidfieldname, por defecto es ROWID
     tbl-queue - si queremos rellenar o no el valor del campo TBL. Por defecto **true**
     sqlfieldname - nombre del campo en el que vamos a introducir el insert, update o delete ejecutados en el queue, siendo por defecto SQL
     oracle-session - modificar la sesión de oracle en la que entre otras opciones podemos cambiar el formato de fecha con el que queramos trabajar en la ITF, se ejecuta sobre cada conexión provider OracleClient, por defecto ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
   -->
   <connection>
	<!--
	provider: especificamos el provider de la conexión, interpretando SqlClient, OracleClient, MySQLClient, SQLServerCE, 
	OleDB y ODBC. Por defecto es ODBC.
	connstring: cadena de conexión de la base de datos
        -->
	<client name="NOMBRE1" provider="System.Data.PROVIDER" connstring="Data Source=ORACLESRC;Persist Security Info=False;User ID=CGSOFT;password=CGSOFT;Unicode=True"/>
	<source name="NOMBRE2" provider="System.Data.PROVIDER" connstring="Data Source=CGSOFTBD;Persist Security Info=False;User ID=CGSOFT_ITF;password=CGSOFT_ITF;Unicode=True"/>
	<optional name="NOMBRE3" provider="System.Data.PROVIDER" connstring="......."/>
	<!--
	Se pueden definir todas las conexiones optinal que se deseen (conexion auxiliar), se llaman seleccionando 
	su "name" en data-source o data-dest dependiento de si se usan como source o dest en la transformación, 
	dentro del nodo coll
	-->
	<!-- Conexiones de ejemplo 
	**ODBC**:  dsn=nombredsnsistema;uid=usuario;pwd=clave
	**OleDB**: provider=ej.[SQLOLEDB.1,MSDAORA...];data source=DATASOURCE;user id=USER;password=PWD..
	**System.Data.SqlClient**: User ID=USER;Password=PWD;Initial Catalog=BD;Data Source=DATASOURCE...
	**MySql.Data.MySqlClient**:Database=[NOMBRE BD];Data Source=[SERVIDOR{localhost...}];User Id=[USER];Password=[PASSWORD]
	**System.Data.OracleClient**: Data Source=[datasource{tns service name}];Persist Security Info=False;User ID=[user];Password=[password];Unicode=True 
	-->
    </connection>
</app>




TRANSFORMS Node

TRANSFORMS

Node that defines the address of sending the data, by deciding which connection is the source and which one is the destination, being able to vary between client-to-source or source-to-client.

ADDRESS TYPE DESCRIPTION
client-to-source Definition of the source table the connection of the client node, and as destination the sourceconnection. This connection is the one used to replicate the information to the devices, since the SQL sentences are inserted in the master_replica_queue table.
source-to-client Definition of the source table the connection of the source node and as destination the client connection. With this option nothing is written in any additional table, only in the one that is defined to which table the destination data go.





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

TRANSFORM Node

TRANSFORM

Node that defines the data transfer to be made. Each transform is for one or several tables dependent on each other, and only have an attribute, name, which must be unique for the whole xml configuration file of the interface. If there is any error, the log will show the name of the transform where there is in and where the possible error did occur.

It is important to remark that if there is a fail when executing the active transform , it will skip to the next transform node, in such way that the tables dependent on each other, must go in the same transform node consecutively. So, the not dependent tables or in which ones an error is produced in the previous one we can continue with it, they must go in different transform nodes.



Transformation with a single coll node, in such way that the table does not depend on other tables.

<transform name="tabla_sin_dependencias">
       <coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
	FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
	WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
	AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
		</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
	    <oper type="mapval" source="gen_tarifas" targetfld="ID">
		<prop mapfld="CODSEC" mapvalue="##SELF##"/>
    	    </oper>
	</prop>
	<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
		<oper type="copy"/>
	</prop>
	<delete>
	        <action name="delete-conditional" delete-dest-table="gen_DetTarifa" use-saved-dest="true"/>
	</delete>		
	<after-action>
		<action name="execute-query">
			<param name="source" value="delete from carga_parcial	WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
</transform>


Transform with two coll nodes, that have been inserted in the same transform, because they are tables dependent on each other, and if they do not transfer the data of the first one, the second one must not be inserted.

<transform name="gen_dettarifacgs_cparcial"> 
	<coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
	FROM TARIFAS_CGS a,ARTICULOS_CGS a2
	WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
		</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
		<oper type="mapval" source="gen_tarifas" targetfld="ID">
			<prop mapfld="CODSEC" mapvalue="##SELF##"/>
		</oper>
	</prop>
	<prop dest-name="UTARREM" type="N">
			<oper type="setval" value="9"/>
	</prop>
	<prop source-name="U_TAR_REM" dest-name="UTARREM" type="N">
			<oper type="copy"/>
	</prop>
	<after-action>
		<action name="execute-query">
			<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
	<coll dest-table="GEN_DETTARIFA" source-table="
	SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
	FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
	WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
	AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
	<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
	</prop>
	<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
		<oper type="mapval" source="gen_tarifas" targetfld="ID">
			<prop mapfld="CODSEC" mapvalue="##SELF##"/>
		</oper>
	</prop>
	<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
		<oper type="copy"/>
	</prop>
	<after-action>
		<action name="execute-query">
			<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
		</action>
	</after-action>
	</coll>
</transform>




COLL Node

Node in which the source and destination tables of the data to insert are taken.

The attributes of the node are:

ATTRIBUTE DESCRIPTION
dest-table Name of the destination table.
source-table Selection from a query of the data to integrate from the source into the destination.
rows-limit DEPRECATED. If we want to limit the number of records processed, we should have to rely on the functions of the DBMS we are using (TOP, LIMIT, ROWNUM, etc).
norowid Its value by default is false. With true value it would not fill the ROWID field of the DEST-TABLE table.
type-not-allowed It may take values 1 yand3, in such way that if the value is 1, it will not execute inserts and with value 3 it will not execute updates.
extrainfo By defaulttrue. With false value it would not let any record in the master_replica_queue table.
data-source Attribute that admits the name of any connection <optional> over we want to get the source of the coll data, if we want it to be different from the one that was configured by default in the client node or source node in interface / app / connection.
data-dest Attribute that admits the name of any connection <optional> over we want to get the destination of the coll data, if we want it to be different from the one that was configured by default in the client node or source node in interface/app/connection.
always-insert Attribute that if we are going to make an update by change in the data transfered, really at the replica queue: master_replica_queue, it puts it like an insert.
repeat-until-end With true value (false by default) it will repeat that transformation until the query stops returning results.
continue-on-error With true value (false by default) it will continue with the rest of transformations, although the current one is giving errors.



Within the coll node, each and every one of the fields that have to be transfered from a table into another one. For this, we have to put different prop nodes, which will be the ones that have the source field name and the destination field name.
Later, we will put several attributes defining the type or which type of action we need to do.


PROP Node

Node where is defined which field will be transfered and which action will be made in the destination table.
The attributes of this node are:

ATTRIBUTE DESCRIPTION
source-name Name of the source field.
dest-name Name of the destination field.
type Attribute to tell it the type of data of the field which is going to be transfered. The options are: T(type text field). Nx:(numeric field. The x character means that it can be put the number of decimals it has. It will vary between not putting anything, N, or putting the number of decimals that are going to be transfered, N2, N3, N4, etc.) D:(type date field.) X:(To pass data that are going to be keys.)
key To tell it that the field is primary key or not in the destination table. Searches for this field are performed if the record exists in the destination table. The key can of course be multiple, so there can be more than one field that is the primary key of the record. This field is mandatory that at least one of the “prop” that has the “coll”, have this attribute to true .
saved-source Attribute that is going to save the value of the source-name field in a macro, to later perform operations over the fields that meet the condition that the macro saves. The macro is ##SOURCEKEYVALUES##.
saved-dest Attribute that saves the value of the dest-name field in a macro, to later operate with the records that meet the condition. The macro ##DESTKEYVALUES##.
not-replicate Attribute to indicate that if the operation of this field is inserted or it is not in the operations of the master_replica_queue. If it is true this value is not inserted, if it does not exist or it is false, it will be inserted at the replica operations.
always-insert If the attribute is true we are forcing it to insert an update in the “master_replica_queue” replica queue, although its value does not change.



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

OPER 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.

SETVAL Action


In this operation, what is done is to put a given value in the destination field. Therefore, the prop node, only has the type of the field, type , and the name of the field that is the destination, dest-name .
In the example, it can be seen 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 value “xone” in the destination field NAME, which is of type text.

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

COPY Action

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. You can also see how it is the primary key, so to find if there is such a record in the destination, you will search for the value of this field. It is also being told to save the value of the source in the ## SOURCEKEYVALUES ## macro, and to save the value of the destination in the ## DESTKEYVALUES ## macro.

<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 to 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 to the “INCIDENCE” field. It is of text type. We also see that it has the attribute “always-update”, so we are forcing it into the “master_replica_queue” replication 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 to the “DATE” field. The field is a 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 told in the value attribute. In the example, when you 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 to the “TIPOPARTE” field, and we see that it is of 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. If the attribute is not defined is “true”, that is, it deletes the blank spaces. In order it works, 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 to the “PRICE” field. It is numeric, and specifically with 5 decimals. 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>

MAPVAL Action


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 say 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 it, it takes the value of the field that is defined in the attribute targetfld , “ID” for this case, and put it in the field dest-name , field “IDEMPRESA”.

In SQL: IDEMPRESA=SELECT ID FROM Gen_Empresa WHERE ETIQUETA=delegacion

<prop source-name="delegacion" 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 operation, 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>  

MAIL Action

If we want to send a certain email with fields rescued in the coll query, we will use the type=“mail” oper.
For the other side, in the mailfield attribute we specify which type of value is going to be transfered, by being able to take the values: To, Subject, Message and File.

In the end, there is the value attribute, which is the one that transfers the value chosen in the SQL.

The interface will try to send an email as long as the oper with value To is defined.

<prop source-name="DESTINO" type="T">
	<oper type="mail" mailfield="To" value="##SELF##"/>
</prop>
<prop source-name="ASUNTO" type="T">
	<oper type="mail" mailfield="Subject" value="##SELF##"/>
</prop>
<prop source-name="MENSAJE" type="T">
	<oper type="mail" mailfield="Message" value="##SELF##"/>
</prop>
<prop source-name="FICHERO" type="T">
	<oper type="mail" mailfield="File" value="##SELF##"/>
</prop>

AFTER-ACTION Node

Node that is executed when we finish performing the data transfer from one table to another.

EXECUTE-QUERY Action

It executes a SQL. This is quite useful to put a field in the source table with a value, to indicate that this value it was already transfered to the destination table.

At the example, we can see that it has two param nodes, so we are telling it which SQL is going to be executed, in the value attribute. Also, in which database is put “dest” to the name attibute, that is in the destination database, and “source”, that is the source database.

Besides, we can see that it uses the ##SOURCEKEYVALUES## and ##DESTKEYVALUES## macros, that have been filled while the data are being transfered from a database into another one.

<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>

BEFORE-ACTION Node

Node that is executed before starting the data transfer. You can use all the operations that exist in the after-action node.

DELETE Node

Node to perform an erasure of the indicated data. We can delete both the origin and destination.

At the examples we see below, we only see the “delete” node, but we have to keep in mind that we must do a “coll” node as if we were to transfer data from a table into another one, so it must have as minimum a “prop” node which has the “key” attribute equal to “true”.


There are several types of “delete” operations to be made, such as:

DELETE Node Actions DESCRIPTION
delete-conditional This is to make the data deletion which coincides with the data searched in the source, from the field or fields which coincide with the primary key we have defined.
delete-sql This operation is useful to make the deletion both in the client and in the XOne database, so that, besides clearing the normal table, it inserts the deletion at the replica queue.
delete-fichero Operation useful to make the deletion of a file.


DELETE-CONDITIONAL


This is to make the data deletion that coincide with the data searched at the source, from the field or fields which coincide with the primary key we have defined.

Example of use

<delete>
	/* En el ejemplo se realiza el borrado en la tabla “gen_sat” y "gen_usuarios, de la base de datos de destino, 
	   por utilizar el atributo “delete-dest-table”, de aquellos datos que coinciden con la macro ##DESTKEYVALUES##. */
 
	<action name="delete-conditional" delete-dest-table="gen_sat" use-saved-dest="true"/>
	<action name="delete-conditional" delete-dest-table="gen_usuarios" use-saved-dest="true"/>
 
 
	/* Con este ejemplo se realiza el borrado en la base de datos origen. 
	   Para indicar una tabla de origen se utiliza el atributo “delete-source-table”, 
	   con este atributo se utiliza la macro ##SOURCEKEYVALUES##. */
 
	<action name="delete-conditional" delete-source-table="EVUSUA" use-saved-source="true"/>
</delete>


DELETE-SQL


This operation is useful to make the deletion both in the client and in the XOne database, so that besides clearing the normal table, it inserts the deletion in the replica queue.


Attributes
WHERE It is the deletion condition. The macros ##SOURCEKEYVALUES## and ##DESTKEYVALUES## can be used.
DELETE-DEST-TABLE Attribute to indicate in which table we want to make the deletion. If this attribute is not put, we take the value there is in attribute of the “coll” node, “dest”. It makes the deletion of the records that have the ROWID that meet the condition put in the “where” attribute.
SELECT-DEST-TABLE Attribute to make the search of the records we want to delete. It may be any table, but we have to keep in mind, that the search of the values is made in that table, by taking the ROWID of the records that meet the “where” attribute, and from that ROWID the deletion is made. If nothing is put, the value there is in the attribute of the “coll” node, “dest” is taken.
ALIAS-DEST-TABLE Alias of the table from the search is made, and later the deletion.
SOURCE-TABLE To indicate from which source table we want to make the deletion.
DELETE-SOURCE If this attribute is declared, and besides the “source-table” attribute is declared, it makes the deletion in the source table also, with the condition put in the “where” attribute.



Example of use:

<delete>
	/* 1.- Se realiza la búsqueda de en la base de datos de destino, en la tabla “gen_colla”(select-dest-table) de los ROWID 
	   que cumplen la condición del “where”(where), con el alias “c”(alias-dest-table). 
 
	   SELECT c.ROWID FROM Gen_Colla WHERE ESTADO=1
 
	   2.- Si hay valores con esa condición, los borra de la tabla “gen_colla”( delete-dest-table).
	   3.- Si se encontró algún registro con el punto 1, existe el atributo delete-source=”true”, el atributo “source-table” 
	       con un nombre de tabla y hay una condición, se realiza el borrado en la tabla “Gen_USUARIOS”( source-table), con la condición
 	       que hay en el atributo “where”. */
 
	<action name="delete-sql" source-table="Gen_USUARIOS" delete-source="true" where="ESTADO=1" delete-dest-table="Gen_Colla" 
	 select-dest-table="Gen_Colla" alias-dest-table="c"/>
</delete>			


DELETE-FILE


Operation useful to make the deletion of a file.

Example of use:

<delete>
	/* Realiza el borrado del fichero que existe en el atributo “PATH”, con lugar y nombre del mismo. */
 
	<action name="delete-fichero" path="C:\Archivos de programa\CGSoft\CGInterclient\Captura\Ficheros\ESPPDA.TXT"/>
</delete>

DATA-CONDITION Node


For changes control.

For this it will be put within the nodo client, with type=“##LASTMODIFIED##“, in which it will enter when finishing the transformation in the interclient_sync_tables table, the values defined in the data-condition, where ##LASTMODIFIEDDATETIME## is the date and the time when the transformation starts.

<data-condition>
	<client type="##LASTMODIFIED##">
		<param name="FECHA" value="##LASTMODIFIEDDATETIME##">
			<format value="MM-dd-yyyy"/>
		</param>
		<param name="HORAINI" value="##LASTMODIFIEDDATETIME##">
			<format value="HH:mm:ss"/>
		</param>
		<param name="HORAFIN" value="##NOW##">
			<format value="HH:mm:ss"/>
		</param>
	</client>
</data-condition>


Within the client node, the “param” node will be put, which has the attributes:

PARAM Node Attributes
NAME destination field of the interclient_sync_tables.
VALUE value that the field will take.


MACRO Node


Macros as auxiliary values can be used, both to a source-table level within the coll node, and to performance an action.

Within the source-table attribute, to a coll level.

<coll name="COLL_PASAR" source-table="SELECT t.* FROM Tabla1 t WHERE t.CAMPO='Valor1' and t.FECHA>='##MACRO##'" ...


Here the “macro” node is shown, which will be within the “coll” node.

<macro name="##MACRO##" source-table="XONEPIMAUX" sql="SELECT TIMESTAMP AS X FROM INTERCLIENT_SYNC_TABLES WHERE OBJECTNAME='gen_contactos'" field="X" type="T">
	<format value="yyyy-MM-dd HH:mm:ss"/>
</macro>

The macro node attributes are:

Attributes Description
NAME Name used to call to the macro that we will create.
SOURCE-TABLE Connection over which we are going to call to the macro, being mandatory to be about an optional connection.
SQL Query to choose the values which we are going to replace the macro with. It will take the first record returned, in case more than 1 records will be returned. In case of returning more than 1 field, the first field obtained, as long as the field attribute do not specify it.
FIELD Query field that we are going to return, by default it will be first one that exists in case not being FIELD.
TYPE Attribute which returns us the type of data, being possible the same types that exist in the props nodes which will be detailed below.


Example Code


It is an example code about how an interclient.xml file will be.

<xml>
	<interface>
		<app name="ITF_PRUEBA" logoper="true" rowidfieldname="CGSROWID"  sqlfieldname="SQL" oracle-session="ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'">
			<connection>
				<client name="NOMBRE1" provider="System.Data.PROVIDER" connstring="Data Source=ORACLESRC;Persist Security Info=False;User ID=CGSOFT;password=CGSOFT;Unicode=True"/>
				<source name="NOMBRE2" provider="System.Data.PROVIDER" connstring="Data Source=CGSOFTBD;Persist Security Info=False;User ID=CGSOFT_ITF;password=CGSOFT_ITF;Unicode=True"/>
				<optional name="NOMBRE3" provider="System.Data.PROVIDER" connstring="......."/>				
			</connection>
		</app>
		<transforms type="client-to-source">
			<transform name="gen_usuarios_cp">
				<coll dest-table="GEN_USUARIOS"
				source-table="SELECT a.*, 1 AS IDEMPRESA,trim(TARIFA) || '-' AS CODSEC FROM DELEGADOS_CGS a
				WHERE a.CG_ESTADO IN(7)">
					<prop source-name="DELEGADO" dest-name="LOGIN" key="true" saved-source="true">
						<oper type="copy"/>
					</prop>					
					<prop source-name="NOMBRE" dest-name="NOMBRE" type="T">
						<oper type="copy"/>
					</prop>													
					<prop source-name="CODSEC" dest-name="IDTARIFA" type="T">
						<oper type="mapval" source="gen_tarifas" targetfld="ID">
							<prop mapfld="CODSEC" mapvalue="##SELF##"/>
						</oper>
					</prop> 
					<prop source-name="TARIFA_ESPECIALISTA_SN" dest-name="TARIFA_ESPECIALISTA_SN" type="N">
						<oper type="copy"/>
					</prop> 
					<prop  dest-name="TIPO" type="N">
						<oper type="setval" value="1"/>
					</prop>	
					<after-action>
						<action name="execute-query">
							<param name="source" value="UPDATE DELEGADOS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
						</action>
					</after-action>	
				</coll>
			</transform>
			<transform name="gen_dettarifacgs_cparcial"> 
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
				FROM TARIFAS_CGS a,ARTICULOS_CGS a2
				WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
				<data-condition>
						<client type="##LASTMODIFIED##">
							<param name="FECHA" value="##LASTMODIFIEDDATETIME##">
								<format value="MM-dd-yyyy"/>
							</param>
						 	<param name="HORAINI" value="##LASTMODIFIEDDATETIME##">
							 	<format value="HH:mm:ss"/>
							</param>
							<param name="HORAFIN" value="##NOW##">
							 	<format value="HH:mm:ss"/>
							</param>
						</client>
					</data-condition>
					<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
					</prop>
					<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
						<oper type="mapval" source="gen_tarifas" targetfld="ID">
							<prop mapfld="CODSEC" mapvalue="##SELF##"/>
						</oper>
					</prop>
					<prop source-name="ARTICULO" dest-name="IDARTICULO" key="true" type="N">
						<oper type="mapval" source="gen_articulos" targetfld="ID">
							<prop mapfld="REFERENCIA1" mapvalue="##SELF##"/>
						</oper>
					</prop>
					<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T">
						<oper type="copy"/>
					</prop>			
					<prop source-name="U_MAXIMA" dest-name="CANTMAX" type="N">
						<oper type="copy"/>
					</prop>
					<after-action>
						<action name="execute-query">
							<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
						</action>
					</after-action>
				</coll>
			</transform>
			<transform name="gen_dettarifacgs_cparcial"> 
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC
				FROM TARIFAS_CGS a,ARTICULOS_CGS a2
				WHERE a.ARTICULO=a2.ARTICULO AND a.CG_ESTADO IN(7)">
				<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
					</prop>
				<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N">
					<oper type="mapval" source="gen_tarifas" targetfld="ID">
						<prop mapfld="CODSEC" mapvalue="##SELF##"/>
					</oper>
				</prop>
				<prop dest-name="UTARREM" type="N">
						<oper type="setval" value="9"/>
				</prop>
				<prop source-name="U_TAR_REM" dest-name="UTARREM" type="N">
						<oper type="copy"/>
				</prop>
				<after-action>
					<action name="execute-query">
						<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
					</action>
				</after-action>
				</coll>
				<coll dest-table="GEN_DETTARIFA" source-table="
				SELECT a.*,trim(a.TARIFA) || replace(a.SECUENCIA,' ','-') AS CODSEC,b.ID
				FROM TARIFAS a,CARGA_PARCIAL b,ARTICULOS a2
				WHERE b.ARTICULO=a2.ARTICULO AND a.ARTICULO=b.ARTICULO AND a.TARIFA=b.TARIFA AND a.SECUENCIA=b.SECUENCIA
				AND b.TABLA='TARIFAS' AND b.ESTADO='B'">
				<prop source-name="ID" dest-name="IDTARIFA" key="true" saved-source="true" type="N" saved-dest="false">
				</prop>
				<prop source-name="CODSEC" dest-name="IDTARIFA" key="true" type="N" saved-dest="true">
					<oper type="mapval" source="gen_tarifas" targetfld="ID">
						<prop mapfld="CODSEC" mapvalue="##SELF##"/>
					</oper>
				</prop>
				<prop source-name="SECUENCIA" dest-name="SECUENCIA" key="true" type="T" saved-dest="true">
					<oper type="copy"/>
				</prop>
				<after-action>
					<action name="execute-query">
						<param name="source" value="UPDATE TARIFAS_CGS SET CG_ESTADO=5 WHERE ##SOURCEKEYVALUES##"/>
					</action>
				</after-action>
				</coll>
			</transform>
		</transforms>
		<transforms type="source-to-client">
			<transform name="clientes_ent_cgs">
				<coll dest-table="CLIENTES_ENT_CGS" 
				source-table="SELECT c.*,
				SUBSTR(c.CP,1,5) AS CP,SYSDATE AS MAP_FMODIF,
				SUBSTR(replace(replace(c.CUENTABANC,'-',''),' ',''),1,20) AS MAP_CUENTABANC,u.LOGIN AS DELEGADO,t.CODIGO AS TARIFA,g.CODIGO AS GRUPO_COMPRAS,p.CODIGO AS POTENCIAL_MERCADO,
				NVL(c.FORMAPAGO,'_') AS MAP_FORMAPAGO
				FROM ((((Gen_Clientes_Mod c
				LEFT OUTER JOIN Gen_Usuarios u ON c.IDUSUARIO=u.ID)
				LEFT OUTER JOIN Gen_Tarifas t ON c.IDTARIFA=t.ID)
				LEFT OUTER JOIN Gen_GruposCompra g ON c.IDGRUPOCOMPRA=g.ID)
				LEFT OUTER JOIN Gen_PotencialMercado p ON c.IDPOTMERCADO=p.ID)
				WHERE (c.MODIFICADO=1 or c.MODIFICADO=3) AND u.LOGIN IS NOT NULL AND c.EXPEDIENTE IS NOT NULL AND c.EXPEDIENTE&lt;&gt;''
				">
					<prop source-name="EXPEDIENTE" dest-name="CLIENTE" type="N" key="true">
						<oper type="copy"/>
					</prop>
					<prop source-name="DELEGADO" dest-name="DELEGADO" key="true" type="T">
						<oper type="copy"/>
					</prop>		
					<prop source-name="TIPO" dest-name="TIPO_CLIENTE" type="N">
						<oper type="copy"/>
					</prop>					
					<prop source-name="ACCIONISTA" dest-name="ACCIONISTA" type="N">
						<oper type="copy"/>
					</prop>
					<prop source-name="MAP_FMODIF" dest-name="F_MODIF" key="true" type="T">
						<oper type="copy"/>
						<format name="dest-name" value="dd/MM/yyyy HH:mm:ss"/>   
					</prop>																																												
				</coll>
			</transform>
		</transforms>		
	</interface>
</xml>
en/wiki/3.-servidor/3.9.-bus-integracion/c.-conector-base-datos/start.txt · Last modified: 2018/11/16 17:52 by ejetoro