How to access SQL Server with COBOL apps 13

[dlong1969]

I am in the process of changing a legacy software suite to a GUI front end using Visual Basic. This is a large suite that is in production. Because we have a very limited staff, we cannot commit the resources of doing a complete rewrite in VB using SQL as the data source. Right now everything is done in Microfocus COBOL using a CISAM file system as the data source.

We need to be able to do this migration in steps. To facilitate this, I am using Ratrix COBJects control to read COBOL files from VB applications, and using a transaction file/COBOL backend to update the files(corruption issues when writing with the control). The discussion was raised about using SQL Server as the database. In order to do this, I would need to be able to change existing COBOL applications to read/write to SQL. I am not having much luck finding out how to go about this. I have used embedded SQL before with a Supra database, but it doesn't appear that Microfocus is selling this any longer.

Does anyone have any ideas of what I can use to facilitate the COBOL applications connecting to a SQL database (specifically the MSDE)?

Thanks in advance for your help,

Don

 

[3gm]

I've done this by simply(?) instantiating an ADO object. You then use the normal ADO connection object and recordset objects to retrieve, manipulate, and update the database. I don't know exactly how you do that in Microfocus as my platform at the time was AcuCOBOL. But I assume Microfocus can create/manipulate ActiveX objects, so it can be handled that way.

Regards.

Glenn

 

[dlong1969]

We are using Microfocus COBOL 4.1 (I believe) residing on SCO or AIX. I don't think it has the ability to access Active X objects.... if I am incorrect, someone please instruct me how to do this.

Thanks for your answer,

Don

 

[tgoepfert]

Hi Don,

with Microfocus Cobol you really can access databases with SQL. The simplest way is to use embedded SQL. There are different solutions to do this and it depends on which Microfocus product you use. You said you believe it is Cobol 4.1. If so it must be the OCDS 4.1 - that's Object Cobol Developer Suite running on different Unix systems. With this product you have to use a specific precompiler from your database vendor, e.g. Oracle.
But if you change to newer products of Microfocus you can use the embedded feature 'Open ESQL' - that's embedded SQL in Cobol independed from a database vendor. This feature is integrated in 'Server Express' on Unix and in 'Net Express' on Windows. Links to Microfocus online documentation:
1. Server Express

http://supportline.microfocus.com/documentation/books/sx2011sp1/sx20indx.htm

2. Net Express

http://supportline.microfocus.com/documentation/books/nx31sp1/nx31indx.htm

3. Object Cobol 4.1

http://supportline.microfocus.com/documentation/books/ocds4140/oc41indx.htm

Hope this helps, Torsten

 

[SandieT]

This is interesting as we are now in the midst of converting from the flat files to SQL Server using NetExpress 3.1 in Windows. The sad part is there is NO documentation available that details what is required as far as project or environment settings to make the interface to SQL Server work. We do have pieces working but it is pretty much trial and error -- not very productive!

I would be interested to know if ANYONE has successfully implemented a NetExpress application interfacing to SQL Server.

Sandie

 

[dchristensen]

I've worked on a number of projects interfacing Net Express to a variety of databases using Open ESQL. These include SQL Server, MySQL, Interbase, Access and Oracle, plus DB2 (both AIX and mainframe based) and CA's IDMS-SQL/IDMS-R products. Once you have an ODBC datasource for the particular database you wish to hit, it's very simple to create the embedded SQL to handle your interactions.

If you have any questions, let me know.

Dave Christensen
Brokers International, Ltd.

 

[tgoepfert]

Sandie,

what are you missing? Online documentation:
Net Express Bookshelf, chapter Database Access
Examples are in the folder NetExpress\Base\DEMO\ODBCESQL
Ever tried the OpenESQL Assistant in Net Express, Tools?
Independent from this, i agree with you - it's a hard job if you never worked with a database before. It's pure SQL ...

Regards, Torsten

 

[PUMASOFT]

Being both an experienced VB and COBOL developer - just a few comments on my current experiences as team leader on this very topic:-

I have been involved in a migration of a legacy COBOL (Microfocus NetExpress) system on ISAM into SQL Server for 2 years now. It was decided that we would initially match ISAM files with SQL Tables and in the code each ISAM start/read/insert/update/delete etc would be changed to a ESQL statement(s) by use of libraries. This decision was taken as the company didnot have the resources to redevelop (this probably sounds familiar to some).

Problems I have encountered include:-

The mind set of existing ISAM only colleagues - this has been a major stumbling block - it is essential that ALL staff involved in this type of project MUST be thoroughly trained in RDMS systems and SQL Server itself.

Lack of documentation on use of SQL and COBOL (especially on use of stored procedures).

