Has anyone used embedded SQL with Postgresql in COBOL?

I am new to SQL.  Postgresql has a .NET data provider called Npgsql and with it I can do selects, inserts, updates and deletes from my .NET code.  We would also like to use embedded SQL calls if possible.  We have Postgresql installed on a remote computer and access it over the Web.  I found this on the forum: http://community.microfocus.com/microfocus/cobol/visual_cobol/f/18/t/8309.aspx, and tried using OpenESQL but if I select DBMAN(ADO) on the SQL tab of my project and then try to connect to my database I get error -19703 Could not make connection; when I select DBMAN(ODBC) I get error -19703 Data source name too long.  COBOL-IT has a COBOL precompiler for Postgresql so maybe that's what we need.

Tags:

  • 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.

  • Verified Answer

    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.

  • 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.