The XOne Interface is one of the components of XOne Technology, its function is to transfer or convert data from one database to another , through a simple XML configuration file.

The conversion or transfer of data does not necessarily have to be destined to another Data Base, being able to have as destination text files, XML …

Although this product can be used independently of the other XOne technology systems, it is usually used when customers do not want to make any changes to their systems and want to mobilize their application, creating an independent database for the mobility project that interacts with your system through the XOne Interface.

The file with the path where the configuration XML is, has a unique line where the path and the XML file name are put.

[XML]
 
Name=C:\Programas_Xone\CGInterClientXone\itfxone.xml

• Both the [XML] node and the “Name” attribute are mandatories.

• The XML configuration file here referenced, can be name as we want to, not necessarily itfxone.xml.

In this XML file everything is configured.

• Connection with the source database.

• Connection with the destination database.

• How often the process is executed.

• What is done with the source fields in the destination.

• Operations to made after passing the data from the source to the destination.

• It allows the data transfer of text, integer, with decimals type, of date type, files…
  

This file has 3 main nodes:

<?xml version="1.0" encoding="ISO-8859-1"?>
 
<xml>
 
	<interface>
 
		<app name="INTERFASE" logoper="true" sqlfieldname="CGSSQL"  slash="false" rowidfieldname="CGSROWID">
 
			<dsn>
 
			...
 
			</dsn>
 
			<schedule type="continue" interval="30"/>
 
		</app>
 
 
 
		<transforms type="client-to-source">
 
			<transform name="DELEGACION">
 
				<coll ..... >
 
				...
 
				</coll>
 
				...
 
			</transform>
 
			...
 
		</transforms>
 
 
 
		<transforms type="source-to-client">
 
			<transform name="pedidos">
 
				<coll ..... >
 
				...
 
				</coll>
 
				....
 
			</transform>
 
			...
 
		</transforms>	
 
	</interface>
 
</xml>

Node that is placed in the upper part of the file. It is useful so that it can be used the ISO-8859-1 codification, and admits the accents, among other things.

Main node, it is the father of all the nodes, and every file must have this node in which everything else will go.

Child node of the xml node, within this one the rest of options will go.

Node in which the origin and destination databases are defined to be able to perform the Interface.

MANDATORY. This node has several attributes, such as:

This table has an SQL field, which is used to enter the SQL of the replica. Well, that name of that field is reserved word in some database system, like MYSQL 5.0.

Then what you have to do is create the replica queue, and change the SQL name to any other name, and then put this name in that attribute, so that the program knows what change there is.

Duplicating is because there are systems, that attribute is a reserved word, and to tell you that we do not want you to do anything, because we duplicate it, and then the system removes the duplicate.

Node in which there are all the calls to the databases with the CGIntercliente acts with. It is made up of children nodes, with the calls to the databases.

<client type="system" name="DSN_BD_Origen" user="usuario" pwd="contraseña"/>
 
<source type="system" name="DSN_BD_Destino" user="usuario" pwd="contraseña"/>

The first node, CLIENT, is the node that has the call to the source database, to the client's database. The second node, SOURCE, is the call to the destination database, the mobility database, of XOne. These nodes have the following attributes:

There is a third node, that can be put for the multi connection.

<optional type="system" name="DSN_BD_OPTIONAL_CLIENTE" user="root"/>

Node in which is indicated how often the interface is executed.

It has two attributes:

Node with which it is decided in which order the data is passed, whether from the database declared in the CLIENT node to the SOURCE or vice versa. The type is put in the TYPE attribute, putting “client-to-source” or “source-to-client”, depending on the order.

Once we have decided in which order the data are transfered, we begin to define each one of the transform nodes. Each of these nodes is made, for each of the tables that are passed from one side to another.

This node has an attribute, NAME, which value is the name given to the transform node, and it must be unique in the whole XML file.

Node in which the source table and the destination of the data are taken.

Its attributes are:

<data-condition>  
 
<client type="##ALL##"/>    
 
</data-condition>

From this moment on, we start to put the different nodes, “prop”, that take the different fields, saying of each field, the type of transfer that is made of it, whether it is primary key or not, or the type of field that is.

Attributes of the “prop” node:

Types of operations than can be made with the fields we want transfer from a table into another one, in the “prop” node:

At the example, we are putting in the IDFATHER field of the destination table that is numeric type, N, the value 1.

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

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

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

Now, we are going to put different examples that made this operation:

EXAMPLE 1
Node which transfers the value of the “delegación” field to the “ETIQUETA” field. This field is text type. Also is a primary key so to look if this field exists in the destination, we look with the “where” by this field. Besides, it is being told that saves 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>

EXAMPLE 2
Node which transfers the value of the “PASSWORD_E” field to the PWD field. As we can see it has the X type, so it is being told that is a password field, and its value is saved encrypted.

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

