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

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.