As you have to define fields to fetch into in WS, changes to SP's are restricted because we have to change each COBOL that uses it, and cobol does not support NULL as a value - you need a separate flag and check if it is set of not in code.

Dates - as SQL can only store a valid date or nulls in a date time - ISAM could have it initialised as zeroes, spaces, low-values, all 9's a whole multitude of other nasties (especially where redefined have been used) which programmers may have used to indicate no date entered etc.

SQL server does not lock the same way as ISAM - so access becomes very different in the legacy code.

You cannot rely on the FD's and data of the ISAM being a true reflection of what data is really required in the table - for example how do you convert redefines etc?, does your referential integrity work etc (we had to abandon this one!).

ISAM reads every field on a file - doing the same in SQL server is hugely inefficient - indeed the whole scenario of building cursors that mimic the Start of ISAM files and fetches has been fraught with performance issues, especially on large databases.

That with inefficient legacy 'spaghetti' code is now resulting in a near impossible maintenance regime.

still suffering the fall-out now (after 18 months of doing the initial migration), my advice would be really anaylze the data in ISAM before you even start, look at any anomolies/access etc system wide - can whole programs be redeveloped for better SQL access etc. Be prepared for the unexpected, have plenty of reaction/redevelopment time in the project time scales.

The really frustrating element of the project - we still do not have WEB access, a decent DB design which allows simple ODBC queries/reporting etc, rapid development environment. These elements now have to be developed in yet more phases (whilst still trying to troubleshoot/maintain the migrated application with the same resources).

I ask 'has the 2 years been worth it? What improvements have we made to the application that makes it more usable/saleable?'.

Answer No and None. Although the application now runs on SQL Server, it is deadly slow (in some cases over 30 times slower than on ISAM). The 2 years worth of resources spent on this project and those that still have to be spent) alone would have been much better spent redeveloping moving forward and using modern technologies.

 

[Patten]

Great comments hear PUMASOFT.
We are looking as well to migrate a RM/Cobol application using ISAM files to a web application using a database.
Based on your (and other) comments we are getting away from the idea of replacing READ/WRITE on ISAM files by the corresponding SQL statements: seems this is not the way to do it.
So we are considering redevelopping our application. Do you have an idea/estimation if there are big differences in development time considering everybody has the appropiate skills between following development configurations.
a) RM/Cobol - ISAM files
b) PHP - database - SQL
Anybody out there having experience with both? Can one state that development in PHP/database environment is easier and therefore faster? (Consider the developer knows as good Cobol as SQL as PHP).

 

[k5tm]

Patten,

What you probably don't realize is that there are several ways to do this without redeveloping your application.

1. Use some new XML-based technology from Liant to use your existing application directly on the web - i.e. use a browser as the user interface to your existing RM/COBOLapplication.

2. Use COBOL WOW if you want a more intense GUI interface.

3. Use Relativity with PHP (using the PHP ODBC library) to provide access to your existing COBOL database.

You need to call Liant sales, and perhaps attend one of Liant's monthly seminars if you are considering the expense of redeveloping your application...

Tom Morrison

http://www.liant.com

 

[amial]

OK -
SQLServer DBA here.
I'm doing research on COBOL, since I'm on the database side looking into things COBOL.
I read Pumasoft's post, and as a database person, it scared me - particularly, this bit:
>> It was decided that we would initially match ISAM files
>> with SQL Tables and in the code each ISAM
>>start/read/insert/update/delete etc would be changed to a
>>ESQL statement(s) by use of libraries
That right there is where everything went wrong, and you were probably better off not even going forward with the project from that decision.

