Por Pablo Almunia
© Copyrights 1998 by Pablo Almunia, All rights reserved
FoxPress, Julio 1998
Introducción
En el artículo anterior dejamos a medias la sección sobre obtención de información sobre el driver, la base de datos o el gestor por medio de funciones del API de ODBC que continuamos ahora.
En este artículo también mostraremos cómo se deben gestionar los errores que puedan producirse al llamar a funciones del APU y cómo se puede realizar una consulta por medio de estas funciones y tener todo el control sobre la obtención de los datos.SQLGetTypeInfo
Por medio de SQLGetTypeInfo podemos obtener información sobre los tipos de datos soportados por el gestor o tipo de fichero con el cual queremos trabajar.
ODBC puede aislar en gran medida de las variaciones en cuanto a sintaxis del SQL de los distintos gestores, pero no es posible abstraernos totalmente de los tipos de datos que soporta cada uno. Aun cuando existen funciones de conversión, hay algunos gestores que no soportan el tipo DateTime o campos de más de 255 caracteres, otros gestores definen tipos de datos especiales como los Picture (imágenes) o General (asociados a OLE), etc.
Para conocer todos los tipos de datos que soporta el gestor o tipo de fichero de un determinado Data Source utilizaremos SQLGetTypeInfo:
DECLARE SHORT SQLGetTypeInfo IN odbc32.dll; INTEGER nHstmt, ; INTEGER nSqlType
Esta función necesita como primer parámetro un handle a una ejecución de ODBC. Para obtener este handle debemos llamar a la función:
DECLARE SHORT SQLAllocStmt IN odbc32.dll ; INTEGER nhdbc, ; INTEGER @nHstmt
Pero ahora nos encontramos que esta función necesita como primer parámetro un handle a una conexión de ODBC. En este caso podríamos llamar a SQLAllocConnect y a SQLConnect (no confundir con la función de igual nombre de VFP). Posiblemente es más fácil utilizar estas funciones SQLCONNECT y SQLGETPROP de Visual FoxPro, para luego llamar a SQLAllocStmt:
nConexion = SQLCONNECT( cDSN ) nHdbc = SQLGETPROP( nConexion, ; "ODBCHdbc" ) nHstmt = 0 nResult=SQLAllocStmt( nHdbc, @nHstmt )
Una vez obtenido el handle podemos llamar a SQLGetTypeInfo de dos formas, preguntando si es soportado un determinado tipo o bien preguntando por todos ellos por medio del segundo parámetro y las siguientes constantes:
#define SQL_CHAR 1 #define SQL_NUMERIC 2 #define SQL_DECIMAL 3 #define SQL_INTEGER 4 #define SQL_SMALLINT 5 #define SQL_FLOAT 6 #define SQL_REAL 7 #define SQL_DOUBLE 8 #define SQL_DATETIME 9 #define SQL_VARCHAR 12 #define SQL_TYPE_DATE 91 #define SQL_TYPE_TIME 92 #define SQL_TYPE_TIMESTAMP 93 #define SQL_ALL_TYPES 0
Una vez llamada la función obtendremos los datos por medio de las funciones SQLFetch, que nos obtiene el resultado para un tipo, y SQLGetData que nos retorna los valores de las informaciones que podemos obtener.
odbctypes.prg
Cómo ejemplo de estas funciones hemos escrito el programa odbctypes.prg que guarda en un cursor de VFP la información sobre todos los tipos soportados por un Data Source:
LPARAMETERS cDSN, cCursor *** Cargar el API de ODBC #include "ODBCAPI.H" DO ODBCAPI LOCAL nConexion,nHdbc,nHstmt,nResult LOCAL nNumCols,nOldSelect,nCont,nColCount *** Preparación nHenv = val( sys(3053) ) IF EMPTY( cDSN ) nConexion = SQLCONNECT( ) ELSE nConexion = SQLCONNECT( cDSN ) ENDIF nHdbc = SQLGETPROP( nConexion, "ODBCHdbc" ) nHstmt = 0 nResult=SQLAllocStmt( nHdbc, @nHstmt ) IF nResult == SQL_SUCCESS *** Ejecución de la orden SQLGetTypeInfo( nHstmt, SQL_ALL_TYPES ) IF nResult # SQL_SUCCESS ODBCError( nHenv, nHdbc, nHstmt ) SQLFreeStmt( nHstmt, SQL_CLOSE ) SQLDISCONN( nConexion ) RETURN ENDIF *** Crear el cursor para los datos *** sobre los tipos soportados IF EMPTY( cCursor ) cCursor = "DataTypes" ENDIF CREATE CURSOR &cCursor; ( TYPE_NAME C(128) NOT NULL, ; DATA_TYPE I NOT NULL, ; PRECISION I NULL, ; LITERAL_PREFIX C(128) NULL, ; LITERAL_SUFFIX C(128) NULL, ; CREATE_PARAMS C(128) NULL, ; NULLABLE I NOT NULL, ; CASE_SENSITIVE I NOT NULL, ; SEARCHABLE I NOT NULL, ; UNSIGNED_ATTRIBUTE I NULL, ; MONEY I NOT NULL, ; AUTO_INCREMENT I NULL, ; LOCAL_TYPE_NAME C(128) NULL, ; MINIMUM_SCALE I NULL, ; MAXIMUM_SCALE I NULL ) *** Obtención de los datos DO WHILE ; SQLFetch( nHstmt ) = SQL_SUCCESS *** Añadir un nuevo registro APPEND BLANK *** Número de columnas del tratamiento nNumCols = 0 nNumCols = AFIELDS( aCampos ) *** Datos de cada columna FOR nColCount = 1 TO nNumCols *** Obtención de datos cBuffer = REPLICATE( CHR(0), 256 ) nResult = SQLGetData( nHstmt, ; nColCount, 1, @cBuffer, 256, 15) *** Dependiendo del tipo de dato *** hacer una conversion u otra cField = FIELD( nColCount ) DO CASE CASE AT( CHR(0), cBuffer ) = 1 REPLACE (cField) WITH .NULL. CASE INLIST( aCampos[nColCount,2],; 'C', 'M' ) REPLACE (cField) WITH ; SUBSTR( cBuffer, 1, ; AT( CHR(0), cBuffer ) -1 ) CASE INLIST( aCampos[nColCount,2],; 'N', 'I', 'B' ) REPLACE (cField) WITH ; VAL( SUBSTR( cBuffer, 1, ; AT( CHR(0), cBuffer ) -1 ) ) CASE aCampos[nColCount,2] == 'D' REPLACE (cField) WITH ; CTOD( SUBSTR( cBuffer, 1, ; AT( CHR(0), cBuffer ) -1 ) ) CASE aCampos[nColCount,2] == 'T' REPLACE (cField) WITH ; CTOT( SUBSTR( cBuffer, 1, ; AT( CHR(0), cBuffer ) -1 ) ) OTHERWISE REPLACE (cField) WITH .NULL. ENDCASE NEXT ENDDO *** Fin del bucle de obtención de datos ELSE *** Si se produce un error ODBCError( nHenv, nHdbc, nHstmt ) ENDIF *** Desconexión SQLFreeStmt( nHstmt, SQL_CLOSE ) SQLDISCONN( nConexion )
Gestión de Errores
Dentro del programa anterior se llama a odbcerror.prg, uno de los programas que se distribuye con los fuentes de la revista, que contiene un pequeño gestor de errores de las funciones del API de ODBC llamando a la función SQLError:DECLARE SHORT SQLError IN odbc32.dll ; INTEGER nHenv, ; INTEGER nHdbc, ; INTEGER nHstmt, ; STRING @cSqlState, ; INTEGER @nNativeError, ; STRING @cErrorMsg, ; INTEGER nErrorMsgMax, ; INTEGER @nErrorMsgSize
Cuando una función del API de ODBC a realizado su trabajo sin problemas devuelve un valor reflejado en las siguientes constantes:
#define SQL_SUCCESS 0 #define SQL_SUCCESS_WITH_INFO 1
Si el valor devuelto es diferente debemos llamar a la función SQLError para saber que es lo que ha pasado.
Los tres primeros parámetros de esta función requieren los handles de ODBC, Conexión y Ejecución que tengamos, si no tenemos todavía alguno de ellos deberemos pasar un 0. Es siguiente parámetro debe ser un buffer de 5 posiciones para el código del error, el siguiente una variable de tipo numérica para obtener el número de error y por último tres parámetros para obtener el mensaje del error.
Veamos el código fuente de odbcerror.prg
LPARAMETER nHenv, nHdbc, nHstmt *** Constantes de ODBC #include "ODBCAPI.h" *** Normalmente ya estarán cargadas *** DO ODBCAPI.prg *** Comprobar los parámetros enviados IF TYPE( 'nHenv' ) == 'L' nHenv = SQL_NULL_HENV ENDIF IF TYPE( 'nHdbc' ) == 'L' nHdbc = SQL_NULL_HDBC ENDIF IF TYPE( 'nHstmt' ) == 'L' nHstmt = SQL_NULL_HSTMT ENDIF *** Crear los buffers cSqlState = REPLICATE( CHR(0), 5 ) nNativeError = 0 cErrorMsg = REPLICATE( CHR(0), 1024 ) nErrorMsgSize = 0 *** Llamar a la función del API de ODBC *** que informa de los errores IF SQLError( nHenv, ; nHdbc, ; nHstmt, ; @cSqlState, ; @nNativeError, ; @cErrorMsg, ; LEN( cErrorMsg ), ; @nErrorMsgSize ) ; == SQL_SUCCESS *** Construir la cadena del error cMessageError = 'State : ' ; + cSqlState + CHR(13) cMessageError = cMessageError ; + 'Native Error : ' ; + STR( nNativeError ) ; + CHR(13) cMessageError = cMessageError ; + 'Error : ' ; + SUBSTR( cErrorMsg, ; 1, ; nErrorMsgSize - 1 ) ; + CHR(13) ELSE *** Si no se pudo obtener se da *** un error por defecto cMessageError = 'Error :' ENDIF *** Mostrar el error MESSAGEBOX( cMessageError, ; 64, ; 'ODBC API Error' )
Ejecución del Sentencias
La ejecución de sentencias directamente con el API de ODBC sólo es rentable cuando necesitamos un control muy fino de esta ejecución. Normalmente es más fácil ejecutar sentencias por medio de las funciones SQLEXECUTE() o SQLPREPARE() de Visual FoxPro.
La ejecución de sentencia con ODBC suele seguir el siguiente esquema:
odbcexcecution.prg
Como ejemplo de este tipo de ejecución hemos construido una clase denominada odbcexecution que nos permite lanzar una sentencia SQL a un servidor y obtener dos datos uno a uno de forma voluntaria. Cuando lanzamos una sentencia con la función SQLEXECUTE() de Visual FoxPro no tenemos mucho control sobre la recepción de los datos, por medio de la clase odbcexecution podemos controlar cuando se obtiene cada uno de los datos.
La descripción de la clase ODBCExecution es la siguiente:
Métodos:
Connect
Conecta el objeto por medio de ODBC
Parámetros
Retorno
.T. si es correcto y .F. si no fue así
Disconn
Desconecta el objeto de ODBC si fue creado con el método Connect()
Parámetros
No tiene
Retorno
.T. si se desconecto o .F. si no fue así
Exec
Lanza la ejecución de la sentencia
Parámetros
Retorno
No tiene
Fetch
Obtiene más datos de la ejecución en curso
Parámetros
No tiene
Retorno
.T. si ha funcionado o .F. si no se obtuvieron más datos
Propiedades
nConnection
Handle de la conexión de ODBC de VFP. Puede ser creado por medio del método Connect o bien asignar una conexión ya existente
cSQL
Sentencia SQL que queremos ejecutar. Se puede asignar y luego llamar al método Exec o ser pasado como parámetro a este método.
cCursor
Nombre del cursor donde se guardarán los datos. Se puede asignar y luego llamar al método Exec o ser pasado como parámetro a este método
Este sería un ejemplo de ejecución de una sentencia SELECT por medio de esta clase:
SET PROCEDURE TO odbcexecution oExec = CREATEOBJECT( "odbcexecution") oExec.Connect() IF oExec.Exec("select * from orders") DO WHILE oExec.Fetch() WAIT WIND "Registro insertado" ENDDO GO TOP BROWSE NOWAIT ENDIF El código fuente de la clase es: *** Incluir la cabecera del API de ODBC #include "odbcapi.h" *** Definición de la Clase DEFINE CLASS ODBCExecution AS CUSTOM *** Propiedades públicas nConnection = 0 cSQL = "" cCursor = "" *** Propiedades protegidas PROTECTED lCreatedConnection lCreatedConnection = .F. PROTECTED nHdbc nHdbc = 0 PROTECTED nHstmt nHstmt = 0 PROTECTED nNumCols nNumCols = 0 *** Métodos públicos FUNCTION Connect LPARAMETER cDSN, cUID, cPWD *** Comprobación de los parámetros IF EMPTY( cDSN ) cDSN = "" ENDIF IF EMPTY( cUID ) cUID = "" ENDIF IF EMPTY( cPWD ) cPWD = "" ENDIF *** Crear la conexión LOCAL nConexion nConexion = SQLCONNECT(cDSN,cUID,cPWD) *** Comprobar la conexión IF nConexion <> -1 *** Guardar en propiedades this.nConnection = nConexion this.lCreatedConnection = .T. *** Se obtiene el manejador de *** ODBC a la conexión this.nHdbc = SQLGETPROP( ; This.nConnection, ; "ODBCHdbc" ) RETURN .T. *** Si no conectó ELSE RETURN .F. ENDIF ENDFUNC FUNCTION Disconn *** Comprobar si estamos ejecutando IF This.nHstmt <> 0 SQLFreeStmt( This.nHstmt, SQL_CLOSE ) ENDIF *** Comprobar si creamos nosotros *** la conexión IF This.lCreatedConnection *** Desconectar SQLDISCON( This.nConnection ) this.lCreatedConnection = .F. RETURN .T. *** Devolver False para indicar *** que no se realizó *** la desconexión ELSE RETURN .F. ENDIF ENDFUNC FUNCTION Exec LPARAMETER cSQL, cCursor *** Comprobación de los parámetros IF EMPTY( This.cSQL ) AND EMPTY( cSQL ) RETURN .F. ENDIF DO CASE IF NOT EMPTY( cSQL ) this.cSQL = cSQL ENDIF CASE EMPTY( This.cCursor ) ; AND EMPTY( cCursor ) this.cCursor = "sqlresult" CASE NOT EMPTY( cCursor ) this.cCursor = cCursor ENDCASE *** Si no existe conexión se *** solicita una IF This.nConnection = 0 this.Connect() *** Si no se realiza la conexión *** se devuelve falso IF This.nConnection = 0 RETURN .F. ENDIF ENDIF *** Se obtiene el Handle de ODBC *** a la conexión this.nHdbc = SQLGETPROP( ; This.nConnection, "ODBCHdbc" ) *** Se crea en Handle de ODBC *** a una ejecución nHstmt = 0 nResult = SQLAllocStmt( This.nHdbc, ; @nHstmt ) this.nHstmt = nHstmt IF nResult == SQL_SUCCESS *** Se ejecuta la sentencia SQL nResult = SQLExecDirect( ; This.nHstmt,; cSQL, ; SQL_NTS ) IF nResult == SQL_SUCCESS ; OR nResult == SQL_SUCCESS_WITH_INFO this.CreateCursor() *** Error en la ejecución *** de la sentencia ELSE ODBCError( VAL(SYS(3053)), ; This.nConnection, ; This.nHstmt ) RETURN .F. ENDIF *** Error en la obtención del handle *** a la ejecución ELSE ODBCError( VAL(SYS(3053)), ; This.nConnection ) RETURN .F. ENDIF ENDFUNC FUNCTION Fetch LOCAL nReturn nReturn = SQLFetch( This.nHstmt ) IF nReturn = SQL_SUCCESS *** Añadir un nuevo registro *** en el cursor APPEND BLANK IN (this.cCursor) *** Obtener los datos de datas *** las columnas FOR nColCount = 1 TO This.nNumCols *** Obtener el dato cBuffer = REPLICATE( CHR(0), 256 ) nResult = SQLGetData( This.nHstmt,; nColCount, ; 1, ; @cBuffer, ; 256, ; 15 ) *** Realizar la conversión ; *** de tipos cField = FIELD( nColCount ) DO CASE CASE INLIST( TYPE(cField),'C','M') REPLACE (cField) ; WITH SUBSTR(cBuffer,1, ; AT( CHR(0), cBuffer ) -1 ) CASE INLIST( TYPE(cField),; 'N','I', 'B' ) REPLACE (cField) ; WITH VAL(SUBSTR(cBuffer,1,; AT( CHR(0), cBuffer ) -1 ) ) CASE INLIST( TYPE(cField),'D') REPLACE (cField) ; WITH CTOD(SUBSTR(cBuffer,1,; AT( CHR(0), cBuffer ) -1 ) ) CASE INLIST( TYPE( cField ), 'T' ) REPLACE (cField) ; WITH CTOT(SUBSTR(cBuffer,1,; AT( CHR(0), cBuffer ) -1 ) ) ENDCASE NEXT RETURN .T. *** Error en el Fetch ELSE RETURN .F. ENDIF ENDFUNC PROTECTED PROCEDURE CreateCursor *** Obtener el número de columnas nNumCols = 0 nResult = SQLNumResultCols( ; This.nHstmt, @nNumCols ) this.nNumCols = nNumCols IF nResult == SQL_SUCCESS *** Crear la cadena con *** la sentencia cEject = 'CREATE CURSOR ' ; + This.cCursor + ' (' *** Se obtiene la información de *** todas las columnas FOR nColCount = 1 TO nNumCols cColName = REPLICATE( CHR(0), 256 ) nColDef = 0 nSqlType = 0 nColDef = 0 nScale = 0 nNullable = 0 *** Se llama al API para obtener *** la información SQLDescribeCol( This.nHstmt, ; nColCount, ; @cColName, ; LEN( cColName ), ; @nColDef, ; @nSqlType, ; @nColDef, ; @nScale, ; @nNullable ) *** Nombre de la columna cEject = cEject ; + SUBSTR(cColName,1,; AT(CHR(0),cColName)-1); + ' ' *** Tipo de la columna DO CASE CASE INLIST( nSqlType, ; SQL_BINARY, ; SQL_VARBINARY, ; SQL_LONGVARBINARY ) cEject = cEject + 'M' CASE INLIST( nSqlType, ; SQL_DECIMAL, ; SQL_NUMERIC ) cEject = cEject + 'N(' ; + LTRIM(STR(nColDef)) ; + ',' ; + LTRIM(STR(nScale)) ; + ')' CASE nSqlType = SQL_BIT cEject = cEject + 'L' CASE INLIST( nSqlType, ; SQL_TINYINT, ; SQL_SMALLINT, ; SQL_INTEGER ) cEject = cEject + 'I' CASE nSqlType = SQL_BIGINT cEject = cEject ; + 'C(' ; + LTRIM(STR(nColDef)) ; + ')' CASE INLIST( nSqlType, ; SQL_REAL, ; SQL_FLOAT, ; SQL_DOUBLE ) cEject = cEject + 'B(' ; + LTRIM(STR(nColDef)) ; + ')' CASE nSqlType = SQL_DATE cEject = cEject + 'D' CASE INLIST( nSqlType, ; SQL_TIME, ; SQL_TIMESTAMP ) cEject = cEject + 'T' OTHERWISE *** Si es de estos tipos SQL_CHAR, *** SQL_VARCHAR y SQL_LONGVARCHAR *** o de cualquier otro no conocido IF nColDef > 254 cEject = cEject + 'M' ELSE cEject = cEject + 'C(' ; + LTRIM(STR(nColDef)); + ')' ENDIF ENDCASE *** Contruir la sentencia IF nColCount != nNumCols cEject = cEject +', ' ENDIF NEXT *** Cerrar la sentencia de *** construcción del cursor cEject = cEject + ')' *** Ejecutar la sentencia CREATE *** CURSOR (con macrosustitución) &cEject *** Error en la obtención del número *** de columnas ELSE ODBCError( VAL(SYS(3053)), ; This.nConnection, ; This.nHstmt ) RETURN .F. ENDIF ENDPROC PROTECTED PROCEDURE Init *** Carga las declaraciones del API DO odbcapi ENDPROC PROTECTED PROCEDURE Destroy *** Cierra la conexión si esta existe *** y fue creada por nosotros IF This.nHstmt <> 0 SQLFreeStmt( This.nHstmt, SQL_CLOSE ) ENDIF IF This.lCreatedConnection ; AND This.nConnection > 0 SQLDISCONN( This.nConnection ) ENDIF ENDPROC ENDDEFINE
Otras herramientas
Como complemento a los ejemplos hemos incluido en el código fuente de los artículos varias utilidades para el desarrollo con ODBC, ya sea sólo con funciones de VFP o con el API de ODBC.
odbclistcon.scx
Nos muestra las conexiones ODBC que tenemos abiertas. Cuando los programas cancelan en el proceso de programación es muy habitual no ejecutar la función SQLDISCONN() y las conexiones se van quedando abiertas. Por medio de esta utilidad podemos liberar estas conexiones.
odbccommand.scx
Por medio de esta utilidad podemos ejecutar directamente ordenes contra una fuente ODBC y de esta forma poder comprobar su sintaxis, consultar o modificar datos directamente sin necesidad de programar, etc.
odbctables.scx
Aquí se puede visualizar la estructura de tablas y columnas a las que podemos acceder por medio de un determinado Data Source.
Conclusión
No hemos podido ver más que una pequeña parte de las APIs que ODBC pone a nuestra disposición, pero espero que te anime a seguir estudiando sus posibilidades.
Pablo Almunia Sanz pertenece al grupo de Consultoría y Control de Calidad de la Unidad de Informática de MAPFRE.