Click here to Skip to main content
Click here to Skip to main content

Connecting to MySQL or SQL-Server from COBOL using ADO.NET

, 19 Jan 2010 CC (ASA 2.5)
Rate this:
Please Sign up or sign in to vote.
How about a .NET COBOL program which uses ADO.NET to connect to either MySQL or SQL Server?

Introduction - Why?

How about a .NET COBOL program which uses ADO.NET to connect to either MySQL or SQL Server? This might seem a bit unusual - but it turns out to be really simple. What is more, it is a potentially useful thing to do; as more and more data is stored in MySQL, why not talk to it directly from COBOL? What is more, the MySQL instance does not need to reside on a Windows machine for this to work. I guess my aim is to drive home the concept that anything we can do in .NET we can do in COBOL .NET - as well as - everything we can do in traditional COBOL, we can also do in COBOL .NET.

To make this work, we need to download and install MySQL's .NET connector from the MySQL website: http://dev.mysql.com/downloads/connector/net/.

Once I downloaded and installed the connector (it installs itself and then wires up Visual Studio to use it - pretty cleaver really), I needed to make a reference to it from my project. Once that was done, I could reference MySQL via ADO.NET just the same way I could MS SQL Server.

This COBOL code gives me the MySQL connection string:

*>> <summary>
*>> This is the MySQL version of GetSQLConnectionString.
*>> </summary>
*>> <returns></returns>
method-id. "GetMySQLConnectionString" private.
   procedure division returning return-value as string..
   move "database=cobol_test;server=localhost;user id=root; pwd=pickles;" to return-value.
end method "GetMySQLConnectionString".

This a not 'nice' because I have hard coded the connection into my program. This is OK here because this is a 'sand pit'. I have almost deliberately coded some of it with bad practices - just to make learning with it easier. However, please do notice how Micro Focus COBOL .NET supports in-line documenting. Just hit *>> and Enter, and it fills in the template for you.

In COBOL .NET, we can either reference each class as a type, or add definitions in the repository paragraph of the configuration section of the current class or program. Nowadays, I generally only reference types inline. That is partly because I can work with the newer 'quoteless' syntax. In this post, I am using the older 'quoted' syntax, and will use a mix of the repository and in-line approaches. Here is the difference by example:

repository.
   class cls-sql-connection as "System.Data.SqlClient.SqlConnection"
   class cls-mysql-connection as "MySql.Data.MySqlClient.MySqlConnection"
   class cls-exception as "System.Exception".

But where a class is referred to less often using a type is just fine:

working-storage section.
    dbh type "System.Data.Common.DbConnection".

By reference, the new quoteless syntax (for general release soon!) looks like this:

working-storage section.
   dbh type System.Data.Common.DbConnection.

With these bits and bobs in place, I can do something like this:

set sqlConnection to cls-mysql-connection::"New"()

Which makes my variable sqlConnection refer to a new "MySql.Data.MySqlClient.MySqlConnection" object. As sqlConnection is actually defined in the returning of the procedure section of the method, this means my method returns a connection to MySQL. What method you ask - it is in the class in the second listing at the end of this post!

A few other things in these listings that might be of interest. The first listing is the program which I use to instantiate and use the class I wrote to connect to a database. Please note how this has a try/catch construct. If you wanted to know how to do that in COBOL .NET, now you know.

Also, in this program, I show how to run an SQL query using:

move "SELECT * FROM junk" to command::"CommandText"
set reader to command::"ExecuteReader"()

And then to loop over the record set one row at a time until no more are found:

set hasMore to reader::"Read"()
perform until hasMore = false
    set hasMore to reader::"Read"()
    invoke type "Console"::"WriteLine"("Key={0}, Value='{1}'"
        reader::"Item"(0) reader::"Item"(1))
end-perform

Where hasMore is a condition-value which is identical to bool in C# or Boolean in VB.NET. This piece of code also shows a handy short-hand way of writing out to the console using invoke type "Console"::"WriteLine".

OK - here is the listing of the test program:

$Set SourceFormat "FREE".

program-id. Program1 as "ADODN_Sandpit.Program1".

environment division.
configuration section.
repository.
    class cls-Connector as "ADODN_Sandpit.ADOConnector".

data division.
working-storage section.
    *> My little class to grab a connection to the db
    01 connector cls-Connector.
    *> Stores my connection
    01 dbh type "System.Data.Common.DbConnection".
    *> Lets me execute SQL
    01 reader type "System.Data.Common.DbDataReader".
    *> Holds the returned rows from a query
    01 command type "System.Data.Common.DbCommand".
    *> Used to check if more rows are availible
    01 hasMore condition-value.
    01 ex type "Exception".
    
procedure division.
try
   set connector to cls-Connector::"new"
   set dbh to connector::"ConnectMySQL"()
   invoke type "Console"::"WriteLine"("Connected to test DB OK!")
   *> Now we are connected - lets see if we have records in the test table
   set command to dbh::"CreateCommand"()
   move "SELECT * FROM junk" to command::"CommandText"
   set reader to command::"ExecuteReader"()
   set hasMore to reader::"Read"()
   perform until hasMore = false
        set hasMore to reader::"Read"()
        invoke type "Console"::"WriteLine"("Key={0}, Value='{1}'"
            reader::"Item"(0) reader::"Item"(1))
   end-perform
catch ex 
    invoke type "Console"::"WriteLine"("Exception: {0}" ex::"ToString"())
end-try
goback.
end program Program1.

And here is the class which encapsulates the logic for connecting to MySQL or SQL Server:

