Have you set up your ADO DSN using the ADO Connection Editor?

Look at Start-->All Programs-->Micro Focus Visual COBOL-->Data Tools-->Data Connections-->ADO Connection Editor.

If you can set up an ADO DSN using the Postgresql ADO Provider and the test connect is successful in the editor then you should be able to connect in your program.

You must make sure that your managed code application is set to a target cpu of either x86 or x64 according to the bitism of the DSN and provider that you are using as anyCPU will not work.

You should then use SQL(DBMAN=ADO)

Thanks.

The data provider is not displayed in the list on the Provider tab in the ADO Connection Editor because it's not actually installed on the machine.  You copy a set of files into your project directory and add project references for any that are dll's.  One of them is npgsql.dll, the .NET data provider.  Npgsql.dll is a library with different classes you invoke to access the database.  I can't embed SQL but I can still access the database.

It might be possible to use OpenESQL with embedded SQL for this provider by either using a format 6 CONNECT statement or by opening a connection object directly and then using the EXEC ADO BIND CONNECTION statement prior to executing your EXEC SQL statements for the database.

The EXEC ADO BIND CONNECTION statement is documented here.

The following is an example of using the format 6 connect statement to connect to a mySQL database which does not have a connection DSN defined using the connection editor.

            ****************************************************************
            * Copyright (C) Micro Focus 2010-2013. All rights reserved.
            *
            * This sample code is supplied for demonstration purposes only
            * on an "as is" basis and is for use at your own risk.
            *
            ****************************************************************
              working-storage section.
            *  Include the SQL Communications Area. This includes the
            *  definitions of SQLCODE, etc
              EXEC SQL INCLUDE SQLCA END-EXEC.
              EXEC SQL BEGIN DECLARE SECTION END-EXEC
              01 connectionstring pic x(300) value spaces.
              01 namestring       pic x(35).
              01 countrycode      pic x(3).
              01 nametosearch     pic x(35).
              EXEC SQL END DECLARE SECTION END-EXEC
              procedure division.
            *  Connect to a data store.
TYPE6 *  The connectionstring host variable should be populated as
TYPE6 *  appropriate for the data source you wish to use.
TYPE6 *  For example, if using the Access data store as referenced
TYPE6 *  in the readme for this sample, uncomment the following
TYPE6 *  MOVE statement, ensuring that the path to DEMO.MDB is
TYPE6 *  correct for your environment:
TYPE6 * MOVE "Provider=Microsoft.Jet.OLEDB.4.0;Data Source"
TYPE6 * & "=""C:\\Users\\Public\\Documents\\Micro Focus\\Visual COBOL"
TYPE6 * & " 2010\\Samples\\sql\\openesql\\SampleData\\Access\\DEMO.MDB"";"
TYPE6 * & "Persist Security Info=False;User ID=admin;"
TYPE6 * & "factory=System.Data.OleDb;"
TYPE6 *  TO connectionstring
TYPE6 *  If using Microsoft SQL Server, then :
TYPE6 *  1. If using Windows authentication, uncomment the
TYPE6 *  following MOVE statement, updating the :
TYPE6 *
TYPE6 *  Data Source parameter to be the name of your SQL Server
TYPE6 *  instance.
TYPE6 *
TYPE6 *  Initial Catalog parameter to point to the database you
TYPE6 *  wish to use.
TYPE6 *  MOVE "Data Source=(local);Initial Catalog=mydatabase;"
TYPE6 *  & "Integrated Security=True;MultipleActiveResultSets=True;"
TYPE6 *  & "MultipleActiveResultSets=True;"
TYPE6 *  & "factory=System.Data.SqlClient;" TO connectionstring
TYPE6 * 2. If using SQL Server authentication, uncomment the
TYPE6 * following MOVE statement, updating the :
TYPE6 *
TYPE6 *  Data Source parameter to be the name of your SQL Server
TYPE6 *  instance.
TYPE6 *
TYPE6 *  Initial Catalog parameter to point to the database you
TYPE6 *  wish to use.
TYPE6 *
TYPE6 *  User ID and Password to be the appropriate SQL Server
TYPE6 *  connection criteria for connection.
TYPE6 *  MOVE "Data Source=(local);Initial Catalog=mydatabase;"
TYPE6 *  & "User ID=myuser;Password=mypassword;"
TYPE6 *  & "MultipleActiveResultSets=True;"
TYPE6 *  & "factory=System.Data.SqlClient;" TO connectionstring
TYPE6 *  You may wish to uncomment the following verification
TYPE6 *  that the connectionstring host variable has been set
TYPE6 *  appropriately.
TYPE6 * if connectionstring = spaces
TYPE6 *   display " "
TYPE6 *   display "In order to execute this sample, connectionstring"
TYPE6 *    "needs to be populated"
TYPE6 *   display "with the appropriate connection criteria for your"
TYPE6 *   display "database. Update the source and rebuild."
TYPE6 *   goback
TYPE6 * end-if
TYPE6 *sample MySQL ADO.NET connection string
TYPE6 *refer to MySQL documentation for complete list of connection properties and appropriate values
TYPE6 *remember to include the factory property as shown (MySql.Data.MySqlClient).
TYPE6  MOVE "server=localhost;database=world;uid=root;password=yourpsw;factory=MySql.Data.MySqlClient;" TO connectionstring
TYPE6  EXEC SQL
TYPE6      connect using :connectionstring
TYPE6  END-EXEC
              if sqlcode not = 0
                   display "Error: cannot connect "
                   display sqlcode
                   display sqlerrmc
                   goback
             end-if
           *sample query to the world database included with MySQL installation
             MOVE 'Gaza' TO nametosearch
             EXEC SQL
                   SELECT  name,  countrycode
                         INTO   :namestring, :COUNTRYCODE
                         FROM city
                      WHERE name = :nametosearch
             END-EXEC
             DISPLAY namestring countrycode
             goback.

