EXEC SQL statements do not appear to execute and no record returned

I have a program coded for reading a record from a MS SQL Server database Table.  When I step through the code in debug mode it lands on the EXEC SQL statement and when I step again it lands outside of the EXEC SQL/END-EXEC structure. It looks as though none of the statements inside this structure are executing.  Is this normal?  If it is, is there a way to step through this structure to observe the back end process?  I'm not returning the record from my SQL  query and need to figure out why.

My database and table are able to display in the 'Server Explorer' of Visual Studio.

The database object is not displayed in the Solution Explorer.

I'm using a ADO.NET DSN and it's connected to my MS SQL Server database successfully.

I'm using GEN-SQLCA directive in the ESQL Preprocessor-OPENESQL.

Here is my code:

IDENTIFICATION DIVISION.
PROGRAM-ID. FirstDBProg.

ENVIRONMENT DIVISION.

CONFIGURATION SECTION.

DATA DIVISION.
*
WORKING-STORAGE SECTION.
*
EXEC SQL
INCLUDE SQLCA
END-EXEC
*
COPY CustContacts.
*
PROCEDURE DIVISION.

*Test error handling
EXEC SQL
CONNECT TO ‘SQLNOEXIST’
END-EXEC
*Connect to DSN that uses Windows authentication

EXEC SQL
CONNECT TO ‘MSSQLSADONET’
END-EXEC
*Retrieve record(s)
PERFORM Execute-Select
*
EXEC SQL
DISCONNECT CURRENT
END-EXEC

STOP RUN.
*
Execute-Select.
EXEC SQL
SELECT A.CustFirstName INTO :WS-CustFirstName
FROM TblCustContacts A
WHERE (A.CustNum = 1)
END-EXEC
*
DISPLAY WS-CustFirstName.
*
OpenESQL-ERROR.
DISPLAY "Error = " SQLERRM
DISPLAY "SQLCode = " SQLCODE.
*
END PROGRAM FirstDBProg.

******************************************************

Parents
  • 0  

    Hi Kevin.

    You have an error checking paragraph in your program but I dont see any code that actually performs the check of SQLCODE after each exec sql statement. 

    If you perform this paragraph after each statement, what is the value of SQLCODE after the select statement?

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    I had to code a perform of the paragraph "OpenESQL-ERROR" to force it to execute and got the following error after the statement line EXEC SQL CONNECT TO ‘MSSQLSADONET’ END-EXEC:

    Error = ; Data source name not found and no default driver specified
    SQLCode = -0000019703

    I show the following properties of the data source in the ADO.NET Connection Editor:

    I'm not sure where to verify the driver so if you can help with that i'd appreciate it.

  • 0   in reply to 

    If you code an EXEC SQL WHENEVER statement at the top of your procedure division in your program a perform of the specified error paragraph will be done when an error occurs.

    EXEC SQL WHENEVER sqlerror PERFORM OpenESQL-ERROR END-EXEC

    It looks like your connection string is not complete. You need to fill in the data source name which is your SQL Server database name and perhaps the initial catalog field with a value such as NORTHWIND.

    You can click the test button in the ADO connection editor to make sure it works before trying it in your program.

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    I tried unsuccessfully to test the connection in the ADO connection editor.  Here is the error:

  • 0   in reply to 

    Set the Context Connection option to false.

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    After setting context connection to false, Retrying and entering my user name i get the following error:

  • 0   in reply to 

    Try turning off the MultiSubnetFailover option.

    Is your database on the same machine on which you are creating the connection or on a remote machine? If it is on a different machine then you have to make sure it allows remote connections.

    See here:

    If you open up SQL Server Management Studio is the Server name that you connect to the same as the name that you are using in the ADO connection?

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    My database is on the same machine that I’m attempting to make a connection and the MS SQL Server setting is already set to “allow remote connections to this server.”

    The Server name I connect to is the same as the name that I’m using in the ADO connection.

    After setting MultiSubnetFailover option to False and attempting to login using my userid (there is no password set in MSQL Server account), I get a different error: 

  • 0   in reply to 

    Can you please show me what the complete contents of the Connection String field is?

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    Connection string in ADO Connection Editor is:  Data Source=<device name here>;Initial Catalog=MiscCustomer;Integrated Security=True;MultipleActiveResultSets=True;Context Connection=True

Reply Children
  • 0   in reply to 

    You have the Context Connection set to true again. Please set it back to false.

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    That worked.  I now have a successful connection from the ADO.NET connection editor.  When I include that connection string in my cobol program it throws the error "Data source name not found and no default driver specified   SQLCode = -0000019703"

    Here is a extract from my cobol code for the connection:

    EXEC SQL
    CONNECT DSN DBConn
    END-EXEC

    Where DBConn is coded as:

    01 DBConn PIC X(111) VALUE "Data Source=<device name here>;"      &
    "Initial Catalog=MiscCustomer;Integrated Security"                                   &
    "=True;MultipleActiveResultSets=True".

  • 0   in reply to 

    You just created a DSN using the ADO connection editor. You should use the DSN name.

    EXEC SQL CONNECT TO "MSSQLSADONET" END-EXEC

    If you want to use a connection string directly then you need to use a format 6 connect statement as documented here:

    It would look something like:

    01  connString    string. 
        set connString    to "Data Source=DESKTOP-USJT69L;Initial Catalog=MiscCustomer;" &
    "Integrated Security
    =True;MultipleActiveResultSets=True;factory=System.Data.SqlClient;". procedure division. EXEC SQL CONNECT USING :connString END-EXEC

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    I tried both methods-using the DSN only and using the connection string in your example and got the same error ("Data source name not found and no default driver specified SQLCode = -0000019703")

  • 0   in reply to 

    Can you show me the two connect statements that you are trying?

    Are you creating the DSN as a 64-bit DSN and the project is also set to 64-bit
    It should show the bitism at the bottom of the connection editor. This can be changed using the settings icon at the top of the window.

    also what are the SQL directives that you are setting in your program?

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    method 1

    EXEC SQL CONNECT TO "MSSQLSADONET" END-EXEC

    method 2

    EXEC SQL
    CONNECT USING :connString
    END-EXEC

    Where connString is coded as:

    01 connString PIC X(142) VALUE
    "Data Source=<device name here>;Initial Catalog=MiscCustomer;"
    &
    "Integrated Security=True;MultipleActiveResultSets=True;" &
    "factory=System.Data.SqlClient;".

    Yes the DSN was created as 64 bit and the project is 64 bit.

    Here are the SQL directives:

  • 0   in reply to 

    You are not setting the DBMAN directive to ADO. Please add this directive to your SQL settings.

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    There is only one value "ODBC" available for DBMAN in the application properties:

  • Verified Answer

    +1   in reply to 

    ADO can only be used with managed .NET Framework projects. The project type that you selected is a native.COBOL project.  Native projects use ODBC connections and not ADO. Everything that we have done so far would be required if you selected .NET Framework as a project type.

    ODBC DSN's are not created with the ADO Connection Editor. Instead you use the ODBC Data Source Adminstration tool. For a 64-bit project you use the 64-bit version of this tool. For 32-bit projects you use the 32-bit version of this tool.

    Please see the documentation here:

    Chris Glazier
    Rocket Software - Principal Technical Support Specialist
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button

  • 0 in reply to   

    It's working now when using the ODBC DSN.  Thanks for working through this with me.