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

 

[bizchaplain]

Dimandja,

Further appreciation for your assistance. I found more.

An example is this (bear with me for the PLSQL syntax):
EXEC SQL
declare vcurs_1 static optccval cursor for dynamic_sql
END-EXEC

By making WS-SELECT contain a variable content, I was able to populate the statement and open the cursor as needed, such as
INITIALIZE WS-SELECT.
STRING
"SELECT "
"CMM_ENT_ID, "
"CMM_YEAR, "
"NVL(CMM_PRICE_INC, '0'), "
"NVL(CMM_REPORT_STAT, ' ') "
" FROM CM_MBR_MASTER "
"WHERE CMM_ENT_ID > TRIM(?) "
"ORDER BY CMM_ENT_ID, CMM_YEAR "
INTO WS-SELECT.

EXEC SQL
PREPARE dynamic_sql FROM :WS-SELECT
END-EXEC

EXEC SQL
OPEN vcurs_1 USING :WS-KEY-FIELD-01
END-EXEC

This gave me the flexibility to put the OPEN and FETCH statements into "IF" logic, so that I could use multiple key fields and multiple host variable combinations as necessary.

 

[Dimandja]

Nice to hear from you again, bizchaplain. I'm glad things are working out for you.

__________________________________________
Try forum1391 for lively discussions

 

[canpay1]

Please bear with me as I am a newbie to using forums but I am hoping that someone could assist me with a problem I am having. We currently use NetExpress 3.0 to develop our software and in our app we are adding a feature to allow end users the ability to import data into our database (ISAM) via ODBC connectivity. Where I am running into a problem is at the point where we perform the "exec sql execute dynamic_sql end-exec" statement. Up to this point the procedures and links to the database file (in this case an Excel file) seem to be fine and do not return error codes. We prepare an import_temp table and write out the header record, yet when we perform the execute as mentioned above the table cannot be found. The table is included in the same Excel file where we write the import_temp table so I know my ODBC connectivity is working. Any thoughts as to why this may be happening?

 

[fredericofonseca]

canpay1. please post your question on a new thread.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Truusvlugindewind]

Reading thru this subject I conclude:

- porting cobol/indexed files to a RDBMS 1:1 whitout redesign? Many projects started, very few (maybe none) ended succesfull.

- performance. RDBMS's perform well when 'well designed' and compared to other RDBMS's. Look at DB2 on MVS: performes very well.... until you look at IMS/vsam/qsam apps on that same mainframe. Wow

