____ _ _ ____ ___ _ _ _ _____ / ___|___ | |__ ___ | |/ ___| / _ \| | (_) |_ ___ |___ / | | / _ \| '_ \ / _ \| |\___ \| | | | | | | __/ _ \ |_ \ | |__| (_) | |_) | (_) | | ___) | |_| | |___| | || __/ ___) | \____\___/|_.__/ \___/|_||____/ \__\_\_____|_|\__\___||____/
| Author: | Robert W.Mills |
|---|---|
| Date: | September 2017 |
| Rights: | Copyright © 2017-2021, Robert W.Mills. |
| Email: | rwm.cobol@gmail.com |
| Purpose: | An SQLite3 Interface Library for GnuCOBOL |
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License, in the file COPYING, along with this program. If not, see <http://www.gnu.org/licenses/gpl.txt>.
Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.
A copy of this license, in the file COPYING, should be included with this software. If not, see <http://www.gnu.org/licenses/fdl.txt>.
SQLite is in the Public Domain
All of the code and documentation in SQLite has been dedicated to the public domain by the authors. All code authors, and representatives of the companies they work for, have signed affidavits dedicating their contributions to the public domain and originals of those signed affidavits are stored in a firesafe at the main offices of Hwaci <https://www.hwaci.com/>. Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.
All of the deliverable code in SQLite has been written from scratch. No code has been taken from other projects or from the open internet. Every line of code can be traced back to its original author, and all of those authors have public domain dedications on file. So the SQLite code base is clean and is uncontaminated with licensed code from other projects.
The CobolSQLite3 Library is an interface to the SQLite3 Database Engine.
The interface consists of a Dynamic Library (.so file in Linux & .dll in Windows). It containing User Defined Functions that perform the following actions:
- Open/Create an SQLite Database.
- Close an SQLite Database.
- Compile an SQL Statement into byte-code.
- Bind values to parameters in a compiled SQL Statement.
- Execute a compiled SQL Statement.
- Release (delete) a compiled SQL Statement.
- Reset (re-initialise) a compiled SQL Statement.
- Compile, execute and release an SQL Statement.
- Retrieve all columns from a SELECTed row.
- Request information about the Database (currently a limited function).
- Translate a Status Code into human-readable text.
D Richard Hipp, architect and primary author of SQLite as well as the Fossil SCM, said:
"SQLite is a C library that implements an embeddable SQL database engine. Programs that link with the SQLite library can have SQL database access without running a separate RDBMS process. The distribution comes with a standalone command-line access program (SQLite) that can be used to administer a SQLite database and which serves as an example of how to use the SQLite library.
SQLite is not a client library used to connect to a big database server. SQLite is the server. The SQLite library reads and writes directly to and from the database files on disk."
It is not useful to evaluate the exact same SQL Statement more than once. More often, you want to execute similar statements. For example, you might want to execute an INSERT statement multiple times with different values. Or you might want to execute the same query multiple times using a different key in the WHERE clause.
To accommodate this, CobolSQLite3 allows SQL Statements to contain parameters which are bound to values prior to being executed. These values can later be changed and the same compiled SQL Statement can be executed a second time using the new values.
A question-mark (?) is used as a place-holder for the parameter within the SQL Statement. CobolSQLite3 allows a parameter wherever a string literal, numeric constant, or NULL is allowed. It may not be used for column or table names. A call to DBINFO Mode 200 will return the expanded SQL Statement.
A parameter initially has a value of NULL. Prior to calling DBEXECUTE the first time or immediately after a DBRESET, the program can call DBBIND to attach values to the parameters. Each use of DBBIND overrides prior bindings on the same parameter. Note that existing bindings are not cleared by DBRESET.
There is no arbitrary limit to the number of SQL Statements that can be compiled. A program can call DBCOMPILE multiple times at start-up, to create all of the compiled SQL Statements it will ever need, and then execute them as needed.
DBCOMPILE and DBSQL will only use the 1st statement passed in the sql-statement parameter, any additional statements will be dropped.
This helps prevent SQL Injection Attacks against the Database.
Returns the Status Code of the last executed CobolSQLite3 function.
move DBSTATUS to status-code
if DBSTATUS <> ZERO then ... end-if
status-code is a size 4 signed binary integer.
Opens the specified database and creates a Database Object.
move DBOPEN(db-name) to db-object
db-name is either an alphanumeric variable or quoted string. It holds the name of the Database to be opened. The path to the Database must be specified if it's not in the working directory.
db-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the Database Object.
If the database does not exist then it will be automatically created.
If db-name specifies a file that is not an SQLite3 Database then DBOPEN will return an error (use DBSTATUS to return the Status Code).
Closes the specified database and destroys its Database Object.
if DBCLOSE(db-object) <> ZERO then ... end-if
db-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the Database Object.
If DBCLOSE returns a non-zero value then use DBSTATUS to obtain the Status Code.
Compiles an SQL Statement, into byte-code, and creates an SQL Object for it.
move DBCOMPILE(db-object, sql-statement) to sql-object
db-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the Database Object.
sql-statement is either an alphanumeric variable or quoted string. It holds the SQL Statement to be compiled.
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
Use DBSTATUS to obtain the Status Code and check for a non-zero return.
Binds a value to a compiled SQL statement parameter.
if DBBIND(sql-object, param-idx, param-value) <> ZERO then ... end-if
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
param-idx is either an alphanumeric variable or an unquoted string. It indicates which parameter, in the compiled SQL statement, is to be bound.
param-value is either an alphanumeric variable, or a quoted string. It holds the value to be bound to the parameter pointed to by param-idx.
The leftmost SQL Statement parameter has an index of 1.
See Section 3. Reusing Compiled Statements for more details.
Executes an SQL Object (a compiled SQL Statement).
if DBEXECUTE(sql-object) <> ZERO then ... end-if
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
Releases (deletes) an SQL Object (a compiled SQL Statement).
if DBRELEASE(sql-object) <> ZERO then ... end-if
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
This function MUST be executed against all SQL Objects before the database is closed as failure to do so result's in memory leaks.
Resets the SQL Object back to initial state so it can be re-executed.
if DBRESET(sql-object) <> ZERO then ... end-if
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
If the SQL Object contains any bound parameter values, they will not be cleared.
Executes an SQL Statement against the Database Object.
if DBSQL(db-object, sql-statement) <> ZERO then ... end-if
db-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the Database Object.
sql-statement is either an alphanumeric variable or quoted string. It holds the SQL Statement to be executed.
This function combines the DBCOMPILE, DBEXECUTE and DBRELEASE functionality.
Returns the current result row of an SQL Query (Select).
if DBGET(sql-object, row-delims, row-buffer) <> ZERO then ... end-if
sql-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the SQL Object created for a compiled SQL Statement.
row-delims is a size 2 alphanumeric variable. It containing the delimiters to use when loading the row-buffer.
- The 1st character is placed between each column (see Notes below).
- The 2nd character is placed after the last column to indicate the end of data in row-buffer (see Notes below)
row-buffer is an alphanumeric variable that contains the complete row in delimited format. All numeric column data is converted to display format.
The row-delims entry in the Working-Storage Copybook sets default values of HEX 1D (Group Separator) and HEX 1E (Record Separator) for the 1st and 2nd delimiter. These values are the developers choice and you can keep them as-is, modify the row-delims entry in the Copybook, or override the values within your program by using one-or-more MOVE commands.
A modified version of the following unstring statement should be used to extract the data for each column from row-buffer. You, as the developer using this Library, are in complete control of the picture format/size of the Working-storage field that will be the recipient of the column data. It is suggested that you use the SQLite Command-Line Shell (see Section 5.1) to look at the current data formats/sizes used and code accordingly.
unstring row-buffer delimited by field-delimiter or row-delimiter into ws-column-1 ws-column-2 ws-column-n end-unstring
Returns information about the Database being accessed.
move DBINFO(dbinfo-mode, db-object) to dbinfo-buffer
dbinfo-mode is a size 4 unsigned numeric variable. It indicates what information to return (see DBINFO Modes table below).
db-object is a size 8 alphanumeric variable. It is used to hold the internal handle to the Database/SQL Object.
dbinfo-buffer is an alphanumeric variable for returning the requested information. Refer to the mapping for dbinfo-buffer in the Working-Storage Copybook (CobolSQLite3-WS.cpy) for further details.
Mode Description 100 The number of rows modified, inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement against the database. Changes caused by triggers, foreign key actions or REPLACE constraint resolution are not counted. 200 Return a compiled SQL Statement with bind parameters expanded.
Returns the error message associated with the last Status Code setting.
move DBERRMSG to error-message
display DBERRMSG
error-message is a size 256 alphanumeric variable. It will hold the error message text for the last Status Code.
The returned error-message will have one of following prefixes:
DBINF is not an actual error but for information. DBINFO 0: Successful completion. is the only entry with this prefix.
DBERR errors are generated by the CobolSQLite3 Library.
SQLite3ERR errors are generated by the SQLite3 Library.
Compile the CobolSQLite3 source (CobolSQLite3.cob) by entering the following command within any terminal program:
cobc -o CobolSQLite3.so -debug CobolSQLite3.cob -lsqlite3
Compile the source file as for Linux/Unix.
I do not have access to Windows, Windows/Cygwin or Windows/MinGW but I understand that the following commands should work [YMMV].
Windows/MinGW and Windows/Cygwin (inside the terminal program):
Compile the source file as for Linux/Unix.Windows/MinGW and Windows/Cygwin (outside the terminal program):
cobc -debug -lsqlite3 [drive:][path/to/]CobolSQLite3.cobNative Windows:
cobc -debug -lsqlite3 [drive:][path\\to\\]CobolSQLite3.cob
If you want a Hard-copy Listing then:
cobc -F -Xref -T CobolSQLite3.lst CobolSQLite3.cobThe source listing, including cross-reference, will be written to the file CobolSQLite3.lst.
GnuCOBOL: Version 3.1 (or greater) installed and tested fully working.
See the documentation supplied with GnuCOBOL. You must have run both the sanity checks created by the test procedures included within the Cobol85 suite as well as the make check procedure.SQLite3: Version 3.14 (or greater).
As a minimum you need to install the Shared Library (Libsqlite3-0) and Development Files (Libsqlite3-dev). Available from most Linux Distros, but for Windows you will have to download them from sqlite.org.
Note: If updating from a previous version you may need to execute the 'ldconfig' command.
Although you do not need it you will find it an advantage to also install the following:
- Command-Line Shell (sqlite3) is a utility that allows the manual entry and execution of SQL statements against an SQLite database. Documentation is at <https://www.sqlite.org/cli.html>.
- SQLite Database Analyzer (sqlite3_analyzer) reads an SQLite database and outputs a file showing the space used by each table and index and other statistics. Documentation is at <https://www.sqlite.org/sqlanalyze.html>.
- SQLite Database Diff (sqldiff) compares two SQLite database files and outputs the SQL needed to convert one into the other. Documentation is at <https://www.sqlite.org/sqldiff.html>.
The SQLite Download page <https://www.sqlite.org/download.html> contains Source Code files and precompiled binaries for multiple platforms. As of writing, February 2021, version 3.34.1 is available from here.
See <https://www.sqlite.org/about.html> for information on SQLite.
Before you use the CobolSQLite3 Library for the first time (and also after a new release) you must generate the Copybook modules required by the programs using the Library.
Enter the following commands:
cobcrun ./CobolSQLite3 --repository > CobolSQLite3-CSR.cpy cobcrun ./CobolSQLite3 --copylib > CobolSQLite3-WS.cpyThe first command generates a copylibrary containing a list of the functions that are available in the CobolSQLite3 Library. This module needs to be included in the REPOSITORY statement within the programs CONFIGURATION SECTION.
For example,
configuration section. repository. copy "CobolSQLite3-CSR.cpy". function all intrinsic.The second command generates a copylibrary containing the Library data definitions. This module needs to be included in the programs WORKING-STORAGE SECTION.
For example,
working-storage section. copy "CobolSQLite3-WS.cpy". *> additional data definitionsThere are two additional commands that you can run:
cobcrun ./CobolSQLite3 --version cobcrun ./CobolSQLite3 --helpThe first command displays the CobolSQLite3 Library and SQLite3 versions.
For example,
CobolSQLite3/A.01.00 SQLite3 Interface Functions for GnuCOBOL Copyright (c) Robert W.Mills <rwm.cobol@gmail.com>, 2017-2021 SQLite3 Library Version 3.13 ** Please update SQLite3 Library to at least Version 3.14 ** This is free software; see the source for copying conditions. There is NO WARRANTY; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.The second command displays some help text.
For example,
cobcrun [path/]CobolSQLite3 [option] [>filename] option: -h, --help Display this help text. -v, --version Display the Library and SQLite3 Versions. -r, --repository Generate the Repository Copylibrary module. -c, --copylib Generate the Working-storage Copylibrary module. Output from the --repository and --copylib options is written to Standard Out. Use >filename to redirect it to a disc file instead.
Below are examples of the two Copybook's that are generated by CobolSQLite3.
CobolSQLite3-CSR.cpy contains the Library function definitions. It needs to be COPYed into the Repository paragraph within the Configuration Section.
*> CobolSQLite3 Function Definitions. *> *> DO NOT EDIT THIS MODULE. See User Guide Section 6.2. Function DBSTATUS Function DBOPEN Function DBCLOSE Function DBCOMPILE Function DBBIND Function DBEXECUTE Function DBRELEASE Function DBRESET Function DBSQL Function DBGET Function DBINFO Function DBERRMSGCobolSQLite3-WS.cpy contains the Library data definitions. It needs to be COPYed into the Working-Storage Section. It is recommended that copy statement is placed at the start of the section.
*> CobolSQLite3 Working Storage Definitions. *> *> DO NOT EDIT THIS MODULE. See User Guide Section 6.2. 01 db-object pic x(008). 88 database-is-closed value NULL. 01 db-status pic s9(04) comp. 88 call-successful value ZERO. *> -- CobolSQLite3 Function codes ---------------------- 88 database-already-open value -1. 88 database-open-failed value -2. 88 database-not-open value -3. 88 unreleased-sql-objects-exist value -4. 88 sql-compile-failed value -5. 88 database-lock-failed value -6. 88 sql-object-not-released value -7. 88 sql-object-not-reset value -8. 88 invalid-bind-index value -9. 88 bind-value-to-big value -10. 88 datatype-unknown-unsupported value -11. 88 datatype-undefined value -12. 88 invalid-dbinfo-mode value -13. 88 not-an-sqlite-database value -14. 88 no-data-returned value -15. 88 row-buffer-overflow value -16. 88 dbinfo-buffer-overflow value -17. *> -- SQLite3 Library codes ---------------------------- 88 database-row-available value 100. 88 sql-statement-finished value 101. 01 sql-object pic x(008). 88 object-released value NULL. 01 row-delims. 05 field-delimiter pic x(001) value x'1D'. 05 row-delimiter pic x(001) value x'1E'. 01 dbinfo-mode pic 9(004) value zeros. 88 dbinfo-changed-rows value 100. 88 dbinfo-expand-sql value 200. 01 dbinfo-buffer pic x(2048). *> 2K should be big enough. 01 redefines dbinfo-buffer. 05 dbinfo-rows-changed pic s9(09) comp. 01 error-message pic x(128).
Prior to executing a program that uses the CobolSQLite3 Library you need to set the COB_PRE_LOAD environment variable. This tells the GnuCOBOL runtime link resolver what dynamic link modules are included in a run.
export COB_PRE_LOAD=CobolSQLite3If the Library is not in the current working directory along with the executable, the COB_LIBRARY_PATH environment variable can be set to find them.
export COB_LIBRARY_PATH=/path/to/library
Test-1.cob Open a new SQLite3 Database; create a Table with an Integer, Real (IEEE Floating Point) and Text column; load 4 entries into the Table; display all entries from the Table; and close the Database.
*> ** >>SOURCE FORMAT IS FREE *> *> Author : Robert W.Mills <CobolMac@btinternet.com> *> Dedicated to the public domain. *> *> Purpose : CobolSQLite3 example and test program. *> *> Tests : See Test-1 entry in the 'Example/Test Programs' section of the *> CobolSQLite3 User Guide. *> *> Written : September 2017 *> Modified : Not yet. *> *> Tectonics : Install the SQLite3 library (sqlite.org), if required. *> prompt$ cobc -x -fdebugging-line Test-1.cob *> or prompt$ cobc -x Test-1.cob *> prompt$ export COB_PRE_LOAD=CobolSQLite3 *> prompt$ ./Test-1 identification division. program-id. Test-1. environment division. configuration section. repository. copy "CobolSQLite3-CSR.cpy". function all intrinsic. data division. working-storage section. copy "CobolSQLite3-WS.cpy". 01 database-name pic x(080) value "./test.sdb". 01 sql-statements. 05 create-table-foo pic x(128) value "create table foo (foo_integer int, foo_real real, foo_text text);". 05 insert-into-foo-1 pic x(128) value "insert into foo (foo_integer, foo_real, foo_text) " & "values (-12345, -12.345, '1st line, numbers should be -12345 and -12.345');". 05 insert-into-foo-2 pic x(128) value "insert into foo (foo_integer, foo_real, foo_text) " & "values (2, 2.99, '2nd line, numbers should be 2 and 2.99');". 05 insert-into-foo-3 pic x(128) value "insert into foo (foo_integer, foo_real, foo_text) " & "values (-3, -3.99, '3rd line, numbers should be -3 and -3.99');". 05 insert-into-foo-4 pic x(128) value "insert into foo (foo_integer, foo_real, foo_text) " & "values (4, 4.99, '4th line, numbers should be 4 and 4.99');". 05 select-from-foo pic x(128) value "select * from foo;". 01 row-buffer pic x(128). 01 foo-heading-1. 05 pic x(001) value spaces. 05 pic x(007) value "integer". 05 pic x(003) value spaces. 05 pic x(007) value "real". 05 pic x(003) value spaces. 05 pic x(059) value "Text". 01 foo-heading-2. 05 pic x(001) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(059) value all "-". 01 foo-detail. 05 pic x(002) value spaces. 05 fd-integer pic -(5)9(1). 05 pic x(003) value spaces. 05 fd-real pic -(3).9(3). 05 pic x(003) value spaces. 05 fd-text pic x(059). procedure division. Test-1-mainline. >>D display "- opening database ", trim(database-name) end-display call "C$DELETE" using trim(database-name), 0 *> required but unused end-call move DBOPEN(database-name) to db-object if DBSTATUS <> ZERO then display "DBOPEN: ", DBERRMSG end-display goback end-if >>D display "- creating table foo" end-display if DBSQL(db-object, create-table-foo) <> ZERO then display "DBSQL (create table foo): ", DBERRMSG end-display goback end-if >>D display "- adding record(s) to table foo" end-display if DBSQL(db-object, insert-into-foo-1) <> ZERO then display "DBSQL (insert foo 1): ", DBERRMSG end-display goback end-if if DBSQL(db-object, insert-into-foo-2) <> ZERO then display "DBSQL (insert foo 2): ", DBERRMSG end-display goback end-if if DBSQL(db-object, insert-into-foo-3) <> ZERO then display "DBSQL (insert foo 3): ", DBERRMSG end-display goback end-if if DBSQL(db-object, insert-into-foo-4) <> ZERO then display "DBSQL (insert foo 4): ", DBERRMSG end-display goback end-if >>D display "- selecting all records from foo" end-display move DBCOMPILE(db-object, select-from-foo) to sql-object if DBSTATUS <> ZERO then display "DBCOMPILE (select foo): ", DBERRMSG end-display goback end-if move DBEXECUTE(sql-object) to db-status evaluate true when database-row-available perform print-column-headings perform get-print-data until sql-statement-finished display space end-display display "-- End of Report --" end-display when sql-statement-finished continue when database-lock-failed display "DBEXECUTE: ", DBERRMSG end-display goback when other display "DBEXECUTE: ", DBERRMSG end-display goback end-evaluate if DBRELEASE(sql-object) <> ZERO then display "DBRELEASE: ", DBERRMSG end-display end-if >>D display "- closing database ", trim(database-name) end-display if DBCLOSE(db-object) <> ZERO then display "DBCLOSE: ", DBERRMSG end-display end-if move zero to return-code goback . print-column-headings. *> Print the column heading lines. display foo-heading-1 end-display display foo-heading-2 end-display . get-print-data. *> Get the current result row and extract the values. if DBGET(sql-object, row-delims, row-buffer) <> ZERO then display "DBGET (foo): ", DBERRMSG end-display end-if unstring row-buffer delimited by field-delimiter or row-delimiter into fd-integer fd-real fd-text end-unstring *> Print the detail line. display foo-detail end-display *> Get the next row. move DBEXECUTE(sql-object) to db-status . end program Test-1.Test-2.cob is a copy of Test-1.cob that passes alphanumeric variables to the functions instead of quoted strings.
*> ** >>SOURCE FORMAT IS FREE *> *> Author : Robert W.Mills <CobolMac@btinternet.com> *> Dedicated to the public domain. *> *> Purpose : CobolSQLite3 example and test program. *> *> Tests : See Test-3 entry in the 'Example/Test Programs' section of the *> CobolSQLite3 User Guide. *> *> Written : September 2017 *> Modified : Not yet. *> *> Tectonics : Install the SQLite3 library (sqlite.org), if required. *> prompt$ cobc -x -fdebugging-line Test-2.cob *> or prompt$ cobc -x Test-2.cob *> prompt$ export COB_PRE_LOAD=CobolSQLite3 *> prompt$ ./Test-2 identification division. program-id. Test-2. environment division. configuration section. repository. copy "CobolSQLite3-CSR.cpy". function all intrinsic. data division. working-storage section. copy "CobolSQLite3-WS.cpy". 01 row-buffer pic x(128). 01 foo-heading-1. 05 pic x(001) value spaces. 05 pic x(007) value "integer". 05 pic x(003) value spaces. 05 pic x(007) value "real". 05 pic x(003) value spaces. 05 pic x(059) value "Text". 01 foo-heading-2. 05 pic x(001) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(059) value all "-". 01 foo-detail. 05 pic x(002) value spaces. 05 fd-integer pic -(5)9(1). 05 pic x(003) value spaces. 05 fd-real pic -(3).9(3). 05 pic x(003) value spaces. 05 fd-text pic x(059). procedure division. Test-2-mainline. >>D display "- opening database ./test.sdb" end-display call "C$DELETE" using "./test.sdb", 0 *> required but unused end-call move DBOPEN("./test.sdb") to db-object if DBSTATUS <> ZERO then display "DBOPEN: ", DBERRMSG end-display goback end-if >>D display "- creating table foo" end-display if DBSQL(db-object, "create table foo (foo_integer int, foo_real real, foo_text text);" ) <> ZERO then display "DBSQL (create table foo): ", DBERRMSG end-display goback end-if >>D display "- adding record(s) to table foo" end-display if DBSQL(db-object, "insert into foo (foo_integer, foo_real, foo_text) " & "values (-12345, -12.345, '1st line, numbers should be -12345 and -12.345');" ) <> ZERO then display "DBSQL (insert foo 1): ", DBERRMSG end-display goback end-if if DBSQL(db-object, "insert into foo (foo_integer, foo_real, foo_text) " & "values (2, 2.99, '2nd line, numbers should be 2 and 2.99');" ) <> ZERO then display "DBSQL (insert foo 2): ", DBERRMSG end-display goback end-if if DBSQL(db-object, "insert into foo (foo_integer, foo_real, foo_text) " & "values (-3, -3.99, '3rd line, numbers should be -3 and -3.99');" ) <> ZERO then display "DBSQL (insert foo 3): ", DBERRMSG end-display goback end-if if DBSQL(db-object, "insert into foo (foo_integer, foo_real, foo_text) " & "values (4, 4.99, '4th line, numbers should be 4 and 4.99');" ) <> ZERO then display "DBSQL (insert foo 4): ", DBERRMSG end-display goback end-if >>D display "- selecting all records from foo" end-display move DBCOMPILE(db-object, "select * from foo;") to sql-object if DBSTATUS <> ZERO then display "DBCOMPILE (select foo): ", DBERRMSG end-display goback end-if move DBEXECUTE(sql-object) to db-status evaluate true when database-row-available perform print-column-headings perform get-print-data until sql-statement-finished display space end-display display "-- End of Report --" end-display when sql-statement-finished continue when database-lock-failed display "DBEXECUTE: ", DBERRMSG end-display goback when other display "DBEXECUTE: ", DBERRMSG end-display goback end-evaluate if DBRELEASE(sql-object) <> ZERO then display "DBRELEASE: ", DBERRMSG end-display end-if >>D display "- closing database ./test.sdb" end-display if DBCLOSE(db-object) <> ZERO then display "DBCLOSE: ", DBERRMSG end-display end-if move zero to return-code goback . print-column-headings. *> Print the column heading lines. display foo-heading-1 end-display display foo-heading-2 end-display . get-print-data. *> Get the current result row and extract the values. if DBGET(sql-object, row-delims, row-buffer) <> ZERO then display "DBGET (foo): ", DBERRMSG end-display end-if unstring row-buffer delimited by field-delimiter or row-delimiter into fd-integer fd-real fd-text end-unstring *> Print the detail line. display foo-detail end-display *> Get the next row. move DBEXECUTE(sql-object) to db-status . end program Test-2.Test-3.cob is a copy of Test-1.cob that demonstrates Reusing Compiled Statements. It calls the DBCOMPILE, DBBIND, DBEXECUTE and DBRELEASE functions, instead of DBSQL, to load the 4 entries into the Table.
*> ** >>SOURCE FORMAT IS FREE *> *> Author : Robert W.Mills <CobolMac@btinternet.com> *> Dedicated to the public domain. *> *> Purpose : CobolSQLite3 example and test program. *> *> Tests : See Test-3 entry in the 'Example/Test Programs' section of the *> CobolSQLite3 User Guide. *> *> Written : October 2017 *> Modified : Feburary 2021 *> *> Tectonics : Install the SQLite3 library (sqlite.org), if required. *> prompt$ cobc -x -fdebugging-line Test-3.cob *> or prompt$ cobc -x Test-3.cob *> prompt$ export COB_PRE_LOAD=CobolSQLite3 *> prompt$ ./Test-3 identification division. program-id. Test-3. environment division. configuration section. repository. copy "CobolSQLite3-CSR.cpy". function all intrinsic. data division. working-storage section. copy "CobolSQLite3-WS.cpy". 01 database-name pic x(080) value "./test.sdb". 01 sql-statements. 05 create-table-foo pic x(128) value "create table foo (foo_integer int, foo_real real, foo_text text);". 05 insert-into-foo pic x(128) value "insert into foo (foo_integer, foo_real, foo_text) values (?, ?, ?);". 05 select-from-foo pic x(128) value "select * from foo;". 01 row-buffer pic x(128). 01 foo-heading-1. 05 pic x(001) value spaces. 05 pic x(007) value "integer". 05 pic x(003) value spaces. 05 pic x(007) value "real". 05 pic x(003) value spaces. 05 pic x(059) value "Text". 01 foo-heading-2. 05 pic x(001) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(007) value all "-". 05 pic x(003) value spaces. 05 pic x(059) value all "-". 01 foo-detail. 05 pic x(002) value spaces. 05 fd-integer pic -(5)9(1). 05 pic x(003) value spaces. 05 fd-real pic -(3).9(3). 05 pic x(003) value spaces. 05 fd-text pic x(059). procedure division. Test-3-mainline. set dbinfo-expand-sql to true >>D display "- opening database ", trim(database-name) end-display call "C$DELETE" using trim(database-name), 0 *> required but unused end-call move DBOPEN(database-name) to db-object if DBSTATUS <> ZERO then display "DBOPEN: ", DBERRMSG end-display goback end-if >>D display "- creating table foo" end-display if DBSQL(db-object, create-table-foo) <> ZERO then display "DBSQL (create table foo): ", DBERRMSG end-display goback end-if >>D display "- adding record(s) to table foo" end-display perform add-records-to-table if DBRELEASE(sql-object) <> ZERO then display "DBRELEASE: ", DBERRMSG end-display end-if >>D display "- selecting all records from foo" end-display move DBCOMPILE(db-object, select-from-foo) to sql-object if DBSTATUS <> ZERO then display "DBCOMPILE (select foo): ", DBERRMSG end-display goback end-if >>D display "-- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display move DBEXECUTE(sql-object) to db-status evaluate true when database-row-available perform print-column-headings perform get-print-data until sql-statement-finished display space end-display display "-- End of Report --" end-display when sql-statement-finished continue when database-lock-failed display "DBEXECUTE: ", DBERRMSG end-display goback when other display "DBEXECUTE: ", DBERRMSG end-display goback end-evaluate if DBRELEASE(sql-object) <> ZERO then display "DBRELEASE: ", DBERRMSG end-display end-if >>D display "- closing database ", trim(database-name) end-display if DBCLOSE(db-object) <> ZERO then display "DBCLOSE: ", DBERRMSG end-display end-if move zero to return-code goback . add-records-to-table. move DBCOMPILE(db-object, insert-into-foo) to sql-object if DBSTATUS <> ZERO then display "DBCOMPILE: ", DBERRMSG end-display goback end-if >>D display "-- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display >>D display "-- Add record 1 to table." end-display if DBBIND(sql-object, 1, '12345') <> ZERO then display "DBBIND (1-1): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 2, '23.456') <> ZERO then display "DBBIND (1-2): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 3, '1st line, numbers should be 12345 and 23.456') <> ZERO then display "DBBIND (1-3): ", DBERRMSG end-display goback end-if >>D display "--- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display >>D display "--- Insert the record." end-display move DBEXECUTE(sql-object) to db-status if not call-successful and not sql-statement-finished then display "DBEXECUTE (1): ", DBERRMSG end-display goback end-if >>D display "--- Reset the sql object." end-display if DBRESET(sql-object) <> ZERO then display "DBRESET (1): ", DBERRMSG end-display goback end-if >>D display "-- Add record 2 to table." end-display if DBBIND(sql-object, 1, '2') <> ZERO then display "DBBIND (2-1): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 2, '2.990') <> ZERO then display "DBBIND (2-2): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 3, "2nd line, numbers should be 2 and 2.990") <> ZERO then display "DBBIND (2-3): ", DBERRMSG end-display goback end-if >>D display "--- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display >>D display "--- Insert the record." end-display move DBEXECUTE(sql-object) to db-status if not call-successful and not sql-statement-finished then display "DBEXECUTE (2): ", DBERRMSG end-display goback end-if >>D display "--- Reset the sql object." end-display if DBRESET(sql-object) <> ZERO then display "DBRESET (2): ", DBERRMSG end-display goback end-if >>D display "-- Add record 3 to table." end-display if DBBIND(sql-object, 1, '-3') <> ZERO then display "DBBIND (3-1): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 2, '-3.990') <> ZERO then display "DBBIND (3-2): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 3, "3rd line, numbers should be -3 and -3.990") <> ZERO then display "DBBIND (3-3): ", DBERRMSG end-display goback end-if >>D display "--- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display >>D display "--- Insert the record." end-display move DBEXECUTE(sql-object) to db-status if not call-successful and not sql-statement-finished then display "DBEXECUTE (3): ", DBERRMSG end-display goback end-if >>D display "--- Reset the sql object." end-display if DBRESET(sql-object) <> ZERO then display "DBRESET (3): ", DBERRMSG end-display goback end-if >>D display "-- Add record 4 to table." end-display if DBBIND(sql-object, 1, '4') <> ZERO then display "DBBIND (4-1): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 2, '4.99') <> ZERO then display "DBBIND (4-2): ", DBERRMSG end-display goback end-if if DBBIND(sql-object, 3, "4th line, numbers should be 4 and 4.990") <> ZERO then display "DBBIND (4-3): ", DBERRMSG end-display goback end-if >>D display "--- ", trim(DBINFO(dbinfo-mode,sql-object)) end-display >>D display "--- Insert the record." end-display move DBEXECUTE(sql-object) to db-status if not call-successful and not sql-statement-finished then display "DBEXECUTE (4): ", DBERRMSG end-display goback end-if >>D display "--- Reset the sql object." end-display if DBRESET(sql-object) <> ZERO then display "DBRESET (4): ", DBERRMSG end-display goback end-if . print-column-headings. *> Print the column heading lines. display foo-heading-1 end-display display foo-heading-2 end-display . get-print-data. *> Get the current result row and extract the values. if DBGET(sql-object, row-delims, row-buffer) <> ZERO then display "DBGET (foo): ", DBERRMSG end-display end-if unstring row-buffer delimited by field-delimiter or row-delimiter into fd-integer fd-real fd-text end-unstring *> Print the detail line. display foo-detail end-display *> Get the next row. move DBEXECUTE(sql-object) to db-status . end program Test-3.
Version A.01.01 February 2021 Robert W.Mills (rwm.cobol@gmail.com)
- We now use the CONTENT-OF function instead of a BASED variable and address manipulation to access the character data returned by an SQLite3 char function. See the call to sqlite3_libversion in the libraries outer block for an example of it's use.
- Added work-around for GnuCOBOL 3.1.2 COMPUTE bug in DBBIND. Should be fixed in 3.2.
- Added a makefile. Use the command 'make help' to list the available options.
- Removed the genhtml script (use the command 'make html' instead).
Version A.01.00 February 2021 Robert W.Mills (rwm.cobol@gmail.com)
- This release requires that Version 3.14 (or greater) of the SQLite3 Library is installed.
- The 'cobcrun ./CobolSQLite3 --version' command will display a warning message if the current SQLite3 Library needs to be updated.
- Changed contact email address.
- Fixed DBOPEN problem discovered by Laszlo Erdos.
- Added section 1.3. SQLite3 to the User Guide documenting that SQLite is in the Public Domain.
- Added the DBBIND function.
- Added Mode 200 to DBINFO that will return a compiled SQL Statement with bind parameters expanded.
- Increased size of DBINFO-BUFFER (in CobSQLite3 Working Storage) from 80 characters to 1024 characters. This is required because of the DBINFO Mode 200 addition. Error -17 will be returned if this buffer overflows.
- Added a new section, 3. Reusing Compiled Statements, to the User Guide documenting how the DBBIND function is used. Note: The number of the original section 3, and all following sections, has been increased by 1 (one).
- CobolSQLite3 now requires use of runtime-options to generate the Copylibrary modules instead of ask if it should create them. See section 6.2. of the User Guide for further details.
- Added a third Example/Test Program. Test-3.cob is a modified copy of Test-1.cob that demonstrates the DBBIND function and Reusing Compiled Statements.
- Updated the User Guide to document the changes made.
Version A.00.00 September 2017 Robert W.Mills (CobolMac@btinternet.com)
- First production release to GnuCOBOL Contributions.
Version X.03.00 [BETA] September 2017 Robert W.Mills (CobolMac@btinternet.com)
- Created a User Guide that is in HTML format. The source text is held within the CobolSQLite3 source file in ReStructuredText format. It is extracted by ocdoc and then passed to Doccutils which generates the HTML file. The Modification History and Planned Enhancements are held in external files (ChangeLog and ToDo) which are merged into the User Guide when Docutils generates it.
- Added the DBGET function (replaces the DBGETSTR and DBGETINT functions).
Version X.02.00 [BETA] September 2017 Robert W.Mills (CobolMac@btinternet.com)
- Added this ChangeLog document.
- Added check in DBOPEN for SQLite3 Database before attempting to open it. A new status code, -14, and error message was added.
- Allow quoted strings to be used in place of string variables in parameters.
- Cleaned up comments (too many uses of the word 'the').
- Cleaned up error messages (too many uses of the word 'the').
Version X.01.00 [BETA] September 2017 Robert W.Mills (CobolMac@btinternet.com)
- Initial release to GnuCOBOL Contributions.
Version X.00.00 [ALPHA] August 2017 Robert W.Mills (CobolMac@btinternet.com)
- Start of development.
- DBERRMSG: Add optional parameter to return error message for any Status Code.
- DBINFO: Add Mode 300 (Check if specified table exists).