Menu ▾ ▴

VERY STUCK DEBUG PLEASE ASSIST OCESQL ANY TIP APPRECIATED THANKS

Help getting started
J McNamara
2024-11-30
2024-12-02

  • J McNamara

    J McNamara - 2024-11-30

    HI ALL-
    MY DB IS WORKING FINE THERE IS SOME CONTROL FLOW ISSUE WITH
    INSERTING INTO MY_TABLE SQ0005.

    ANY TIP WILL HELP.

    I AM GOING TO CRASH FOR A WHILE. I AM VERY TIRED.

    THANKS

    >> SOURCE FORMAT IS FIXED
IDENTIFICATION DIVISION.
PROGRAM-ID. testrun.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
    FILE-CONTROL.
       SELECT LOG-FILE ASSIGN TO "logfile.txt"
           ORGANIZATION IS LINE SEQUENTIAL.
DATA DIVISION.
FILE SECTION.
    FD  LOG-FILE.
    01  LOG-RECORD PIC X(100).

WORKING-STORAGE SECTION.
01 Keyboard-Status.
   03 Accept-Status Pic 9.
   03 Function-Key Pic X.
   03 System-Use Pic X.
01 Cursor-Position.
   03 Cursor-Row      Pic 9(2) Value 1.
   03 Cursor-Column Pic 9(2) Value 1.
01  I           PIC 9(2).
01  IDX         PIC 9(2).
01  WS-ID       PIC 9(11).
01  WS-SEQ-ID       PIC X(25).
01  WS-TRANS-ID         PIC X(20).
01  WS-AMOUNT           PIC 9(11).
01  WS-TRANS-TYPE       PIC X(50).
01  WS-TRANS-DATE       PIC X(10).
01  WS-ASSET-CLASS      PIC X(50).
01  WS-ACCT-ID      PIC 9(2).
01  SQL-STATUS          PIC X(5).
01  SQL-STATE       PIC X(100).
01  SQL-CODE        PIC X(100).
01  WS-BORDER1          PIC X(1).
01  WS-ACCT-CLASS-REC       PIC X(15).
01 WS-CLASS-NAME          PIC X(50).
    01 WS-CLASS-VALUE         PIC 9(2).
01 WS-AC-ID       PIC 9(5).
01 TMP-TRIM       PIC X(15).
01  MENU-OPTION     PIC 9 VALUE 0.
    01  NAME            PIC X(30).
    01  RECORD-EXISTS   PIC X VALUE 'N'.
01 LOG-MESSAGE PIC X(200).
01 error-msg  PIC X(100).

