Trabajo con SQL en Visual Pro5
Esta apartado cubre los siguientes aspectos:
Bases de Datos Locales
Ejecución de Comandos SQL
Pro5 ofrece comandos y funciones para simplificar el acceso a las bases de datos externas a PRO / 5, y permitir el acceso SQL a los archivos de datos propios de Pro5. Los comandos están diseñados para proporcionar un óptimo acceso a las capacidades de SQL disponibles en el Motor de base de SQL y en otros productos SQL con los cuales Pro5 está diseñado para comunicarse.
Bases de Datos Locales
El motor SQL de BASIS permite acceso a bases de datos nativas y bases de datos ODBC. Las bases de datos ODBC se definen utilizando la plataforma local dependiente del Administrador ODBC. Una vez que una base de datos se define y se asocia con un controlador ODBC desde el Administrador de ODBC, estará disponible para el acceso desde el motor SQL de BASIS. Las bases de datos nativas consisten de uno o más archivos Pro5 y un diccionario de datos que describe las características de los archivos. Cada base de datos, deberá ser descrita en un archivo sql.ini. El número de canales de SQL está limitado a 32.
NOTA: Usted debe incluir la siguiente línea en su archivo config.bbx antes de poder acceder a los comandos SQL:
SQL{=ruta}
El parámetro opcional ruta apunta a la ubicación de su archivo sql.ini. Si la ruta no se indica, Pro5 asume que el archivo sql.ini está en el directorio actual. Esta línea habilita la capacidad SQL en Visual Pro5.
El archivo sql.ini informa al motor SQL de Basis sobre las bases de datos nativas que haya disponibles. Por ejemplo, considere el siguiente archivo sql.ini:
[BASIS Data Sources]
Chile Company
Reunion de Clases
[Chile Company]
CONFIG=C:/BASIS/ejemplos/chile/config.tpm
[Reunion de Clases]
CONFIG=C:/BASIS/ejemplos/reunion/config.tpm
La sección “BASIS Data Sources” en el archivo sql.ini es requerida. Esta sección es sensible a mayúsculas y minúsculas y debe aparecer como se muestra en el ejemplo. Bajo la sección fuente de datos hay una lista de bases de datos disponibles. Cada base de datos representa un solo diccionario de datos.
Cada fuente de datos debe tener su propia sección, escrita tal cual fue mencionada en la sección “BASIS Data Sources”. Cada fuente de datos debe tener su propio archivo de configuración. Este archivo de configuración indica donde esta el diccionario de datos en el disco y donde existen los datos lógicos de esta fuente de datos. Considere el siguiente archivo de configuración:
# Chile Company Configuration File
DICTIONARY=C:/BASIS/ejemplos/chile/bbdict/
DATA=C:/BASIS/ejemplos/chile/data/
Las líneas que comienza con “#” son comentarios. El valor para DICTIONARY debe apuntar a la ubicación física del diccionario de datos. A menudo, para resolver la ruta de ubicación se requiere establecer una ruta global para éstos datos. Si este es el caso, debería definir todas las variables globales en archivo de configuración de diccionario requeridas para encontrar la ruta física de sus datos. Este es típicamente el valor DATA.
Existen dos herramientas para definir diccionarios. DDBuilder provee una interfaz gráfica para configurar y mantener diccionarios de datos. Se provee con Visual Pro5. La utilidad _ddedit provee una interfaz de caracter para realizar el mismo trabajo. Esta forma parte del set de Utilidades Extendidas.
Conceptos SQL
[Sección Pendiente de Traducción]
Ejecución de Comandos SQL
Una vez que ha seleccionado una base de datos, puede adquirir una lista de las tablas disponibles usando la función SQLTABLES(). Las sentencias SQL se ejecutan en dos pasos. Primero, debe ejecutar una sentencia SQL mediante SQLPREP. SQLPREP verifica la validez de la sentencia y prepara la ejecución estratégicamente. Segundo, usando SQLEXEC. SQLEXEC ejecuta la sentencia que fue preparada con SQLPREP.
Si la sentencia SQL (o sentencias) contiene argumentos reemplazables, éstos se pueden ajustar usando la instrucción SQLARG u, opcionalmente, usando la instrucción SQLEXEC.
Leyendo datos desde SQL
Una vez que la sentencia SQL fue ejecutada usando SQLEXEC, los datos pueden rescatarse usando la función SQLFETCH. Cuando todos los datos desde dicha sentencia se entregan, la función SQLFETCH arroja un !ERROR=2. Si se reporta cualquier otro error de parte de las instrucciones SQLPREP o SQLEXEC, podrá hallar una explicación del error en la documentación de la función SQLERR().
Se puede cerrar una conexión con una base de datos usando la instrucción SQLCLOSE.
Como ejemplo, considere acceder a la base de datos “Chile Company”, disponible en Visual Pro5. En esta base de datos de muestra hay varias tablas disponibles,y el siguiente programa muestra la selección de datos desde una de ellas.
Primero. Enlace la base de datos. Puesto que el nombre de la base de datos la conocemos, podemos obviar el uso de la función SQLLIST() y enlazar directamente con la base de datos:
0010 SQLOPEN(1)"Chile Company"
Segundo. Ejecute la sentencia deseada. En este caso una selección desde la tabla clientes (customers) con su actual balance:
0020 SQLPREP(1)"select * from CUSTOMER where CURRENT_BAL>0"
Tercero. Formatee una cadena para recibir las filas de datos generadas por la sentencia SQL:
0030 DIM CLIENTE$:SQLTMPL(1)
Cuarto. Ejecute la sentencia preparada via SQLPREP():
0040 SQLEXEC(1)
Quinto. Dado que la sentencia select no ordena los datos lo suficiente, es probable que comience a recibir datos inmediatamente, así que ejecute:
0050 CLIENTE$=SQLFETCH(1,ERR=90)
Sexto. El control de error manejará el fin del set de registros obtenidos (parecido a la condición “end of file” o fin de archivo que encontramos al leer datos desde un archivo Pro5 tradicional). Si no se cae por error, la variable CLIENTE$ recibirá la primera o siguiente fila desde la tabla seleccionada por la sentencia SQL. Usted puede hacer algo como esto y luego ir a recuperar otro registro:
0060 PRINT CLIENTE.CUST_NUM$," ",CLIENTE.COMPANY$," ",CLIENTE.CURRENT_BAL
0070 GOTO 50
Septimo. El control de error en la instrucción manejaría el esperado “end of file”, y generará un diagnóstico de error para otros errores o condiciones inesperadas:
0080 IF ERR=2 THEN GOTO 0110
0090 PRINT "Error",ERR,"encotrado, el texto del error sql es:"
0100 PRINT SQLERR(1,ERR=0110); GOTO 0100
0110 END
La función SQLFETCH() recuperará cualquier dato disponible como resultado de las instrucciones SQLPREP y SQLEXEC. Si no hay datos como resultado de la sentencia SQL, o si todos los datos ya fueron recuperados, SQLFETCH() arrojará un ¡!ERROR=2. Si una sentencia ejecutada entregó una lista de filas y ahora se prepara otra sentencia y ésta se ejecuta, la antigua lista de filas se descarta sin aviso. Además, una consulta puede ser reiniciada simplemente haciendo otro SQLEXEC, sin ejecutar un nuevo SQLPREP. Esta es una buena práctica, pues SQLPREP puede consumir mucho tiempo.
Insertando una Fila en una Tabla
Para insertar una fila en una base de datos, use las siguientes instrucciones:
0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP (1)"insert into CUSTOMER(CUST_NUM,FIRST_NAME,LAST_NAME)
0020:VALUES(6,'Cristian','Diaz')"
0030 SQLEXEC(1)
Modificando una Fila en una Tabla
SQLPREP insertará en la tabla CUSTOMER una nueva fila con la información proporcionada en las columnas apropiadas, y todas las columnas no especificadas contendrán datos por defecto para dichas columnas. Para modificar datos en el registro de un cliente en particular:
0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP(1)"update CUSTOMER set CREDIT_CODE='01'
0020:where CUST_NUM=10"
0030 SQLEXEC(1)
Eliminando una Fila en una Tabla
Para eliminar una fila:
0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP(1)"delete from CUSTOMER where
0020:CUST_NUM=10"
0030 SQLEXEC(1)
Múltiples Instrucciones SQLPREP
Los ejemplos anterior crean una nueva conexión y compilas un nuevo comando para cada operación requerida. En la práctica es relativamente fácil mantener múltiples conexiones a bases de datos, cada cual con comandos previamente compilados con argumentos parametrizables, para seleccionar la fila con la cual queremos trabajar. Sería mucho más efectivo para muchas de las operaciones antes mencionadas ejecutar un código como el siguiente:
0010 DATABASE$="Chile Company"
0020 GETREC=SQLUNT; SQLOPEN(GETREC)DATABASE$
0021 SQLPREP(GETREC)"select * from CUSTOMER where
0021:CUST_NUM=?"
0030 DELREC=SQLUNT; SQLOPEN(DELREC)DATABASE$
0031 SQLPREP(DELREC)"delete from CUSTOMER where
0031:CUST_NUM=?"
0040 DIM CUST$:SQLTMPL(GETREC)
0050 INPUT "numero de cliente: ",CUST:("end"=1000,9999)
0060 SQLEXEC (GETREC)CUST
0070 CUST$=SQLFETCH(GETREC,ERR=0200)
0080 PRINT CUST.CUST_NUM," ",CUST.COMPANY$
0090 INPUT (0,ERR=0090)"Elimina? (S/N) ",
0090:ANSWER$:("S"=100,"s"=100,"n"=50,"N"=50)
0100 SQLEXEC(DELREC)CUST
0110 PRINT CUST.CUST_NUM," eliminado"
0120 GOTO 0050
0200 PRINT "el registro no se encuentra"; GOTO 0050
Columnas de Tipo Fecha en Grillas “Data Aware”
Cuando una sentencia SELECT en un canal SQL contiene columnas de tipo fecha, el programador tiene dos opciones básicas:
1.- Desplegar la fecha como número en notación juliana (opción por defecto), o
2.- Primero convertir la fecha a un formato específico de fecha.
Por ejemplo, si usamos la siguiente instrucción en una Grilla “Data Aware”, la fecha se desplegaría como valor en notación Juliana:
SELECT order_date as ORDER_DATE, invoice_date as INVOICE_DATE FROM order
La plantilla generada sería:
ORDER_DATE:I(4),INVOICE_DATE:I(4)
Sin embargo, la segunda opción es convertir el valor fecha a una cadena formateada y entregar la cadena de representación de fecha. La siguiente sentencia SELECT formateará los valores fechas como dd/mm/aaaa (día, mes, año):
SELECT date(order_date, '%Mz/%Dz/%Yd') as ORDER_DATE,
date(invoice_date, '%Mz/%Dz/%Yd') as INVOICE_DATE
FROM order
La plantilla generada para esta instrucción sería:
ORDER_DATE:C(10),INVOICE_DATE:C(10)
Vea la documentación de función DATE() para conocer la construcción de formatos para fechas.