$Set SourceFormat "FREE".
*>> <summary>
*>> A development only place holder for the logic to connect to my 
*>> test database.
*>> </summary>
class-id. ADOConnector as "ADODN_Sandpit.ADOConnector".
environment division.
configuration section.
repository.
   class cls-sql-connection as "System.Data.SqlClient.SqlConnection"
   class cls-mysql-connection as "MySql.Data.MySqlClient.MySqlConnection"
   class cls-exception as "System.Exception".

static.
working-storage section.
*> Instance data would go here - but there isn't any in this example
end static.

object.
working-storage section.

*>> <summary>
*>> Uses MSSQL connector to connect to a sql server instance using ADO.net
*>> </summary>
*>> <returns>The DbConention object</returns>
method-id. "ConnectSQL" public.
local-storage section.
    01 connect string.
    01 ex cls-exception.
       
procedure division returning sqlConnection as type "System.Data.Common.DbConnection".
   set connect to self::"GetSQLConnectionString"()
   invoke type "System.Console"::"WriteLine"(connect)
   set sqlConnection to cls-sql-connection::"New"()
   move connect to sqlConnection::"ConnectionString"
   try
       invoke sqlConnection::"Open"()
   catch ex
       invoke self::"WriteLine"(ex)
       raise ex
   end-try.
   
   goback.
end method "ConnectSQL".

*>> <summary>
*>> Uses MySQL donnet connector to connect to a MySQL database.
*>> </summary>
*>> <returns>The database connection as a DbConnection object</returns>
method-id. "ConnectMySQL" public.
local-storage section.
    01 connect string.
    01 ex cls-exception.
       
procedure division returning sqlConnection as type "System.Data.Common.DbConnection".
   set connect to self::"GetMySQLConnectionString"()
   invoke type "Console"::"WriteLine"(connect)
   set sqlConnection to cls-mysql-connection::"New"()
   move connect to sqlConnection::"ConnectionString"
   try
       invoke sqlConnection::"Open"()
   catch ex
       invoke self::"WriteLine"(ex)
       raise ex 
   end-try.
   
   goback.
end method "ConnectMySQL".

*>> <summary>
*>> Returns a connect string for SQL Server, this should not be hard coded
*>> but in a development test bed like this, it is handy just to put the string
*>> in an easy place to find
*>> </summary>
*>> <returns></returns>
method-id. "GetSQLConnectionString" private.
   procedure division returning return-value as string..
   move "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;" 
        to return-value.
end method "GetSQLConnectionString". 

*>> <summary>
*>> This is the MySQL version of GetSQLConnectionString.
*>> </summary>
*>> <returns></returns>
method-id. "GetMySQLConnectionString" private.
   procedure division returning return-value as string..
   move "database=cobol_test;server=localhost;user id=root; pwd=dog;" to return-value.
end method "GetMySQLConnectionString". 

*>> <summary>
*>> This method is really just because these are debug classes.
*>> It write info out to the console. I expect it to be canned soon.
*>> </summary>
*>> <param name="msg"></param>
method-id. "WriteLine" private.
   procedure division using by value msg as object.
       invoke type "Console"::"WriteLine"(msg::"ToString"())
   goback.
end method "WriteLine".

end object.
end class ADOConnector.

Also of note in the above is the way COBOL supports inline documentation; e.g.:

*>> <summary>
*>> Returns a connect string for SQL Server, this should not be hard coded
*>> but in a development test bed like this, it is handy just to put the string
*>> in an easy place to find
*>> </summary>
*>> <returns></returns>

Which I keep forgetting to put in my code!

Conclusions

This post is all about what is possible and a few pointers to using COBOL on .NET. Is there a huge demand out there for connecting to MySQL and SQL-Server on the Windows platform? Frankly, I doubt it. However, generalizations of this approach are potentially interesting as ADO.NET can access more and more data sources.

License

This article, along with any associated source code and files, is licensed under The Creative Commons Attribution-ShareAlike 2.5 License

Share

About the Author

alex turner
Web Developer
United Kingdom United Kingdom
I am now a Software Systems Developer - Senior Principal at Micro Focus Plc. I am honoured to work in a team developing new compiler and runtime technology for Micro Focus.
 
My past includes a Ph.D. in computational quantum mechanics, software consultancy and several/various software development and architecture positions.
 
For more - see
 
blog: http://nerds-central.blogspot.com
 
twitter: http://twitter.com/alexturner

Comments and Discussions

 
QuestionWhy? PinmemberPablo Aliskevicius19-Jan-10 5:20 
AnswerRe: Why? Pinmemberalex turner19-Jan-10 5:29 
GeneralRe: Why? PinmemberPablo Aliskevicius19-Jan-10 5:40 
One of my former jobs was maintaining an application which stored its data in .DAT/.IDX file couples. One of the advantages of such an application (compared to using an RDBMS) is that your customers don't have to buy a SQL server / Oracle license (and some of them would not consider MySQL, at least back then).
 
The application had COBOL for business logic and data access, and VB6 for UI; plugging in a new UI (written using .NET) without modifying the infrastructure code would be really nice (the business logic works fine); if the BL/DA code can be compiled as .NET, debugging all layers using Visual Studio becomes possible.
 
Another approach would be revamping the UI, and using the old unmanaged DLLs under it. This approach makes COBOL.NET redundant.
 
I like COBOL: it's case insensitive, simple, and elegant. Anyone who can read English, can read COBOL: I taught a VB programmer to read COBOL in about an hour. But forcing an object-oriented paradigm into COBOL seems just wrong.
 
Best wishes,
 
Pablo.
 
"Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).

GeneralRe: Why? Pinmemberalex turner19-Jan-10 5:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 19 Jan 2010
Article Copyright 2010 by alex turner
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid