Extendiendo Velneo

Bases de datos externas con Access

Velneo V7 tiene su propia base de datos, su propio lenguaje de programación y su propio servidor de base de datos y aplicaciones. Si necesitamos acceder a datos de otras plataformas diferentes a Velneo disponemos de varias opciones:

Las dos primeras opciones proporcionan acceso nativo multiplataforma a bases de datos externas mediante el uso de plugins desarrollados con la plataforma Qt. Hay disponibles plugins para el acceso a bases de datos compatibles con el driver ODBC, SQLite y PosgreSQL.

En este artículo vamos a ver un caso particular de acceso a bases de datos externas, en concreto el acceso a una base de datos de escritorio Microsoft Access usando el plugin ODBC. Normalmente siempre nos centramos en la importación de datos desde bases de datos externas, pero en este ejemplo haremos justo lo contrario, exportaremos las tablas de Velneo para crear la base de datos Access desde cero y rellenarla con datos.

En los siguientes enlaces puedes explorar las características más interesantes de la base de datos de escritorio Microsoft Access para un desarrollador de Velneo:

El conjunto de comandos nativos y la clase VSqlDatabase del API proporcionan funciones de conexión a la base de datos externa, de obtención de información y de ejecución de sentencias SQL para el intercambio de datos.

En este módulo he preferido usar la clase VSqlDatabase porque resulta mucho más rápida y flexible tanto en la recuperación de datos como en la posterior gestión del formato JSON con javascript.

He programado con Velneo un módulo para que puedas comprobar el uso de las herramientas disponibles y experimentar con el lenguaje SQL de Access. He dividido la aplicación en 3 secciones:

La VRL de acceso al módulo con los ejercicios es vatps://PRUEBAS:pruebas@c3.velneo.com:10190/0PT_Modulos_iapp.

Con el módulo delante, veamos en detalle el proceso de conexión a la base de datos Access, la descarga de los datos de la base de datos remota Velneo y la exportación de los datos a la base de datos Access.

Conexión ODBC a la Base de datos Access

Lo primero que hay que configurar es la base de datos Access con la que queremos trabajar. La primera vez que iniciamos el módulo se nos solicita el path de un nuevo archivo con extensión accdb. Este archivo accdb es el contenedor para todos los componentes de la base de datos Access. El path se guardará en una clave del registro de configuración para que se use automáticamente en las siguientes ejecuciones del módulo.

EV07 Conexion ODBC

Con el path de la base de datos Access se construye la cadena de conexión para el driver ODBC que usa el plugin QODBC3 de Velneo.

Prescindimos de DSN y establecemos la conexión con la Base de datos Access usando la cadena de conexión ODBC. Una cadena de conexión traslada la información de conexión directamente al Administrador de controladores ODBC y ayuda a simplificar la configuración al eliminar el requisito de que un administrador o usuario del sistema cree un DSN antes de usar la base de datos.

La cadena de conexión necesita solo 2 opciones:

El siguiente paso es establecer la conexión con la base de datos Access a través del driver ODBC usando los comandos de bases de datos externas de Velneo.

El botón CONECTAR ejecutará el proceso que instancia la clase del API VSqlDatabase para configurar el plugin QODBC3 y abrir la conexión.

importClass("VSqlDatabase")
// Establecemos el Plugin para ODBC versión 3
var cDriver = "QODBC3"
var cConexion = theRoot.varToString("CCADENA_CONEX")
var cUsu = ""
// La clave si está cifrada la base de datos
var cClave = ""
// Objeto para conectarnos al Driver ODBC
var oSQL = new VSqlDatabase()
// Configura la conexión con el driver y la cadena de conexión
oSQL.configure(cDriver, cConexion)
// Conectamos a la Base de datos de Access
if (oSQL.open(cUsu, cClave)) {
	theRoot.setVar("OK_CONEXION", 1)
        ...
	// Cierra la conexión
	oSQL.close()
} else {
	alert("<b>Error</b>:<br>" +  oSQL.getLastError())
}

La contraseña es necesaria si hemos cifrado la base de datos accdb.
En Microsoft Access abre la base de datos en Modo Exclusivo > Opción Archivo > Información > Cifrar con contraseña.

