Highlighted
Absent Member.
Absent Member.
2374 views

Function to Create an ADO Connection

Let me start by saying that I'm very, very new to Visual COBOL.  I have been programming primarily in Visual Basic since version 3 (now VB 2013).  I have written many applications through the years for work, friends, and for myself.

I have downloaded the Visual COBOL v.2.2 Personal Edition from Micro Focus in attempts to learn from and to possibly help my coworker who is having a lot of trouble trying to learn Visual COBOL (purchased version). 


I would like to know how to take the following code (Visual Basic) from one of my classes in one of my projects and write it in Visual COBOL (again, personal edition):

Imports System.Data.SqlClient
Imports System
Imports System.IO
Imports System.Collections

Public Class LocalSQLDB

    Public Shared Function GetConnection() As SqlConnection
        Dim myConnStr As String = ""

        myConnStr = "Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True"

        Return New SqlConnection(myConnStr)
    End Function

End Class

I tried to add a reference, System.Data.SqlClient, to my test application but it wasn't in the list for me to select.  I use this class / function to create my connection to my database being that I open my database / tables only when I need them and then close the connection.  I do this to prevent database corruption in the case of a power outage.  Users, in the past, have corrupted my database tables due to power outages. 

If anyone can assist me with this, then I'd greatly appreciated getting past this hurdle in my learning process.


Thanks.

0 Likes
5 Replies
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Function to Create an ADO Connection

Hi

I am using Open Embedded SQL with project properties 'SQL(DBMAN=ADO) SQL(TARGETDB=MSSQLSERVER) SQL(BEHAVIOR=ANSI)'. The DB connection looks like:

move "Server=WLSQLServer;Database=TESTDB;Trusted_Connection=yes;MultipleActiveResultSets=True;factory=System.Data.SqlClient;" to myConnStr

exec SQL

 connect using :myConnStr

end-exec.

If you like I can send you a small solution with access to a single DB table.

Freundliche Grüsse

Werner Lanter

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Function to Create an ADO Connection

Thank you for your response.

I would greatly appreciate a small solution to learn by.

Where do I set these project properties you mentioned in your first statement?

Will this method still allow me to only open my SQL EXPRESS database when I need it?  I only need it open when I do SELECTs and UPDATEs.  As stated in my original post, I do this to prevent corruption in my database.

As one programmer to another, we all need a little nudge when we feel that our backs are to the wall so to say.

Again, thank you for your time.

James

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Function to Create an ADO Connection

The recommended approach to access databases in Visual COBOL .NET is to use OpenESQL as Werner points out. This allows you to embed your SQL commands directly within the COBOL code using EXEC SQL statements.

You can also code directly to the native ADO.NET classes like your VB example shows by creating a connection object, calling its Open method, etc.

Your VB code above can be written in VC as follows:

      $set ilusing"System.Data.SqlClient"
      $set ilusing"System"
      $set ilusing"System.IO"
      $set ilusing"System.Collections"
       class-id adoconnect.LocalSQLDB.
       working-storage section.
       method-id GetConnection.
       procedure division returning myconnection as type SqlConnection.
           declare myConnStr as string = "Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=True"
           set myconnection to new SqlConnection(myConnStr)
           goback.
       end method.
       end class.

For more information on this subject I would recommend that you read the section under Programming-->Data Access-->Database Access in the Help and to also watch the following:

Visual COBOL in a Nutshell: Using ADO.NET with Visual COBOL
Visual COBOL in a Nutshell OpenESQL Database Connections

Also watch the other videos for OpenESQL posted at the same location.

0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Function to Create an ADO Connection

Hi James

These are part of the project properties, selection SQL.

Do use exec sql begin and commit transaction, so your database will be save. I have used SQL Server for 20 years and I never had a data loss.

Pease send me your email-adress so I can send you the test solution and an example program using EXEC ADO to fill a dataset.

Do also watch the Videos recommended by Chris Glazier.

Freundliche Grüsse

Werner Lanter

Mail: werner.lanter@lanter-edv.ch

0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

RE: Function to Create an ADO Connection

Hi

I would like to make sure, that you received my mail with the test solution for OpenESQL und ADO sent on April 8th.

Freundliche Grüsse

Werner Lanter

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.