#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    [COBOL] Can't execute SQL query


    Hello everyone,

    I'm new on this forum and I joined because I have a question about COBOL, I'm making a school project and we have to connect to an SQL-based database (I'm using MySQL). My program can connect to the database but it doesn't want to execute the SQL query I entered, although I do exactly the same as they do on multiple websites.

    I want to insert a new row into a table called jonathan in the database called jojo. The login is working since I get SQLCODE 0 & SQLSTATE 00000.

    This is my code:

    Code:
           IDENTIFICATION DIVISION.
           PROGRAM-ID. dbConn.
           
           DATA DIVISION. WORKING-STORAGE SECTION.
           01 name PIC X(5).
           01 idcode PIC 9(2).
           01 city PIC X(200).
           EXEC SQL 
             BEGIN DECLARE SECTION 
           END-EXEC
          * SQLCODE is 0 for success, 100 for no data, -1 for failure 
           01 SQLCODE PIC S9(3).
          * SQLSTATE is a 5 character communication code; 00xxx is success. 
           01 SQLSTATE PIC X(5).    
           01 JdbcString PIC X(255).
           EXEC SQL
             END DECLARE SECTION 
           END-EXEC
           
           PROCEDURE DIVISION.
           MAIN-PARAGRAPH. 
          * Initial code 
           PERFORM DO-CONNECT
           
           DISPLAY "After connecting to the database:"
           
           DISPLAY "SQLCODE= " + SQLCODE. 
           DISPLAY "SQLSTATE= " + SQLSTATE
           
           MOVE "Jonathann" TO name
           MOVE 28 TO idcode
           MOVE "GENT" TO city
           
          * Use the database
           EXEC SQL
              INSERT INTO jonathan (name, idcode, city) 
              VALUES (:name, :idcode, :city)
           END-EXEC
           
           PERFORM DO-DISCONNECT 
           ACCEPT SQLSTATE 
          * Terminate the program 
           GOBACK
           
          * The SQL connect statement must be completed with the information 
          * appropriate to the actual JDBC driver in use. JDBC stands for 
          * Java DataBase Connectivity, and it is the method by which PERCobol 
          * accesses databases and database-like data sources. 
          * 
          * The JDBC driver itself must be included in the Java library path 
          * in order to successfully connect to the database. The JDBC driver 
          * is generally included with the database itself; see the database 
          * documentation for more details. * 
          * When connecting to a datasource, the jdbc:url may be 
          * ds:data-source-name. 
          * 
          * jdbc:url The JDBC url to the database itself
          * com.driver.name This is the classname of the driver 
          * 
           DO-CONNECT.
           
           STRING "jdbc:mysql://localhost:3306/jojo?" 
           DELIMITED BY SIZE 
           "user=root&password=jojo123" 
           DELIMITED BY SIZE 
           INTO JdbcString
           
           EXEC SQL 
              CONNECT 
              TO :JdbcString 
          *    DRIVER "com.microsoft.sqlserver.jdbc.SQLServerDriver"
               DRIVER "com.mysql.jdbc.Driver" 
           END-EXEC.
           .
          * Disconnect from the SQL database connection. This allows the 
          * JDBC driver to free any resources required for the connection.
    
           DO-DISCONNECT. 
           EXEC SQL 
              DISCONNECT 
           END-EXEC.
    Thanks in advance.

    Greetings, Jojofromjory
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2007
    Posts
    765
    Rep Power
    929
    One wonders what type of school assigns projects in COBOL of all things...

    Anyways, one thing that jumps out at me is you're not performing any error checking on the INSERT (or the DISCONNECT). The value of SQLSTATE after the insert might help debug the problem.

    Some databases connect with autocommit disabled. You may need to execute a commit statement or set autocommit=TRUE before your data will be written to the database.

    Have you tried running your insert command from whatever sql shell MySQL uses?

    I can't comment as to the correctness of the COBOL code. Hopefully someone with COBOL experience will show up, but that's a bit of a rare language.

    Comments on this post

    • jojofromjory agrees : Thanks for helping me!
    sub{*{$::{$_}}{CODE}==$_[0]&& print for(%:: )}->(\&Meh);
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by OmegaZero
    One wonders what type of school assigns projects in COBOL of all things...

    Anyways, one thing that jumps out at me is you're not performing any error checking on the INSERT (or the DISCONNECT). The value of SQLSTATE after the insert might help debug the problem.

    Some databases connect with autocommit disabled. You may need to execute a commit statement or set autocommit=TRUE before your data will be written to the database.

    Have you tried running your insert command from whatever sql shell MySQL uses?

    I can't comment as to the correctness of the COBOL code. Hopefully someone with COBOL experience will show up, but that's a bit of a rare language.
    Thanks!

    I didn't know about the committing part (I'm not really that used to SQL and only have a basic knowledge of COBOL).

    I just had to add:

    Code:
    EXEC SQL
       COMMIT
    END-EXEC
    I added this right before disconnecting from the SQL server, this way, I suppose every change made during the connection will be applied.

    Thank you very much for the quick response, this will be a great help for the college project

    Greetings, Jojofromjory

IMN logo majestic logo threadwatch logo seochat tools logo