Si la conexión con la base de datos Access tiene éxito se mostrarán las tablas en el control TreeWidget.

EV07 Conexion ODBC OK

Inicialmente la base de datos estará vacía, tenemos que usar la opción de Descargar datos desde vGestion de Velneo para crear las tablas en la base de datos Access y exportar los datos.

Descarga de Velneo vGestion con API REST

En este ejercico no vamos a usar tablas de Velneo. A través de la URL siguiente obtendremos los datos en formato JSON desde la base de datos vGestion usando el API REST:

https://c3.velneo.com:10192/miapp_server/v1/<tabla_velneo>?page[size]=1000&page[number]=1&api_key=apiVelneo

En el siguiente esquema se muestran las 8 tablas disponibles para descargar desde vGestion y las relaciones entre ellas a través de los punteros uno a varios.

EV07 Esquema VGestion

Los datos de las tablas se descargan en formato JSON, pudiendo paginar la descarga en grupos de hasta 1000 registros como máximo e indicar el número de página. Son los parámetros page[size] y page[number] de la consulta API REST.

Junto a los datos se descarga también la información de los campos de las tablas Velneo. Se usa la URL siguiente:

https://c3.velneo.com:10192/miapp_server/v1/_process/pro_rest_tablas_info?param[tablas]=<lista de tablas>&api_key=apiVelneo

Esta información nos permitirá construir las sentencias SQL CREATE TABLE e INSERT INTO.

EV07 API REST Descarga

Los botones CREAR y AÑADIR LOS REGISTROS ejecutan respectivamente las sentencias SQL CREATE TABLE e INSERT INTO con cada una de las tablas de vGestion descargadas con el API REST.

Por ejemplo, la sentencia CREATE TABLE para la tabla art es la siguiente:

CREATE TABLE art 
    ( [id] INTEGER NOT NULL PRIMARY KEY, [name] TEXT(40), [fam] TEXT(8), 
    [ref] TEXT(20), [cod_bar] TEXT(20), [dsc] LONGTEXT, 
    [img] LONGTEXT, [pre_vta] DOUBLE, [pre_cos] DOUBLE )

El TreeWidget con la estructura de la base de datos Access mostrará la lista de tablas con los campos, el Primary Key y el tipo y longitud.

EV07 TablasAccess

La lista de tablas se obtiene con la función tables de VSqlDatabase. Las propiedades de los campos se obtienen con las funciones de campos y el Primary Key con las funciones de Índice primario.

Tipos de datos SQL Access

Para la equivalencia entre los tipos de datos de Velneo con los tipos de datos de SQL Access usaremos la siguiente tabla:

VTableInfo
fieldType		 VTableInfo
fieldObjectType		 VTableInfo
fieldBufferLen		 VTableInfo
fieldDecimals		 Dato SQL
Alfa Bytes TEXT(Bytes)
Numérico 0 INTEGER
Numérico Mayor que 0 DOUBLE
Fecha, Hora, Tiempo DATETIME
Booleano BIT
Objeto Text, RichText, Picture LONGTEXT
Objeto Binary BINARY

En Access no existen los tipos de datos Fecha y Hora, se exportarán como datos DATETIME. Los campos DATETIME en formato JSON no funcionan en Access, se insertarán en la sentencia INSERT INTO como un string "dd/MM/yyyy hh:mm"

Los campos de tipo Objeto Picture de Velneo se descargan en formato Byte64 desde API REST, por lo tanto se deben exportar como LONGTEXT.

Integridad referencial

La clave única ID de las tablas de Velneo se define en la sentencia SQL CREATE TABLE a través de la restricción (CONSTRAINT) NOT NULL PRIMARY KEY junto al campo ID.

Las Relaciones entre las tablas se definen en Access desde la pestaña Herramientas de base de datos. Abriendo la pestaña Relaciones podemos arrastrar las tablas al área de diseño y manualmente establecer las conexiones entre los campos maestro y detalle. La Integridad referencial para una determinada relación hay que habilitarla expresamente.