EXAMPLE 3
Node which transfers the value of the “DESCPTE” field to the “INCIDENCIA” field. It is text type. Also, we can see that it has the “always-update” attribute, so we are forced it to insert at the “master_replica_queue” replica queue an update although its value does not change.

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

EXAMPLE 4
Node which transfers the value of the “FECCARGA” field to the “FECHA” field. The field is date type. Also, we can see a new node, FORMAT, node used within the “prop” nodes, it is useful to format the value in the destination with the features indicated in the value attribute.
At the example, when putting it, we are telling that in the destination the date must be inserted in this way: day-month-year.

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

EXAMPLE 6
Node which transfers the data of the “CODAVISO” field to the “TIPOPARTE” field, and we see that is text type. Besides, we can see there is the “format” node. Here we can see it has a new attribute, “trim”.
This attribute deletes or not blank spaces that come from the source in the destination. If the attribute is not defined it is to “true”, that is, it deletes the blank spaces. In order it works, it is necessary to put in the “value” attribute, the “NULL” value.

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

EXAMPLE 7
Node which transfers the data of the “PRETAR” field to the “PRECIO” field. It is numeric type, and specifically with 5 decimals. In order the copy becomes well, it is being put the “format” node, by putting exactly the number of decimals that is going to be put.

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

EXAMPLE 8
Node which transfers the value of the “delegacion” to the “NOMBRE2” field. It is text type. Also we can see that it has the “fillfield” attribute, with which we are telling it that in the destination we have to add 0.
With the “length” attribute we tell it that the destination field must have 10 characters as total value, so the program takes the length of the source field, and the amount of characters having less than 10, is the number of 0 that has to insert in the destination field.
The 0 will be added by the right part, this is indicated with the “align” attribute.

        <prop source-name="delegación" dest-name="NOMBRE2" key="true" fillfield="0" length="10" align="rigth" type="T" saved-source="true" saved-dest="true">
 
	        <oper type="copy"/>					
 
        </prop>

The example saves the value that there is in IDUSUARIO into IDUSUARIO2. This is useful for the selectivity, to save the value in a second field, and this field is also a field which decides the selectivity of this table.

        <prop source-name=" IDUSUARIO " dest-name=" IDUSUARIO2"  type="N">
 
	         <oper type="source-copy"/>
 
        </prop>

The example sums the value there is in the source field of the source-name, AMOUNT, to the value there is in the destination of dest-name, AMOUNT.

        <prop source-name="CANTIDAD" dest-name="CANTIDAD"  type="N2">
 
	        <oper type="sum"/>
 
	        <format name="dest-name" value="0.00"/>
 
        </prop>

EXAMPLE
At the example we can see, we take the value of the “delegación” field and it is compared to the “ETIQUETA” field puts in the “mapfld” attribute that exists in the “Gen_Empresa” table, defined in the “source” attribute.
If it exists a record having that value, that there only has to be one, since the field by which the search is made in the “Gen_Empresa” table, has to be the primary key of it, taking the value of the field defined in the “targetfld” attribute, ID, and puts it in the “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>

EXAMPLE
Example that takes the value there is in “COD_EMP”, that has to exist as “prop” in the current “coll” and it looks for it in the “sql”, with or without where.
Then it concatenates the different values which meet the condition, by putting the value in OBSERVACIONES, separated by the value there is in the “separator” attribute. Also, there is the possibility to put the table option, that looks in the whole table, without condition or anything.
The example has the 3 possibilities of records search that the fields to concatenate have, but only one option is put.

 
<oper type="aggregate" function="CONCAT(##FLD_COD_EMP##)" separator="-">
 
        <param name="sql" value="select * from gen_usuarios"/> 
 
	<param name="sql" value="select * from gen_usuarios where COD_EMP=##FLD_COD_EMP##"/>
 
	<param name="table" table="gen_usuarios"/>
 
</oper>

This option concatenates as many times the “A” value separated by the value there is in the “separate” attribute, as many times the condition of the SQL value is met. It is like make a count with the select sent in the table that is set to perform the search.

        <oper type="aggregate" function="CONCAT(A)" separator="-">
 
	        <param name="sql" value="select * from gen_usuarios"/> 
 
	        <param name="sql" value="select * from gen_usuarios where COD_EMP=##FLD_COD_EMP##"/>
 
	        <param name="table" table="gen_usuarios"/>
 
        </oper>

Option that concatenates the first value defined in the gen_usuarios table, separated by the “separator” attribute, in the OBSERVACIONES field.

        <oper type="aggregate" function="CONCAT" separator="-">
 
        	<param name="sql" value="select * from gen_usuarios"/> 
 
        	<param name="sql" value="select * from gen_usuarios where COD_EMP=##FLD_COD_EMP##"/>
 
        	<param name="table" table="gen_usuarios"/>
 
        </oper>