01  WS-RECORDS.
   05  WS-RECORD OCCURS 7 TIMES.
      10  WS-KEY-ID         PIC 9(11).
      10  WS-SEQ            PIC X(25).
      10  WS-AMOUNT-REC             PIC S9(9)V99.
      10  WS-TRANS-TYPE-REC         PIC X(50).
      10  WS-TRANS-DATE-REC         PIC X(10).
      10  WS-ASSET-CLASS-REC        PIC X(50).
      10  WS-ACCT-CLASS-ID          PIC S9(2).

    OCESQL EXEC SQL BEGIN DECLARE SECTION END-EXEC.
    01 WS-REC.
    05 REC-ID PIC 9(5).
    05 REC-CLASS-NAME PIC X(50) VALUE "Expense".
    05 REC-CLASS-VALUE PIC 9(2).
    OCESQL     EXEC SQL END DECLARE SECTION END-EXEC.
    OCESQL EXEC SQL BEGIN DECLARE SECTION END-EXEC.
    01 DBNAME PIC X(50) VALUE SPACE.
    01 USERNAME PIC X(30) VALUE SPACE.
    01 PASSWD PIC X(13) VALUE SPACE.
    01 EMP-REC-VARS.
    03 EMP-NO PIC S9(04) VALUE ZERO.
    03 EMP-NAME PIC X(20) .
    03 EMP-SALARY PIC S9(04) VALUE ZERO.
    OCESQL     EXEC SQL END DECLARE SECTION END-EXEC.

    OCESQL* EXEC SQL INCLUDE SQLCA END-EXEC.
    OCESQL copy "sqlca.cbl".

    OCESQL
    OCESQL 01 SQ0001.
    OCESQL 02 FILLER PIC X(040) VALUE
    "DELETE FROM MY_TABLE WHERE ID = '135'".
    OCESQL 02 FILLER PIC X(1) VALUE X"00".
    OCESQL

        SCREEN SECTION.
    01  Data-Entry-Screen.
       05  BLANK SCREEN.
       05  LINE 1 COLUMN 1 VALUE "Transaction Data".
       05  LINE 1 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 3 COLUMN 2 VALUE "ID: ".
       05  LINE 3 COLUMN 15 PIC Z(11) USING WS-ID 
           REVERSE-VIDEO .
       05  LINE 3 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 4 COLUMN 1 VALUE "Seq Num: ".
       05  LINE 4 COLUMN 15 PIC X(20) USING WS-SEQ-ID 
           REVERSE-VIDEO JUSTIFIED RIGHT.
       05  LINE 4 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 5 COLUMN 1 VALUE "Amount: ".
       05  LINE 5 COLUMN 15 PIC Z(11) USING WS-AMOUNT 
           REVERSE-VIDEO .
       05  LINE 5 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 6 COLUMN 1 VALUE "Trans Type: ".
       05  LINE 6 COLUMN 15 PIC X(50) USING REC-CLASS-NAME 
           REVERSE-VIDEO.
       05  LINE 6 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 7 COLUMN 1 VALUE "Trans Date: ".
       05  LINE 7 COLUMN 15 PIC X(10) USING WS-TRANS-DATE 
           REVERSE-VIDEO JUSTIFIED RIGHT.
       05  LINE 7 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 9 COLUMN 1 VALUE "GL Account: ".
       05  LINE 9 COLUMN 15 PIC X(50) USING WS-ASSET-CLASS
           REVERSE-VIDEO.
       05  LINE 9 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.
       05  LINE 10 COLUMN 1 VALUE "Asset Class: ".
       05  LINE 10 COLUMN 15 PIC 9(2) USING REC-CLASS-VALUE
           REVERSE-VIDEO.
       05  LINE 10 COLUMN 1 PIC X(1) VALUE "*" USING WS-BORDER1.

    OCESQL
    OCESQL 01 SQ0004.
    OCESQL 02 FILLER PIC X(53) VALUE "INSERT INTO MY_TABLE VALUES ($"
    OCESQL & "1, $2)".
    OCESQL 02 FILLER PIC X(1) VALUE X"00".
    OCESQL
    OCESQL
    OCESQL 01 SQ0005.
    OCESQL 02 FILLER PIC X(56) VALUE "INSERT INTO MY_TABLE VALUES ($1, $"
    OCESQL & "2, $3, $4, $5, $6, $7)".
    OCESQL 02 FILLER PIC X(1) VALUE X"00".
    OCESQL
    * TEMPLATE CODE BECAUSE PREPROCESSOR WONT WORK
    OCESQL 01 SQ0002.
    OCESQL 02 SQL-QUERY PIC X(210) VALUE
    "UPDATE MY_TABLE SET sequence_id = $2, amount = $3, "
    & "transaction_type = $4, transaction_date = $5, "
    & "asset_class = $6, "
    & "account_class_id = $7 WHERE id = $1".

    OCESQL 02 FILLER PIC X(1) VALUE X"00".
    OCESQL 01 SQ0003.
    OCESQL 02 SQL-QUERY PIC X(350) VALUE
    "SELECT id, sequence_id, amount, transaction_type, "
    & "transaction_date, "
    & "asset_class, account_class_id FROM MY_TABLE "
    & " WHERE id = $1".
    OCESQL 02 FILLER PIC X(1) VALUE X"00".

    OCESQL 01 SQ0007.
    OCESQL 02 SQL-QUERY PIC X(350) VALUE
    "SELECT class_name, class_value from accounting_classes"
    & " WHERE class_name = $1".

    OCESQL 02 FILLER PIC X(1) VALUE X"00". 

    PROCEDURE DIVISION.
MOVE 0 TO MENU-OPTION.
MAIN-RTN.


  * Start the loop until the user selects option 3

 PERFORM UNTIL MENU-OPTION = 3
        DISPLAY "Menu:"
        DISPLAY "1. Enter data"
        DISPLAY "2. Delete data"
        DISPLAY "3. Update data"
        DISPLAY "Choose an option (1-3): "
        ACCEPT MENU-OPTION

        EVALUATE MENU-OPTION
               WHEN 1
                   PERFORM LOG-IN
                   PERFORM ENTER-DATA
               WHEN OTHER
                   DISPLAY "Invalid option. Please choose again."
           END-EVALUATE
       END-PERFORM
       STOP RUN.



    LOG-IN. 
    MOVE "ocesql@127.0.0.1:5432"   TO   DBNAME.
