Continuamos con la segunda parte del artículo dedicado a herramientas de Velneo para el intercambio de datos entre una Instancia de Velneo y una aplicación externa.
En la primera parte del artículo terminamos diciendo que las funciones remotas conectan distintas aplicaciones sin establecer un lenguaje de comunicación entre los extremos.
Ahora le toca el turno al componente Velneo ODBC Driver, con el que además de acceder a nuestra base de datos de Velneo desde una aplicación externa, disponemos de un lenguaje estandar de comunicación SQL.
ODBC
Las siglas ODBC vienen de Open Database Connectivity (Conectividad Abierta de Bases de Datos) y es un API (Interfaz de Programación de Aplicaciones) que ha sido y es ampliamente usado para el acceso a múltiples bases de datos. Se basa en la especifcación Call-Level Interface (CLI) de Open Group e ISO/IEC para las API de base de datos, y usa el Lenguaje de Consulta Estructurado (SQL) como acceso a las mismas.
La interfaz ODBC ha sido diseñada para permitir el acceso de una misma aplicación a diferentes sistemas de gestión de bases de datos DBMS o SGBD, tanto relacionales como no relacionales, siempre que cada sistema de base de datos proporcione un controlador ODBC para su producto.
La arquitectura de la conectividad de datos basada en ODBC es la siguiente:
Aplicación compatible con ODBC
Es cualquier aplicación que puede usar ODBC, como Velneo, Microsoft Excel, Crystal Reports, Microsoft Power BI o una aplicación similar (hoja de cálculo, procesador de texto, herramienta de acceso y recuperación de datos, etc.). La aplicación habilitada para ODBC realiza el procesamiento, enviando sentencias SQL y recibiendo resultados del Administrador de controladores ODBC.
En Velneo habilitamos la aplicación mediante el plugin QODBC, el cual permite conectar vClient a un gestor de controladores ODBC y acceder a los orígenes de datos disponibles usando los comandos de Bases de datos externas.
Administrador de drivers ODBC
El Administrador carga y descarga los controladores ODBC desde la aplicación habilitada. El sistema operativo Windows viene con un Administrador de controladores predeterminado, mientras que otras plataformas usan unixODBC o iODBC. El Administrador de controladores ODBC procesa las llamadas a funciones ODBC y las pasa al controlador ODBC adecuado.
Driver ODBC
El controlador o driver ODBC procesa las llamadas a funciones ODBC, enviando solicitudes SQL a una fuente de datos específica y retornando los resultados a la aplicación. El controlador ODBC también puede modificar la solicitud de una aplicación para que la solicitud se ajuste a la sintaxis admitida por la base de datos asociada. El controlador se carga en tiempo de ejecución y solo es necesario que coincida con la arquitectura 32 o 64 bits de la aplicación.
Fuente de datos
La Fuente de datos puede ser un archivo o una base de datos en un SGBD, relacional o no. Los datos pueden estar en una ubicación local o remota.
Velneo ODBC Driver
El componente Velneo ODBC Driver permite a cualquier aplicación, que está habilitada para usar ODBC, conectar con una Instancia de datos de vServer.
Cumple con la especificación ODBC versión 3.52 y conformidad Level 1 con el API ODBC. En cuanto al lenguaje, ODBC SQL cumple con la conformidad e interface Core Level y ANSI SQL-92 Entry Level.
En Windows el componente Velneo ODBC Driver instala el controlador VELODBC.DLL dentro del Administrador de origen de datos ODBC. Podemos descargar e instalar las versiones de 32 y 64 bits.
La instalación creará la carpeta habitual para cualquier componente de Velneo, con las librerías Qt necesarias para establecer la comunicación con el vServer mediante el protocolo VATP.
En la carpeta %programfiles%\velneo se instalan los siguientes archivos para ser usados por el Administrador de origen de datos ODBC:
- velodbc.dll – controlador o driver ODBC de Velneo.
- velodbcui.dll – interface para configurar el origen de datos DSN.
Para establecer la conexión entre el driver ODBC y la instancia de datos de Velneo tenemos que configurar un origen de datos y asignarle un nombre DSN.
Los Orígenes de datos DSN pueden ser de usuario (accesibles solo por el usuario conectado), o de sistema (accesibles por todos los usuarios del equipo). En Windows las configuraciones de los DSN se guardan en el registro en las ramas \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI o \HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI.
El componente Velneo ODBC Driver permite crear y configurar un nuevo DSN mediante una ventana de diálogo en la que se introducen todos los parámetros necesarios.
- Data Source Name o DSN: nombre único del origen de datos en el Administrador de controladores ODBC.
- Description: campo opcional para documentar el DSN.
- Username: nombre de usuario necesario para acceder a la Instancia de datos de vServer. El grupo de usuarios al que pertenezca el usuario debe poder ejecutar Proyectos de datos con vDataClient y tener permisos para la Instancia de datos.
- Password: contraseña del usuario en vServer.
- Hostname: dirección o URL VATP de acceso al servidor vServer (poner siempre el esquema vatp:// y no incluir el puerto).
- Port: indicar el puerto de acceso si es distinto de 690.
- Database: nombre de la Instancia de datos accesible desde la aplicación habilitada. El botón Get obtiene la lista de Instancias de datos a las que el usuario tiene acceso.
No confundir el Nombre de la Instancia con el Identificador de la Instancia. El Identificador de la Instancia es único en el vServer y sin embargo el Nombre no, por lo tanto hay que asegurarse de que el nombre de Instancia sea único en el vServer. Los nombres de Instancia duplicados solo aparecen una vez en la lista y no sabremos a qué Instancia vamos a conectar.
En el treeview de la derecha del panel Soluciones de vAdmin podemos ver las Instancias declaradas de un proyecto de datos concreto. En la imagen vemos que para el proyecto de datos vgestion_dat se han definido cuatro Instancias diferentes, cada una con su nombre y senda de datos.
Una vez creado el origen de datos, el controlador ODBC tendrá acceso a las tablas de la instancias de datos del proyecto principal y de los heredados. Para acceder a las tablas de Instancias heredadas se debe anteponer el Alias del proyecto de datos y añadir un #.
<alias_proyecto_heredado>#Tabla
La cadena de conexión ODBC correspondiente es la siguiente:
DRIVER=Velneo ODBC Driver;SERVER=IP_servidor;PORT=num_puerto;DATABASE=ID_INSTANCIA_DATOS;PWD=contraseña;UID=usuario;DESCRIPTION=descripción
Normalmente en el DSN guardamos solo los campos Hostname, Port y Database. Los valores de Username y Password se suministran desde la aplicación cliente.
Test del origen de datos DSN
Pulsando el botón Test comprobamos si la conexión funciona correctamente.
En el panel de Enganches de vAdmin veremos que se ha creado un enganche de tipo vRunner con el Usuario e Instancia de datos indicados en el DSN.
Con VerboseLevel igual a 2 se mostrará en el panel de Mensajes dos mensajes de tipo Enganche con los eventos de Conectado y Desconectado identificados por el mismo Testigo.
Lenguaje SQL
Para intercambiar información entre la aplicación y el origen de datos, el controlador ODBC usa un lenguaje de consulta estructurado denominado SQL.
El componente Velneo ODBC Driver proporciona cuatro sentencias SQL:
- SELECT – Consultar registros de una tabla que satisfagan un criterio determinado.
- INSERT – Añadir lotes de registros en una única operación.
- UPDATE – Modificar los valores de los campos de los registros seleccionados.
- DELETE – Eliminar los registros seleccionados.
Son sentencias de consulta y manipulación de datos correspondientes a la parte DML del lenguaje SQL (Data Manipulation Language). Si usamos dos tablas en una selección obtendremos el producto cartesiano de los registros de ambas tablas. No existe la posibilidad de relacionar dos tablas usando la cláusula JOIN.
Sentencia SELECT
- ALL por defecto devuelve todas las filas
- DISTINCT solo devuelve registros con valores distintos en los campos
- expresión nombre de una columna de la tabla o una expresión válida
- AS nombre asigna un nombre a la expresión o columna. No se puede usar en la clúsula WHERE pero sí en las cláusulas GROUP BY y HAVING.
- FROM tabla desde la que se obtienen los registros seleccionados
- WHERE criterios de selección de los registros de la tabla
- GROUP BY separar los registros seleccionados en grupos
- HAVING condiciones que deben cumplir los grupos de la cláusula GROUP BY
- UNION combina los registros seleccionados de 2 consultas
No hay cláusulas ORDER BY, LIMIT ni TOP.
Consultar la documentación de Velneo para más detalles sobre expresiones aritméticas y operadores para los criterios de selección.
La expresión de una columna puede ser una función de agregado que devuelve un solo valor desde un conjunto agrupado de registros.
Sentencia INSERT
- INTO tabla en la que se insertan los registros
- columna nombre de la columna de la tabla
- VALUES lista de valores para las columnas
- valor el valor que se asignará a la columna
- SELECT subconsulta que proporciona los valores de las columnas
Más detalles sobre la sentencia INSERT en la documentación de Velneo.
Sentencia UPDATE
- UPDATE tabla en la que se actualizan los campos
- SET lista de asignaciones de valores a columnas de la tabla
- WHERE criterios de selección de los registros actualizados
En la documentación de Velneo dispones de más información sobre la sentencia UPDATE y la forma de seleccionar los registros.
Sentencia DELETE
- FROM tabla de la que se eliminan los registros
- WHERE criterios de selección de los registros eliminados
En la documentación de Velneo dispones de más información sobre la sentencia DELETE y la forma de seleccionar los registros eliminados.
Probar y evaluar Velneo ODBC Driver
Una vez instalado el componente Velneo ODBC Driver y creado el DSN, necesitamos una aplicación habilitada para ODBC desde la que podamos verificar y evaluar las posibilidades de intercambio de datos con nuestra base de datos Velneo. Existen multitud de herramientas para el programador que necesita trabajar con diferentes bases de datos. Un ejemplo es la completa aplicación multiplataforma DBeaver Community.
Para este artículo he descargado una aplicación sencilla de tipo ODBC Query Tool, que además de ser sofware libre no necesita instalación. Podéis descargarla del enlace https://sourceforge.net/projects/odbc-query-tool/.
Para las pruebas voy a conectar con la Instancia de datos vgestion_idat del proyecto de datos vgestion_dat, perteneciente a la Solución vgestion y que podéis descargar desde el Asistente de primeros pasos de vDevelop.
Abrimos la carpeta con la herramienta ODBC Query Tool y ejecutamos el archivo ODBC_Qry.exe. En la barra de herramientas pulsamos Connect y establecemos la cadena de conexión seleccionando el DSN (con la información de Hostname, Port y Database) y proporcionando los valores de username y password.
Pulsando el botón Connect! se realiza la conexión de Velneo ODBC Driver con la Instancia de datos.
La pantalla principal muestra a la izquierda un árbol con el esquema de la Base de datos compuesto de tres ramas principales. Nos interesa la primera rama que contiene el Catálogo de la Instancia de datos con el esquema de tablas de la base de datos. Las otras dos ramas contienen información del servidor de base de datos Velneo y la versión del driver velodbc.dll respectivamente.
El esquema muestra el identificador de la tabla, los identificadores de los campos de cada tabla y el tipo de dato del campo. Navegando por el esquema observamos que solo hay dos tipos de datos, VARCHAR y DOUBLE.
La conversión de tipo de campos Velneo a ODBC es la siguiente:
- Campo clave primaria ID: double- Campo alfa: varchar- Campo numérico: double + precision- Campo booleano: double(1)- Campo objeto (texto, dibujo, ...): varchar(8)- Campo puntero a maestro: double o varchar- Campo fecha, hora, tiempo: varchar(30)- Campo fórmula: varchar(0)- Campo puntero a tabla estática: varchar(1)- Campo indirecto real: varchar
Probamos con nuestra primera consulta SQL de tipo SELECT para obtener la lista completa de registros de la tabla ART.
SELECT * FROM ART
Las sentencias SELECT sin la cláusula WHERE devolverán todos los registros de la tabla, incluídos los registros eliminados que no han sido regenerados. Los registros eliminados los identificamos porque tienen el ID en blanco y todos los campos vacíos. Si abrimos el panel de Mensajes de vAdmin nos encontraremos con mensajes del tipo Error vatp: Leer una ficha → el elemento no existe.
Por lo tanto, hay que usar siempre la cláusula WHERE que forzará al controlador ODBC a usar un índice de la tabla y realizar una búsqueda, desechando de esa manera los registros eliminados. En la siguiente consulta la condición ID > 0 produce un resultado ordenado por la columna ID.
SELECT ID, NAME AS ARTICULO, REF, PRE_VTA AS VENTA FROM ART WHERE ID > 0
Los registros obtenidos desde la Instancia de datos se mostrarán en una Rejilla cuyas cabeceras son los identificadores de los campos o los nombres usados en la sentencia SELECT.
Con la tecla F9 cambiamos la vista de Rejilla por la vista de Texto en la que se muestran los mensajes con el número de filas devueltas o afectadas, la duración de la consulta y en su caso el error producido.
A partir de aquí y con el esquema de la base de datos a la vista, ya podemos ejecutar cualquier sentencia SQL compatible con el controlador ODBC de Velneo.
Lista de artículos en un rango de precios de venta.
SELECT REF AS REFERENCIA, PRE_VTA AS VENTA FROM ART WHERE PRE_VTA >= 10000 AND PRE_VTA <= 30000
Lista de clientes con alguna observación.
SELECT NAME AS CLIENTE, OBS AS OBSERVACIONES FROM CLT WHERE OBS LIKE '_%'
Precio medio de venta de los artículos. Devuelve un único valor numérico.
SELECT AVG(PRE_VTA) AS PRECIO_MEDIO FROM ART WHERE PRE_VTA > 0
Lista de clientes de un pais cuyas ventas totales superan un determinado importe.
SELECT CLT AS CLIENTE, SUM(IMP_VTA) AS VENTA, SUM(IMP_COS) AS COSTE FROM EST_CLT_ART WHERE CLT IN (SELECT ID FROM CLT WHERE PAI = 10) GROUP BY CLIENTE HAVING VENTA > 300000
Lista de clientes a los que se ha facturado alguna vez.
SELECT ID, NAME AS CLIENTE, CIF, OBS AS OBSERVACIONES, PAI AS PAIS FROM CLT WHERE ID IN ( SELECT DISTINCT CLT FROM VTA_FAC )
Lista de facturas de compra de tres proveedores en un periodo determinado. Las fechas en las sentencias SQL se expresan como un string ‘yyyy/MM/dd’.
SELECT ID, FCH AS FECHA, PRV AS PROVEEDOR, TOT AS TOTAL FROM COM_FAC WHERE PRV IN (23, 45, 123) AND FCH >= '2012/01/01' AND FCH <= '2019/12/31'
Actualizar el descuento para las familias de producto de un cliente concretos.
UPDATE DTO_CLT_FAM SET POR_DTO = POR_DTO * 1.2 WHERE CLT = 234
Eliminar los proveedores a los que no se ha comprado nada.
DELETE FROM PRV WHERE ID > 0 AND ID NOT IN (SELECT DISTINCT PRV FROM COM_FAC WHERE PRV > 0)
El lenguaje SQL facilita enormemente el intercambio de información con las tablas de la Instancia de datos, ya que solo es necesario conocer el esquema de la base de datos. Con SQL solo debemos indicar qué datos hay que obtener, actualizar, añadir o eliminar. El driver ODBC de Velneo se encarga de transformar las sentencias SQL en comandos nativos de Velneo como Cargar lista, Recorrer lista, Modificar ficha, etc …
Esquema de uso
Una vez probado el driver ODBC de Velneo y el lenguaje SQL, ya podemos plantearnos cómo se puede usar este componente en nuestras aplicaciones, ya sean Velneo o de otro tipo.
En la siguiente imagen se muestra el esquema y la secuencia del proceso de ejecución de una sentencia SQL desde una aplicación externa habilitada para usar ODBC.
- La aplicación, habilitada para usar ODBC, tendrá acceso al administrador de orígenes DSN para cargar y usar Velneo ODBC Driver. Antes de iniciar el envío de sentencias SQL, hay que establecer una conexión con la Instancia de datos del servidor Velneo vServer.
- Una vez establecida la conexión ya se pueden enviar sentencias SQL al driver ODBC.
- El driver ODBC analiza la sintaxis de la sentencia SQL y si es correcta, se descompone en comandos nativos de consulta y edición de datos de Velneo.
- Desde el driver ODBC, a través del protocolo VATP, se envían los comandos de ejecución equivalentes a la sentencia SQL.
- El servidor vServer ejecuta los comandos contra la Instancia de datos, obteniendo el resultado como listas de fichas desde las tablas de la base de datos.
- El resultado de los comandos de ejecución son devueltos al driver ODBC a través del protocolo VATP.
- El driver ODBC envía el resultado a la aplicación con los datos del resultado y el número de filas afectadas por la sentencia SQL. Finalmente desconecta de la Instancia de datos.
El driver ODBC es una librería DLL que se carga en el mismo espacio de memoría de la aplicación, por lo tanto, cualquier error o desbordamiento de memoria del driver puede provocar el cierre inesperado de la aplicación.
Usando Velneo ODBC Driver con vClient
¿Qué sentido tiene conectar mediante ODBC una aplicación Velneo con una base de datos también de Velneo?
Imagina el desarrollo de una aplicación Velneo que acceda directamente a una Instancia de datos (al estilo de vDataClient) pero no puedes integrar esa Instancia en tu aplicación con herencia ni publicarla como un servicio web. En ese caso la solución es Velneo ODBC Driver, donde tienes implementada toda la funcionalidad SELECT, UPDATE, INSERT y DELETE de manera estandar sobre protocolo VATP y la seguridad integrada de vServer.
El componente Velneo ODBC Driver es un cliente de datos que solo puede acceder a las Instancias de datos del servidor vServer. Es como un componente vDataClient sin interface, pero con un lenguaje propio de consulta y edición de datos.
Habilitar vClient para usar Velneo ODBC Driver
Las aplicaciones Velneo pueden conectar a un origen de datos ODBC mediante los comandos de Bases de datos externas o la clase del API VSqlDatabase .
Los comandos de Bases de datos externas no contemplan todas las opciones del driver ODBC, y por lo tanto en este artículo usaremos la clase del API VSqlDatabase con más opciones para el programador.
Hay tres apartados que debemos comprobar para que nuestra aplicación Velneo esté habilitada para usar el componente Velneo ODBC Driver.
1.- Plugin QODBC disponible en la aplicación
Comprobar que disponemos del plugin adecuado para usar orígenes ODBC. Velneo proporciona cuatro plugins para acceder a bases de datos SQL. El plugin QODBC es el que usaremos para conectar vClient con el administrador de orígenes ODBC del sistema operativo y enlazar con Velneo ODBC Driver.
Puedes confirmarlo usando la función VSqlDatabase.drivers() que devuelve un array con los plugins SQL disponibles en vClient.
2.- Configurar el origen de datos DSN
Se pueden administrar los orígenes de datos DSN desde la aplicación Velneo usando los comandos de Configuración del sistema.
En el DSN guardaremos el path del driver ODBC (velodbc.dll), el URL VATP del servidor, el puerto de acceso y la Instancia con la base de datos.
Estas serían las funciones para editar y eliminar un DSN de la configuración del sistema.
3.- Probar el DSN desde la aplicación Velneo
Para conectar con la Instancia de datos y obtener el esquema de la base de datos solo es necesario el DSN y las credenciales de acceso.
El siguiente código, usando el API y la clase VSqlDatabase, muestra un ejemplo para conectar y obtener información de las tablas con los índices primarios y campos con su tipo, longitud y precisión.
importClass("VSqlDatabase")
importClass("VSettings")
// DSN para la conexión a la Base de datos
var cDSN = theRoot.varToString("CDSN_PROBAR")
// Credenciales de conexión a la Instancia
var cUsu = theRoot.varToString("CUSUARIO")
var cClave = theApp.globalVarToString("0PS_ARTIPACO_dat/ODBC_CLAVE")
// Objeto para conectarnos al Driver ODBC
var oSQL = new VSqlDatabase()
// Leemos la Instancia desde el DSN en el registro de Windows
var oDSN_cfg = new VSettings("ODBC", "ODBC.INI\\" + cDSN)
var cInstancia = oDSN_cfg.value("DATABASE", "")
// Lista de tablas de la Instancia de datos
var aListaTablas = []
theRoot.setVar("NNUM_TABLAS", 0)
// Array con los tipos de Campo
var aTipos = ["Invalid", "Bool", "Int", "UInt", "LongLong", "ULongLong", "Double", "Char", "Map", "List", "String", "StringList", "ByteArray", "Date", "Time", "DateTime", "Pixmap", "Image", "Bitmap", "BitArray"]
// Control TreeView para mostrar las tablas y campos
var oArbol = theRoot.dataView().control("TRW_TABLAS")
oArbol.clear()
// Establecemos el Plugin para ODBC versión 3
oSQL.configure("QODBC3", cDSN)
// Conectamos a la Base de datos
if (oSQL.open(cUsu, cClave)) {
// Lista de tablas de la Instancia de datos
aListaTablas = oSQL.tables(1)
theRoot.setVar("NNUM_TABLAS", aListaTablas.length)
// Rellenamos el árbol de tablas/campos
aListaTablas.forEach( function (cTabla) {
var oRamaTabla = oArbol.addTopLevelItem()
// Campos del Índice de clave única
var nNumIds = oSQL.primaryIndexFieldCount(cTabla)
var cCampoID = ""
for (var nId=0; nId < nNumIds; ++nId ) {
cCampoID += oSQL.primaryIndexFieldName(cTabla,nId) + " "
}
oRamaTabla.setText(0, cTabla + " (" + cCampoID + ")")
oRamaTabla.setForegroundColor(0, 139, 0, 0, 255)
// Campos de la Tabla
var nCampos = oSQL.fieldCount(cTabla)
for (var nCol=0; nCol < nCampos; ++nCol ) {
var oItemCampo = oRamaTabla.addChild()
// Obtenemos Nombre, Tipo, Tamaño y Precisión
oItemCampo.setText(0, oSQL.fieldName(cTabla,nCol))
oItemCampo.setText(1, aTipos[oSQL.fieldType(cTabla,nCol)])
oItemCampo.setText(2, oSQL.fieldLength(cTabla,nCol))
oItemCampo.setText(3, oSQL.fieldPrecision(cTabla,nCol))
}
})
theRoot.setVar("CMENSAJE", "Instancia <b>" + cInstancia + "</b>:<br>OK – Conectado correctamente")
// Cierra la conexión
oSQL.close()
} else {
theRoot.setVar("CMENSAJE", "<b>Error</b>:<br>" + oSQL.getLastError())
}
theRoot.dataView().updateControls()
La imagen siguiente muestra un formulario de configuración con los tres apartados para habilitar y probar el acceso al componente Velneo ODBC Driver.
Intercambiar datos con la Instancia de datos
Ya sabemos que Velneo ODBC Driver se comporta como un componente vDataClient sin interface y con un lenguaje propio SQL para el acceso a los datos.
Diseñemos entonces un Interface que facilite al usuario la integración del componente Velneo ODBC Driver para el intercambio de datos entre la aplicación local y una Instancia de datos remota. Será nuestra propia herramienta ODBC Query Tool diseñada con Velneo.
Pero antes vamos a definir un nuevo objeto que facilitará este trabajo.
Las Vistas de datos
Una Vista de datos es una sentencia SELECT SQL personalizable que guardamos en nuestra aplicación, de tal forma que podamos reutilizarla posteriormente de manera sencilla.
Tiene las siguientes características:
- Guarda los parámetros de conexión a la Instancia de datos.
- Guarda información de la tabla afectada, como el identificador y el índice primario.
- La cláusula WHERE es parametrizable para personalizar las condiciones de búsqueda. Los parámetros usan el caracter ? como marcador y seguido del nombre del parámetro. Los valores de los parámetros, por defecto y personalizados, se obtienen de un string en formato json.
- Opcionalmente puede ser actualizable, en cuyo caso el resultado se guarda en una tabla temporal, indicando además la lista de campos actualizables y la correspondencia de cada columna de la tabla temporal con el campo de la tabla de la Instancia de datos.
- Con toda esta información se pueden generar automáticamente las sentencias UPDATE, INSERT y DELETE para sincronizar la tabla temporal con la Instancia de datos.
Las definiciones de las Vistas de datos se guardan en una tabla con la siguiente estructura:
Ejemplo de Vista de datos con las «Ventas por cliente»
Definamos una Vista de datos con nombre «Ventas por cliente» que obtiene desde la tabla de estadísticas EST_CLT_ART la suma total de los Importes de Venta y de los Importes de Coste de los Artículos, agrupados por los Clientes y pertenecientes a un determinado Pais. La Vista no es actualizable, solo obtenemos el resultado.
Las cláusulas WHERE y HAVING están parametrizadas. Los valores de los parámetros, en formato json, se obtienen desde el campo «Valor parámetros» = {«NPAI»: 1, «NIMPORTE»: 0}.
El parámetro NPAI determina el rango de clientes seleccionados y el parámetro NIMPORTE fija un límite inferior para el total de Ventas del cliente.
La imagen siguiente muestra el formulario de configuración de la Vista de datos con todo lo necesario para ejecutar automáticamente la sentencia SQL parametrizable.
La pantalla principal de nuestro ODBC Query Tool muestra la Lista con las Vistas de datos y un panel con la sentencia SQL, el valor de los parámetros y el resultado de la sentencia en una Rejilla o Tree Widget.
Seleccionamos la Vista Ventas por cliente y establecemos los valores de los parámetros a 10 para el ID del Pais y 50000 para el valor mínimo de las Ventas por Cliente.
Pulsamos el botón Ejecutar para conectar y obtener los registros desde la Instancia de datos y mostrarlos en un control Tree Widget, que construirá las columnas dinámicamente.
El siguiente código del manejador javascript es un ejemplo de cómo programarlo.
// Conecta con Velneo ODBC Driver y envía la Sentencia SELECT
// Con el resultado rellena el TreeWidget y devuelve un JSON
#include "(CurrentProject)/ODBC/odbc_conexion.js"
var cAlias = "0PS_ARTIPACO_dat"
// Leemos los datos de conexión oDBC en el registro de la tabla de Vistas de datos
var cDSN = theRegisterIn.fieldToString("DSN")
var cUsuario = theRegisterIn.fieldToString("USUARIO")
var cTabla = theRegisterIn.fieldToString("TABLA")
var nID_Vista = theRegisterIn.fieldToString("ID")
// Sentencia SQL
var cSQL = theApp.stripHtml(theRegisterIn.fieldToString("SELECT_CMD"))
// Devuelve el Nº de registros afectados y un JSON con el resultado
theRoot.setVar("NSQL_RESUL_FILAS", 0)
theRoot.setVar("CJSON_RESUL", "")
// Vaciar el TreeWidget para los registros obtenidos
var oArbol = theRoot.dataView().control("TRW_RESUL")
for (var nCol=0; nCol < oArbol.columnCount; ++nCol)
oArbol.setHeaderLabel(nCol, "")
oArbol.clear()
// Parámetros de la consulta -> ?<parámetro>
var aPara = cSQL.match(/\?(\S*)/g)
if (aPara) {
// JSON con los parámetros de la forma {"PARA1": VALOR1, …}
var oValoresPara = JSON.parse(theRegisterIn.fieldToString("PARAMETROS"))
// Obtenemos un Array con los nombres de los parámetros
var aValoresPara = Object.keys(oValoresPara)
for (var nPar=0; nPar < aValoresPara.length; ++nPar)
cSQL = cSQL.replace("?" + aValoresPara[nPar], oValoresPara[aValoresPara[nPar]])
// alert(cSQL)
}
// Abrir la conexión y determinar si hay acceso
var cClave = theApp.globalVarToString(cAlias + "/ODBC_CLAVE")
if (oConexion.conectar(cDSN, cUsuario, cClave)) {
// La Barra de progreso NO funciona cuando rellenamos el TreeWidget
theMainWindow.showMessageStatusBar("Ejecutando la consulta … ", 3000)
theApp.processEvents()
// Ejecutar la sentencia SQL
if (oConexion.ejecutar(cSQL) > 0) {
// Nº de registros devueltos desde el Driver ODBC
var nNumReg = oConexion.nNumReg
theRoot.setVar("NSQL_RESUL_FILAS", nNumReg)
// Nº de columnas devueltas
var nNumCol = oConexion.oBDSql.getColumnCount()
// Lista JSON de registros
var aLista = []
// Nombres de Columnas del TreeWidget
for (var nCol=0; nCol < nNumCol; ++nCol)
oArbol.setHeaderLabel(nCol, oConexion.oBDSql.getColumnName(nCol))
theMainWindow.showMessageStatusBar("Leyendo los registros … ", 3000)
theApp.processEvents()
// Contador de registros
var nReg = 0
// Recorremos los registros obtenidos en la consulta SQL
while (oConexion.oBDSql.nextRegister()) {
// Añadir Rama en el TreeWidget
var oRamaReg = oArbol.addTopLevelItem()
// Registro JSON
var oReg = {}
// Recorrer las columnas del resultado
for (var nCol=0; nCol < nNumCol; ++nCol) {
var cCampo = oConexion.oBDSql.getColumnName(nCol)
var vValor = oConexion.oBDSql.getColumn(nCol)
oReg[cCampo] = vValor
// Valor del campo
oRamaReg.setText(nCol, vValor)
}
// Guardamos la información del registro en formato JSON
aLista.push(oReg)
}
// Ajustamos anchos de columnas
// La columna 0 se deja fija
for (var nCol=1; nCol < nNumCol; ++nCol)
oArbol.resizeColumnToContents(nCol)
// Guarda el JSON
theRoot.setVar("CJSON_RESUL", JSON.stringify(aLista, null, " "))
}
theRoot.endProgressBar()
oConexion.cerrar()
theMainWindow.showMessageStatusBar("Leídos " + nNumReg + " registros", 3000)
}
Vista de datos actualizable
Cuando la definición de la Vista de datos tiene activado el campo #TABLA_TMP, los registros obtenidos con la sentencia SELECT se guardan en una tabla temporal de nombre tabla_TEMP. La tabla temporal debe estar definida como un objeto tabla en memoria en el proyecto de datos de la aplicación.
Con los registros en la tabla temporal ya podemos convertir la Vista de datos en actualizable, de tal forma que podemos construir las sentencias UPDATE, INSERT y DELETE a partir de las modificaciones que haga el usuario.
Hay tres campos de la definición de la Vista de datos que permitirán la actualización de los datos:
- CAMPOS_ID – Columnas de la tabla temporal que determinan la Clave primaria de los registros y que permiten relacionar de manera inequívoca un registro de la tabla temporal con el correspondiente en la tabla remota.
- CAMPOS_UPD – Columnas de la tabla temporal, separadas por comas, que son editables y actualizables.
- CAMPOS_ASO – Correspondencia en formato Json entre las columnas de la tabla temporal y los campos de la tabla remota.
Supongamos que deseamos editar los registros de las tablas Clientes, Artículos y Descuento Cliente/Familia pertenecientes a nuestra Instancia de datos de ejemplo vgestion_idat.
Primero duplicamos la estructura de estas tablas en nuestra aplicación pero con la propiedad Reside en memoria. El identificador de la tabla en memoria es el valor del campo #TABLA de la Vista de datos añadiendo el sufijo _TEMP. Solo es necesario definir los campos que haya en la sentencia SELECT que llamaremos columnas de la tabla temporal.
Las tablas maestras PAI y FAM también se descargan en tablas temporales para que funcionen los punteros a maestro del resto de las tablas. Las Vistas de PAI y FAM no son actualizables.
Configuración de la Vista actualizable de Artículos
Marcamos la opción de Guardar en tabla de memoria e introducimos la lista de Columnas Editables y el Json Columnas:Campos Tabla. No hay que olvidarse de la columna de Clave primaria.
De la lista de Vistas de datos seleccionamos la Vista Artículos y fijamos el parámetro CFAM con el valor «F4%» para devolver todos los artículos de la familia 4. En la lista Maestros de la Vista añadimos la Vista Familias de artículos para que funcione el puntero a maestro de la columna FAMILIA.
Pulsando el botón EJECUTAR se envía la sentencia SELECT y el resultado se guarda en la tabla temporal ART_TEMP. A continuación se ejecuta la Vista Familias de articulos para rellenar la tabla FAM_TEMP que habilita el puntero a maestro. Efectívamente, la columna FAMILIA muestra el nombre de la Familia en lugar del ID.
El código javascript que rellena la tabla temporal desde el resultado de la sentencia SQL es el siguiente:
// Ejecuta la sentencia SELECT para rellenar la tabla temporal
// La Ficha de entrada es de la tabla ODBC_VISTAS
// Hay 2 variables locales CSQL Y CPARAMETROS que permiten ejecutar una
// sentencia SQL personalizada con los parámeros asignados
#include "(CurrentProject)/ODBC/odbc_conexion.js"
var cAlias = "0PS_ARTIPACO_dat"
var cDSN = ""
// Conexión – datos desde el registro de ODBC_VISTAS
var cDSN = theRegisterIn.fieldToString("DSN")
var cUsuario = theRegisterIn.fieldToString("USUARIO")
var cClave = theApp.globalVarToString(cAlias + "/ODBC_CLAVE")
var lConectado = oConexion.conectar(cDSN, cUsuario, cClave)
if (lConectado) {
// Ejecutamos la Sentencia SQL de la Vista de datos y
// además también los SELECT de los Maestros (Plurales de la Vista)
var nNumResul = ejecutarSQL(theRegisterIn,true)
if (nNumResul > 0) {
theRoot.setVar("NSQL_RESUL", nNumResul)
// Vistas para habilitar los punteros a maestro
var oListaMaestros = theRegisterIn.loadPlurals("ODBC_VISTAS_VM_VISTA")
for (var nReg=0; nReg < oListaMaestros.size(); ++nReg) {
var oRegPlural = oListaMaestros.readAt(nReg)
var oRegMaestro = oRegPlural.readMaster("MAESTRA")
// Comprobamos si la tabla temporal del maestro está vacía
var cTablaM_tmp = oRegMaestro.fieldToString("TABLA") + "_TEMP"
var oListaM_tmp = new VRegisterList(theRoot)
if (oListaM_tmp.setTable(cAlias + "/" + cTablaM_tmp)) {
if (oListaM_tmp.load("ID", [])) {
if (oListaM_tmp.size() == 0)
// Rellena la tabla temporal del Maestro
ejecutarSQL(oRegMaestro, false)
}
}
}
}
oConexion.cerrar()
}
function ejecutarSQL(oRegVistaDatos, lEsPrincipal) {
if (lEsPrincipal) {
// Sentencia SQL principal y parámetros
// Se puede personalizar la Vista con las variables locales
var cSQL = theRoot.varToString("CSQL") || oRegVistaDatos.fieldToString("SELECT_CMD")
var cParametros = theRoot.varToString("CPARAMETROS") || oRegVistaDatos.fieldToString("PARAMETROS")
} else {
// Sentencia SQL de los maestros sin personalizar
var cSQL = oRegVistaDatos.fieldToString("SELECT_CMD")
var cParametros = oRegVistaDatos.fieldToString("PARAMETROS")
}
cSQL = theApp.stripHtml(cSQL)
var cTabla = oRegVistaDatos.fieldToString("TABLA")
var cTabla_tmp = cTabla + "_TEMP"
var cNameVista = oRegVistaDatos.fieldToString("NAME")
var nID_Vista = oRegVistaDatos.fieldToString("ID")
// Parámetros de la consulta -> ?<parámetro>
var aPara = cSQL.match(/\?(\S*)/g)
if (aPara) {
// JSON con los parámetros de la forma {"PARA1": VALOR1, …}
var oValoresPara = JSON.parse(cParametros)
// Obtenemos un Array con los nombres de los parámetros
var aValoresPara = Object.keys(oValoresPara)
for (var nPar=0; nPar < aValoresPara.length; ++nPar)
cSQL = cSQL.replace("?" + aValoresPara[nPar], oValoresPara[aValoresPara[nPar]])
// alert(cSQL)
}
// Barra de progreso
theRoot.initProgressBar()
theRoot.setTitle("Ejecutando la consulta … ")
// Ejecutar la sentencia SQL
if (oConexion.ejecutar(cSQL) > 0) {
// Nº de registros devueltos desde el Driver ODBC
var nNumReg = oConexion.nNumReg
// Nº de columnas devueltas
var nNumCol = oConexion.oBDSql.getColumnCount()
// Registro para guardar en la tabla temporal
var oRegTabla = new VRegister(theRoot)
oRegTabla.setTable(cAlias + "/" + cTabla_tmp)
// Puntero a los datos de la Vista
oRegTabla.setField("ODBC_VISTA", nID_Vista)
// Control de transacción
var hayTrans = theRoot.existTrans();
if (hayTrans == false)
var newTrans = theRoot.beginTrans("Llenar tabla temporal " + cTabla_tmp + " desde " + cNameVista)
// Barra de progreso
theRoot.setTitle("Leyendo los registros … ")
// Contador de registros
var nReg = 0
// Recorremos los registros obtenidos en la consulta SQL
while (oConexion.oBDSql.nextRegister()) {
// Barra de progreso
nReg++
theRoot.setProgress(nReg/nNumReg * 100)
// Recorrer las columnas del resultado
for (var nCol=0; nCol < nNumCol; ++nCol) {
var cCampo = oConexion.oBDSql.getColumnName(nCol)
var vValor = oConexion.oBDSql.getColumn(nCol)
oRegTabla.setField(cCampo, vValor)
}
// Guardamos la información del registro en la tabla
oRegTabla.addRegister()
}
}
theRoot.endProgressBar()
// Finalizar transacción
if (newTrans) theRoot.commitTrans()
theMainWindow.showMessageStatusBar("Leídos " + nNumReg + " registros desde " + cNameVista, 3000)
return nNumReg
}
Formulario de edición de la Vista actualizable
Una vez ejecutada la consulta disponemos de los datos en sendas tablas temporales de artículos y familias, por lo tanto podemos crear y asociar un formulario de edición a la ficha del artículo.
El formulario de edición construye y ejecuta las sentencias UPDATE, INSERT y DELETE a partir de la información contenida en la definición de la Vista de datos.
El campo CAMPOS_ID de la definición de la Vista determina qué columnas forman la clave primaria y de esta forma la sentencia SQL solo actuará sobre el registro del formulario.
Por ejemplo, el botón UPDATE ejecuta la sentencia UPDATE con la cláusula SET compuesta por los campos modificados y los nuevos valores.
El botón REFRESH vuelve a ejecutar la sentencia SELECT para el artículo actual y actualiza los valores remotos en la tabla temporal.
El botón DELETE ejecuta la sentencia DELETE eliminando el registro actual de la Instancia de datos.
Finalmente también se puede añadir un nuevo artículo usando la sentencia INSERT. En este caso el servidor vServer asignará un ID al nuevo registro que en principio no tenemos forma de conocer. En la tabla temporal el nuevo ID tomará un valor negativo para identificar los registros nuevos.
Veamos el proceso javascript que permite la generación automática de las sentencias SQL:
// Ejecuta las sentencias UPDATE e INSERT
// La Ficha de entrada es de la tabla temporal
#include "(CurrentProject)/ODBC/odbc_get_sentencia.js"
#include "(CurrentProject)/ODBC/odbc_conexion.js"
// Debe existir una lista de campos actualizables
if (theRegisterIn.fieldToString("ODBC_VISTA.CAMPOS_UPD")) {
var cDSN = theRegisterIn.fieldToString("ODBC_VISTA.DSN")
var cUsuario = theRegisterIn.fieldToString("ODBC_VISTA.USUARIO")
var cClave = theApp.globalVarToString("0PS_ARTIPACO_dat/ODBC_CLAVE")
if (oConexion.conectar(cDSN, cUsuario, cClave)) {
// Tipo de sentencia SQL
var cSentencia = (theRegisterIn.exist() ? "UPDATE" : "INSERT")
var cSQL = oSQL_clausula.get_sentencia(cSentencia)
alert(cSQL)
cSQL = theApp.stripHtml(cSQL)
// Ejecutar la sentencia SQL
if (oConexion.ejecutar(cSQL) > 0) {
alert("Ejecutada sentencia " + cSentencia + " -> " + oConexion.nNumReg + " registro")
// Acceso a la variable local LEXITO
theRoot.setVar("LEXITO", true)
}
}
// Terminar conexión
oConexion.cerrar()
} else {
alert("La Vista no tiene columnas actualizables")
}
// odbc_get_sentencia.js
// Devuelve la sentencia SQL
// La Ficha de entrada es de la tabla temporal
// La información de la Vista SQL actualizable se encuentra en
// el maestro theRegisterIn.ODBC_VISTA
// TABLA – Nombre de la tabla en la Instancia de datos del vServer
// CAMPOS_ID – Lista de campos del Índice primario (normalmente es ID)
// CAMPOS_ASO – Json con los pares "COLUMNA" : "CAMPO_TABLA TIPO"
// Relaciona la columna de la tabla en memoria con el campo de la Tabla del vServer
// El tipo de dato TIPO (N o C) nos servirá para actualizar desde el TreeWidget
// Cuando usamos una Tabla temporal el Tipo de dato se obtiene desde tableInfo()
// CAMPOS_UPD – Lista de columnas de la tabla en memoria que son Editables y actualizables
var oSQL_clausula = {
cTabla: theRegisterIn.fieldToString("ODBC_VISTA.TABLA"),
cCamposID: theRegisterIn.fieldToString("ODBC_VISTA.CAMPOS_ID"),
cCamposASO: theRegisterIn.fieldToString("ODBC_VISTA.CAMPOS_ASO"),
cCamposUPD: theRegisterIn.fieldToString("ODBC_VISTA.CAMPOS_UPD"),
get_where: function() {
var oCamposASO = JSON.parse(this.cCamposASO)
var aCamposID = this.cCamposID.split(" ")
aCamposID.forEach( function (cCampoID, index, arr) {
var cValorID = theRegisterIn.fieldToString(cCampoID)
var cCampoTabla = oCamposASO[cCampoID].split(" ")[0]
arr[index] = cCampoTabla + " = " +
(isNaN(parseInt(cValorID)) ? "’" + cValorID + "’" : parseInt(cValorID))
})
return aCamposID.toString(" AND ")
},
get_upd_set: function() {
var oCamposASO = JSON.parse(this.cCamposASO)
var aCamposUPD = this.cCamposUPD.split(",")
var nNumUPD = aCamposUPD.length
var aSet = []
var oTablaInfo = theRegisterIn.tableInfo()
// Recorremos la lista de campos Actualizables y construimos el SET
for (var nCol=0; nCol < nNumUPD; ++nCol) {
var cColumnaUPD = aCamposUPD[nCol].trim()
if (theRegisterIn.isFieldModified(cColumnaUPD)) {
// Campo de la tabla remota
var cCampoTabla = oCamposASO[cColumnaUPD].split(" ")[0]
// Nº de la columna en la tabla temporal
var nNumCol = oTablaInfo.findField(cColumnaUPD)
// nTipo = 6 es numérico
var nTipo = oTablaInfo.fieldType(nNumCol)
var cValor = (nTipo == 6 ? "" : "’")
cValor = cValor.concat(theRegisterIn.fieldToString(cColumnaUPD),cValor)
aSet.push(cCampoTabla + " = " + cValor)
}
}
return aSet.toString(", ")
},
get_ins_valores: function() {
var oCamposASO = JSON.parse(this.cCamposASO)
var aCamposUPD = this.cCamposUPD.split(",")
var nNumUPD = aCamposUPD.length
var aCamposInsert = []
var aValoresInsert = []
var oTablaInfo = theRegisterIn.tableInfo()
// Recorremos la lista de campos Actualizables y construimos el INSERT
for (var nCol=0; nCol < nNumUPD; ++nCol) {
var cColumnaUPD = aCamposUPD[nCol].trim()
// Campo original en la tabla remota
var cCampoTabla = oCamposASO[cColumnaUPD].split(" ")[0]
aCamposInsert.push(cCampoTabla)
// Nº del campo en la tabla temporal
var nNumCol = oTablaInfo.findField(cColumnaUPD)
// nTipo = 6 es numérico
var nTipo = oTablaInfo.fieldType(nNumCol)
var cValor = (nTipo == 6 ? "" : "’")
cValor = cValor.concat(theRegisterIn.fieldToString(cColumnaUPD),cValor)
aValoresInsert.push(cValor)
}
return "(" + aCamposInsert.toString(",") + ") <br><b>VALUES</b> (" + aValoresInsert.toString(",") + ")"
},
get_sentencia: function(cTipo) {
switch(cTipo) {
case "UPDATE":
return "<b>UPDATE</b> " + this.cTabla + " <br><b>SET</b> " + this.get_upd_set() + " <br><b>WHERE</b> " + this.get_where()
break;
case "INSERT":
return "<b>INSERT INTO</b> " + this.cTabla + "<br> " + this.get_ins_valores()
break;
case "DELETE":
return "<b>DELETE FROM</b> " + this.cTabla + " <br><b>WHERE</b> " + this.get_where()
break;
default:
}
}
}
Monitorizar las sentencias SQL
La ejecución de las sentencias SQL por parte de Velneo ODBC Driver desencadena un proceso de conversión de la sintaxis de dicha sentencia a comandos nativos de Velneo que son enviados a la Instancia de datos remota a través del protocolo VATP.
En vDevelop disponemos de la Extensión Monitor de VClient, que sirve para monitorizar las peticiones enviadas a vServer desde vClient durante la ejecución de una aplicación.
El componente Velneo ODBC Driver se carga en la misma zona de memoria que vClient y eso le permite al Monitor de VClient reportar los comandos de ejecución con destino la Instancia de datos conectada.
Monitorizar la Vista de datos «Dtos. Cliente/Familia»
Para mostrar el funcionamiento del Monitor de vClient usaremos la Vista Dtos. Cliente/Familia que devuelve el descuento de los clientes para cada familia de artículos. Hacemos la Vista actualizable en la columna DESCUENTO e indicamos las columnas de Clave primaria compuesta por las columnas CLIENTE y FAMILIA.
Las columnas CLIENTE y FAMILIA son punteros a maestro y por lo tanto se cargarán las Vistas de las tablas CLT y FAM en sus respectivas tablas temporales.
En la imagen siguiente puedes ver los comandos de ejecución VATP que genera la sentencia SELECT.
Hay 8 eventos que podemos identificar a partir del log generado por el Monitor de vClient.
- Driver ODBC – Establece conexión con el vServer usando la información del origen de datos DSN y las credenciales correctas. El driver ODBC obtiene una lista de las Instancias de datos a las que el usuario tiene acceso y obtiene un enganche válido, en este caso 2-1823-28960-4205.
- Driver ODBC – Ejecuta la sentencia SELECT sobre la tabla DTO_CLT_FAM de la Instancia de datos. Los comandos VATP son Buscar entre límites y Leer múltiples fichas.
- APLICACIÓN – El resultado de la sentencia SELECT se guarda en la tabla temporal DTO_CLT_FAM_TEMP. Se crea transacción porque estamos rellenando la tabla temporal desde un proceso javascript.
- Driver ODBC – Ejecuta la sentencia SELECT sobre la tabla CLT para habilitar el puntero a maestro.
- APLICACIÓN – El resultado de la sentencia SELECT se guarda en la tabla temporal CLT_TEMP.
- Driver ODBC – Ejecuta la sentencia SELECT sobre la tabla FAM para habilitar el puntero a maestro.
- APLICACIÓN – El resultado de la sentencia SELECT se guarda en la tabla temporal FAM_TEMP.
- Driver ODBC – Desconecta de la Instancia de datos.
Monitorizar la sentencia SQL UPDATE
Ahora veamos los comandos de Velneo enviados al servidor cuando ejecutamos una sentencia UPDATE. Para verlo cambiamos el porcentaje de descuento para un determinado cliente y familia de artículos.
El Monitor de vClient muestra la secuencia de conexión y la búsqueda del registro en la Instancia de datos. A continuación inicia transacción para bloquear y modificar la ficha. Finalmente completa la transacción y desconecta la Instancia.
También podemos comprobar qué comandos se ejecutan cuando la sentencia UPDATE afecta a varios registros. Por ejemplo, enviamos la sentencia para actualizar un 20% los descuentos del cliente con ID igual a 10.
La secuencia de comandos VATP es similar. Ejecuta la búsqueda de los clientes que cumplen la condición de la cláusula WHERE y seguidamente inicia transacción para modificar el descuento de todos los registros.
Las sentencias SQL con subconsultas y el predicado IN pueden resultar lentas porque se ejecutan tantas consultas VATP como registros cumplan la condición de búsqueda.
Por ejemplo, en la siguiente sentencia SQL se devuelven los clientes que han facturado el mes de diciembre del año 2015. Primero se obtiene la subconsulta con las filas de la tabla VTA_FAC y a continuación se recorre el resultado de dicha subconsulta devolviendo las fichas de los clientes.
Plano de ejecución
Analizando el Monitor de vClient, podemos deducir que todas las operaciones de transformación de la sentencia SQL a comandos nativos de Velneo se realiza en primer plano por el propio driver ODBC. Habrá que tenerlo en cuenta a la hora de comprobar por qué nuestra aplicación externa se queda congelada con determinadas consultas complejas.
Analizar nuestros datos con Velneo ODBC Driver
Una de las razones principales por las que una aplicación externa se conecta a una Instancia de datos de Velneo es la necesidad de procesar la información para la toma de decisiones.
Existen multitud de herramientas en el mercado especializadas en el análisis de datos, destinadas a programadores y no programadores, comerciales o gratuitas y orientadas a diferentes áreas del conocimiento.
En este artículo voy a citar a Microsoft Excel porque es una herramienta analítica muy popular y casi fundamental para el procesamiento de los datos propios de la empresa. Muchos son los usuarios avanzados que usan Excel u otra herramienta similar para el análisis de datos en sus equipos de escritorio. Estos usuarios no requieren ni enormes cantidades de datos ni aplicaciones de gestión de alto coste, solo demandan sencillez, interfaz accesible y potencia de cálculo.
El usuario, sin conocimientos de programación, solo necesitará instalar Velneo ODBC Driver, configurar el DSN en el administrador de orígenes ODBC y conocer las credenciales de acceso a la Instancia de datos. El complemento Power Query de Excel conectará a la base de datos e importará las tablas usando consultas SQL, filtrando y combinando la información para crear un Modelo de datos que estará disponible en las tablas y gráficos dinámicos de la Hoja de cálculo. Todo sin escribir una sola línea de código.
Analizando los datos de vgestion
Veamos un ejemplo de análisis de datos a partir de la información de las ventas en la Instancia de datos de la solución vgestion. Seleccionamos la tabla de estadísticas EST_CLT_ART con las ventas por artículo, cliente y año. Hay que cargar también las tablas de los maestros correspondientes.
En la imagen siguiente tenemos un Modelo de datos en el complemento Power Pivot de Excel, creado a partir de cuatro consultas ODBC diseñadas en el editor visual de Power Query y almacenadas en la hoja de cálculo. El Modelo de datos guarda también las relaciones entre las tablas y se puede actualizar con las tablas de la Instancia vgestion en cualquier momento.
A partir del modelo de datos ya podemos crear tablas y gráficos dinámicos. La opción de Actualizar volverá a ejecutar las sentencias SQL de las Consultas y el Modelo de datos se sincroniza con los datos de la Instancia. Las tablas y gráficos mostrarán los cambios.
Betatester de Velneo ODBC driver
El componente Velneo ODBC Driver disponible en la sección de descargas ha sido una versión beta desde su presentación pública.
En su condición de beta este componente solo está accesible para suscriptores de nivel 4, lo que permite darles soporte en esta larga fase de prueba y desarrollo.
No sabemos si habrá versión definitiva, teniendo en cuenta que las tecnologías web han desplazado este tipo de drivers por otros métodos de acceso multiplataforma y no SQL. En cualquier caso van apareciendo novedades y mejoras del componente Velneo ODBC Driver en las sucesivas versiones. Concretamente, la versión 25 incorporó algunas mejoras importantes.
Escribendo este artículo no he tenido más remedio que hacer de betatester y aplicar la técnica del prueba y error en aquellos aspectos no documentados.
A continuación enumero algunos errores con los que me he encontrado:
- No se realiza correctamente la codificación de caracteres fuera del conjunto ASCII (acentuados, ñ, …), tanto en la sentencia SQL como en valores de los campos. Esto solo se produce cuando usamos como aplicación cliente el componente vClient. Incidencia 7905.
- Los campos de tipo Objeto Texto no se pueden actualizar con la sentencia UPDATE. Incidencia 7933.
- Mostrar una Vista de datos después de usar Velneo ODBC Driver provoca el cierre de vClient. Incidencia 8542.
- Los nombres de Instancia de datos duplicados aparecen solo una vez en la lista de Instancias del selector de la ventana de diálogo de configuración del DSN.
- Una sentencia SELECT sin cláusula WHERE devuelve los registros eliminados como registros en blanco.
A fecha de hoy, y usando aplicaciones distintas a vClient, el único error grave es la imposibilidad de actualizar campos de tipo Texto. Para aplicaciones de consulta y análisis de datos funciona básicamente bien.
Funciones remotas, driver ODBC y procesos web (I)
Conclusiones
Ha sido un artículo extenso, pero creo que necesario para entender la arquitectura que hay detrás de un componente que quizás ha sido olvidado injustamente y que habría que tener más en cuenta en nuestros próximos proyectos.
Hemos visto una aplicación de tipo SQL Query Tool pero programada en Velneo para poder usar la API de Bases de datos externas y la extensión Monitor de vClient. Esto nos ha permitido analizar y comprender el funcionamiento de las sentencias SQL sobre una Base de datos Real típica Velneo.
Debe quedar la idea de que con Velneo ODBC Driver, extender nuestra aplicación para conectarnos con una Instancia de datos remota de vServer, solo requiere conocer las credenciales de conexión. Disponemos entonces de un componente como el vDataClient, pero con toda la potencia del lenguaje estandar SQL. No es necesario tener procesos programados en el vServer, solo conocer el esquema de la base de datos.
En la próxima entrega revisaremos los procesos web como herramienta Velneo que extiende nuestras aplicaciones al entorno web, es decir, conecta nuestras Instancias de datos con el protocolo HTTP. Además, de igual manera que con ODBC, necesitamos un componente que nos haga la conversión de las peticiones HTTP a comandos de ejecución del protocolo VATP, y en este caso el tema se complica un poco más.
Pero esto lo veremos en la tercera parte. Os espero.
Artículo relacionado: Funciones remotas, driver ODBC y procesos web (I)