I could not connect using a format 6 CONNECT statement.  

I tried using EXEC ADO BIND CONNECTION, but I get compiler error code "COBCH0801  Incorrect SQL statement systax near: from" on my EXEC SQL SELECT statement.  

I then tried running ESQLCONFIGW.EXE per this link to fix the COBCH0801 error:

community.microfocus.com/.../20644.program-using-openesql-gets-compile-errors-when-moving-from-net-express-to-visual-cobol.aspx

but when I run it I get "Unhandled exception .. Object reference not set to an instance of an object".  If I select "Continue" I can select "No change in default behavior" but I can't save it.

I then tried adding the directive SQL(BEHAVIOR=UNOPTIMIZED) to my project but that did not help.

When you state that the format 6 CONNECT statement did not work, what error did you receive?

When you use the bind connection the syntax should be as follows:

    exec ado bind connection to :connectobj with transaction :transobj end-exec

where connectobj and transobj must be defined as type object.

01 connectobj   object.

01 transobj       object.

transobj can be null but connectobj must be set to a valid ado.net connection object like SqlConnection, etc.

I am not sure what is going on with esqlconfigw.exe on your system.

Are you running this as administrator?

You should open up a support incident with customer care for this issue.

Thanks.

The COBCH0801 was caused by a syntax error in my SQL statement.  I fixed this and the compile error went away.

I am running esqlconfigw.exe as administrator and I'll open a support incident on the error I'm getting.

I am setting the SQL(BEHAVIOR=UNOPTIMIZED) directive in my project, in case that's needed.

The format 6 CONNECT returns error -19703, could not connect.  

My bind connection looks like this, where sql-conn and sql-trans are defined in the SQL DECLARE section as type object.  I am setting sql-conn to an instance of the Postgresql data handler's connection class and sql-trans to null:

EXEC ADO

        BIND CONNECTION TO :sql-conn WITH TRANSACTION :sql-trans

END-EXEC

I am able to connect successfully, but EXEC ADO BIND CONNECTION returns  error code -2146233080, index was outside the bounds of the array.

I can still access my database using the classes in the data handler.  I just can't do embedded SQL.

I installed the PostgresSQL database on my system and attempted to get this to work but did not have any success using embedded SQL with the ADO.NET provider that they distribute.

Their installer is incomplete and you have to manually add entries to the machine.config file in order to support provider factories which is a requirement in order to use the OpenESQL support foir ADO.NET.

The PostgresSQL docs are incorrect also as they had the incorrect version number specified in the section for factories.

I changed it to the correct version and inserted it into the machine.config and then I could get the ADO Connection Editior to work.

<DbProviderFactories>
     <add name="Npgsql Data Provider" invariant="Npgsql" support="FF"
       description=".Net Framework Data Provider for Postgresql Server"
       type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
   </DbProviderFactories>

But, alas, even when the connection is sucessful the sql statements fail.

I believe that this ado.net provider is a work in progress.

----------------
I did get the embedded sql to work by creating a dsn using the ADO Connection editor and using the System.Data.Odbc provider and pointing it to an ODBC DSN that I setup for Postgres using the 32-bit ODBC driver that was installed for it.

I was then able to connect using EXEC SQL CONNECT TO dsnname END-EXEC and all statements executed correctly.

-------------------------

I also installed the dotConnect PostgreSQL ADO.NET Provider from DevArt and that one seems to work fine.

I used:

01 dsn      pic x(300) value "Server=127.0.0.1;Port=5432;User Id=postgres;Password=mypassword;Database=postgres;factory=Devart.Data.PostgreSql;".

      procedure division.

         exec sql connect using :dsn end-exec

