How to start using SQLite from COBOL

Question

I'm a COBOL developer and I need to connect to a SQLite DB. I'm using NetExpress. I've seen this guide and also this post but I need a previous step, please. I don't know how to pass those c calls to COBOL. The guide explains that:

sqlite3_open("test.db", &db)

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

I have the database 'test.db', with one table 'tabla' with 2 columns (type text). I tried with this COBOL solution for open/insert/select:

  *
  *----------------------------------------------------------------*
  *                                                                *
  *                                                                *
  *                                                                *
  * SYSTEM                                                         *
  *                                                                *
  * PROGRAM-ID    Pxxxxxx
  *                                                                *
  * AUTHOR                                                         *
  *                                                                *
  * DATE          10/2016                                          *
  *                                                                *
  *                                                                *
  *                                                                *
  *                                                                *
  *----------------------------------------------------------------*
  *                                                                *
  *                                                                *
  *                                                                *
  *----------------------------------------------------------------*
   IDENTIFICATION DIVISION.
   PROGRAM-ID.  Pxxxxxx.

   ENVIRONMENT DIVISION.
   CONFIGURATION SECTION.

  /----------------------------------------------------------------*
   INPUT-OUTPUT SECTION.
   FILE-CONTROL.

  *----------------------------------------------------------------*



  /----------------------------------------------------------------*
   DATA DIVISION.
   FILE SECTION.
  *----------------------------------------------------------------*

  /----------------------------------------------------------------*
   WORKING-STORAGE SECTION.
  *----------------------------------------------------------------*



   01 proc-ptr usage procedure-pointer.


   01 sqlite3-db         pointer.

   01 err-msg            pointer.
   01 sqlite             pointer.
   01 res                pointer.



   01 rc                 pic 9 comp-5.
   01 dbName             pic x(08).






   01 sqlQuery       pic x(100).

   01 result         pic x(100).
   01 argv.
       03  firstColumn   pointer.
       03  secondColumn  pointer.

   01 azColName          pointer.
   01 argc               pic 99 comp-5.
   01 notused            pointer.

   01 Writefunction-Ptr  procedure-pointer.


   01 PERSON-POST.
       03 NAMN     PIC X(60).
       03 AGE      PIC 9(3).
       03 ADRESS   PIC X(100).
       03 TELNR    PIC X(20).
   01 Column-Id       pic X(3).
   01 Column-Name     pic X(20).



  *-----------------------------------------------------------------
   Linkage Section.





  /-----------------------------------------------------------------
   procedure division.
  *-----------------------------------------------------------------

  *
            set proc-ptr to entry "sqlite3.dll"

            display sqlQuery


            set sqlite3-db to null
            set err-msg    to null
            set res        to null

            move z"test.db" to dbName

            display "Running sqlite3_open"

            call "sqlite3_open" using
                    by reference  z"test.db"
                    by reference  sqlite3-db
                    returning     rc
            end-call


            if rc not = zero
               display "error opening database."

            else
               display "database opened."

            end-if


            move "INSERT INTO tabla VALUES ('020', '8855');"
               to sqlQuery

           initialize Column-Id
           initialize Column-Name





  *
  *
  *
            call "sqlite3_exec" using
               by value sqlite3-db
               by reference sqlQuery
               by value     0
               by value     0
               by reference err-msg
               returning rc
            end-call


          set Writefunction-Ptr to entry "sqlite-callback".

          initialize sqlQuery
          move "SELECT * FROM tabla;" to sqlQuery


          call "sqlite3_exec" using
             by value sqlite3-db
             by reference sqlQuery
             by value Writefunction-Ptr
             by value 0
             by reference err-msg
           returning rc
          end-call
  *
          entry "sqlite-callback" using
             by value notused
             by value argc
             by reference argv
             by reference azColName.

  *    how to get column1
  *    how to get column2


       display Column-Id "|" Column-Name

  *


          call "sqlite3_close" using
                  by reference sqlite3-db
          end-call

          display "sqlite3_close"

    .

  *----------------------------------------------------------------
    stop run.


   Entry-Termination.

Now, open & insert are working fine, but I need to know how to get the records from Select sentence. I need to get several rows and show them.