- my personal experience: COBOL with embedded SQL works great! You can monitor the evolution of COBOL programmers by comparing the portions of work being done by COBOL-code, to SQL. Your first programs have simple SQL's to retrieve a row based upon a key-value (sound familiar right?) and a lot of logic in COBOL. Later on you'll see more advanced database design (cardinalities, relationships, stored procedures, userview's) and more advanced SQL embedded and less and relatively simple COBOL code. For me: that's the road to Utopia.

 

[TLeaders]

TruusVlugindewind


Porting Cobol/indexed file to SQL without redesign, can be and has been done. But it is not a project for the faint-of-heart. Let me tell you a couple of stores.

Once upon a time, long ago (7-8 years) and far away (Ohio), I worked for an ERP software company that decided to move to SQL server data base. What we did was externalize all the file handing routines. The system was designed so that all Cobol programs called a file system DLL. This C++ application read a series of files the type and location of a file/table. This information would allow us to define at a file/table level whether the data was stored as an indexed file, a Betrieve table, PSQL table or a SQL Server table. Oracle was also in the original plan. The file system DLL would access the data and build a Cobol data record in memory and pass a memory pointer back to the Cobol program.
This design allow us to run the same program (same object code) in Betrieve and a few records in a data file and run the same program again in SQL Server.
The performance hit for the process was huge. I think that a 30X slower would not be out of line. We did finally get to a 5-8X slower with a lot of work.

So why do it. While it is true that a straight up conversion is slower once you have converted to SQL you can limit the records that are processed. If I have an invoice file with 20,000 records and you are writing a report listing all orders over $10,000. A normal Cobol program will read all 20,000 records. With a SQL data base you can limit the records returned with a simple where statement.
Select * from invoice_header where inv_total > 10000
If there are 100 invoices over $10,000 only 100 records will be processed. Reading 100 records in SQL is faster than reading 20,000 records using indexed files.

During the time that the SQL file system was being developed the Cobol developers continued enhance and develop new applications. From their point of view the back end data base did not matter. We added SQL specific enhancements later.

This may seem like a lot of effort to separate these processes. But we had 10 million lines of Cobol code, 18-24 modules depending on who was counting, 1800 files/tables, 38,000 sites, and 175,000 users in 17 countries. We could not afford to disrupt the code or the product.


Story 2. … then we sold the company.
I took a year off and then I wanted to work on some Cobol code from projects of mine before the ERP company. What I didn’t have was a SQL server file system. But I thought “I know how this should work, HOW HARD CAN IT BE?”. True to the old saying anyone that uses the phrase “How hard can it be?” does not understand the issue.

So I developed some code using the EXEC … END-EXEC. It worked but I did not like the way the code looked. So I started learning VB.NET and moved to Fujitsu’s Cobol.Net product. My third VB.Net application was a file handler. I liked the way that the sqldatareader worked so that is the product that I used. I wrote the code, converted 850 programs and 300 tables. The batch programs worked fine I will be converting the screen over the next 6 months to finish the process.

Things you should know.
1. Read Next logic for a multiple part key is the most difficult.
2. Key fields should be defined as NOT NULL. There is a serious performance hit if you do not do this.
3. Normalize all tables. The implicit redefines that occurs when you use multiple 01 levels in and FD can cause data corruption and other issues. Flatten these files.
4. Fix nulls in VB, don’t send them back to Cobol.
5. Forget multiple data types for moving data back and forth between Cobol and VB. Use COMP-1, COMP-2, and strings. I transferred numbers as COMP-2 then in Cobol moved the data to the actual Cobol data type.


Good luck
Tom Leaders



The Cobol code.

SET-A-UPCXX-FILE.
SET A_UPCXX_FILE_STATUS OF A_UPCXX_FILEOBJ
TO A-UPCXX-FILE-INV-READ.
SET A_UPCXX_FILE_GET_WHERE OF A_UPCXX_FILEOBJ
TO A-UPCXX-FILE-WHERE.
MOVE AA-UPCXX-FILE-KEY
TO PDA_AA_UPCXX_FILE_KEY.
SET AA_UPCXX_FILE_KEY
OF A_UPCXX_FILEOBJ TO PDA_AA_UPCXX_FILE_KEY.
MOVE AA-UPCXX-CATALOG
TO PDA_AA_UPCXX_CATALOG.
SET AA_UPCXX_CATALOG
OF A_UPCXX_FILEOBJ TO PDA_AA_UPCXX_CATALOG.

GET-A-UPCXX-FILE.
SET A-UPCXX-FILE-INV-READ
TO A_UPCXX_FILE_STATUS OF A_UPCXX_FILEOBJ.

SET PDA_AA_UPCXX_FILE_KEY
TO AA_UPCXX_FILE_KEY OF A_UPCXX_FILEOBJ.
MOVE PDA_AA_UPCXX_FILE_KEY
TO AA-UPCXX-FILE-KEY.
SET PDA_AA_UPCXX_CATALOG
TO AA_UPCXX_CATALOG OF A_UPCXX_FILEOBJ.
MOVE PDA_AA_UPCXX_CATALOG
TO AA-UPCXX-CATALOG.


ZA-UPCXX-FILE-READ.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_READ".
PERFORM GET-A-UPCXX-FILE.

ZA-UPCXX-FILE-S-EQUAL.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_START_EQUAL".
PERFORM GET-A-UPCXX-FILE.

ZA-UPCXX-FILE-S-LESS.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_START_LESS".
PERFORM GET-A-UPCXX-FILE.

ZA-UPCXX-FILE-S-NOT-LESS.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ
"A_UPCXX_FILE_START_NOT_LESS".
PERFORM GET-A-UPCXX-FILE.




Some of the VB.NET code for a direct read. I’m not going to post the READ_NEXT code because while it works I am not happy with the way is works. I hope to see other concepts so I don’t want to prejudice anyone

Public Function A_UPCXX_FILE_READ()
A_UPCXX_FILE_INV_READ = 1
sSQL = "SELECT TOP 1 " & _
"AA_UPCXX_CATALOG, " & _
"AA_UPCXX_FILE_KEY, " & _
"IDI " & _
"FROM A_UPCXX_FILE " & _
" WHERE AA_UPCXX_CATALOG = '" & SQL_AA_UPCXX_CATALOG & "'" & _
" AND AA_UPCXX_FILE_KEY = '" & SQL_AA_UPCXX_FILE_KEY & "'" & _
" "
Dim myData As SqlClient.SqlDataReader
myData = GetDataReader(sSQL)
If myData.HasRows Then
myData.Read()
If myData.IsDBNull(000) Then SQL_AA_UPCXX_CATALOG = " " Else SQL_AA_UPCXX_CATALOG = myData.GetString(000)
If myData.IsDBNull(001) Then SQL_AA_UPCXX_FILE_KEY = " " Else SQL_AA_UPCXX_FILE_KEY = myData.GetString(001)
A_UPCXX_FILE_INV_READ = 0
End If
myData.Close()
End Function