and the connection was successful and all statements executed successfully as well so this might be another alternative for you.

Thanks.

Thinks Chris !

I have different question. I dont know where to post a question. That's why posting here.

In Mainframe we used DSNUTILB utility to load and unload. Apart from these it is also used to maintain tablespace like RUNSTATS, REPAIR, QUIESCE etc.

In Microfocus I found a similar utility SQLUTB utility to load and unload. But I don't know if these utility supports RUNSTATS, REPAIR, QUIESCE etc. 

Kindly help to clarify on the same. Please let me know if there is any utility in Microfocus that also supports RUNSTATS, REPAIR, QUIESCE etc.

Thanks in advance,

You need to create a new thread for this question or it will get lost. If you sign into the Community site and go to the main forum page of the product for which you have a question you should see at the very top:

Ask a Question or Start a Discussion

Underneath this is a field for asking your question.
Since your question relates to mainframe compatibility I am assuming that you are not using Visual COBOL and are instead using one of our Enterprise products such as Enterprise Developer. You should therefore post your question under the correct product forum. You can get to the Enterprise Developer forum by going to the main page for Application Modernization & Connectivity and click on the link for Enterprise and then select the appropriate product.

Thanks.

I installed / added it to my VC project through Nuget, Npgsql.EntityFrameworkCore.PostgreSQL, then I added:

$ set ilusing "Npgsql".

and put in References

Npgsql.dll

This done in the program

01 NConnection type NpgsqlConnection.
01 Ncmd type NpgsqlCommand.
01 QRYString string.
01 listCamposDataBase type DataTable.

set NConnection to new NpgsqlConnection
set NConnection to new NpgsqlConnection ("Server = xxxxxxxxxxxxxxxxxxx; Port = xxxxx; User Id = xxxxx; Password = xxxxxxxxxxxx; Database = postgres;")
invoke NConnection :: Open ()

move 'INSERT INTO XXXXXXXXXX. "TABLE"' & '("COLUMN1", "COLUMN2", ......' &
'VALUES (' & xxxxxxxxxx & ',' & xxxxxxx & ',' & "'" & ........ '&') 'to QRYString

set Ncmd to new NpgsqlCommand (QRYSTRING, NConnection)
invoke Ncmd :: ExecuteNonQuery ()

move 'SELECT "columns/fields" &
'FROM xxxxxxxxx. "Table" WHERE ......' to QRYSTRING

set Ncmd to new NpgsqlCommand (QRYSTRING, NConnection)
declare dataBase as type NpgsqlDataAdapter = new NpgsqlDataAdapter (Ncmd)
declare listFieldBaseData as type DataTable = new DataTable ()
invoke dataBase :: Fill (listFieldBaseData)

move listFieldBaseData ::Rows::Count to wCountTotal

move listFieldBaseData :: Rows [0] ["COLUMN / FIELD"] to VARIABLE
....

invoke NConnection :: Close ()

I hope it helps and that I didn't get your question wrong, I used Google Translate.

Eu instalei / adicionei no meu projeto VC pelo GitHub, depois eu acrescentei:

$set ilusing "Npgsql".
e coloquei nas References
Npgsql.dll

Isso feito no programa

01 NConnection type NpgsqlConnection.
01 Ncmd type NpgsqlCommand.
01 QRYString string.
01 listaCamposDataBase type DataTable.

set NConnection to new NpgsqlConnection
set NConnection to new NpgsqlConnection("Server=xxxxxxxxxxxxxxxxxxx;Port=xxxxx;User Id=xxxxx;Password=xxxxxxxxxxxx;Database=postgres;")
invoke NConnection::Open()

move 'INSERT INTO XXXXXXXXXX."TABELA"' &
'("COLUNA1", "COLUNA2", ......' &
'VALUES (' & xxxxxxxxxx & ', ' &
xxxxxxx & ',' & "'" &
........' & ')' to QRYString
set Ncmd to new NpgsqlCommand(QRYSTRING, NConnection)
try
invoke Ncmd::ExecuteNonQuery()

move 'SELECT "colunas"
'FROM xxxxxxxxx."tabela" WHERE ......
to QRYSTRING

try
set Ncmd to new NpgsqlCommand(QRYSTRING, NConnection)
declare dataBase as type NpgsqlDataAdapter = new NpgsqlDataAdapter(Ncmd)
declare listaCamposDataBase as type DataTable = new DataTable()
invoke dataBase::Fill(listaCamposDataBase)
move listaCamposDataBase::Rows[0]["COLUNA/CAMPO"] to VARIÁVEL

invoke NConnection::Close()

Espero que ajude e que eu não tenha entendido errado sua pergunta, utilizei o Google Tradutor.