If anyone out there is looking to move legacy file processing systems into any kind of RDBMS, the very first thing that needs to be done is get a data architect to completely go through the file layouts and business rules and create a RELATIONAL design for ALL the tables.
If you don't have the money or time for it, then just keep chugging along with what you have...
Copying the ISAM layouts is not feasible for a relational database, and will be the initial cause of all problems after that, including locking and timing as well as possible data issues.
The next thing is: SQLServer cursors are extremely inefficient. You can get away with them in Oracle, but the rule for SQLServer is to do set-based processing whereever possible (which is why you'd be working with the database in the first place...).
Things would run better if COBOL interfaced with the database through procedures, so all RI and processing can be done in the database (and dealing with NULLS on result sets). And file processing might run smoother if the entire thing (including all the business rules) was in SQLServer.

I just wanted to point these things out to maybe save someone else some pain.
Databases are a completely different world - set-based as opposed to procedural. All RDBMS's are written to work with sets.
They can work really well,
but things need to be done completely different from COBOL.

 

[mikecarus]

I run a small software house in England. We used to use microfocus.
We switched to Acucobol. We started with one system using their own file structures called Vision. Everything we do now uses embedded SQL with odbc connectivity to Microsoft SQL server or mysql. We use their own GUI designer and reporting tools. Performance is absolutely fantastic with either Vision or embedded SQL. I remember all those ISAM index corruption return codes when using Microfocus. Acucorps file structure has NEVER failed.
Fantastic development tools, incredible programmer productivity. It is absolutely brilliant. Do I sound biased, you bet I am.
My recommendation to anyone is stop wasting time. Acucorp are very very serious about cobol in the modern environment.

 

[gregsimpson]

Mike,

interested about your post re corruption with MF COBOL. I encountered the same when I used it in earnest many years ago. Was it an old version you were using or is it still a problem with Micro Focus?

Greg

 

[bizchaplain]

What I'm seeing is the closest thing to my struggles with Microfocus and embedded SQL. I too have a legacy app to convert, specifically from BTRIEVE to Oracle though that is not really important.

My specific issue is this. In the embedded SQL syntax, such as EXEC SQL ... sql text ... END-EXEC (not to be too concerned with correct syntax), how do I make the "sql text" variable? That is, how do I pass table names, column names, etc. into the embedded text?

Forgive me if I seem brash, but I cannot imagine that I'm the first person to have this problem. Assuming this problem has been encountered and resolved by some means in the past, I hope to take advantage of the ground that others have previously gone over before me.

Someone more experienced than I just told me to search out the use of the SQLDA to use something like a PREPARE statement (which, functionally if not actually, builds a temp SQL procedure) followed by an EXECUTE statement. Now all I need is correct syntax.

Thanks for any thoughts y'all may have.

Regards,
Charles+

 

[Dimandja]

bizchaplain,

If all you need is the syntax, then try:

EXEC SQL PREPARE mystmt FROM some-var END-EXEC
some-var contains a syntactically correct SQL statement.

EXEC SQL EXECUTE mystmt END-EXEC
As you have guessed mystmt is a simple variable initialized by the PREPARE statement.

Now, passing table names and all the other stuff should be as simple as this (a very brief example):

01 some-var.
   05  part-1     PIC X(12) VALUE "INSERT INTO ".
   05  table-name PIC X(20) VALUE.
   05  part-3     PIC X(20) VALUE etc...

MOVE the table name into "table-name". Allow the same for your other variable values in order to complete your SQL statement. I could write the complete code, but I am sure you get my drift; if not, let me know.

Dimandja

 

[Dimandja]

bizchaplain,

After re-reading your question, this is probably the best answer/example:

http://manuals.sybase.com/onlinebooks/group-cnarc/cng1110e/esqlcob/@Generic__BookTextView/5372

Dimandja

 

[bizchaplain]

Dimandja,

Thanks for both replies.

Of course, I knew how to populate a working-storage area to build the SQL statement. But your post confirmed that the syntax is simply PREPARE and EXECUTE, surrounded by the EXEC and END-EXEC. I didn't know it would be that simple, but it was my first experience with this.

I do intend to refer to the link you sent and to work out my specific requirements. You've already given insights, and I expect more as I check out the lead. My needs also include the ability to create dynamic cursors and dynamic arguments, which I believe to be a natural extension of what you sent already. An example I have is too literal - or too static:

EXEC SQL open vcurs using :int-col END-EXEC​

I assume that I will be stretching this to get the enclosed statement from working-storage and using PREPARE and EXECUTE instead. Cursor manipulation (open, fetch, and close) can also be dynamic, right?

Thanks again for the prompt reply and the referral.

Charles+

 

[Dimandja]

bizchaplain,

I did write "dynamic cursors and dynamic arguments" before. I don't have the source with me at this moment, but this is the actual manual I used to figure it out (Look at Chapter 10: "Dynamic SQL Operations" - everything you ask about is there):

http://h30163.www3.hp.com/NTL/view/...p=280-2bf/090015ea8009a298/TPCONTNT.pdf&toc=y

Your COBOL and OS may not be axactly as shown but the principles are the same. It takes a little patience, but once you have successfully tested a small program, everything falls into place.

If you run into a bind, feel free to ask for assistance.

Good luck.

Dimandja

 

[bizchaplain]

Dimandja,

Thanks, your kindness is appreciated.

BTW, did you ever work in Atlanta, on a Tandem system? Forgive me if your name is more common than I think, but in the mid-80s I worked with a man who had your name. He was as helpful as you, but with TAL. If that was you, then it's a small world.

Charles+

 

[Dimandja]

Yup, it's a small word!

I worked at NDC in the mid-1980s. After that, I embarked on a consulting drive which started at GM in 1984.

You can drop me a line at jUNDERSCOREdimandjaAThomailDOTcom (replace words in cap with the appropriate symbol - they are there to hopefully foil spammers.

Dimandja