La información de la base de datos Velneo descargada con el API REST contiene las propiedades fieldBindType y fieldBoundedTableId de VTableInfo, que señalan los punteros a maestro de las tablas y definirán las Relaciones uno a varios entre las tablas de la base de datos Access.

Con la información de punteros a maestro se construye la lista de Relaciones entre las tablas. La columna Aplicar indica si la Relación forzará la integridad referencial.

Aparece desmarcada la columna Aplicar para las relaciones de la tabla mov con las tablas clt, vta_fac y com_fac. Los campos clt, vta_fac y com_fac no siempre apuntan a registros de la tabla maestra ya que depende del valor del campo tip_mov.

EV07 Restricciones

Una vez que se han exportado todos los datos de Velneo a la base de datos Access se puede usar el botón AÑADIR RESTRICCIONES para ejecutar las sentencias SQL ALTER TABLE ADD CONSTRAINT.

El lenguaje SQL de Access no admite la cláusula NOCHECK de la sentencia ALTER TABLE ADD CONSTRAINT, por esta razón todas las relaciones entre las tablas de Access que se crean con las sentencias SQL deben cumplir la integridad referencial o de lo contrario devolverán error.

Por supuesto, las relaciones entre las tablas y la integridad referencial se pueden definir desde el interfaz de la aplicación de escritorio Microsoft Access. El objetivo de este ejercicio es comprobar cómo desde Velneo y con los comandos de bases de datos externas muchas tareas se automatizan fácilmente.

En la figura puedes ver cómo queda el esquema final de la base de datos Access. Las relaciones marcadas en amarillo no tienen aplicada la integridad referencial y se han creado directamente en la aplicación Access. El resto del esquema se ha generado íntegramente con el lenguaje SQL desde la función executeSQL de la clase VSqlDatabase.

EV07 RelacionesAccess

Ejecutar sentencias SQL contra la Base de datos Access

Conforme vayamos creando tablas y exportando datos disponemos del botón EJECUTAR SENTENCIA SQL para probar el lenguaje SQL contra la Base de datos Access seleccionada.

Cada vez que descargamos una tabla desde vGestion se escribe la sentencia SQL SELECT * FROM tabla para que comprobemos si los registros se han exportado correctamente.

El siguiente código javascript es el encargado de ejecutar las sentencias SQL.

// Abrir la conexión y determinar si hay acceso 
if (oSQL.open(cUsu, cClave)) {
	// Barra de progreso en la Barra de estado
	var cBarra = ""
	for (var n=0; n<50; n++) cBarra += "▉"
	// Ejecutar la sentencia SQL
	if (oSQL.executeSQL(cSQL)) {
		// Obtener el Nº de registros
		// La función oSQL.numRows() no funciona
		while (oSQL.nextRegister()) nNumReg++
		theRoot.setVar("CSQL_RESUL_FILAS", nNumReg)
		var nNumCol = oSQL.getColumnCount()
		var aLista = []
		var nAni = 0
		// Recorremos los registros obtenidos en la consulta SQL
		oSQL.goBeforeFirstRecord()
		// Creamos un JSON con los registros
		while (oSQL.nextRegister()) {
			var oReg = {}
			for (var nCol=0; nCol < nNumCol; ++nCol) {
				oReg[oSQL.getColumnName(nCol)] = oSQL.getColumn(nCol)
			}
			// Guardamos la información del registro
			aLista.push(oReg)
			// Con processEvents es posible refrescar la Barra de Estado (MessageStatusBar)
			theMainWindow.showMessageStatusBar("Leyendo " + aLista.length + " de " + nNumReg + "  " + cBarra.substring(0,(aLista.length/nNumReg) * 50), 5000)
			theApp.processEvents()
		}
		theRoot.setVar("CSQL_JSON_RESUL", JSON.stringify(aLista,null," "))
	} else {
		theRoot.setVar("CSQL_ERROR", "ERROR: " + oSQL.getLastError())
	}
	oSQL.close()
} else {
	theRoot.setVar("CSQL_ERROR", "ERROR: " + oSQL.getLastError())
}

 
Destacamos lo siguiente:

