Enviando una consulta a EXCEL

2 comentarios

Hay muchas formas de enviar el resultado de una consulta a una planilla Excel, aquí veremos una de esas formas.

NOTA: el código fuente está basado en Visual FoxPro pero puede ser fácilmente adaptado a otros lenguajes de programación.

La ventaja de este método es que nos resultará muy fácil enviar la consulta que queremos, al libro Excel que queremos, a la hoja Excel que queremos, en el rango que queremos mostraremos la consulta, y esa consulta puede estar basada en cualquier tabla o en cualquier vista de nuestra Base de Datos del Firebird.

Primero, creamos la función que se encargará de realizar la tarea.

Listado 1.

FUNCTION ENVIAR_EXCEL_CON_DSN
LParameters tcDSN, tcUsuario, tcPassword, tcConsulta, tcRango
Local lcMensajeError, lcConsulta, loExcel, loLibro, loHoja, loTabla, lcConexion, lcBaseDatos

  lcMensajeError = ""

  tcRango = iif(VarType(tcRango) <> "C", "A1", tcRango)    && Si no se definió donde colocar la tabla, se la coloca en la celda A1

  *-- Tratamos de crear un objeto Excel
  if Empty(lcMensajeError)
    loExcel = CreateObject("Excel.Application")
    lcMensajeError = iif(VarType(loExcel) <> "O", "No se pudo crear el objeto EXCEL, verifica si está instaldo EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear un libro Excel
  if Empty(lcMensajeError)
    loLibro = loExcel.Workbooks.Add
    lcMensajeError = iif(VarType(loLibro) <> "O", "No se pudo crear el libro de EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear una hoja en el libro Excel
  if Empty(lcMensajeError)
    loHoja = loLibro.Worksheets(1)
    lcMensajeError = iif(VarType(loHoja) <> "O", "No se pudo crear la hoja en el libro EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, creamos el string de conexión a la Base de Datos del Firebird, usando el DSN de esa Base de Datos
  if Empty(lcMensajeError)
    lcConexion = "ODBC;DSN=" + tcDSN + ";USER=" + tcUsuario + ";PASSWORD=" + tcPassword
  endif

  *-- Si todo OK, tratamos de conectarnos a la Base de Datos del Firebird
  if Empty(lcMensajeError)
    loTabla = loHoja.QueryTables.Add(lcConexion, loHoja.Range(tcRango), tcConsulta)
    lcMensajeError = iif(VarType(loTabla) <> "O", "No se pudo crear la tabla de consulta en la planilla Excel", lcMensajeError)
  endif

  *-- Si todo OK, insertamos la tabla en la planilal Excel
  if Empty(lcMensajeError)
    #DEFINE xlInsertEntireRows 2
    loTabla.RefreshStyle = xlInsertEntireRows
    loTabla.Refresh
  endif

  *-- Si todoOK, mostramos la planilla Excel
  if Empty(lcMensajeError)
    loExcel.Visible = .T.
  endif

  *-- Liberamos de la memoria los objetos que habíamos creado
  loTabla = .NULL.
  loHoja  = .NULL.
  loLibro = .NULL.
  loExcel = .NULL.

Return(lcMensajeError)
*
*

Y después llamamos a esa función, con los parámetros que deseemos usar.

Listado 2.

Local lcDSN, lcUsuario, lcPassword, lcConsulta, lcMensajeError, lcRango

  lcDSN = "AUTOMUNDO-FIREBIRD"

  lcUsuario = "SYSDBA"
  lcPassword = "masterkey"

   lcConsulta = "SELECT " ;
              + "   ASC_ANOEJE, " ;
              + "   ASC_CODSUC, " ;
              + "   ASC_FECHAX, " ;
              + "   ASC_NUMERO " ;
              + "FROM " ;
              + "   V_CONTA_ABM_ASIENTOS " ;
              + "WHERE " ;
              + "   ASC_NUMERO >= 100 AND " ;
              + "   ASC_NUMERO <= 120"

  lcRango = "B5"

  lcMensajeError = ENVIAR_EXCEL_CON_DSN(lcDSN, lcUsuario, lcPassword, lcConsulta, lcRango)

  *-- Si ocurrió algún error, mostramos un mensaje al usuario
  if !Empty(lcMensajeError)
    #DEFINE MSG_ICONO_ERROR 16
    #DEFINE MSG_BOTON_ACEPTAR 0
    =MessageBox(lcMensajeError, MSG_ICONO_ERROR + MSG_BOTON_ACEPTAR, "Hay un problema...")
  endif

Return
*
*

Y así obtendremos una planilla similar a la siguiente:

EXCEL01

Captura 1. Si haces clic en la imagen la verás más grande

Por lo tanto, nos resultará muy sencillo crear una planilla Excel con los datos que el usuario pueda necesitar. Inclusive podríamos tener en nuestra aplicación una opción que le muestre todas las vistas disponibles y que le permita crear consultas personalizadas, a su gusto y paladar. Ya depende de la creatividad de cada uno hacer algo así.

Artículos relacionados:

Usando EXCEL para leer bases de datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Evitando que el tamaño de la Base de Datos se incremente demasiado

4 comentarios

Como Firebird usa la arquitectura MGA el tamaño de sus bases de datos puede crecer mucho y muy rápido.

La arquitectura MGA es muy buena para nosotros porque nos permite regresar la Base de Datos a su estado anterior. Cada vez que se modifica o se borra una fila esta arquitectura guarda una copia de la fila original. De esa manera siempre tenemos disponible la última versión de la fila y la ante-última versión de la fila. Y con los comandos COMMIT y ROLLBACK decidimos cual de esas versiones queremos usar.

En algunas transacciones nuestros cambios afectan a solamente una fila, pero en otras transacciones podrían afectar a decenas, miles, o millones de filas. Y gracias a MGA no debemos preocuparnos porque si queremos retrotraer la Base de Datos a su estado anterior, un simple comando ROLLBACK hará la tarea por nosotros.

Pero no todo son ventajas, también MGA tiene sus desventajas. Y es que esas filas que se fueron agregando con cada comando UPDATE o DELETE que ejecutamos van ocupando espacio dentro de la Base de Datos haciendo que se vuelva cada vez más grande. Y al volverse más grande también muchas operaciones se vuelven más lentas. El conjunto de todas esas filas que están dentro de la Base de Datos pero que ya son totalmente inútiles se llama “basura”.

La buena noticia es que podemos eliminar a la basura muy fácilmente. La mala noticia es que requiere de tiempo, no es algo instantáneo. Como consecuencia, lo recomendable es que la basura sea eliminada cuando nadie usa la Base de Datos. Si esto no es posible, entonces se debe eliminarla el día o la hora en que menos usuarios estén conectados (por ejemplo, todos los Domingos a las 23:00)

Entonces, nuestra tarea de mantenimiento tiene 2 partes:

  1. Un ciclo backup/restore
  2. Un barrido de la basura

El ciclo backup/restore nos asegura que solamente la última versión de cada fila se encuentra en la Base de Datos. Por lo tanto su tamaño disminuirá. Y en ocasiones la disminución es asombrosa, una Base de Datos de 350 Mb podría pasar a tener 40 Mb cuando el ciclo finaliza.

El barrido de la basura nos permitirá evitar que el tamaño de la Base de Datos crezca sin control. Si periódicamente hacemos un barrido entonces siempre tendremos una Base de Datos en muy buen estado de salud. Podríamos barrer un vez al día, o quizás una vez a la semana, ya dependerá de la cantidad de transacciones que durante ese tiempo ocurrieron. Un método es el siguiente:

  1. Detener el Servidor del Firebird
  2. Reiniciar el Servidor del Firebird (no debería durar ni 5 segundos el ciclo detener/reiniciar)
  3. Abrir la ventanita “Símbolo del sistema”
  4. Ejecutar el comando: GFIX -sweep -user SYSDBA -password masterkey MiBaseDatos

Realizar un ciclo backup/restore puede ser muy complicado en las empresas que trabajan 24/7/365, en cambio el barrido de la basura es más factible de encontrar el momento adecuado para hacerlo.

Sea como sea, mantener la Base de Datos con poca o nada de basura es una tarea muy importante, que está implícita cuando se trabaja con Firebird, y que debe realizarse sí o sí, no hay excusas, al menos si queremos que los tiempos de respuesta que obtienen los usuarios sean aceptables.

Artículos relacionados:

La arquitectura MGA

Entendiendo sweep y garbage collection

El índice del blog Firebird21

El foro del blog Firebird21