Connect to Quick Books Database with Cobol using ODBC and SQL

Question

We are trying to just open a connection to our QB DB and get any data out of it. We managed to achieve it with C#, but translating to CoBOL (a language that is foreign to us) is proving very difficult. Here is the C# code:

class Program {
        static void Main(string[] args)
        {
            var odbcConnection = new OdbcConnection("DSN=QuickBooks Data;DFQ=C:\\Users\\Public\\Documents\\Intuit\\QuickBooks\\Company Files\\" +
                                                "ourQuickBooksFile.qbw;OLE DB Services=-2;OpenMode=S");
            odbcConnection.Open();

            var odbcDataAdapter = new OdbcDataAdapter("SELECT ListID, FullName, CompanyName FROM Customer", odbcConnection);
            var dataSet = new DataSet();

            odbcDataAdapter.Fill(dataSet);

            DataRowCollection dataRowCollection = dataSet.Tables[0].Rows;
            foreach (DataRow dataRow in dataRowCollection)
            {
                Console.WriteLine(dataRow["ListID"] + " " + dataRow["FullName"] + " " + dataRow["CompanyName"]);    
            }
            Console.ReadLine();
            odbcConnection.Close();

        }
    }

We are open to alternative methods, but the COBOL part is essential.

We are using Windows 10 and GnuCOBOL.

Forget "translating" as such, just find out how to do it in COBOL. Although, if COBOL is new to you it would seem not such a bright idea for it to be essential to use it. 0.218 of a search engine finds examples in "COBOL", but for this type of thing exactly which COBOL is necessary to be known. — Bill Woodger, Dec 11 '15 at 16:43
"Finding out how to do it in COBOL" is a much harder task than it seems. I've been googling for a day and a half, and have yet to come up with any super solid results. Currently thumbing through some COBOL books, but figured someone might actually be helpful here. — mrjigglejam, Dec 11 '15 at 19:24
"Cobc for Cobol, Windows is the OS – mrjigglejam 4 hours ago" Cobc is the compiler. I'm downloading minGW, I think it might help??? — mrjigglejam, Dec 11 '15 at 21:05
Cobc is not the name of the compiler. Try `cobc -i`. Does it say `GNU Cobol`? Actually, that's funny. It should say GnuCOBOL. — Bill Woodger, Dec 11 '15 at 21:12
Here's a place where your question will fit better: https://sourceforge.net/p/open-cobol/discussion/?source=navbar — Bill Woodger, Dec 11 '15 at 23:57

Brian Tiffin · Answer 1 · 2015-12-13T02:21:34.873