MOVE  "postgres"        TO   USERNAME.
MOVE  "mypass"           TO   PASSWD.

    OCESQL EXEC SQL
    OCESQL     CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBNAME
    OCESQL END-EXEC.
    OCESQL     EXEC SQL
    OCESQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBNAME
    OCESQL     END-EXEC.
    OCESQL CALL "OCESQLConnect" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE USERNAME
    OCESQL BY VALUE 30
    OCESQL BY REFERENCE PASSWD
    OCESQL BY VALUE 15
    OCESQL BY REFERENCE DBNAME
    OCESQL BY VALUE 30
    OCESQL END-CALL.

    IF  SQLSTATE NOT = ZERO PERFORM ERROR-RTN STOP RUN.












EXIT.

LOG-IN2. 
    MOVE "ocesql@127.0.0.1:5432"   TO   DBNAME.
MOVE  "postgres"        TO   USERNAME.
MOVE  "MYPASS"           TO   PASSWD.

    OCESQL EXEC SQL
    OCESQL     CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBNAME
    OCESQL* END-EXEC.
    EXIT.

    LOG-IN3. 
    MOVE "ocesql@127.0.0.1:5432"   TO   DBNAME.
MOVE  "postgres"        TO   USERNAME.
MOVE  "MYPASS"           TO   PASSWD.

    OCESQL EXEC SQL
    OCESQL     CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :DBNAME
    OCESQL* END-EXEC.
    OCESQL CALL "OCESQLConnect" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE USERNAME
    OCESQL BY VALUE 30
    OCESQL BY REFERENCE PASSWD
    OCESQL BY VALUE 15
    OCESQL BY REFERENCE DBNAME
    OCESQL BY VALUE 30
    OCESQL END-CALL.
    OCESQL CALL "OCESQLExec" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE "COMMIT" & x"00"
    OCESQL END-CALL
    IF SQLSTATE NOT = ZERO PERFORM ERROR-RTN STOP RUN. EXIT.

        EXIT.

    ENTER-DATA.

    INITIALIZE WS-RECORD(1).
INITIALIZE LOG-RECORD.

    DISPLAY Data-Entry-Screen.
ACCEPT Data-Entry-Screen.



MOVE WS-ID TO WS-KEY-ID(1).
MOVE WS-SEQ-ID TO WS-SEQ(1).
MOVE WS-AMOUNT TO WS-AMOUNT-REC(1).
MOVE WS-TRANS-TYPE TO WS-TRANS-TYPE-REC(1).
MOVE WS-TRANS-DATE TO WS-TRANS-DATE-REC(1).
MOVE WS-ASSET-CLASS TO WS-ASSET-CLASS-REC(1).
MOVE WS-CLASS-VALUE TO WS-ACCT-CLASS-ID(1).




DISPLAY "F1"
MOVE FUNCTION TRIM(REC-CLASS-NAME) TO TMP-TRIM.
MOVE SPACES TO REC-CLASS-NAME
MOVE TMP-TRIM TO REC-CLASS-NAME
DISPLAY "F2"


IF REC-CLASS-NAME = "Asset"
MOVE 1 TO REC-CLASS-VALUE
ELSE IF REC-CLASS-NAME = "Liability"
MOVE 2 TO REC-CLASS-VALUE
ELSE IF REC-CLASS-NAME = "Equity"
MOVE 3 TO REC-CLASS-VALUE
ELSE IF REC-CLASS-NAME = "Revenue"
MOVE 4 TO REC-CLASS-VALUE
ELSE If REC-CLASS-NAME = "Expense"
MOVE 5 TO REC-CLASS-VALUE
ELSE
MOVE 0 TO REC-CLASS-VALUE
END-IF.

    OPEN OUTPUT LOG-FILE.



    STRING LOG-MESSAGE DELIMITED BY SPACE
              WS-AMOUNT " " DELIMITED BY SPACE
              "AMT" DELIMITED BY SIZE
              WS-ID " " DELIMITED BY SPACE
              "PKEY " DELIMITED BY SIZE
              WS-SEQ-ID " " DELIMITED BY SPACE
              "SEQ " DELIMITED BY SIZE
              "DATE" DELIMITED BY SIZE
              WS-TRANS-DATE " " DELIMITED BY SPACE
               "TYPE" DELIMITED BY SIZE
              WS-TRANS-TYPE " " DELIMITED BY SPACE
                "class" DELIMITED BY SIZE
              WS-ASSET-CLASS " " DELIMITED BY SPACE
                "cVAL" DELIMITED BY SIZE
              REC-CLASS-VALUE " " DELIMITED BY SPACE
              INTO LOG-MESSAGE.

    WRITE LOG-RECORD FROM LOG-MESSAGE.

    MOVE REC-CLASS-NAME TO WS-CLASS-NAME.
    MOVE REC-CLASS-VALUE TO WS-CLASS-VALUE.

    CLOSE LOG-FILE.


     DISPLAY "Inserting data into MY_TABLE: "
