Highlighted
Super Contributor.
Super Contributor.
626 views

using OpenESQL with an Oracle ODBC 32-bit DSN

Hi,

We are trying to use OpenESQL with an Oracle ODBC 32-bit DSN in our managed code.

Connecting to the ODBC DSN is working fine in both Visual Studio Server Explorer and ODBC Data Source Administrator (32-bit).   However, I am not able to get it working with my managed Cobol program.

What are the steps that need to be done in order  to use OpenESQL for an Oracle ODBC DSN connection?

We have been using ProCobol in native code and just started to explore managed code using Winforms. Thanks!

0 Likes
9 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

You should not use an ODBC driver when you are using a managed .NET COBOL program. ODBC is for use with native COBOL programs.

For managed .NET programs or projects you would connect using the Oracle ADO.NET Provider (ODP.NET). This is part of the Oracle ODAC downloads. You should use the managed driver if you are using a later version of VC 4.0 or higher ? If you are using an older release than you will have to use the unmanaged version.

Do not use the .NET Core version unless you are actually compiling a .NET Core COBOL application which is available starting with VC 5.0.

Find this driver here:

 

 

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

Hi,

What are the steps for setting up the ADO connection?  Is there an example?  This is new to me.

Thanks!

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

There is some documentation that covers this but just like for the ODBC driver we do not cover the various options that are available with the driver as this is up to the vendor of the particular driver to document.

Take a look here.

The ADO.NET Connection Editor is documented here

There are demo programs available in the Samples Browser under the managed category.

It is similar to setting up OpenESQL to work with ODBC in native.

1. Install the correct version of the ODP.NET driver. This is delivered in 32-bit and 64-bit packages. You should install the bitism that corresponds with the bitism of your managed code application.

2. Create and configure a DSN to point to your specific Oracle Data Provider and database. 
     Instead of the ODBC Administrator you use the ADO.NET Connection Editor which is available from the Visual COBOL Start menu or from within Visual Studio under View-->Micro Focus SQL Tools. You would catalog the connection under either 32-bit or 64-bit depending on the bitism of your application. You can catalog it under both but you have to install both the 32-bit and 64-bit versions of the ADO driver.

3. Compile your program with the correct directive.
     SQL(DBMAN=ADO, TARGETDB=ORACLE, PROCOB)
     The PROCOB directive is covered here

4.  Your program would then perform EXEC SQL CONNECT TO <dsnname> END-EXEC or one of the other formats of connect statements.

 

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

Thanks, Chris!

One last thing - how do we dynamically pass username, password, database at once  for the connection at run time?

Thanks!

Fran

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

If you do not want to embed this information into a DSN then you could format a connect string using the required parameters using a format 6 connect.

See the docs here

There is also a demo program in the Samples browser that demonstrates this.

In the left-hand pane select managed and then SQL and then in the right-hand panel select OESQL - Getting Started (VS).

You can get the formatted Connection string to use from the Connection Editor after filling in the appropriate options.

Example for unmanaged Oracle provider

01 connect-string pic x(300) value "DATA SOURCE=ORCL;USER ID=scott;PASSWORD=tiger;factory=Oracle.DataAccess.Client;".
           
       procedure division.
           
           EXEC SQL 
              CONNECT USING :connect-string
           END-EXEC 

 The factory entry is required for Open ESQL. You can find its value in the Connection Editor

provider.JPG

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

Using that connection string works great on development machine!  But when deployed to any user machine, it cannot connect.  We get an invalid sqlcode back and no message in SQLCA.  Thanks!

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

What is the invalid SQLCODE value being returned?

Do you have the Same ODP.NET driver setup on each user machine?
The connection string that I provided was just a real simple sample. It may be different for you or on your user machines.

Can you connect to Oracle from the .ADO.NET Connection Editor on the non-development machines using the Test button?

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

The sqlcode returned is -2147024809 and the sqlerrm is F. The same odp.net is being used on both machines.  Not sure where the ado.net connection editor on the user machine is.  we install cobol on a server. There are not installs on user machine. When we take out the ado connection, the application ran fine.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: using OpenESQL with an Oracle ODBC 32-bit DSN

That is not a valid SQLCODE that is being returned so something is setup incorrectly in your environment. You will not have the ADO.NET Connection Editor on the user machines as this is part of the Visual COBOL and COBOL Server products so will only be installed on the server computer on which COBOL Server is installed.

What Visual COBOL/COBOL Server product versions are you using? Are they the same?

Can you connect to your Oracle database from these user machines using SQL Developer, etc? What does the connection string look like in SQL Developer?

It may be best to create a support incident for this problem if we can not resolve it quickly.

Thanks

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.