Instead of just posting the link, (at bottom, which you'll need, to get to grips with compiler options in play), here is some skeleton code from the GnuCOBOL FAQ regarding ODBC access with the esqlOC preprocessor by Sergey Kashyrin.

   IDENTIFICATION DIVISION.
   PROGRAM-ID. esqlOCGetStart1.
   DATA DIVISION.
   WORKING-STORAGE SECTION.
   EXEC SQL
     BEGIN DECLARE SECTION
   END-EXEC.
   01  HOSTVARS.
       05 BUFFER               PIC X(1024).
       05 hVarD                PIC S9(5)V99.
       05 hVarC                PIC X(50).
       05 hVarN                PIC 9(12).
   EXEC SQL
      END DECLARE SECTION
   END-EXEC.
   PROCEDURE DIVISION.
   MAIN SECTION.
  *-----------------------------------------------------------------*
  * CONNECT TO THE DATABASE
  * also possible with DSN: 'youruser/yourpasswd@yourODBC_DSN'
  *-----------------------------------------------------------------*
     STRING 'DRIVER={MySQL ODBC 5.2w Driver};'
            'SERVER=localhost;'
            'PORT=3306;'
            'DATABASE=test;'
            'USER=youruser;'
            'PASSWORD=yourpasswd;'
  * example for DB specific ODBC parameter:
  *   no compressed MySQL connection (would be the DEFAULT anyway)
            'COMRESSED_PROTO=0;'
       INTO BUFFER.
     EXEC SQL
       CONNECT TO :BUFFER
     END-EXEC.
     PERFORM SQLSTATE-CHECK.
  *-----------------------------------------------------------------*
  * CREATE  TABLEs
  *-----------------------------------------------------------------*
  * TESTPERSON
     MOVE SPACES TO BUFFER.
     STRING
       'CREATE TABLE TESTPERSON('
         'ID DECIMAL(12,0), '
         'NAME CHAR(50) NOT NULL, '
         'PRIMARY KEY (ID))'
       INTO BUFFER.
     EXEC SQL
       EXECUTE IMMEDIATE  :BUFFER
     END-EXEC
     IF SQLSTATE='42S01'
       DISPLAY ' Table TESTPERSON already exists.'
     ELSE
       PERFORM SQLSTATE-CHECK
       DISPLAY ' created Table TESTPERSON'
       PERFORM INSDATAPERSON.
  * TESTGAME
     MOVE SPACES TO BUFFER.
     STRING
       'CREATE TABLE TESTGAME('
         'ID DECIMAL(12,0), '
         'NAME CHAR(50) NOT NULL, '
         'PRIMARY KEY (ID))'
       INTO BUFFER.
     EXEC SQL
       EXECUTE IMMEDIATE  :BUFFER
     END-EXEC
     IF SQLSTATE='42S01'
       DISPLAY ' Table TESTGAME already exists.'
     ELSE
       PERFORM SQLSTATE-CHECK
       DISPLAY ' created Table TESTGAME'
       PERFORM INSDATAGAME.
  * TESTPOINTS
     MOVE SPACES TO BUFFER.
     STRING
       'CREATE TABLE TESTPOINTS('
         'PERSONID DECIMAL(12,0), '
         'GAMEID DECIMAL(12,0), '
         'POINTS DECIMAL(6,2), '
         'CONSTRAINT POINTS_CONSTRAINT1 FOREIGN '
           'KEY (PERSONID) REFERENCES TESTPERSON(ID), '
         'CONSTRAINT POINTS_CONSTRAINT2 FOREIGN '
           'KEY (GAMEID) REFERENCES TESTGAME(ID),'
         'PRIMARY KEY (PERSONID, GAMEID))'
       INTO BUFFER.
     EXEC SQL
       EXECUTE IMMEDIATE  :BUFFER
     END-EXEC
     IF SQLSTATE='42S01'
       DISPLAY ' Table TESTPOINTS already exists.'
     ELSE
       PERFORM SQLSTATE-CHECK
       DISPLAY ' created Table TESTPOINTS'
       PERFORM INSDATAPOINTS.
  *-----------------------------------------------------------------*
  * SELECT SUM of POINTS for persons >1
  *-----------------------------------------------------------------*
     EXEC SQL
       SELECT
         SUM(POINTS)
       INTO
         :hVarD
       FROM
         TESTPERSON, TESTPOINTS
       WHERE PERSONID>1 AND PERSONID=ID
     END-EXEC
     PERFORM SQLSTATE-CHECK
     IF SQLCODE NOT = 100
       DISPLAY 'SELECTED '
       DISPLAY '  SUM of POINTS for persons >1 ' hVarD
     ELSE
       DISPLAY ' No points found'
     END-IF.
  *-----------------------------------------------------------------*
  * SELECT ALL with CURSORS
  *-----------------------------------------------------------------*
     EXEC SQL
       DECLARE CUR_ALL CURSOR FOR
       SELECT
         TESTPERSON.NAME,
         POINTS
       FROM
         TESTPERSON, TESTPOINTS
       WHERE PERSONID=ID
     END-EXEC
     PERFORM SQLSTATE-CHECK
     EXEC SQL
       OPEN CUR_ALL
     END-EXEC
     PERFORM SQLSTATE-CHECK
     PERFORM UNTIL SQLCODE = 100
       EXEC SQL
         FETCH CUR_ALL
         INTO
           :hVarC,
           :hVarD
       END-EXEC
       PERFORM SQLSTATE-CHECK
       IF SQLCODE NOT = 100
         DISPLAY 'FETCHED '
         DISPLAY '  person ' hVarC ' points: ' hVarD
       ELSE
         DISPLAY ' No points found'
       END-IF
     END-PERFORM.
  *-----------------------------------------------------------------*
  * DROP  TABLEs
  *-----------------------------------------------------------------*
  *   MOVE 'DROP TABLE TESTPOINTS' TO BUFFER.
  *   EXEC SQL
  *     EXECUTE IMMEDIATE  :BUFFER
  *   END-EXEC
  *   PERFORM SQLSTATE-CHECK.
  *   MOVE 'DROP TABLE TESTGAME' TO BUFFER.
  *   EXEC SQL
  *     EXECUTE IMMEDIATE  :BUFFER
  *   END-EXEC
  *   PERFORM SQLSTATE-CHECK.
  *   MOVE 'DROP TABLE TESTPERSON' TO BUFFER.
  *   EXEC SQL
  *     EXECUTE IMMEDIATE  :BUFFER
  *   END-EXEC
  *   PERFORM SQLSTATE-CHECK.
  *   DISPLAY ' dropped Tables '
  *-----------------------------------------------------------------*
  * COMMIT CHANGES
  *-----------------------------------------------------------------*
     EXEC SQL
       COMMIT
     END-EXEC.
     PERFORM SQLSTATE-CHECK.
  *-----------------------------------------------------------------*
  * DISCONNECT FROM THE DATABASE
  *-----------------------------------------------------------------*
     EXEC SQL
       CONNECT RESET
     END-EXEC.
     PERFORM SQLSTATE-CHECK.
     STOP RUN.
     .
  *-----------------------------------------------------------------*
  * CHECK SQLSTATE AND DISPLAY ERRORS IF ANY
  *-----------------------------------------------------------------*
   SQLSTATE-CHECK SECTION.
       IF SQLCODE < 0
                  DISPLAY 'SQLSTATE='  SQLSTATE,
                          ', SQLCODE=' SQLCODE
          IF SQLERRML > 0
             DISPLAY 'SQL Error message:' SQLERRMC(1:SQLERRML)
          END-IF
          MOVE SQLCODE TO RETURN-CODE
          STOP RUN
       ELSE IF SQLCODE > 0 AND NOT = 100
                  DISPLAY 'SQLSTATE='  SQLSTATE,
                          ', SQLCODE=' SQLCODE
          IF SQLERRML > 0
             DISPLAY 'SQL Warning message:' SQLERRMC(1:SQLERRML)
          END-IF
       END-IF.
       .
   INSDATAPERSON SECTION.
  *-----------------------------------------------------------------*
  * INSERT Data
  *-----------------------------------------------------------------*
  * TESTPERSON
     MOVE 0 TO hVarN.
     PERFORM UNTIL hVarN > 2
       COMPUTE hVarN = hVarN + 1
       STRING 'Testpers '
              hVarN
         INTO hVarC
       EXEC SQL
        INSERT INTO TESTPERSON SET
         ID=:hVarN,
         NAME=:hVarC
       END-EXEC
       PERFORM SQLSTATE-CHECK
       DISPLAY 'INSERTED '
       DISPLAY '  Person ' hVarN ' NAME ' hVarC
     END-PERFORM.
   INSDATAGAME SECTION.
  * TESTGAME
     MOVE 0 TO hVarN.
     PERFORM UNTIL hVarN > 3
       COMPUTE hVarN = hVarN + 1
       STRING 'Testgame '
              hVarN
         INTO hVarC
       EXEC SQL
        INSERT INTO TESTGAME SET
         ID=:hVarN,
         NAME=:hVarC
       END-EXEC
       PERFORM SQLSTATE-CHECK
       DISPLAY 'INSERTED '
       DISPLAY '  Game ' hVarN ' NAME ' hVarC
     END-PERFORM.
   INSDATAPOINTS SECTION.
  * TESTPOINTS
     MOVE 0 TO hVarN.
     MOVE 0 TO hVarD.
     PERFORM UNTIL hVarN > 2
       COMPUTE hVarN = hVarN + 1
       COMPUTE hVarD = hVarN + 0.75
       EXEC SQL
        INSERT INTO TESTPOINTS SET
         PERSONID=:hVarN,
         GAMEID=:hVarN,
         POINTS=:hVarD
       END-EXEC
       PERFORM SQLSTATE-CHECK
       DISPLAY 'INSERTED '
       DISPLAY '  POINTS for person/game ' hVarN ' : ' hVarD
     END-PERFORM.

Then preprocess

esqlOC.exe -static -o c:\Temp\esqlOCGetStart1.cob c:\Temp\esqlOCGetStart1.sqb

Then compile

SET OC_RUNTIME=c:\OpenCobol_bin
SET esqlOC_RUNTIME=c:\esqlOC\release
SET COB_CFLAGS=-I %OC_RUNTIME%
SET COB_LIBS=%OC_RUNTIME%\libcob.lib %OC_RUNTIME%\mpir.lib %esqlOC_RUNTIME%\ocsql.lib
SET COB_CONFIG_DIR=%OC_RUNTIME%\config\
set PATH=C:\WINDOWS\system32;%OC_RUNTIME%
call "%PROGRAMFILES%\Microsoft Visual Studio 10.0\VC\vcvarsall.bat"
%OC_RUNTIME%\cobc.exe -fixed -v -x -static -o c:\Temp\esqlOCGetStart1.exe c:\Temp\esqlOCGetStart1.cob

Then run

SET OC_RUNTIME=c:\OpenCobol_bin
SET esqlOC_RUNTIME=c:\esqlOC\release
set PATH=%OC_RUNTIME%;%esqlOC_RUNTIME%
c:\Temp\esqlOCGetStart1.exe

Your details will vary, of course. More information at

http://open-cobol.sourceforge.net/faq/#getting-started-with-esqloc

score 1 · Answer 2 · answered Dec 12 '15 at 08:48

You don't mention which compiler/OS your Cobol is on. If you are using Enterprise Cobol from IBM, then you can take your C# code (which is really just a one-for-one translation of Java) and extend it in a Cobol program.

Or since you are obviously a .NET shop, you can use a .NET flavor of Cobol and just extend the C# code you have above.

But without more detail about your environment, it is very hard to give you a concrete answer.

Connect to Quick Books Database with Cobol using ODBC and SQL

2 Answers2