DISPLAY "ID: " WS-ID " Seq: " WS-SEQ-ID " Amount: " WS-AMOUNT
DISPLAY "Class Name: " WS-CLASS-NAME " Date: " WS-TRANS-DATE

    OCESQL EXEC SQL
    OCESQL     SELECT class_name, class_value
    OCESQL FROM accounting_classes
    OCESQL     WHERE class_name = :REC-CLASS-NAME;
    OCESQL* END-EXEC.
    OCESQL CALL "OCESQLExec" USING
    OCESQL by REFERENCE SQLCA
    OcESQL END-CALL.
    OCESQL CALL "OCESQLExec" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE "COMMIT" & x"00"
    OCESQL END-CALL.

        PERFORM VARYING IDX FROM 1 BY 1 UNTIL IDX < 2
    MOVE  WS-KEY-ID(IDX) TO WS-ID
    MOVE WS-SEQ(IDX) TO WS-SEQ-ID
    MOVE WS-AMOUNT-REC(IDX) TO WS-AMOUNT
    MOVE WS-TRANS-TYPE-REC(IDX) TO WS-TRANS-TYPE
    MOVE WS-TRANS-DATE-REC(IDX) TO WS-TRANS-DATE
    MOVE WS-ASSET-CLASS-REC(IDX) TO WS-ASSET-CLASS
    MOVE  WS-ACCT-CLASS-ID(IDX) TO WS-CLASS-VALUE

    OCESQL CALL "OCESQLStartSQL"
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 1
    OCESQL BY VALUE 11
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE WS-ID
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 2
    OCESQL BY VALUE 25
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE WS-SEQ-ID
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 3
    OCESQL BY VALUE 11
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE WS-AMOUNT
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 4
    OCESQL BY VALUE 50
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE WS-TRANS-TYPE
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 5
    OCESQL BY VALUE 10
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE WS-TRANS-DATE
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 6
    OCESQL BY VALUE 50
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE REC-CLASS-NAME
    OCESQL END-CALL
    OCESQL CALL "OCESQLSetSQLParams" USING
    OCESQL BY VALUE 7
    OCESQL BY VALUE 02
    OCESQL BY VALUE 0
    OCESQL BY REFERENCE REC-CLASS-VALUE
    OCESQL END-CALL
    OCESQL CALL "OCESQLExecParams" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE SQ0005
    OCESQL BY VALUE 7
    OCESQL END-CALL
    OCESQL CALL "OCESQLEndSQL"
    OCESQL END-CALL
    IF SQLSTATE NOT = ZERO
    PERFORM ERROR-RTN
    EXIT PERFORM
    END-IF
    END-PERFORM.

    OCESQL* EXEC SQL COMMIT WORK END-EXEC.
    OCESQL CALL "OCESQLStartSQL"
    OCESQL END-CALL
    OCESQL CALL "OCESQLExec" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL BY REFERENCE "COMMIT" & x"00"
    OCESQL END-CALL
    OCESQL CALL "OCESQLEndSQL"
    OCESQL END-CALL.

      *    DISCONNECT

    OCESQL EXEC SQL
    OCESQL     DISCONNECT ALL
    OCESQL* END-EXEC.
    OCESQL CALL "OCESQLDisconnect" USING
    OCESQL BY REFERENCE SQLCA
    OCESQL END-CALL.

      *    END
       DISPLAY "*** INSERTTBL FINISHED ***".
       STOP RUN.
     

  • Chuck Haatvedt

    Chuck Haatvedt - 2024-11-30

    J McNamara,

    are you using this for personal usage or in a commercial environment ?

    if personal use, you may consider DB2 Community Edition

    https://www.ibm.com/products/db2-database

    it includes a cobol precompiler which works well. lt also comes with a sample database and a number of sample cobol programs.

         Chuck Haatvedt
     

    • J McNamara

      J McNamara - 2024-11-30

      hey Chuck-

      THANKS I am going to switch.

      I just happen to have a db2/apache derby very old manual at home.

      It is a 2006 manual and details cloudscape and the differences.

      I will start reading and install the next couple of days.

      thanks so much for letting me know,
      jim

      Mod edit: remove reply-to

       

      Last edit: Brian Tiffin 2024-12-01
      alternate

      • Anonymous

        Anonymous - 2024-11-30

        Hello there is a link to the dd2 12.1 documentation for db2 for Linux and
        windows I'll send you that tomorrow it's kind of late right now also there
        are some requirements to compile and bind your programs in db2 I did some
        testing about 2 years ago and at that time the only way it would work is
        with a static Link at it rather than a dynamic call so I have some JCL for
        that actually just some windows batch scripts all of my work was done in
        Windows and it works with 64-bit compiler or the 32-bit compiler either one
        I'll send you more information tomorrow if you want it just send me an
        email and I will respond with that information

        If there are grammar errors in this email it's because I dictated it with
        my phone

        Chuck Haatvedt

        Mod edit: remove reply-to

         

        Last edit: Brian Tiffin 2024-12-01
        alternate

        • J McNamara

          J McNamara - 2024-11-30

          thanks Chuck.

          Please forward it. I am ready to get busy. It sounds like very cool info.
          I'm just sitting down to etack the project.

          I am on GMT-5 time.

          You must be in Asia/East.

          have cool day,
          J. McNamara

          Mod edit: remove reply-to

           

          Last edit: Brian Tiffin 2024-12-01
          alternate

    • J McNamara

      J McNamara - 2024-11-30

      HI ,

      HOW IN THE WORLD DO YOU GET A NICE PRINTOUT OF SCHEMA FOR DB2?

      IT IS VERY CHALLENGING.

      i TRIED ABOUT SEVERAL WAYS AND NO JOY.

      IT WONT COME OUT NICELY FORMATTED.

      THANKS FOR ANY ASSISTANCE,
      JIM

      Mod edit: remove reply-to

       

      Last edit: Brian Tiffin 2024-12-01
      alternate

      • J McNamara

        J McNamara - 2024-11-30

        C:\Users\stealth_droid>DB2 "SELECT COLUMN_NAME, DATA_TYPE FROM SYSIBM.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND TABLE_SCHEMA = 'STEALTH_DROID' ORDER BY COLUMN_NAME";

        COLUMN_NAME DATA_TYPE

        AMOUNT DECIMAL
        ASSET_CLASS CHARACTER VARYING
        CLASS_NAME CHARACTER VARYING
        CLASS_VALUE INTEGER
        ID INTEGER
        SEQ_ID CHARACTER VARYING TRANS_DATE DATE

        IT DIDNT LIKE THE LENGTH, DEFAULT, OR NULLS FIELDS BUT I GOT SOMETHING...

        mod edit: reply-to

         

        Last edit: Brian Tiffin 2024-12-01
        alternate

        • J McNamara

          J McNamara - 2024-11-30

          C:\Users\stealth_droid>DB2 insert into MY_TABLE (SEQ_ID, AMOUNT, CLASS_NAME, TRANS_DATE, ASSET_CLASS, CLASS_VALUE) VALUES ('55A', 15.25, 'EXPENSE', DATE '2024-11-29', 'SALES', 5);
          DB21034E The command was processed as an SQL statement because it was not a
          valid Command Line Processor command. During SQL processing it returned:
          SQL0668N Operation not allowed for reason code "7" on table"STEALTH_DROID.MY_TABLE". SQLSTATE=57007

          I PUT IN ACCORDING TO THE SCHEMA BUT I DONT KNOW WHY IT PROBABLY HAS SOMETHING TO DO WITH THE GENERATOR I LEFT I LEFT IT OFF THINKING IT COULD HANDLE GENERATING IF I OMMITTED

          THANKS FOR ANY ASSISTANCE
          JIM

          Mod edit: remove reply-to

           

          Last edit: Brian Tiffin 2024-12-01
          alternate

  • Chuck Haatvedt

    Chuck Haatvedt - 2024-12-01

    I use DBeaver to browse databases, it works with multiple RDBMS.

    https://dbeaver.io/

         Chuck Haatvedt
     

Anonymous
Anonymous

Add attachments
Cancel





Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.