Node that is executed when we finish performing the data transfer from one table to another. Within it, 5 different operations can be performed, which are:

Operation that deletes the destination table, or the source table.

In the example we see that in the attribute “name” is put “dest”, to indicate that we want to delete the destination table, if we put any other value, it deletes the source table.

     <action name="delete-table">
 
         <param name="dest"/>					
 
     </action>

It executes the SQL that is given to it to execute. This is quite useful to put a field in the source table with a value, to indicate that this value has already been passed to the destination table.

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

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

Operation that creates a file, in the place and name that is told, and also inserting the text that is put to it.

The example creates a file in the path “D:\Proyectos_Xone\Proyecto1\Interfase\2-pedidos”, the file is going to be named “OK.txt”, and at the same time the “OK” text is inserted.

<action name="create-fichero" path="D:\Proyectos_Xone\Proyecto1\Interfase\2-pedidos\OK.txt" texto="OK"/>

Operation that deletes a file. You put the place where it is and the name.

Example that deletes the “ESSPDA.TXT” file that there is in the path “C:\Archivos de programa\ CGSoft\ CGInterclient\ Captura\ Ficheros”.

<action name="delete-fichero" path="C:\Archivos de programa\ CGSoft\ CGInterclient\ Captura\ Ficheros\ ESPPDA.TXT"/>

Node to create a table in the destination, which will normally be the one that will take the destination data. It can be a table of a database or a text file. It is normal to use it to create the data destination file, when data from a database is transfered to text files.

<create name="dest-table" value="CREATE TABLE pedidos.txt (IDENTIFICADOR Char(11),PEDIDO char(15),CLIENTE char(11),ANYO char(4),MES char(2),DIA char(2),ARTICULO char(15),VENDEDOR char(11),CANTIDAD Char(9),PRECIO Char(15),ALMACEN Char(50))"/>
 
                                                                                                                                                    .

Node that is executed before starting the data transfer. You can use all the operations that exist in the “after-action” node, in addition to the one documented here.

This operation has been carried out exclusively to carry out the transfer of data from a database to files. What it does is find the file that is put in the path attribute. Search for that file and if it finds it, it does not go on and it stops until that file does not exist.

What the example does is to see if the PATH1 file exists, if the same exists, and PATH2 does not exist, create the one of PATH2 with the text that is put there. If the PATH1 file does not exist, the program will be stopped.

<action name="exist-fichero" path1="c:\Proyectos_Xone\Proyecto1\Interfase\2-pedidos\copy\pedidos.txt"
 
path2="c:\Proyectos_Xone\Proyecto1\Interfase\2-pedidos\copy\ok.txt" texto="OK"/>

Node to perform an erasure of the data that is told. You can erase both the origin and the destination.
There are several types of “delete” operations that can be performed, which are:

This is to perform the deletion of the data that matches the data that are searched at the origin, from the field or fields that match the primary key that we have defined.

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

In the example, the deletion is made in the “gen_sat” table, of the destination database, by using the “delete-dest-table” attribute, of those data that match the ##DESTKEYVALUES## macro.

<delete>
 
<action name="delete-conditional" delete-dest-table="gen_sat" use-saved-dest="true"/>
 
</delete>

With this example, the deletion is made in the source database as in the destination database. To indicate a source table the attribute “delete-source-table” is used, with this attribute the macro ## SOURCEKEYVALUES ## is used.

<delete>
 
<action name="delete-conditional" delete-dest-table="gen_usuarios" use-saved-dest="true"/>
 
<action name="delete-conditional" delete-source-table="EVUSUA" use-saved-source="true"/>
 
</delete>

This operation is used to perform the deletion in both the client and in the XOne database, so that in addition to deleting from the normal table, it inserts the deletion in the replication queue.

The example shown below performs the following things:

1. The search in the destination database is made, in the “gen_colla”(select-dest-table) table of the ROWIDs that meet the condition of the “where”(where), with the alias “c”(alias-dest-table).

SELECT c.ROWID FROM Gen_Colla WHERE ESTADO=1

2. If there are values with this condition, it deletes from the “gen_colla”( delete-dest-table) table.

3. If any records was found with the point 1, it exists the delete-source=”true” attribute, the “source-table” attribute with a table name and there is a condition, the deletion in the “Gen_USUARIOS”( source-table) table is made, with the condition there is in the “where” attribute.

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

Operation useful to make the deletion of a file.

Performs the deletion of the file that exists in the “PATH” attribute, with place and name of it.

<action name="delete-fichero" path="C:\Archivos de programa\CGSoft\CGInterclient\Captura\Ficheros\ESPPDA.TXT"/>