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:
*>> 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.
01 connector cls-Connector.
01 dbh type "System.Data.Common.DbConnection".
01 reader type "System.Data.Common.DbDataReader".
01 command type "System.Data.Common.DbCommand".
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!")
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.
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.:
*>> 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.