0
Sqlite3Class.cbl es un envoltorio que permite integrar el motor de base de datos de con las aplicaciones de .
Su implementacion es muy sencilla descargar Binarios precompilados para Windows para su version 32 bits (sqlite3.dll) no hace falta registrarla.
1 - Copiar Sqlite3.dll en la carpeta del proyecto
2 - Agregar la funciones de la libreria en el Archivo COBOL85
3 - Agregar la clase en la opcion Insertar File en el modulo
4 - Crear el Objects sqlite3 y genera la referencia al objeto
COBOL Código:
REPOSITORY CLASS Sqlite3COBOL Código:
WORKING-STORAGE 01 OBJSQLITE3 OBJECT REFERENCE SQLITE3 IS GLOBAL. *>Instance CLASSCOBOL Código:
invoke Sqlite3 "NEW" returning ObjSqlite3
5 - Luego puede invocar cualquier metodo de la clase
6 - Al finalizar destruir la instancia del objeto (set objSqlite3 to Null).COBOL Código:
*> libversion invoke objSqlite3 "LibVersionSql" returning BD-TEXT.
COBOL Código:
*> ---------------------------------------------------------------------------------- *> libreria sqlite3.dll *> cargar las funciones en el archivo cobol85.cbr *> Fecha 20-05-2023 *> Sqlite3Class.cbl *> Version 1.00 *> Author Osvaldo Urbano @fastpho *> Source Fujitsu FJBASE CLASS *> ---------------------------------------------------------------------------------- CLASS-ID. Sqlite3 INHERITS FJBASE. ENVIRONMENT DIVISION. CONFIGURATION SECTION. REPOSITORY. CLASS FJBASE. * OBJECT. DATA DIVISION. WORKING-STORAGE SECTION. 01 ValorVers PIC X(4) PROPERTY VALUE "1.00". *>Version de la Clase 01 pSqlite3 PIC 9(9) COMP-5 PROPERTY WITH NO SET. *>Handle de la BD - Propiedad Solo GET 01 pStmt3 PIC 9(9) COMP-5 PROPERTY WITH NO SET. *>Puntero Statement - Propiedad Solo GET PROCEDURE DIVISION. *> ---------------------------------------------------------------------------------- *> object Sqlite3 *> ---------------------------------------------------------------------------------- *> ---------------------------------------------------------------------------------- *> property 's *> ---------------------------------------------------------------------------------- *======================================================================== *> *> Property GET HANDLE = puntero DE LA BD *> METHOD-ID. GET PROPERTY HANDLE. DATA DIVISION. LINKAGE SECTION. 01 PROP-HANDLE-VALOR PIC 9(9) COMP-5. PROCEDURE DIVISION RETURNING PROP-HANDLE-VALOR. COMPUTE PROP-HANDLE-VALOR = pSqlite3. END METHOD. *======================================================================== *======================================================================== *> *> Property GET FIELDCOUNT = Cantidad de columnas despues del select *> METHOD-ID. GET PROPERTY FIELDCOUNT. DATA DIVISION. LINKAGE SECTION. 01 PROP-VALOR PIC 9(9) COMP-5. PROCEDURE DIVISION RETURNING PROP-VALOR. INVOKE SELF "ColumnaCountSql" RETURNING PROP-VALOR. END METHOD. *======================================================================== *> ---------------------------------------------------------------------------------- *> method's *> ---------------------------------------------------------------------------------- *======================================================================== *> *> Metodo OpenSql *> METHOD-ID. OpenSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-HANDLE PIC 9(9) COMP-5. *>Handle de la BD *======================================================================== * sqlite3_open * * # INPUT: BD-FILE-NAME * # OUTPUT: BD-HANDLE , BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-FILE-NAME PIC X(50). *>Nombre de la BD 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION USING BD-FILE-NAME RETURNING BD-STATUS. MOVE "sqlite3_open" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY REFERENCE BD-FILE-NAME BY REFERENCE BD-HANDLE RETURNING BD-STATUS END-CALL. MOVE BD-HANDLE TO pSqlite3. *> expongo el handler como property *> para todos los metodos. END METHOD OpenSql. *======================================================================== *======================================================================== *> *> Metodo CloseSql *> METHOD-ID. CloseSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_close * * # INPUT: * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION RETURNING BD-STATUS. MOVE "sqlite3_close" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pSqlite3 RETURNING BD-STATUS END-CALL. END METHOD CloseSql. *======================================================================== *======================================================================== *> *> Metodo Open2Sql *> METHOD-ID. Open2Sql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-HANDLE PIC 9(9) COMP-5. *>Handle de la BD *======================================================================== * sqlite3_open_v2 * * # INPUT: BD-FILE-NAME BD-OPEN-TYPE * # OUTPUT: BD-HANDLE , BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-FILE-NAME PIC X(50). *>Nombre de la BD 01 BD-OPEN-TYPE PIC 9(9) COMP-5. *>R RW SC 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION USING BD-FILE-NAME , BD-OPEN-TYPE RETURNING BD-STATUS. MOVE "sqlite3_open_v2" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY REFERENCE BD-FILE-NAME BY REFERENCE BD-HANDLE BY VALUE BD-OPEN-TYPE BY VALUE 0 RETURNING BD-STATUS END-CALL. MOVE BD-HANDLE TO pSqlite3. *> expongo el handler como property *> para todos los metodos. END METHOD Open2Sql. *======================================================================== *======================================================================== *> *> Metodo Close2Sql *> METHOD-ID. Close2Sql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_close_v2 * * # INPUT: * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION RETURNING BD-STATUS. MOVE "sqlite3_close_v2" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pSqlite3 RETURNING BD-STATUS END-CALL. END METHOD Close2Sql. *======================================================================== *======================================================================== *> *> Metodo LibversionSql *> METHOD-ID. LibversionSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-TEXT-POINTER POINTER. *======================================================================== * sqlite3_libversion * * * # OUTPUT: BD-TEXT *======================================================================== LINKAGE SECTION. 01 BD-TEXT PIC X any length. 77 sqlite3-version-text PIC X(256). PROCEDURE DIVISION RETURNING BD-TEXT. MOVE "sqlite3_libversion" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE RETURNING BD-TEXT-POINTER END-CALL. SET ADDRESS OF sqlite3-version-text TO BD-TEXT-POINTER. STRING sqlite3-version-text DELIMITED BY LOW-VALUE INTO BD-TEXT END-STRING. SET BD-TEXT-POINTER TO NULL. END METHOD LibversionSql. *======================================================================== *======================================================================== *> *> Metodo ColumnaCountSql *> METHOD-ID. ColumnaCountSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_column_count * * # INPUT: BD-HANDLE-STMT * # OUTPUT: BD-COLUMNA-CANTIDAD *======================================================================== LINKAGE SECTION. 01 BD-COLUMNA-CANTIDAD PIC 9(9) COMP-5. *>Cantidad de Columnas de la BD PROCEDURE DIVISION RETURNING BD-COLUMNA-CANTIDAD. MOVE "sqlite3_column_count" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 RETURNING BD-COLUMNA-CANTIDAD END-CALL. END METHOD ColumnaCountSql. *======================================================================== *======================================================================== *> *> Metodo PrepareSql *> METHOD-ID. PrepareSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-SQL-STRING-LENGHT PIC S9(9) COMP-5. 01 BD-HANDLE-STMT PIC 9(9) COMP-5. *>Puntero Statement 01 BD-STMT-PTR REDEFINES BD-HANDLE-STMT POINTER. *>Redefines del Puntero Statement *======================================================================== * sqlite3_prepare_v2 * * # INPUT: BD-SQL-QUERY * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-SQL-QUERY PIC X(256). *> string sql 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION USING BD-SQL-QUERY RETURNING BD-STATUS. MOVE "sqlite3_prepare_v2" TO FUNCIONES-SQLITE3-LIB. MOVE ZEROS TO BD-SQL-STRING-LENGHT. INSPECT BD-SQL-QUERY TALLYING BD-SQL-STRING-LENGHT FOR CHARACTERS BEFORE INITIAL X"00". CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pSqlite3 BY CONTENT BD-SQL-QUERY BY VALUE BD-SQL-STRING-LENGHT BY REFERENCE BD-STMT-PTR BY VALUE 0 RETURNING BD-STATUS END-CALL. *> DEVUELVE BD-STMT-PTR MOVE BD-HANDLE-STMT TO pStmt3. *> expongo el ptr statment como property *> para todos los metodos. END METHOD PrepareSql. *======================================================================== *======================================================================== *> *> Metodo FinalizeSql *> METHOD-ID. FinalizeSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_finalize * * # INPUT: * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION RETURNING BD-STATUS. MOVE "sqlite3_finalize" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 RETURNING BD-STATUS END-CALL. *>DEVUELVE BD-STATUS END METHOD FinalizeSql. *======================================================================== *======================================================================== *> *> Metodo StepSql *> METHOD-ID. StepSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_step * * # INPUT: * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION RETURNING BD-STATUS. MOVE "sqlite3_step" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 RETURNING BD-STATUS END-CALL. *>DEVUELVE BD-STATUS END METHOD StepSql. *======================================================================== *======================================================================== *> *> Metodo ErrMsgSql *> METHOD-ID. ErrMsgSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-SQL-ERROR POINTER. *======================================================================== * sqlite3_errmsg * * # INPUT: BD-HANDLE * # OUTPUT: BD-TEXT *======================================================================== LINKAGE SECTION. 01 BD-TEXT PIC X(256). 77 sqlite3-error-text PIC X(256). PROCEDURE DIVISION RETURNING BD-TEXT. MOVE "sqlite3_errmsg" TO FUNCIONES-SQLITE3-LIB. MOVE SPACES TO BD-TEXT. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL USING BY VALUE pSqlite3 RETURNING BD-SQL-ERROR END-CALL. SET ADDRESS OF sqlite3-error-text TO BD-SQL-ERROR. STRING sqlite3-error-text DELIMITED BY LOW-VALUE INTO BD-TEXT END-STRING. SET BD-SQL-ERROR TO NULL. *>DEVUELVE BD-TEXT END METHOD ErrMsgSql. *======================================================================== *======================================================================== *> *> Metodo ExecuteSql *> METHOD-ID. ExecuteSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_exec * * # INPUT: BD-SQL-QUERY * # OUTPUT: BD-STATUS *======================================================================== LINKAGE SECTION. 01 BD-SQL-QUERY PIC X(256). *>string sql (Create , Insert , Delete , Update ) 01 BD-STATUS PIC 9(9) COMP-5. *>Estado de la BD PROCEDURE DIVISION USING BD-SQL-QUERY RETURNING BD-STATUS. MOVE "sqlite3_exec" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pSqlite3 BY CONTENT BD-SQL-QUERY BY VALUE 0 BY VALUE 0 BY VALUE 0 RETURNING BD-STATUS END-CALL. *>DEVUELVE BD-STATUS END METHOD ExecuteSql. *======================================================================== *======================================================================== *> *> Metodo LastInsertIDSql *> METHOD-ID. LastInsertIDSql. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_last_insert_rowid * * # INPUT: * # OUTPUT: BD-FILA-ID *======================================================================== LINKAGE SECTION. 01 BD-FILA-ID PIC 9(9) COMP-5. *> ultima fila insert PROCEDURE DIVISION RETURNING BD-FILA-ID. MOVE "sqlite3_last_insert_rowid" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pSqlite3 RETURNING BD-FILA-ID END-CALL. *>DEVUELVE BD-FILA-ID END METHOD LastInsertIDSql. *======================================================================== *======================================================================== *> *> Metodo ColumnaInt *> METHOD-ID. ColumnaInt. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_column_int * * # INPUT: BD-HANDLE-STMT BD-COLUMNA-NUMERO * # OUTPUT: BD-INT *======================================================================== LINKAGE SECTION. 01 BD-COLUMNA-NUMERO PIC 9(9) COMP-5. 01 BD-INT PIC 9(9) COMP-5. PROCEDURE DIVISION USING BD-COLUMNA-NUMERO RETURNING BD-INT. MOVE "sqlite3_column_int" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 BY VALUE BD-COLUMNA-NUMERO RETURNING BD-INT END-CALL. *>DEVUELVE BD-INT END METHOD ColumnaInt. *======================================================================== *======================================================================== *> *> Metodo ColumnaText *> METHOD-ID. ColumnaText. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-TEXT-POINTER POINTER. *======================================================================== * sqlite3_column_text * * # INPUT: BD-HANDLE-STMT BD-COLUMNA-NUMERO * # OUTPUT: BD-TEXT *======================================================================== LINKAGE SECTION. 01 BD-COLUMNA-NUMERO PIC 9(9) COMP-5. 01 BD-TEXT PIC X(256). 01 sqlite3-value-text PIC X(256). PROCEDURE DIVISION USING BD-COLUMNA-NUMERO RETURNING BD-TEXT. MOVE "sqlite3_column_text" TO FUNCIONES-SQLITE3-LIB. MOVE SPACES TO BD-TEXT. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 BY VALUE BD-COLUMNA-NUMERO RETURNING BD-TEXT-POINTER END-CALL. SET ADDRESS OF sqlite3-value-text TO BD-TEXT-POINTER. STRING sqlite3-value-text DELIMITED BY LOW-VALUE into BD-TEXT END-STRING. SET BD-TEXT-POINTER TO NULL. *>DEVUELVE BD-TEXT END METHOD ColumnaText. *======================================================================== *======================================================================== *> *> Metodo ColumnaType *> METHOD-ID. ColumnaType. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). *======================================================================== * sqlite3_column_type * * # INPUT: BD-COLUMNA-NUMERO * # OUTPUT: BD-COLUMNA-TIPO *======================================================================== LINKAGE SECTION. 01 BD-COLUMNA-NUMERO PIC 9(9) COMP-5. 01 BD-COLUMNA-TIPO PIC 9(4) COMP-5. PROCEDURE DIVISION USING BD-COLUMNA-NUMERO RETURNING BD-COLUMNA-TIPO. MOVE "sqlite3_column_type" TO FUNCIONES-SQLITE3-LIB. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 BY VALUE BD-COLUMNA-NUMERO RETURNING BD-COLUMNA-TIPO END-CALL. *>DEVUELVE BD-COLUMNA-TIPO. END METHOD ColumnaType. *======================================================================== *======================================================================== *> *> Metodo ColumnaName *> METHOD-ID. ColumnaName. DATA DIVISION. WORKING-STORAGE SECTION. 01 FUNCIONES-SQLITE3-LIB PIC X(50). 01 BD-COLUMNA-POINTER POINTER. *======================================================================== * sqlite3_column_name * * # INPUT: BD-COLUMNA-NUMERO * # OUTPUT: BD-COLUMNA-NOMBRE *======================================================================== LINKAGE SECTION. 01 BD-COLUMNA-NUMERO PIC 9(9) COMP-5. 01 BD-COLUMNA-NOMBRE PIC X(256). 01 sqlite3-value-text PIC X(256). PROCEDURE DIVISION USING BD-COLUMNA-NUMERO RETURNING BD-COLUMNA-NOMBRE. MOVE "sqlite3_column_name" TO FUNCIONES-SQLITE3-LIB. MOVE SPACES TO BD-COLUMNA-NOMBRE. CALL FUNCIONES-SQLITE3-LIB WITH STDCALL LINKAGE USING BY VALUE pStmt3 BY VALUE BD-COLUMNA-NUMERO RETURNING BD-COLUMNA-POINTER END-CALL. SET ADDRESS OF sqlite3-value-text TO BD-COLUMNA-POINTER. STRING sqlite3-value-text DELIMITED BY LOW-VALUE INTO BD-COLUMNA-NOMBRE END-STRING. SET BD-COLUMNA-POINTER TO NULL. *>DEVUELVE BD-COLUMNA-NOMBRE. END METHOD ColumnaName. *======================================================================== *> ---------------------------------------------------------------------- *> END OBJECT Sqlite3 *> ---------------------------------------------------------------------- END OBJECT. END CLASS Sqlite3.De esta forma la implementacion es mas clara y la cantidad de lines es mucho menor.COBOL Código:
*> abro sql MOVE "escuela.db" & X"00" TO BD-FILE-NAME. MOVE 2 TO BD-OPEN-TYPE. invoke objSqlite3 "Open2Sql" using BD-FILE-NAME , BD-OPEN-TYPE returning BD-STATUS. *>prepare MOVE "select * from estudiante" & X"00" TO BD-SQL-QUERY. invoke objSqlite3 "PrepareSql" USING BD-SQL-QUERY returning BD-STATUS. *>Record row perform with no limit invoke objSqlite3 "StepSql" returning BD-STATUS *> next row *>Get id invoke objSqlite3 "ColumnaInt" using CAMPO-ID returning BD-INT MOVE BD-INT TO LINEA-ID *>Get nombre invoke objSqlite3 "ColumnaText" using CAMPO-NOMBRE returning BD-TEXT MOVE BD-TEXT TO LINEA-NOMBRE *>Get edad invoke objSqlite3 "ColumnaInt" using CAMPO-EDAD returning BD-INT MOVE BD-INT TO LINEA-EDAD *>Get altura invoke objSqlite3 "ColumnaText" using CAMPO-ALTURA returning BD-TEXT MOVE BD-TEXT TO LINEA-ALTURA COMPUTE ROWNO = ROWNO + 1 MOVE LINEA-ID TO "Text" OF "TableCells"(ROWNO 1) OF CmTable1 MOVE LINEA-NOMBRE TO "Text" OF "TableCells"(ROWNO 2) OF CmTable1 MOVE LINEA-EDAD TO "Text" OF "TableCells"(ROWNO 3) OF CmTable1 MOVE LINEA-ALTURA TO "Text" OF "TableCells"(ROWNO 4) OF CmTable1 end-perform.
Saludos...
Marcadores