What exactly "doesn't work"? What compiler and environment do you use? BTW: the second sample won't work as sq3-dll is no valid entry. — Simon Sobisch
Hi Simon, I've just change the question to show how I'm trying to insert a row. I've also changed sq3-dll. I'm using NetExpress. Thank you. — user6948899
I see, it is much better now. The entry point is still invalid as it doesn't exist in your program. According to the linked C tutorial this entry would be called on error with a descriptive message, therefore I suggest to chippy the necessary parts from the linked post and compile and run the program and provide the output you see. — Simon Sobisch
I've also included the 'sqlite3.dll' entry point, I don't know if this the entry point you're talking about. I've also included the output. — user6948899
For the entry look at the post you've already linked ENTRY 'yourname' USING .... Please post the new output directly as text as this helps more (you can select the text in the command window and use context-menu->copy). You can use the same entry for all calls, just set a flag (or a text like MOVE 'SELECT1' TO stuff-to-do) before the actual CALL` giving you the option to EVALUATE and/or DISPLAY it. — Simon Sobisch

user6948899 user6948899 · Accepted Answer · 2016-10-13T11:00:59

Finally, this is the code to open/insert/select/close in netExpress:

  *
  *----------------------------------------------------------------*
  *                                                                *
  *                                                                *
  *                                                                *
  * SYSTEM                                                         *
  *                                                                *
  * PROGRAM-ID    Pxxxxxx
  *                                                                *
  * AUTHOR                                                         *
  *                                                                *
  * DATE          10/2016                                          *
  *                                                                *
  *                                                                *
  *                                                                *
  *                                                                *
  *----------------------------------------------------------------*
  *                                                                *
  *                                                                *
  *                                                                *
  *----------------------------------------------------------------*
   IDENTIFICATION DIVISION.
   PROGRAM-ID.  Pxxxxx.

   ENVIRONMENT DIVISION.
   CONFIGURATION SECTION.

  /----------------------------------------------------------------*
   INPUT-OUTPUT SECTION.
   FILE-CONTROL.

  *----------------------------------------------------------------*



  /----------------------------------------------------------------*
   DATA DIVISION.
   FILE SECTION.
  *----------------------------------------------------------------*

  /----------------------------------------------------------------*
   WORKING-STORAGE SECTION.
  *----------------------------------------------------------------*



   01 proc-ptr usage procedure-pointer.


   01 sqlite3-db         pointer.

   01 err-msg            pointer.
   01 sqlite             pointer.
   01 res                pointer.



   01 rc                 pic 9 comp-5.
   01 dbName             pic x(08).






   01 sqlQuery       pic x(100).

   01 result         pic x(100).
   01 argv.
       03  firstColumn   pointer.
       03  secondColumn  pointer.

   01 azColName          pointer.
   01 argc               pic 99 comp-5.
   01 notused            pointer.

   01 Writefunction-Ptr  procedure-pointer.







  *-----------------------------------------------------------------
   Local-storage Section.
   Linkage Section.
   01 Column-Id       pic X(3).
   01 Column-Name     pic X(20).





  /-----------------------------------------------------------------
   procedure division.
  *-----------------------------------------------------------------

  *
            set proc-ptr to entry "sqlite3.dll"

            display sqlQuery


            set sqlite3-db to null
            set err-msg    to null
            set res        to null

            move z"test.db" to dbName

            display "Running sqlite3_open"

            call "sqlite3_open" using
                    by reference  z"test.db"
                    by reference  sqlite3-db
                    returning     rc
            end-call


            if rc not = zero
               display "error opening database."

            else
               display "database opened."

            end-if


            move "INSERT INTO tabla VALUES ('002', '8855');"
               to sqlQuery
  *
  *
  *
            call "sqlite3_exec" using
               by value sqlite3-db
               by reference sqlQuery
               by value     0
               by value     0
               by reference err-msg
               returning rc
            end-call
  *
         set Writefunction-Ptr to entry "sqlite-callback".

          initialize sqlQuery
          move "SELECT * FROM tabla;" to sqlQuery


          call "sqlite3_exec" using
             by value sqlite3-db
             by reference sqlQuery
             by value Writefunction-Ptr
             by value 0
             by reference err-msg
           returning rc
          end-call

          call "sqlite3_close" using
                  by reference sqlite3-db
          end-call

          display "sqlite3_close"

            .
  * -------------------------------------------------------
   stop run.
         entry "sqlite-callback"
         using
             by value notused
             by value argc
             by reference argv
             by reference azColName.

        set address of Column-Id   to firstColumn
        set address of Column-Name to secondColumn

        display Column-id "|" Column-Name
        goback.
   Entry-Termination.

How to start using SQLite from COBOL

1 Answers