fastpho
2 de junio de 2023, 14:13
Sqlite3Class.cbl es un envoltorio que permite integrar el motor de base de datos de sqlite con las aplicaciones de PowerCobol.
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
REPOSITORY
CLASS Sqlite3
WORKING-STORAGE
01 OBJSQLITE3 OBJECT REFERENCE SQLITE3 IS GLOBAL. *>Instance CLASS
invoke Sqlite3 "NEW" returning ObjSqlite3
5 - Luego puede invocar cualquier metodo de la clase
*> libversion
invoke objSqlite3 "LibVersionSql" returning BD-TEXT.
6 - Al finalizar destruir la instancia del objeto (set objSqlite3 to Null).
Class
*> ----------------------------------------------------------------------------------
*> 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.
*> 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.
De esta forma la implementacion es mas clara y la cantidad de lines es mucho menor.
Saludos...
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
REPOSITORY
CLASS Sqlite3
WORKING-STORAGE
01 OBJSQLITE3 OBJECT REFERENCE SQLITE3 IS GLOBAL. *>Instance CLASS
invoke Sqlite3 "NEW" returning ObjSqlite3
5 - Luego puede invocar cualquier metodo de la clase
*> libversion
invoke objSqlite3 "LibVersionSql" returning BD-TEXT.
6 - Al finalizar destruir la instancia del objeto (set objSqlite3 to Null).
Class
*> ----------------------------------------------------------------------------------
*> 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.
*> 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.
De esta forma la implementacion es mas clara y la cantidad de lines es mucho menor.
Saludos...