Una vez que hemos completado la exportación de las tablas de vGestion a la base de datos Access, ya podemos ejecutar todo tipo de sentencias SQL desde Velneo.

Algunos ejemplos para practicar:

Clausulas INNER JOIN anidadas

Todos los movimientos con la fecha, nombre de artículo y nombre de familia.

SELECT mov.fch, art.name as articulo, fam.name as familia
FROM (mov INNER JOIN art ON mov.art = art.id) 
    INNER JOIN fam ON mov.fam = fam.id

Agrupar por y funciones de agregado

Total de las Ventas a los Clientes por año, con la Venta media e Importe mínimo de Venta.

SELECT clt.name AS Cliente, mov.año AS Año, 
    Sum(mov.vta_fac) AS [Importe Total], 
    Avg(mov.vta_fac) AS [Importe medio], 
    Min(mov.vta_fac) AS [Importe mínimo]
FROM clt INNER JOIN mov ON clt.id = mov.clt
GROUP BY clt.name, mov.año

Número de registros que devuelve una consulta

Número de Artículos en la Base de datos.

SELECT COUNT (*) AS [Nº de artículos]
FROM art

Operador LIKE

Los caracteres comodin desde ODBC son % y _. Sin embargo en la aplicación Access son * y ?
Artículos cuya Familia comienza por F2.

SELECT id, fam FROM art
WHERE fam LIKE "F2%"

Operador IN

Clientes que no pertenecen a una lista de paises.

SELECT clt.name AS cliente, pai.name AS pais
FROM clt INNER JOIN pai ON clt.pai = pai.id 
WHERE pai.name NOT IN ("PAIS_01","PAIS_02")

Operador UNION

Obtener las facturas de Venta y de Compras en un solo listado ordenadas por Fecha descendente.

SELECT fch AS Fecha, clt.name AS Nombre, tot AS Importe 
FROM vta_fac INNER JOIN clt ON vta_fac.clt = clt.id
UNION
SELECT fch, prv.name, tot 
FROM com_fac INNER JOIN prv ON com_fac.prv = prv.id
ORDER BY Fecha DESC, Nombre

Transformar filas en columnas (Crosstabs o tablas cruzadas)

Obtener una tabla con los clientes en las filas, los años en las columnas y las ventas totales en las celdas de la tabla. Los años se seleccionan de una lista.

TRANSFORM IIF(Sum(vta_fac.tot) IS NULL, '--', Sum(tot)) AS Total 
SELECT clt.name AS Nombre
      FROM clt LEFT JOIN vta_fac 
      ON clt.id=vta_fac.clt 
      GROUP BY clt.name
PIVOT Format(vta_fac.fch, 'yyyy') 
   IN ('2010','2011','2012','2013','2014','2015','2016')

 
Copia y pega la sentencia SQL de los ejemplos y pulsa EJECUTAR SENTENCIA SQL. El resultado obtenido a través del driver ODBC se muestra en formato JSON.

EV07 SentenciaSQL

Archivo de log

En la parte superior derecha tienes disponible un botón, junto con la ayuda, para el acceso al archivo de log con las sentencias SQL que ejecutamos a través del driver ODBC en la base de datos Access.

EV07 LogODBC

Conclusiones

Si has completado los pasos para ejecutar el ejercicio completo, con la descarga de los datos desde vGestion y la posterior exportación a la base de datos Access, habrás comprobado lo sencillo que resulta con Velneo exportar datos a otros entornos.

En este ejercicio he elegido un mecanismo, quizás un poco peculiar, para que no pensemos que exportar datos se limita a abrir una tabla de Velneo y generar un archivo de texto.

bdex EsquemaTotal

El plugin ODBC junto al lenguaje SQL son herramientas muy versátiles, que como hemos visto facilitan la creación y manipulación desde cero de una base de datos de escritorio Access, muy utilizada en entornos empresariales como repositorio de datos para Business Inteligence (BI).

Espero que con este artículo hayas ampliado tus expectativas para compartir tus datos con otros entornos y te animes a seguir extendiendo las funcionalidades de nuestro querido Velneo.