Cobol - Conversion of data & associated logic from ISAM to RDB

Author

Conversion of data & associated logic from ISAM to RDB

DaveM

2007-04-03, 9:55 pm

We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
2000. The concepts/examples we are s

ing however can be more
generic, i.e., not necessarily shown within COBOL source code per se.

Right now we have two realized problems, both of which appear to stem
from our mutual and still-thriving ignorance......

The first problem is as follows: We are having surprising difficulty
in our attempts to find working examples of SQL-related code sequences
for handling low-volume user-entered updates vs large-volume batch
updates. The working examples we need can be represented as pseudo-
code and/or actual code - we don't really care which - we just need
something representative to work from. (See fictitious example
enclosed)

The second problem involves how to handle record locking issues among
multiple users. The lead analyst wants us to code logic that requires
maintenance of a date-time field in every record - this date-time
stamp would then be used for determining the availability of a given
record such that unilaterally-applied changes are not given an
opportunity to sneak in while another user has said record in a state
of flux. I'll spare you the further gory details of this terrifying
scheme for now, but suffice it to say we do not like it because it
seems that we would end up re-inventing the wheel, given that the rdb
is supposed to have various locking detection/tools already built into
it.

Here is a simple/fictitious representation of the type of code
sequence samples that we are looking for...

Typical LOW-VOLUME USER UPDATE module:
1. Open rdb
2. EXEC SQL WHENEVER SQLERROR DO sql_error;
3. Accept record key from user
4. Read matching record w/ shared lock (presume REC-FOUND for this
example)
5. Display fields on screen
6. Accept field updates from user
7. Edit field updates (presume EDIT-PASSED for this example)
8. BEGIN TRANSACTION
9. Read record from table with exclusive lock
10. Move new field values to table
11. Rewrite table record
12. COMMIT
13. END TRANSACTION
14. Close rdb

Typical HIGH-VOLUME BATCH UPDATE module:
1. Open rdb
2. ~?~?~
3. ~?~?~
4. ~?~?~

Perhaps there is a site somewhere that includes sql-related coding
examples? I appreciate in advance any input that anyone may have
about how we should be approaching this data conversion effort.

Sincerely,
Dave Miner

2007-04-03, 9:55 pm

In article <1175626528.510733.311750@n59g2000hsh.googlegroups.com>,
DaveM <renfrew76@xemaps.com> wrote:
>We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
>2000. The concepts/examples we are s

ing however can be more
>generic, i.e., not necessarily shown within COBOL source code per se.
>
>Right now we have two realized problems, both of which appear to stem
>from our mutual and still-thriving ignorance......
>
>The first problem is as follows: We are having surprising difficulty
>in our attempts to find working examples of SQL-related code sequences
>for handling low-volume user-entered updates vs large-volume batch
>updates.

That's interesting... where have you looked for these, so that others here
might not duplicate your efforts?

>The working examples we need can be represented as pseudo-
>code and/or actual code - we don't really care which - we just need
>something representative to work from. (See fictitious example
>enclosed)

I'll do that.

>
>The second problem involves how to handle record locking issues among
>multiple users. The lead analyst wants us to code logic that requires
>maintenance of a date-time field in every record - this date-time
>stamp would then be used for determining the availability of a given
>record such that unilaterally-applied changes are not given an
>opportunity to sneak in while another user has said record in a state
>of flux.

How interesting... this is a time-honored technique that I first heard
described by someone who worked on one of the original airline reservation
systems.

Things have changed a bit since then... your lead analyst seems to want to
apply techniques for indexed files to a database; this has, in my
experience, usually resulted in disappointment for the system's users and
those who maintain the code.

>I'll spare you the further gory details of this terrifying
>scheme for now, but suffice it to say we do not like it because it
>seems that we would end up re-inventing the wheel, given that the rdb
>is supposed to have various locking detection/tools already built into
>it.

I am not sure about Microsoft SQL Server 2000 but I know that Oracle has
some pretty good internals to avoid deadlocks; I suggest that someone dig
into the appropriate manual and present the necessary pages to the lead
analyst.

>
>Here is a simple/fictitious representation of the type of code
>sequence samples that we are looking for...
>
>Typical LOW-VOLUME USER UPDATE module:
>1. Open rdb
>2. EXEC SQL WHENEVER SQLERROR DO sql_error;
>3. Accept record key from user
>4. Read matching record w/ shared lock (presume REC-FOUND for this
>example)
>5. Display fields on screen
>6. Accept field updates from user
>7. Edit field updates (presume EDIT-PASSED for this example)
>8. BEGIN TRANSACTION
>9. Read record from table with exclusive lock
>10. Move new field values to table
>11. Rewrite table record
>12. COMMIT
>13. END TRANSACTION
>14. Close rdb

Hmmmmm... is there anyone on this particular job who knows the difference
between a 'record' and a 'row'?

>
>
>Typical HIGH-VOLUME BATCH UPDATE module:
>1. Open rdb
>2. ~?~?~
>3. ~?~?~
>4. ~?~?~

2. Do a bunch of stuff.
3. Close rdb
4. Get promoted before this comes back to fasten its teeth in one's
gluteals.

>
>
>Perhaps there is a site somewhere that includes sql-related coding
>examples? I appreciate in advance any input that anyone may have
>about how we should be approaching this data conversion effort.

My suggestion is that you find people who know what they are doing and pay
them a lot of money to do it while you train the on-site staff to deal
with the new technology.

DD

Pete Dashwood

2007-04-04, 3:55 am

"DaveM" <renfrew76@xemaps.com> wrote in message
news:1175626528.510733.311750@n59g2000hsh.googlegroups.com...
> We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
> 2000. The concepts/examples we are s

later we were writing applications, which had to be Assembled and
run on a mainframe because there was no hardware available yet :-)). There's
nothing wrong with date/timestamping rows on an RDB, even today, but not for
the reasons your Lead Analyst wants to do so. Nowadays it serves as an audit
trail, rather than a locking or rollback/recovery device.

You are absolutely correct in that your DBMS (SQL Server) can manage its own
transaction isolation, rollback and recovery. Perhaps the Lead Analyst needs
to do a quick course on modern Database Management?

>
> Here is a simple/fictitious representation of the type of code
> sequence samples that we are looking for...
>
> Typical LOW-VOLUME USER UPDATE module:
> 1. Open rdb

That would be a CONNECT...

> 2. EXEC SQL WHENEVER SQLERROR DO sql_error;
> 3. Accept record key from user
> 4. Read matching record w/ shared lock (presume REC-FOUND for this
> example)

Despite the ISAM terminology, you are simply requesting a row. Don't worry
too much about the locking; the advantage of a DBMS is that it takes much of
this concern off you, and, at this point it doesn't matter anyway.

> 5. Display fields on screen
> 6. Accept field updates from user
> 7. Edit field updates (presume EDIT-PASSED for this example)
> 8. BEGIN TRANSACTION (ONLY if it is a distributed transaction and will use
> MS Transact-SQL to access distributed servers)
> 9. Read record from table with exclusive lock

Get a row, with update intent.

> 10. Move new field values to table
> 11. Rewrite table record

That would be an UPDATE... :-)

> 12. COMMIT
> 13. END TRANSACTION (ONLY if it is a distributed transaction and will use
> MS Transact-SQL to access distributed servers)
> 14. Close rdb

No, we don't close the database; other people are using it... :-) Instead we
might DISCONNECT from it.
>
>
> Typical HIGH-VOLUME BATCH UPDATE module:
> 1. Open rdb
> 2. ~?~?~
> 3. ~?~?~
> 4. ~?~?~
>

In fact, you could use almost exactly the same algorithm you did above for
the transaction bit, but without the connection and disconnection, so....

1. establish a connection.
2. start a transaction. (ONLY if it is a distributed transaction and will
use MS Transact-SQL to access distributed servers)
3. read your batch input and get a key and data for the update.
These must be loaded into Host Variables in your WORKING-STORAGE SECTION
(See DECLARE in your SQL manual)
Set a count somewhere of the records you have read. You will use this to
issue a COMMIT after say, 500 updates.

4. issue an UPDATE something like this:

(Sample of COBOL with embedded SQL...)

PROCEDURE DIVISION.
....
EXEC SQL
UPDATE ourTable
SET dataColumn1 = :field-1 (from the batch record. Note that Host
Variables start with a special character which can
vary across
environments. Try using a colon...)
dataColumn2 = :field-2
dataColumn3 = :field-3
dataColumnN = :field-N
...
WHERE ourTableKey = :Batch-record-key

END-EXEC

if function REM (input-rec-count 500) = zero
EXEC SQL
COMMIT WORK (OR COMMIT TRANSACTION if you are running
distributed servers using Transact-SQL)
END-EXEC
(Start a new transaction at this point if you are accessing
distributed servers. If you are not, SQL Server automatically
assigns a transaction where one is needed and you don't need to worry about
it.)
end-if

....

5. When you have hit EOF on your batch file, do the final COMMIT and then
DISCONNECT.

>
> Perhaps there is a site somewhere that includes sql-related coding
> examples?

Most people are a bit cagey about publicising their code :-)

Try the following:

http://developer.mimer.com/interfaces/interface_5.htm
http://www.pdc.kth.se/doc/SP/manual...2a0/db2a002.htm

> I appreciate in advance any input that anyone may have
> about how we should be approaching this data conversion effort.

Now THAT's an entirely different matter... If you are looking for free
strategic advice, what you get is probably worth the price...:-)

Having done a number of successful conversions from COBOL file system to
RDBMS, built tools to automatically analyse COBOL File Definitions and
generate RDB equivalents in third normal form with all repeating groups,
foreign keys, constraints and indexes carried over, and having worked with
RDB since the second IBM course on them in Reading, England, in 1983, I have
a nodding acquaintance with the problems you are facing.

(However, I have never loaded 60 million records to a database so I must
state that caveat up front...)

If you were to ask for my advice (and pay for it) I can tell you that I
could not, in all conscience, recommend what you are doing.

Given the stated environment (SQL Server) you are utilising a tiny fraction
of what is available to you. It's a bit like buying a Ferrari and never
getting out of first gear...

Embedded SQL is not the way to go.

Have a look at ADO.Net (this is not the same as ADO; it is light years
ahead). You would do much better embedding ADO.Net calls against your SQL
Server DB. This allows data and table adapters, automatic binding to
datasources, processing result sets with a single command, and manipulating
SETS of data rather than a row at a time.It is also makes MUCH less
connection demand on the server, so overall throughput is improved.)

Here's an overview:
http://www.developer.com/net/vb/art...10926_1540311_1

(Ironically, because it can employ Reader objects, this approach MAY use
OPEN, BEGIN... and CLOSE... :-))

I have not used it from COBOL (I use C#), but I see no reason why it
couldn't be called from COBOL as a normal COM server. I must have a go at
this when I get some time...:-)

However, given the realities of your current situation, embedded SQL is
probably what will happen.

If you need help with it, post here.

Good luck.

Pete.

DaveM

2007-04-04, 6:55 pm

Pete,

Your response was VERY helpful, and I am grateful to you for taking
the time and patience to put it together. We will check out the sites
you recommend, and I will pass along all of your suggestions and
examples to my team as well as management.

Also, please forgive me if I misspoke in my final para, as I would
never presume the right to ask anyone for the favor of writing me up
an entire detailed game plan on how we should carry out the whole
conversion project. The kind of information that I meant to ask for
is what you have indeed since given me, and I thank you!

Dave Miner

DaveM

2007-04-04, 6:55 pm

On Apr 3, 9:13 pm, docdw...@panix.com () wrote:
> In article <1175626528.510733.311...@n59g2000hsh.googlegroups.com>,
>
> DaveM <renfre...@xemaps.com> wrote:
>
>
>
> That's interesting... where have you looked for these, so that
> others here might not duplicate your efforts?
>

If I'd kept a log of every single place I've been to while s

ing
this information and then posted it here, as you seem to be kindly
requesting now, I am afraid that my inquiry would have become entirely
too enormous for most folks to bother with. Lets just say that I have
looked everywhere, with the obvious exception of those places where
the answers I am looking for are actually being kept.

The gist of what I am currently s

ing should exist mainly within the
minds of many of those who frequent this newsgroup, hence my inquiry.
Forgive me for not making it clearer, but I am not asking anyone to go
out on a google tour on my behalf; I am only asking for information
relevant to people's own real-world experiences such that we might
gain a better perspective about the options we are faced with.

>
> I'll do that.
>
>
>
>
> How interesting... this is a time-honored technique that I first
> heard described by someone who worked on one of the original
> airline reservation systems.
>
> Things have changed a bit since then... your lead analyst seems
> to want to apply techniques for indexed files to a database;
> this has, in my experience, usually resulted in disappointment
> for the system's users and those who maintain the code.

The handling of record (or I guess I should now say ROW) locking
conflicts, esp among multiple users, are supposed to be handled
primarily by functions that are internal to the rdb itself.
Regardless of how time-honored a given technique may be, it seems to
me that by going to the trouble of coding our own locking handler we
will only end up sidestepping what the rdb is designed to take care of
for us, and all in exchange for a manual (and inferior) version of
that capability.

This is rather like harnessing a team of mules up to a tractor to plow
the field. Sure, its possible, but we'd be foolishly wasting the very
reasons that we'd paid extra money to buy the damn tractor in the
first place.

>
> I am not sure about Microsoft SQL Server 2000 but I know that
> Oracle has some pretty good internals to avoid deadlocks; I
> suggest that someone dig into the appropriate manual and
> present the necessary pages to the lead analyst.
>

Deadlocks per se are not of particular concern to us, as these are
supposed to be automatically handled by the rdb. What we are trying
to learn about are the protocols of dealing with wait-locks, time-
outs, and prevention of unilateral changes etc.

>
>
>
>
>
>
> Hmmmmm... is there anyone on this particular job who knows
> the difference between a 'record' and a 'row'?

Yes, we do. Pseudo-code, at least within our organization, is written
for the purpose of communicating ideas and logic flow. Given that you
figured out that my usage of 'record' should have instead been
expressed as 'row', then this pseudo-code has apparently accomplished
its purpose. :)

In any case, point taken.

>
>
> 2. Do a bunch of stuff.
> 3. Close rdb
> 4. Get promoted before this comes back to fasten its teeth
> in one's gluteals.
>
>
>
>
> My suggestion is that you find people who know what they are
> doing and pay them a lot of money to do it while you train the
> on-site staff to deal with the new technology.
>
> DD

Your final suggestion is excellent but I am afraid it is not
practicable because I do not control any of the purse strings. We
have no choice but to work with this analyst, along with a rather
tight-fisted management team who is unwilling to spring for the cost
of formal training. Complaints I have aplenty, of course, but that
won't resolve anything. The only realistic and proactive plan we have
at this point is to continue doing what we are doing, namely, to
research usenet groups and knowledge bases, to ask questions, read
manuals, ask more questions, setup & run tests, analyze results,
borrow or buy additional manuals, and humbly beg somebody in the real
world to lead us to some working examples.

Thank you for your help.

Dave Miner

tleaders@gmail.com

2007-04-04, 6:55 pm

On Apr 4, 3:00 pm, "DaveM" <renfre...@xemaps.com> wrote:
> On Apr 3, 9:13 pm, docdw...@panix.com () wrote:
>
>
>
>
>
>
>
>
>
>
> If I'd kept a log of every single place I've been to while s

ing should exist mainly within the
> minds of many of those who frequent this newsgroup, hence my inquiry.
> Forgive me for not making it clearer, but I am not asking anyone to go
> out on a google tour on my behalf; I am only asking for information
> relevant to people's own real-world experiences such that we might
> gain a better perspective about the options we are faced with.
>
>
>
>
>
>
>
>
>
>
> The handling of record (or I guess I should now say ROW) locking
> conflicts, esp among multiple users, are supposed to be handled
> primarily by functions that are internal to the rdb itself.
> Regardless of how time-honored a given technique may be, it seems to
> me that by going to the trouble of coding our own locking handler we
> will only end up sidestepping what the rdb is designed to take care of
> for us, and all in exchange for a manual (and inferior) version of
> that capability.
>
> This is rather like harnessing a team of mules up to a tractor to plow
> the field. Sure, its possible, but we'd be foolishly wasting the very
> reasons that we'd paid extra money to buy the damn tractor in the
> first place.
>
>
>
> Deadlocks per se are not of particular concern to us, as these are
> supposed to be automatically handled by the rdb. What we are trying
> to learn about are the protocols of dealing with wait-locks, time-
> outs, and prevention of unilateral changes etc.
>
>
>
>
>
>
>
>
>
>
> Yes, we do. Pseudo-code, at least within our organization, is written
> for the purpose of communicating ideas and logic flow. Given that you
> figured out that my usage of 'record' should have instead been
> expressed as 'row', then this pseudo-code has apparently accomplished
> its purpose. :)
>
> In any case, point taken.
>
>
>
>
>
>
>
>
>
>
>
>
> Your final suggestion is excellent but I am afraid it is not
> practicable because I do not control any of the purse strings. We
> have no choice but to work with this analyst, along with a rather
> tight-fisted management team who is unwilling to spring for the cost
> of formal training. Complaints I have aplenty, of course, but that
> won't resolve anything. The only realistic and proactive plan we have
> at this point is to continue doing what we are doing, namely, to
> research usenet groups and knowledge bases, to ask questions, read
> manuals, ask more questions, setup & run tests, analyze results,
> borrow or buy additional manuals, and humbly beg somebody in the real
> world to lead us to some working examples.
>
> Thank you for your help.
>
> Dave Miner- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Dave

First let me say that I have not used Net Express since I got a copy
of the beta release of 4.0 some years ago. I have been using since
Fujitsu then. So the examples below are in Cobol.Net. The 1st program
is written in Cobol.net and the second is in VB.Net. Both programs do
roughly the same thing, they dump a sql table to a text file.

Depending on what your requirements are for the High Volume Batch
Update I would recommend that you read Microsoft's book-on-line (BOL)
the sections relating to Bulk Copy, Bulk Insert, DTS, and odbc.

Good Luck
Tom

IDENTIFICATION DIVISION.
PROGRAM-ID. Program1 AS "ADO_SQL_Generator.Program1".
ENVIRONMENT DIVISION.

CONFIGURATION SECTION.
REPOSITORY.
CLASS SQLCONNECTION AS "System.Data.SqlClient.SqlConnection"
CLASS SQLDATAREADER AS "System.Data.SqlClient.SqlDataReader"
CLASS SQLCOMMAND AS "System.Data.SqlClient.SqlCommand"
CLASS SQLDATETIME2 AS "System.Data.SqlTypes.SqlDateTime"
CLASS SQLDATETIME AS "System.DateTime".

INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT SQL-OUT-FILE
ASSIGN TO "c:\sqlinsrt.txt"
ORGANIZATION IS LINE SEQUENTIAL.

DATA DIVISION.
FILE SECTION.
FD SQL-OUT-FILE
RECORD IS VARYING IN SIZE FROM 10 TO 4000
DEPENDING ON BSUB.
01 SQL-OUT-REC.
05 SOR-CHAR PIC x
OCCURS 10 TO 4000 TIMES DEPENDING ON BSUB.

WORKING-STORAGE SECTION.
01 CONNECTIONOBJ OBJECT REFERENCE SQLCONNECTION.
01 DATAREADEROBJ OBJECT REFERENCE SQLDATAREADER.
01 SQLCOMMANDOBJ OBJECT REFERENCE SQLCOMMAND.
01 DATAREADEROBJ2 OBJECT REFERENCE SQLDATAREADER.
01 SQLCOMMANDOBJ2 OBJECT REFERENCE SQLCOMMAND.
01 MY-BOOLEAN PIC 1 USAGE BIT VALUE B"1".
01 MY-BOOLEAN2 PIC 1 USAGE BIT VALUE B"0".
01 MY-STRING PIC X(455) VALUE SPACES.
* 1111111111222222222233333333334444444444
55555555556
* 1234567890123456789012345678901234567890
12345678901234567890
01 SQL-COLUMNS.
05 FILLER PIC X(41) VALUE
"select A.NAME, B.NAME, C.NAME, B.LENGTH, ".
05 FILLER PIC X(42) VALUE
"B.PREC,B.SCALE,B.ISCOMPUTED, B.ISNULLABLE ".
05 FILLER PIC X(18) VALUE
"FROM SYSOBJECTS A ".
05 FILLER PIC X(39) VALUE
"INNER JOIN SYSCOLUMNS B ON A.ID = B.ID ".
05 FILLER PIC X(43) VALUE
"INNER JOIN SYSTYPES C ON B.XTYPE = C.XTYPE ".
05 FILLER PIC X(20) VALUE
"WHERE A.XTYPE = 'U' ".
05 FILLER PIC X(24) VALUE
"AND B.AUTOVAL IS NULL ".
05 FILLER PIC X(14) VALUE
"and a.nAME = '".
05 SC-TABLE-NAME PIC X(30) VALUE spaces.
05 FILLER PIC X(29) VALUE
"' order bY A.NAME, B.COLID ".

01 STV-SUB PIC 9(4) VALUE 0.
01 SYS-TABLE-VALUES.
05 STV-OCC OCCURS 1000 TIMES.
10 STV-TABLE PIC X(20).
10 STV-COLUMN PIC X(50).
10 STV-DATA-TYPE PIC X(20).
10 STV-LENGTH PIC S9(4) USAGE COMP-5.
10 STV-PREC PIC S9(4) USAGE COMP-5.
10 STV-SCALE PIC S9(9) USAGE COMP-5.
10 STV-ISCOMP PIC S9(9) USAGE COMP-5.
10 STV-ISNULL PIC S9(9) USAGE COMP-5.

01 BUILD-NEW-SELECT PIC X(5000) VALUE SPACES.
01 BUILD-OUT-LINE PIC X(5000) VALUE SPACES.
01 CHAR-DATA PIC X(5000) VALUE SPACES.
01 ASUB PIC 9(4) VALUE 0.
01 BSUB PIC 9(4) VALUE 0.
01 XSUB PIC 9(4) VALUE 0.
01 SAVEB PIC 9(4) VALUE 0.

01 INT8-DATA USAGE BINARY-CHAR UNSIGNED.
01 INT8-FORMAT PIC ----9.
01 INT16-DATA PIC S9(4) USAGE COMP-5.
01 INT16-FORMAT PIC -----9.
01 INT32-DATA PIC S9(9) USAGE COMP-5.
01 INT32-FORMAT PIC ---------9.
01 DATE-DATA OBJECT REFERENCE SQLDATETIME.
01 DATE-DATA2 OBJECT REFERENCE SQLDATETIME2.
01 DATE-FORMAT PIC X(23).

01 DEC0-DATA PIC S9(18) USAGE COMP-5.
01 DEC0-FORMAT PIC ------------------9.
01 DEC1-DATA PIC S9(17)V9(1) USAGE COMP-5.
01 DEC1-FORMAT PIC ------------------.9.
01 DEC2-DATA PIC S9(16)V9(2) USAGE COMP-5.
01 DEC2-FORMAT PIC -----------------.99.
01 DEC3-DATA PIC S9(15)V9(3) USAGE COMP-5.
01 DEC3-FORMAT PIC ----------------.999.
01 DEC4-DATA PIC S9(14)V9(4) USAGE COMP-5.
01 DEC4-FORMAT PIC ---------------.9999.
01 DEC5-DATA PIC S9(13)V9(5) USAGE COMP-5.
01 DEC5-FORMAT PIC --------------.99999.
01 DEC6-DATA PIC S9(12)V9(6) USAGE COMP-5.
01 DEC6-FORMAT PIC -------------.999999.

01 LINE-CTR PIC 99 VALUE 1.
01 WRITE-COUNT PIC 9(8) VALUE 0.
01 WRITE-COUNTD PIC ZZ,ZZZ,ZZ9.
01 WRITE-COUNT2 PIC 9(5) VALUE 0.
01 MY-COUNT PIC 9(5) VALUE 0.
01 JUNK PIC X VALUE SPACE.
01 SERVER-NAME PIC X(30) VALUE SPACES.
01 DATABASE-NAME PIC X(30) VALUE SPACES.
PROCEDURE DIVISION.
GET-SERVER-CONNECTION.
DISPLAY "Enter Server Name or (LOCAL)".
ACCEPT SERVER-NAME FROM CONSOLE.
MOVE "SERVER=" TO BUILD-OUT-LINE
MOVE 1 TO BSUB.
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES ADD 1 TO BSUB END-
PERFORM.
MOVE SERVER-NAME TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES ADD 1 TO BSUB END-
PERFORM.
MOVE ";TRUSTED_CONNECTION=YES;DATABASE=" TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES ADD 1 TO BSUB END-
PERFORM.
DISPLAY "Enter DataBase Name ie 001".
ACCEPT DATABASE-NAME FROM CONSOLE.
MOVE DATABASE-NAME TO BUILD-OUT-LINE(BSUB:)
INVOKE SQLCONNECTION "NEW" USING BUILD-OUT-LINE RETURNING
CONNECTIONOBJ.
INVOKE CONNECTIONOBJ "Open".

DISPLAY "Enter Table Name".
ACCEPT SC-TABLE-NAME FROM CONSOLE.

MOVE SQL-COLUMNS TO MY-STRING.
INVOKE SQLCOMMAND "NEW" USING MY-STRING CONNECTIONOBJ
RETURNING
SQLCOMMANDOBJ.
INVOKE SQLCOMMANDOBJ "ExecuteReader" RETURNING DATAREADEROBJ.
MOVE B"1" TO MY-BOOLEAN.
MOVE 0 TO MY-COUNT.

MOVE 1 TO STV-SUB.
MOVE SPACES TO SYS-TABLE-VALUES.

INVOKE DATAREADEROBJ "Read" RETURNING MY-BOOLEAN.
PERFORM WITH TEST BEFORE UNTIL MY-BOOLEAN NOT = B"1"
INVOKE DATAREADEROBJ "GetString" USING 0 RETURNING STV-
TABLE(STV-SUB)
INVOKE DATAREADEROBJ "GetString" USING 1 RETURNING STV-
COLUMN(STV-SUB)
INVOKE DATAREADEROBJ "GetString" USING 2 RETURNING STV-DATA-
TYPE(STV-SUB)
INVOKE DATAREADEROBJ "GetInt16" USING 3 RETURNING STV-
LENGTH(STV-SUB)
INVOKE DATAREADEROBJ "GetInt16" USING 4 RETURNING STV-
PREC(STV-SUB)
INVOKE DATAREADEROBJ "IsDBNull" USING 5 RETURNING MY-
BOOLEAN2
MOVE 0 TO STV-SCALE(STV-SUB)
IF MY-BOOLEAN2 = B"0"
INVOKE DATAREADEROBJ "GetInt32" USING 5 RETURNING STV-
SCALE(STV-SUB)
END-IF
INVOKE DATAREADEROBJ "GetInt32" USING 6 RETURNING STV-
ISCOMP(STV-SUB)
INVOKE DATAREADEROBJ "GetInt32" USING 7 RETURNING STV-
ISNULL(STV-SUB)
ADD 1 TO STV-SUB
ADD 1 TO MY-COUNT
INVOKE DATAREADEROBJ "Read" RETURNING MY-BOOLEAN
END-PERFORM.

MOVE "Select " TO BUILD-NEW-SELECT.

MOVE 1 TO STV-SUB.
MOVE 8 TO ASUB.
BUILD-SQL-LOOP.
IF STV-COLUMN(STV-SUB) NOT = SPACES
IF STV-SUB NOT = 1
MOVE "," TO BUILD-NEW-SELECT(ASUB:)
ADD 1 TO ASUB
end-if
MOVE STV-COLUMN(STV-SUB) TO BUILD-NEW-SELECT(ASUB:)
PERFORM UNTIL BUILD-NEW-SELECT(ASUB:) = spaces
ADD 1 TO ASUB
END-PERFORM.
ADD 1 TO STV-SUB.
IF STV-SUB < 1000
GO TO BUILD-SQL-LOOP.

ADD 1 TO ASUB.
MOVE 1 TO BSUB.
MOVE "INSERT INTO" TO BUILD-OUT-LINE
MOVE SC-TABLE-NAME TO BUILD-OUT-LINE(13:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM.
MOVE "(" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB.
MOVE BUILD-NEW-SELECT(8:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM.
MOVE ") VALUES (" TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM.
MOVE BSUB TO SAVEB.
MOVE "from" TO BUILD-NEW-SELECT(ASUB:)
ADD 5 TO ASUB.
MOVE SC-TABLE-NAME TO BUILD-NEW-SELECT(ASUB:)

OPEN OUTPUT SQL-OUT-FILE.

INVOKE DATAREADEROBJ "Close".
INVOKE SQLCOMMAND "NEW" USING BUILD-NEW-SELECT CONNECTIONOBJ
RETURNING SQLCOMMANDOBJ2.
INVOKE SQLCOMMANDOBJ2 "ExecuteReader" RETURNING DATAREADEROBJ2.
MOVE B"1" TO MY-BOOLEAN.
MOVE 0 TO MY-COUNT.
INVOKE DATAREADEROBJ2 "Read" RETURNING MY-BOOLEAN.
PERFORM WITH TEST BEFORE UNTIL MY-BOOLEAN NOT = B"1"
PERFORM PROCESS-DATA-RECORD
INVOKE DATAREADEROBJ2 "Read" RETURNING MY-BOOLEAN
END-PERFORM.
IF LINE-CTR NOT = 1
MOVE 1 TO LINE-CTR
MOVE "go" TO SQL-OUT-REC
WRITE SQL-OUT-REC.
INVOKE CONNECTIONOBJ "Close".
DISPLAY "Hit Enter to Exit".
ACCEPT JUNK FROM CONSOLE.
CLOSE SQL-OUT-FILE.
EXIT PROGRAM.

PROCESS-DATA-RECORD.
MOVE 1 TO STV-SUB
MOVE SAVEB TO BSUB
MOVE SPACES TO BUILD-OUT-LINE(BSUB:)
PERFORM READ-VARIABLE-DATA
UNTIL (STV-COLUMN(STV-SUB) = SPACES).

MOVE ")" TO BUILD-OUT-LINE(BSUB:)
MOVE BUILD-OUT-LINE TO SQL-OUT-REC.
WRITE SQL-OUT-REC.
ADD 1 TO LINE-CTR.
IF LINE-CTR > 10
MOVE 1 TO line-ctr
MOVE "go" TO SQL-OUT-REC
WRITE SQL-OUT-REC.
ADD 1 TO WRITE-COUNT.
ADD 1 TO WRITE-COUNT2.
IF WRITE-COUNT2 > 199
MOVE WRITE-COUNT TO WRITE-COUNTD
DISPLAY "Write Record " WRITE-COUNTD
MOVE 0 TO WRITE-COUNT2.

READ-VARIABLE-DATA.
MOVE B"0" TO MY-BOOLEAN2.
COMPUTE ASUB = STV-SUB - 1
MOVE 1 TO XSUB.
INVOKE DATAREADEROBJ2 "IsDBNull" USING ASUB RETURNING MY-
BOOLEAN2
IF STV-COLUMN(STV-SUB) NOT = SPACES
IF STV-SUB NOT = 1
MOVE "," TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB.
IF MY-BOOLEAN2 = B"1"
MOVE "NULL" TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
ELSE
IF STV-DATA-TYPE(STV-SUB) = "char"
INVOKE DATAREADEROBJ2 "GetString" USING ASUB RETURNING CHAR-
DATA
MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB
MOVE CHAR-DATA TO BUILD-OUT-LINE(BSUB:)
IF CHAR-DATA = SPACES
ADD 1 TO BSUB
END-IF
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB
ELSE
IF STV-DATA-TYPE(STV-SUB) = "tinyint"
MOVE 0 TO INT8-DATA
INVOKE DATAREADEROBJ2 "GetByte" USING ASUB RETURNING INT8-
DATA
MOVE INT8-DATA TO INT8-FORMAT
MOVE INT8-FORMAT TO CHAR-DATA
INSPECT CHAR-DATA TALLYING XSUB FOR LEADING " "
MOVE CHAR-DATA(XSUB:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
ELSE
IF STV-DATA-TYPE(STV-SUB) = "smallint"
MOVE 0 TO INT16-DATA
INVOKE DATAREADEROBJ2 "GetInt16" USING ASUB RETURNING INT16-
DATA
MOVE INT16-DATA TO INT16-FORMAT
MOVE INT16-FORMAT TO CHAR-DATA
INSPECT CHAR-DATA TALLYING XSUB FOR LEADING " "
MOVE CHAR-DATA(XSUB:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
ELSE
IF STV-DATA-TYPE(STV-SUB) = "int"
INVOKE DATAREADEROBJ2 "GetInt32" USING ASUB RETURNING INT32-
DATA
MOVE INT32-DATA TO INT32-FORMAT
MOVE INT32-FORMAT TO CHAR-DATA
INSPECT CHAR-DATA TALLYING XSUB FOR LEADING " "
MOVE CHAR-DATA(XSUB:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
ELSE
IF STV-DATA-TYPE(STV-SUB) = "datetime"
INVOKE DATAREADEROBJ2 "GetDateTime" USING ASUB RETURNING DATE-
DATA
INVOKE DATE-DATA "ToString" RETURNING DATE-FORMAT

MOVE DATE-FORMAT TO CHAR-DATA
MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB
INSPECT CHAR-DATA TALLYING XSUB FOR LEADING " "
MOVE CHAR-DATA(XSUB:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB
ELSE
IF STV-DATA-TYPE(STV-SUB) = "decimal"
IF STV-SCALE(STV-SUB) = 0
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC0-DATA
MOVE DEC0-DATA TO DEC0-FORMAT
MOVE DEC0-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 1
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC1-DATA
MOVE DEC1-DATA TO DEC1-FORMAT
MOVE DEC1-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 2
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC2-DATA
MOVE DEC2-DATA TO DEC2-FORMAT
MOVE DEC2-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 3
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC3-DATA
MOVE DEC3-DATA TO DEC3-FORMAT
MOVE DEC3-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 4
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC4-DATA
MOVE DEC4-DATA TO DEC4-FORMAT
MOVE DEC4-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 5
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC5-DATA
MOVE DEC5-DATA TO DEC5-FORMAT
MOVE DEC5-FORMAT TO CHAR-DATA
END-IF
IF STV-SCALE(STV-SUB) = 6
INVOKE DATAREADEROBJ2 "GetDecimal" USING ASUB RETURNING
DEC6-DATA
MOVE DEC6-DATA TO DEC6-FORMAT
MOVE DEC6-FORMAT TO CHAR-DATA
END-IF
INSPECT CHAR-DATA TALLYING XSUB FOR LEADING " "
MOVE CHAR-DATA(XSUB:) TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
ELSE

MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB
MOVE "data-type-not-processed" TO BUILD-OUT-LINE(BSUB:)
PERFORM UNTIL BUILD-OUT-LINE(BSUB:) = SPACES
ADD 1 TO BSUB
END-PERFORM
MOVE "'" TO BUILD-OUT-LINE(BSUB:)
ADD 1 TO BSUB.
ADD 1 TO STV-SUB.

END PROGRAM PROGRAM1.

'= TABLE_EXPORT - This program will accept a SQL table name and export
the
'= information to a text file.

Module CreateInserts

Sub Main()
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
Dim sSql As String
Dim sSqlNew As String
Dim sSqlFrom As String
Dim sSqlInsert As String
Dim MyData As SqlClient.SqlDataReader
Dim bReadFlag As Boolean
Dim iCnt As Integer
Dim iCntMax As Integer
Dim sFileName(500) As String
Dim sFieldName(500) As String
Dim scharName(500) As String
Dim iLength(500) As Integer
Dim iPerc(500) As Integer
Dim iScale(500) As Integer
Dim iIsComputed(500) As Integer
Dim iIsNullable(500) As Integer

Console.WriteLine("Enter File Name")
Dim InputName As String = Console.ReadLine
'= The output file will be stored in "c:\labelprocess\" with
the prefix
'= EXPORT and the suffix SQL.
'=
Dim OutputName As String = "c:\labelprocess\EXPORT "
OutputName = OutputName + InputName + ".SQL"
oWrite = oFile.CreateText(OutputName)

sSql = "select A.NAME, B.NAME, C.NAME, B.LENGTH,
B.PREC,B.SCALE,B.AUTOVAL, B.ISNULLABLE " & _
" FROM SYSOBJECTS A " & _
" INNER JOIN SYSCOLUMNS B ON A.ID = B.ID " & _
" INNER JOIN SYSTYPES C ON B.XTYPE = C.XTYPE " & _
"WHERE A.XTYPE = 'U' " & _
"and a.nAME = '" + InputName + "' " & _
"and c.name <> 'sysname' " & _
"order bY A.NAME, B.COLID "

MyData = File_System.GetDataReader(sSql)
iCnt = 0
'= First the CREATE TABLE statements are written.
If MyData.HasRows Then
sSqlNew = "IF not exists (SELECT * FROM dbo.sysobjects
where id = object_id(N'[dbo].[" + InputName + "]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) "
oWrite.WriteLine(sSqlNew)
sSqlNew = " CREATE TABLE [dbo].[" + InputName + "] ( "
oWrite.WriteLine(sSqlNew)
sSqlNew = " "
bReadFlag = MyData.Read()
Do While bReadFlag

If MyData.IsDBNull(1) Then sSqlNew = " " Else sSqlNew
= sSqlNew + MyData.GetString(1) + " " 'sFieldName(iCnt) =
MyData.GetString(1)
If MyData.IsDBNull(2) Then sSqlNew = " " Else sSqlNew
= sSqlNew + MyData.GetString(2) + " " 'scharName(iCnt) =
MyData.GetString(2)
If MyData.GetString(2).ToLower = "char" _
Or MyData.GetString(2).ToLower = "varchar" _
Or MyData.GetString(2).ToLower = "nchar" _
Or MyData.GetString(2).ToLower = "nvarchar" _
Or MyData.GetString(2).ToLower = "text" _
Or MyData.GetString(2).ToLower = "ntext" Then
If MyData.IsDBNull(3) Then sSqlNew = sSqlNew +
"( )" Else sSqlNew = sSqlNew + "(" + MyData.GetInt16(3).ToString + ")"
' iLength(iCnt) = MyData.GetInt16(3)
ElseIf MyData.GetString(2).ToLower = "decimal" Or
MyData.GetString(2).ToLower = "numeric" Then
sSqlNew = sSqlNew + "("
If MyData.IsDBNull(4) Then sSqlNew = sSqlNew + "0"
Else sSqlNew = sSqlNew + MyData.GetInt16(4).ToString
sSqlNew = sSqlNew + ","
If MyData.IsDBNull(5) Then sSqlNew = sSqlNew + "0"
Else sSqlNew = sSqlNew + MyData.GetInt32(5).ToString
sSqlNew = sSqlNew + ") "
End If
If Not MyData.IsDBNull(6) Then sSqlNew = sSqlNew + "
Identity (1,1) "
If Not MyData.IsDBNull(7) Then
If MyData.GetInt32(7) = 0 Then sSqlNew = sSqlNew +
" not null"
Else
sSqlNew = sSqlNew + " null"
End If
oWrite.WriteLine(sSqlNew)
sSqlNew = ","
bReadFlag = MyData.Read()
Loop
oWrite.WriteLine(")")
oWrite.WriteLine(" ")
oWrite.WriteLine("GO")
oWrite.WriteLine(" ")
oWrite.WriteLine(" ")

Else
MsgBox("Invalid Table Name Entered - Program Ending")
oWrite.WriteLine("Invalid Table Name Entered - Program
Ending")
GoTo endsub
End If
MyData.Close()

sSql = "select A.NAME, B.NAME, C.NAME, B.LENGTH,
B.PREC,B.SCALE,B.ISCOMPUTED, B.ISNULLABLE " & _
" FROM SYSOBJECTS A " & _
" INNER JOIN SYSCOLUMNS B ON A.ID = B.ID " & _
" INNER JOIN SYSTYPES C ON B.XTYPE = C.XTYPE " & _
"WHERE A.XTYPE = 'U' AND B.AUTOVAL IS NULL " & _
"and a.nAME = '" + InputName + "' " & _
"and c.name <> 'sysname' " & _
"order bY A.NAME, B.COLID "

MyData = File_System.GetDataReader(sSql)
iCnt = 0
'= Then each data record from the table is read and the data
is used to create
'= insert statements. The statements are in the form
'= insert into <table name> (field names) values (data
values).
sSqlNew = "Select "
sSqlInsert = "insert into "
If MyData.HasRows Then
bReadFlag = MyData.Read()
Do While bReadFlag And iCnt <= 500
If MyData.IsDBNull(0) Then sFileName(iCnt) = " " Else
sFileName(iCnt) = MyData.GetString(0)
If MyData.IsDBNull(1) Then sFieldName(iCnt) = " " Else
sFieldName(iCnt) = MyData.GetString(1)
If MyData.IsDBNull(2) Then scharName(iCnt) = " " Else
scharName(iCnt) = MyData.GetString(2)
If MyData.IsDBNull(3) Then iLength(iCnt) = 0 Else
iLength(iCnt) = MyData.GetInt16(3)
If MyData.IsDBNull(4) Then iPerc(iCnt) = 0 Else
iPerc(iCnt) = MyData.GetInt16(4)
If MyData.IsDBNull(5) Then iScale(iCnt) = 0 Else
iScale(iCnt) = MyData.GetInt32(5)
If MyData.IsDBNull(6) Then iIsComputed(iCnt) = 0 Else
iIsComputed(iCnt) = MyData.GetInt32(6)
If MyData.IsDBNull(7) Then iIsNullable(iCnt) = 0 Else
iIsNullable(iCnt) = MyData.GetInt32(7)
If iCnt <> 0 Then
sSqlNew = sSqlNew + ", "
sSqlInsert = sSqlInsert + ", "
Else
sSqlInsert = "Insert Into " + sFileName(iCnt) +
"("
End If
sSqlFrom = sFileName(iCnt)
sSqlNew = sSqlNew + sFieldName(iCnt)
sSqlInsert = sSqlInsert + sFieldName(iCnt)
iCnt = iCnt + 1

bReadFlag = MyData.Read()
Loop
Else
MsgBox("Invalid Table Name Entered - Program Ending")
oWrite.WriteLine("Invalid Table Name Entered - Program
Ending")
GoTo endsub
End If
MyData.Close()

sSqlNew = sSqlNew + " from " + sSqlFrom
sSqlInsert = sSqlInsert + ") values ("

MyData = File_System.GetDataReader(sSqlNew)
iCntMax = iCnt - 1
iCnt = 0
sSqlNew = sSqlInsert

If MyData.HasRows Then
bReadFlag = MyData.Read()
Do While bReadFlag
sSqlNew = sSqlInsert
For iCnt = 0 To iCntMax
If iCnt <> 0 Then
sSqlNew = sSqlNew + ", "
End If
If MyData.IsDBNull(iCnt) Then
If iIsNullable(iCnt) = 1 Then
sSqlNew = sSqlNew + "null"
Else
If MyData.GetDataTypeName(iCnt).ToLower =
"char" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"varchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"nchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"nvarchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"text" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"ntext" Then
sSqlNew = sSqlNew + " "
Else
sSqlNew = sSqlNew + "0"
End If
End If
Else
If MyData.GetDataTypeName(iCnt).ToLower =
"char" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"varchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"nchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"nvarchar" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"text" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"ntext" Then
sSqlNew = sSqlNew + "'" +
MyData.GetString(iCnt).ToString.TrimEnd(" ")
If
MyData.GetString(iCnt).ToString.TrimEnd(" ") = "" Then
sSqlNew = sSqlNew + " '"
Else
sSqlNew = sSqlNew + "'"
End If
ElseIf MyData.GetDataTypeName(iCnt).ToLower =
"tinyint" Then
sSqlNew = sSqlNew +
MyData.GetByte(iCnt).ToString
ElseIf MyData.GetDataTypeName(iCnt).ToLower =
"smallint" Then
sSqlNew = sSqlNew +
MyData.GetInt16(iCnt).ToString
ElseIf MyData.GetDataTypeName(iCnt).ToLower =
"int" Then
sSqlNew = sSqlNew +
MyData.GetInt32(iCnt).ToString
ElseIf MyData.GetDataTypeName(iCnt).ToLower =
"datetime" Then
sSqlNew = sSqlNew + "'" +
MyData.GetDateTime(iCnt).ToString + "'"
ElseIf MyData.GetDataTypeName(iCnt).ToLower =
"decimal" _
Or MyData.GetDataTypeName(iCnt).ToLower =
"numeric" Then
sSqlNew = sSqlNew +
MyData.GetDecimal(iCnt).ToString
Else
sSqlNew = sSqlNew + " "
End If
End If
Next iCnt
sSqlNew = sSqlNew + ")"
oWrite.WriteLine(sSqlNew)
bReadFlag = MyData.Read()
Loop
End If
MyData.Close()
oWrite.WriteLine(" ")
oWrite.WriteLine("GO")
oWrite.WriteLine(" ")

sSql = "select C.TEXT from sysobjects A " & _
" INNER JOIN SYSOBJECTS B ON A.ID=B.PARENT_OBJ " & _
" INNER JOIN SYSCOMMENTS C ON B.ID=C.ID" & _
" where A.NAME = '" + InputName + "'"
MyData = File_System.GetDataReader(sSql)
If MyData.HasRows Then
bReadFlag = MyData.Read()
Do While bReadFlag
sSqlNew = " "
If MyData.IsDBNull(0) Then sSqlNew = " " Else sSqlNew
= MyData.GetString(0)
oWrite.WriteLine(sSqlNew)
oWrite.WriteLine(" ")
oWrite.WriteLine("GO")
oWrite.WriteLine(" ")
bReadFlag = MyData.Read()
Loop
End If
MyData.Close()

'= Last the SQL statements to create the index keys are
created

sSql = " select o.name,i.name, kn.name, " & _
" case " & _
" when i.indid = 1 then 'Clustered' " & _
" else ' ' " & _
" end, " & _
" k.keyno " & _
" from sysindexkeys k " & _
" inner join sysindexes I ON k.id = i.id and
k.indid=i.indid " & _
" INNER JOIN syscolumns kn ON I.id = kn.id and
k.colid=kn.colid " & _
" INNER JOIN sysobjects O ON I.id = o.id " & _
" where " & _
" o.name = '" + InputName + "' and " & _
" I.indid > 0 and I.indid < 255 and " & _
" (INDEXPROPERTY(I.id, i.name, N'IsStatistics')
<> 1) and " & _
" (INDEXPROPERTY(I.id, i.name,
N'IsAutoStatistics') <> 1) and " & _
" (INDEXPROPERTY(I.id, i.name,
N'IsHypothetical') <> 1) " & _
" and o.type = 'U' " & _
" order by i.name, k.keyno, k.colid "
MyData = File_System.GetDataReader(sSql)
Dim sLastKey As String = " "
Dim sLastFileName As String = " "
Dim sLastKeyName As String = " "
Dim sLastFieldName As String = " "
Dim sLastClustered As String = " "
If MyData.HasRows Then
bReadFlag = MyData.Read()
Do While bReadFlag
sSqlNew = " "

If MyData.IsDBNull(0) Then sLastFileName = " " Else
sLastFileName = MyData.GetString(0)
If MyData.IsDBNull(1) Then sLastKeyName = " " Else
sLastKeyName = MyData.GetString(1)
If MyData.IsDBNull(2) Then sLastFieldName = " " Else
sLastFieldName = MyData.GetString(2)
If MyData.IsDBNull(3) Then sLastClustered = " " Else
sLastClustered = MyData.GetString(3)
If sLastKey = sLastKeyName Then
sSqlNew = ",[" + sLastFieldName + "]"
oWrite.WriteLine(sSqlNew)
Else
If sLastKey <> " " Then
oWrite.WriteLine(")")
oWrite.WriteLine("GO")
oWrite.WriteLine(" ")
End If
sSqlNew = " CREATE UNIQUE " + sLastClustered + "
INDEX " + sLastKeyName + " ON " + sLastFileName
oWrite.WriteLine(sSqlNew)
oWrite.WriteLine("(")
sSqlNew = "[" + sLastFieldName + "]"
oWrite.WriteLine(sSqlNew)
End If
sLastKey = sLastKeyName
bReadFlag = MyData.Read()
Loop
oWrite.WriteLine(")")
oWrite.WriteLine("GO")
oWrite.WriteLine(" ")
End If
MyData.Close()

EndSub:
oWrite.Close()

End Sub
'=
End Module

James J. Gavan

2007-04-04, 6:55 pm

DaveM wrote:
> On Apr 3, 9:13 pm, docdw...@panix.com () wrote:
>
>
>
> If I'd kept a log of every single place I've been to while s

Pete Dashwood

2007-04-04, 9:55 pm

"DaveM" <renfrew76@xemaps.com> wrote in message
news:1175712344.282357.44480@w1g2000hsg.googlegroups.com...
> Pete,
>
> Your response was VERY helpful, and I am grateful to you for taking
> the time and patience to put it together. We will check out the sites
> you recommend, and I will pass along all of your suggestions and
> examples to my team as well as management.
>
> Also, please forgive me if I misspoke in my final para, as I would
> never presume the right to ask anyone for the favor of writing me up
> an entire detailed game plan on how we should carry out the whole
> conversion project.

Nothing to forgive... :-) I understood what you were requesting. You just
gave me an opportunity to point out that the conventional wisdom to use
embedded SQL is not necessarily the best solution. A whole raft of new
techniques for data access and manipulation are starting to filter through
to the mainstream and the next couple of years will see some major changes
in the way we access data, particularly on PC platforms. By advising the use
of ADO.Net I am ensuring you at least are on the playing field for this
stuff.

SQL Server is a good choice, although I don't know of any current RDBMS that
are actually "bad". DB2, Ingres, Oracle, and even MySQL (which is free; your
management should love that :-)) I have found to be completely adequate
systems. I have one system that uses MySQL and SQLServer together and it all
works fine. (It evolved that way... when I first started building it I
couldn't afford SQL Server :-) Later, I added some components that used SQL
Server and thought... I'll get round to "unifying" it some time... That was
four years ago... :-))

The kind of information that I meant to ask for
> is what you have indeed since given me, and I thank you!
>
> Dave Miner
>
Thanks for the acknowledgement (You'd be surprised how many people here
never even reply or let us know whether what was posted was useful...).

I imagine you have completed the conversion of your ISAM data structures to
Databases, and are now s

ing to load them. In case you haven't, I have a
tool I wrote some years back that can automate that process. (ISAM2RDB). You
feed it your ISAM COBOL definition (Source code) and it creates a Relational
Database in third normal form. It builds linked tables for repeating groups
(COBOL OCCURS) and also copes with REDEFINES in the COBOL source.

I also have a Declaration Generator tool I wrote that will analyse a table
on a RDB and generate a COBOL record definition for interfacing to it. This
is the same facility provided by DECLGEN in IBM mainframe environments. It
actually can go one step further than generating a COPY book to include in
your COBOL, by also generating a COBOL access module that can mainatain the
table (INSERT, UPDATE, SELECT *, and DELETE) so that programmers with no
knowledge of SQL can still access and update the database, but are shielded
from the details. The source code generated is COBOL with embedded SQL, and
can be useful as a learning aid while people are learning SQL.

Given that your management are not prepared to spend money on training
(which SHOULD be their top priority), it is highly unlikely they'll spend
any on tools :-), however, as I wrote these things many years ago when first
looking at converting to RDBMS, and as I am moving away from Embedded SQL
(ESQL) the value of these tools to me now is pretty low. If you are
interested, I can let you have a free demo... send me some COBOL ISAM
definitions and I'll return you an ACCESS DB generated by the tools (this is
easily exportable to SQL Server, but takes less space for mailing.)

I have some stuff going on at the moment that is occupying my time and
effort but I hope within the next month or so to have a Web site up that
will offer certain facilities as a Web Service. (Now that I've cracked
getting COBOL COM and DCOM+ components to run as a Web Service... :-)). I
guess I could put these tools up there too, and people could then use them
remotely on an "as needed" basis... or maybe I'll just take a break :-)

Pete.

2007-04-05, 6:55 pm

In article <1175713257.593859.110760@n59g2000hsh.googlegroups.com>,
DaveM <renfrew76@xemaps.com> wrote:
>On Apr 3, 9:13 pm, docdw...@panix.com () wrote:
>
>If I'd kept a log of every single place I've been to while s

ing
>this information and then posted it here, as you seem to be kindly
>requesting now, I am afraid that my inquiry would have become entirely
>too enormous for most folks to bother with. Lets just say that I have
>looked everywhere, with the obvious exception of those places where
>the answers I am looking for are actually being kept.

Let's just say that when asked for any sort of evidence of your work you
presented none, Mr Minor... there's a bit more data available in this
newsgroup to indicate that than your having 'looked everywhere'.

>
>The gist of what I am currently s

ing should exist mainly within the
>minds of many of those who frequent this newsgroup, hence my inquiry.

That may well be... you are asking a group of what have been called
'knowledge workers'.

>Forgive me for not making it clearer, but I am not asking anyone to go
>out on a google tour on my behalf; I am only asking for information
>relevant to people's own real-world experiences such that we might
>gain a better perspective about the options we are faced with.

This is called 'a consultation', Mr Minor... and a few people here are
accustomed to a phenomenon of which you may not be aware: they use their
brains on the behalf of someone's data-processing project and they get
paid for it.

[snip]

>
>
>The handling of record (or I guess I should now say ROW) locking
>conflicts, esp among multiple users, are supposed to be handled
>primarily by functions that are internal to the rdb itself.

'Supposed to be', Mr Minor? What caused anyone to come to that
conclusion... something they overheard in a pub or read in an article in
an airline magazine?

>Regardless of how time-honored a given technique may be, it seems to
>me that by going to the trouble of coding our own locking handler we
>will only end up sidestepping what the rdb is designed to take care of
>for us, and all in exchange for a manual (and inferior) version of
>that capability.

What 'seems to you', Mr Minor, might be different than what is stated in
the product's documentation or in the product's actual functioning... if
someone has made assertions about the product's capabilities then those
assertions might need to be verified.

>
>This is rather like harnessing a team of mules up to a tractor to plow
>the field. Sure, its possible, but we'd be foolishly wasting the very
>reasons that we'd paid extra money to buy the damn tractor in the
>first place.

I was taught, Mr Minor, that 'The responsibility for the allocation,
co-ordination and motivation of personnel and resources towards the
accomplishment of a stated Executive goal is that of Management'... if you
have Managers who purchase tractors in order to harness programmers to
pull them then the Executives deserve what results.

>
>
>
>Deadlocks per se are not of particular concern to us, as these are
>supposed to be automatically handled by the rdb.

Another 'supposed'... building a product based on suppositions might
result in disappointment.

>What we are trying
>to learn about are the protocols of dealing with wait-locks, time-
>outs, and prevention of unilateral changes etc.

Those should be mentioned in the Product Documentation, along with where
it talks about how 'the Product is supposed to automatically handle
deadlocks'... wonderful things, those 'supposeds' and 'should bes'.

>
>Yes, we do. Pseudo-code, at least within our organization, is written
>for the purpose of communicating ideas and logic flow. Given that you
>figured out that my usage of 'record' should have instead been
>expressed as 'row', then this pseudo-code has apparently accomplished
>its purpose. :)

It seems to have done that, and more.

>
>In any case, point taken.

How pleasant... when one is out scouring the UseNet for free advice it
might be best to do so without the equivalent of 'spinach on one's teeth'.

>
>
>Your final suggestion is excellent but I am afraid it is not
>practicable because I do not control any of the purse strings. We
>have no choice but to work with this analyst, along with a rather
>tight-fisted management team who is unwilling to spring for the cost
>of formal training.

Your management will get what they pay for; try to remind them about the
difference in using an architect who has AIA certification and one who
built a dog-house or two.

>Complaints I have aplenty, of course, but that
>won't resolve anything.

Complaints are not nearly as valid as plans, with cause-and-effect clearly
laid out. Implement a new technology without insuring a trained support
base and it is likely that support will be... less than stellar.

>The only realistic and proactive plan we have
>at this point is to continue doing what we are doing, namely, to
>research usenet groups and knowledge bases, to ask questions, read
>manuals, ask more questions, setup & run tests, analyze results,
>borrow or buy additional manuals, and humbly beg somebody in the real
>world to lead us to some working examples.

Hmmmmm... Read Admiral Grace W Hopper once said something along the lines
of ''But we've *always* done it this way'' is the most dangerous phrase in
the language.' Your approach reminds me of a USSR-era joke from decades
on back:

What is Marxist philosophy? Marxist philosophy is a black cat in a black
room.

What is Marxist-Leninist philosophy? Marxist-Leninist philosophy is
looking for a black cat in a black room.

What is Marxist-Leninist-Stalinist philosophy? Marxist-Leninist-Stalinist
philosophy is looking for a black cat in a black room and, every so often,
shouting out 'I've got it! I've got it!'

There used to be a bit of Workplace Humor about beating a dead horse...
but what I know is that if you go into a bar frequented by longshoremen
and say 'Hey, fellows, we're going to be doing some packing and lifting at
our place... who'd like to give us a free bit of work?' you may find
yourself disappointed with the results.

>
>Thank you for your help.

A pleasure.

DD

DaveM

2007-04-09, 6:55 pm

Mr Dworf,

Hindsight being 20-20, our archival research would have yielded much
better results had we known to search for the term "embedded sql".
Had you bothered to heed my earlier mention about our admitted
starting place of ignorance then you would have already figured out
that it can be quite difficult to ask for something when you don't
even know what it is really supposed to be called in the first
place.

Speaking of my own ignorance, I really should have thought to check
your track record before I responded to you the first time. Having
read a sampling of your archived postings since then I have largely
found that your 'input' and 'help' are much more aptly labeled
'heckling' and 'belittlement'. A large part of your responses seem to
be the result of your purposefully reading between lines that aren't
there, and twisting the ones that are, just to stir up reactions from
people for your subsequent entertainment.

You could learn a lot from the other three respondents to this
inquiry. Without demanding research evidence nor accusing people of
trying to pilfer other's professional thoughts, these individuals have
graciously given me the working examples I was looking for, along with
suggestions of books/resources and pointers to better search criteria
(e.g., Embedded Sql).

Given your track record, Mr Dworf, I believe that there are two things
forthcoming from you that are practically inevitable: 1) You are
absolutely going to have to have the last word in this thread, and,
2) You are going to continue to 'help' other requestors in this same
fashion until either death or getting a life.

You are not worth any more of my time. May you savor the distance and
enjoy your freedom.

Dave

2007-04-09, 6:55 pm

In article <1176138034.214060.257610@q75g2000hsh.googlegroups.com>,
DaveM <renfrew76@xemaps.com> wrote:
>Mr Dworf,

Please... jes' ol' Doc, if you can manage to spell that correctly.

>
>Hindsight being 20-20, our archival research would have yielded much
>better results had we known to search for the term "embedded sql".
>Had you bothered to heed my earlier mention about our admitted
>starting place of ignorance then you would have already figured out
>that it can be quite difficult to ask for something when you don't
>even know what it is really supposed to be called in the first
>place.

The responsibility for knowing where one is going and how to request such
a thing is that of the traveller... knowing neither where one wishes to
end up nor how to request information about it may, indeed, result in
one's getting lost.

>
>Speaking of my own ignorance, I really should have thought to check
>your track record before I responded to you the first time.

You might have checked other things as well... there's an Olde Joke you
might have stumbled across, say, at
http://www.dotnetspider.com/fun/Computer-Joke-838.aspx .

DD

Howard Brazee

2007-04-09, 6:55 pm

On Mon, 9 Apr 2007 18:25:41 +0000 (UTC), docdwarf@panix.com () wrote:

>You might have checked other things as well... there's an Olde Joke you
>might have stumbled across, say, at
>http://www.dotnetspider.com/fun/Computer-Joke-838.aspx .

Although looking up the longitude and latitude mentioned in that joke
leaves me scratching my head.

2007-04-09, 6:55 pm

In article <h83l135hg4pgs80td2kqima848pka3edtm@4ax.com>,
Howard Brazee <howard@brazee.net> wrote:
>On Mon, 9 Apr 2007 18:25:41 +0000 (UTC), docdwarf@panix.com () wrote:
>
>
>Although looking up the longitude and latitude mentioned in that joke
>leaves me scratching my head.

Don't worry, Mr Brazee... somehow that will be the programmer's fault, as
well.

DD

HeyBub

2007-04-09, 6:55 pm

docdwarf@panix.com wrote:
> In article <h83l135hg4pgs80td2kqima848pka3edtm@4ax.com>,
> Howard Brazee <howard@brazee.net> wrote:
>
> Don't worry, Mr Brazee... somehow that will be the programmer's
> fault, as well.

Evidently as the location is about 500 miles east of New York City, in the
Atlantic.

Pete Dashwood

2007-04-09, 6:55 pm

<docdwarf@panix.com> wrote in message news:eve0f5$lsf$1@reader2.panix.com...
> In article <1176138034.214060.257610@q75g2000hsh.googlegroups.com>,
> DaveM <renfrew76@xemaps.com> wrote:
>
> Please... jes' ol' Doc, if you can manage to spell that correctly.
>
>
> The responsibility for knowing where one is going and how to request such
> a thing is that of the traveller... knowing neither where one wishes to
> end up nor how to request information about it may, indeed, result in
> one's getting lost.

It certainly does if one only encounters unhelpful people.

People of goodwill can assist the traveller to more clearly define the goals
and the road, even if these are fuzzy at the start.

Sometimes the actual destination is not the one that was originally
envisioned, but as always, the action of getting there is an opportunity for
growth.

>
>
> You might have checked other things as well... there's an Olde Joke you
> might have stumbled across, say, at
> http://www.dotnetspider.com/fun/Computer-Joke-838.aspx .
>

Not sure what the point is here... we have a patently incompetent
programmer, and a patently stupid Project Manager, neither of whom have any
responsibility for their actions.

As they are both idiots, whatever their interaction is, it is of little
consequence. For this joke to work, it would be necessary to identify with
one or other of the parties. This requires us to assume the same mantle of
idiocy that they both display.

Ah, now I see why SOME might find it amusing... :-)

Pete.

2007-04-09, 9:55 pm

In article <58014tF2f874sU1@mid.individual.net>,
Pete Dashwood <dashwood@removethis.enternet.co.nz> wrote:
>
><docdwarf@panix.com> wrote in message news:eve0f5$lsf$1@reader2.panix.com...
>
>It certainly does if one only encounters unhelpful people.

'Helpful', Mr Dashwood, might be in the mind of the beholder... assisting
someone in learning that it is best to swallow before speaking can not
only assist in clarity of communication, it may decrease someone's chance
of aspirating food.

>
>People of goodwill can assist the traveller to more clearly define the goals
>and the road, even if these are fuzzy at the start.
>
>Sometimes the actual destination is not the one that was originally
>envisioned, but as always, the action of getting there is an opportunity for
>growth.

Being taught that saying 'row' and when to say 'record' when one s

s
free advice from professionals about a business-related matter might cause
one to be taken... a bit less seriously that one desires could well be an
'opportunity for growth' that comes about completely serendipitously,
sure!

>
>Not sure what the point is here... we have a patently incompetent
>programmer, and a patently stupid Project Manager, neither of whom have any
>responsibility for their actions.
>
>As they are both idiots, whatever their interaction is, it is of little
>consequence. For this joke to work, it would be necessary to identify with

Leaving aside that the text in the URL given might be seen as showing as
much about programming comptence as it does about managerial stupidity
(but it might be seen as... significant how someone might leave a
reference to a programmer in lower case while Project Manager gets
Capitalised) it might be wise to recall an interchange from that Classic
of Anciente Philosophie, 'The Day the Earth Stood Still' (as quoted on
http://imdb.com/title/tt0043456/quotes ):

--begin quoted text:

Mr. Harley: Your impatience is quite understandable.
Klaatu: I'm impatient with stupidity. My people have learned to live
without it.
Mr. Harley: I'm afraid my people haven't. I'm very sorry... I wish it were
otherwise.

--end quoted text

[snip]

>Ah, now I see why SOME might find it amusing... :-)

To the best of my knowledge, Mr Dashwood, no-one has posted to this forum
calling it such.

DD

DaveM

2007-04-10, 6:55 pm

Jimmy,

Thank you for your help. Between yours and other responses I am now
aware of more specific terms that we should be using e.g.,
'embedded'), along with other fundamentally important pieces of
information.

Google/web search for 'sql-examples' found 43K hits, but most of these
were about structuring the sql query itself, as opposed to the kinds
of cobol-to-sql-database examples that I was looking for. However, by
narrowing it down further with the added criteria "embedded" and
"cobol" it gave us a much more manageable/relevant 456 hits. The
first page alone gives us more than enough useful links to keep us
constructively busy for a long while!

NE's Database Access online book was/is quite useful, but, of course,
did not contain the real-world examples we were looking for. With
enlightenments since gleaned via this and other threads, I am happy to
say the this book is even MORE useful now because I have gained a
significantly better sense of what many of the pieces really mean and
how they are applied. (I still have a long way to go, obviously, but
it sure feels better to know I'm at least holding the manual right-
side up, so to speak.)

ESQL Assistant looks very useful, and I most certainly will also take
up your suggestion of signing up for MF forum per SQL.

According to one of the other writers I now owe you a consultant's
fee, along with a signed affadavit proving that I have already
achieved expert status in any given subject before having the audacity
to ask anyone else questions about it. ;o)

Again, and in all seriousness, I thank you for your help.

Gratefully,
Dave Miner

DaveM

2007-04-10, 6:55 pm

Oops. Instead of posting my response to you under your recent
message, I mistakenly posted it under TLEADERS's message. Sorry about
that! -Dave

2007-04-10, 6:55 pm

In article <1176223479.428972.39830@l77g2000hsb.googlegroups.com>,
DaveM <renfrew76@xemaps.com> wrote:

[snip]

>According to one of the other writers I now owe you a consultant's
>fee, along with a signed affadavit proving that I have already
>achieved expert status in any given subject before having the audacity
>to ask anyone else questions about it. ;o)

Mr Miner, the concept of 'owe' can carry with it both legal and moral
obligations. To the best of my knowledge there's no contractual agreement
for payment between you and anyone offering responses here so - it might
be concluded - that you owe twice as much as you've already been asked to
pay.

On the other hand... you received information which, by your own
admission, has saved you time; it can be argued that you owe a portion of
your savings to one who assisted you in achieving them... and if
Franklin's Equivalence ('Time is Money') is valid then you owe someone
cash.

As for 'expert status'... that was never asked. Just think of what you
might have gotten were you to have respected the subject matter
sufficiently to learn some basic terminology before trotting your requests
out before the world, hat in your hand and spinach on your teeth, asking
for folks to Do Their Jobs For You For Free.

It turns out that you got... something, sure. How you evaluate whether
what you got is of value or will send you and your team rushing pell-mell
down a dead end is another matter... wouldn't want to find yourself on the
carpet in front of the CFO saying 'But this... guy on the UseNet told me
it'd work, how was *I* to know he was fulla hooey... I'm not Technical,
you know! To Julia... do it to Julia, not me!', would you?

DD

James J. Gavan

2007-04-10, 9:55 pm

DaveM wrote:
> Jimmy,
>
> Thank you for your help. Between yours and other responses I am now
> aware of more specific terms that we should be using e.g.,
> 'embedded'), along with other fundamentally important pieces of
> information.
>
<SNIP>

Don't know how I dreamed up 58K hits - just re-did it and came up with
the same 43K hits that you did :-)

Doesn't give exactly what you were immediately after but check M/F site
for examples/samples - there are three 'general' SQL routines. Plus if
you get into your N/E sub-folders there are also some demos of 'general'
SQL solutions. Once into the M/F Forum you can search previous messages
with "Database", "ODBC" and "SQL" etc. You can't respond to the next -
but you can also look at old ARCHIVED messages. Like M/S, M/F under Net
Express also has a KnowledgeBase. But the whole bunch above tend to be
generalized.

Pete Dashwood's solution and ESQL Assistant. Pete reads your COBOL file
records and generates equivalent DB rows into a table. ESQL Assistant -
*YOU* design the DB Table and determine what type of SQL fields/columns
(Int, Char etc.), you want. Naturally the latter approach means you have
to get au fait with the particular DB package, (how numerics are stored)
- so it could take you a w

or more to convert, depending upon the
number of tables you are going to generate.

There might be a nice solution using both Pete's package and ESQL
Assistant. Pete will read a whole bunch of COBOL files in mere minutes
and generate your DB Table formats. Now using ESQL Assistant, (the major
thing being you can generate your SQL statements knowing they will be
correct), when you test on a particular table the ESQL package will ask
you if you want to generate a copyfile - three parts :-

DB Table format
DB Table NULL columns
COBOL - a typical COBOL record

So Pete provides the initial definition of your DB Table and from that
ESQL using Pete's DB Table, generates the copyfile above which you can
test your individual queries with.

Obviously you are picking up stuff as you do more reading, but for a
quickie comparison, and don't take this as gospel :-

COBOL FILES SQL
----------- -------------

Open/Close Connect/Disconnect (Note you connect/disconnect
to a specific Database which may contain 50 Tables,
(equivalent of 50 COBOL files, or perhaps 30 Files,
split into 50 tables)

Read using PrimeKey Select Distinct

Read Next Select using Cursor - This uses a number for the
cursor. ESQL Assistant automatically generates the
next number, so there's no confusion between
different queries

Rewrite Update

Write Insert

Delete Delete

The use of the various SQL "Verbs" becomes clearer if you use ESQL
Assistant to generate queries.

I think what you balked at in your original message was "How to..." do
equivalent of COBOL Write and Rewrite.

PROCEDURAL using an ISAM (my own style - so you adapt to your way)
------------------------

Display Screen-Record/GUI Dialog
Accept PrimeKey

if Blank - Quit

else set RecordNotFound, RecordNotChanged to true
Read Record using PrimeKey (SQL - SELECT DISTINCT )
End-if

if RecordFound
perform DISPLAY-RECORD
perform ACCEPT-CHANGES
for Changed-fields set RecordChanged to true

else (RecordNotFound)
set RecordChanged to true
perform ACCEPT-NEW-RECORD, (other than the PrimeKey)
then perform ACCEPT-CHANGES (if you want to make corrections)
End-if

if RecordChanged
perform VALIDATION-CHECKS
(errors - go back to perform ACCEPT-CHANGES)
End-if

if ValidationOK (this is also a Level 88)

if RecordFound
REWRITE COBOL-RECORD

else WRITE COBOL-RECORD

End-if

PROCEDURAL using SQL
--------------------

The only real difference between this and COBOL file above is
substituting :-

SELECT DISTINCT = READ FILE using PrimeKey
SQL-UPDATE = REWRITE COBOL-RECORD
SQL-INSERT = WRITE COBOL-RECORD.

REAL WORLD EXAMPLES
-------------------

I don't know that M/F have anything specific, but you could ask if you
join the M/F Forum

I did post a message the latter part of last year - but can't locate it;
e-mail me 'editing' my address above, so that I can confirm a link to
you and in return I'll post the code I'm referring to. It's OO Classes
but when looking at methods being invoked think of CALLING another
program or performing a PARAGRAPH. I'm not recommending *mine* is the
way to do it, but for a simple file it illustrates the program logic
flow I illustrated above and gives you the COMPLETE SQL Statements for
INSERT, UPDATE, CURSOR etc... for a specific table. (I don't mind if you
call them records or rows :-) ).

If/when you get a paperback, or articles on design from the Web,
concentrate on the term 'Normalization' so that you have a handle on it.

Jimmy

Pete Dashwood

2007-04-11, 3:55 am

"James J. Gavan" <jgavandeletethis@shaw.ca> wrote in message
news:sNVSh.51648$6m4.42486@pd7urf1no...
<snip>>
> Pete Dashwood's solution and ESQL Assistant. Pete reads your COBOL file
> records and generates equivalent DB rows into a table.

Not quite.

ISAM2RDB reads your COBOL source definitions and generates one or more
TABLES into a designated DATABASE.

A single ISAM file definition COULD generate several tables. Ths is because
the normalization process removes repeating groups (OCCURS in COBOL) to a
separate linked table for each group. (One advantage of this is that every
"table" in your COBOL system now has unlimited rows (no more maintaining
OCCURS items) and will only take as much space as it actually needs...)

If your ISAM source definition has no OCCURS in it, then you should get a
single table, with a correctly typed column for each of the fields defined
in your ISAM definition. (Both groups and elements are defined, to assist
with data loading and converting existing programs that may reference group
fields. Unreferenced groups can be removed later.)

Certainly this tool gives you a structure that is useful while you are
transitioning from ISAM to RDB. However, I would not suggest that this is
how Relational Databases should be designed and built :-) It is a
non-relational solution forced into a relational framework and, as such, it
can never be as good as a Relational solution built from scratch. Sadly few
of us have the time or funding to restart from scratch when moving to new
technology, so ISAM2RDB provides a helpful bridge. (It also assists with the
learning process; for example, you can look at the PICTURE of a given
element in your COBOL code and see what type of Database definition for the
corresponding column was generated on the RDB.)

ESQL Assistant -
> *YOU* design the DB Table and determine what type of SQL fields/columns
> (Int, Char etc.), you want. Naturally the latter approach means you have
> to get au fait with the particular DB package, (how numerics are stored)
> - so it could take you a w

or more to convert, depending upon the
> number of tables you are going to generate.
>
> There might be a nice solution using both Pete's package and ESQL
> Assistant. Pete will read a whole bunch of COBOL files in mere minutes
> and generate your DB Table formats. Now using ESQL Assistant, (the major
> thing being you can generate your SQL statements knowing they will be
> correct), when you test on a particular table the ESQL package will ask
> you if you want to generate a copyfile - three parts :-
>
> DB Table format
> DB Table NULL columns
> COBOL - a typical COBOL record
>
> So Pete provides the initial definition of your DB Table and from that
> ESQL using Pete's DB Table, generates the copyfile above which you can
> test your individual queries with.

Yes, that could be viable. I guess it depends on how many tables are
involved. If it is just a few, it is probably better to simply use ESQL and
learn how to define tables. If there are many, it might be useful to use
ISAM2RDB and pick up some pointers on how to define and normalize tables, in
passing.
>
>
> If/when you get a paperback, or articles on design from the Web,
> concentrate on the term 'Normalization' so that you have a handle on it.
>

A very important observation, Jimmy.

I have some stuff on this somewhere... I'll see if I can post it to a web
server so people can access it.

Pete.

James J. Gavan

2007-04-11, 3:55 am

Pete Dashwood wrote:
> "James J. Gavan" <jgavandeletethis@shaw.ca> wrote in message
> news:sNVSh.51648$6m4.42486@pd7urf1no...
> <snip>>
>
>
>
> Not quite.
>
> ISAM2RDB reads your COBOL source definitions and generates one or more
> TABLES into a designated DATABASE.

Yes I can see I worded that badly - I really meant as you point out,
that you generate the format for a Row in a particular table, or
perhaps, like the reference to OCCURS below - you may generate
definitions for one or more Tables. (I wonder if that's why the J4 gang
are so keen on an 800 plus page document - so that you dot all the i's
and cross all the t's :-) )

>
> A single ISAM file definition COULD generate several tables. Ths is because
> the normalization process removes repeating groups (OCCURS in COBOL) to a
> separate linked table for each group. (One advantage of this is that every
> "table" in your COBOL system now has unlimited rows (no more maintaining
> OCCURS items) and will only take as much space as it actually needs...)
>
> If your ISAM source definition has no OCCURS in it, then you should get a
> single table, with a correctly typed column for each of the fields defined
> in your ISAM definition. (Both groups and elements are defined, to assist
> with data loading and converting existing programs that may reference group
> fields. Unreferenced groups can be removed later.)
>
> Certainly this tool gives you a structure that is useful while you are
> transitioning from ISAM to RDB. However, I would not suggest that this is
> how Relational Databases should be designed and built :-) It is a
> non-relational solution forced into a relational framework and, as such, it
> can never be as good as a Relational solution built from scratch. Sadly few
> of us have the time or funding to restart from scratch when moving to new
> technology, so ISAM2RDB provides a helpful bridge. (It also assists with the
> learning process; for example, you can look at the PICTURE of a given
> element in your COBOL code and see what type of Database definition for the
> corresponding column was generated on the RDB.)
>
>

If he should opt to go with ISAM2RDB to get his Table definitions - and
it involves OCCURS - please make sure you spell out to him how to use
the resulting 'inter-linking' tables. Aeons ago you produced similar for
me - but without explanation how to use the 're-vamped OCCURS', I was
stumped on how to use them !

Just one other thought which you might comment on. When Dave has done
his setup with tables and definitions, for his real-world COBOL files, I
can't think of another solution, other than generating CSV files to
import to the Database ??? (And given any OCCURS - he may have to
generate more than one CSV from a given COBOL file).

Don't want to insult you Dave - but in case the term is new to you,
'CSV' = Comma, Separated, Variables where records are written to a Line
Sequential in the field/column sequence to make up a record being
imported to a Table row, and usually with the file suffix .txt'.

No more below.

Jimmy

> ESQL Assistant -
>
>
>
> Yes, that could be viable. I guess it depends on how many tables are
> involved. If it is just a few, it is probably better to simply use ESQL and
> learn how to define tables. If there are many, it might be useful to use
> ISAM2RDB and pick up some pointers on how to define and normalize tables, in
> passing.
>
>
>
> A very important observation, Jimmy.
>
> I have some stuff on this somewhere... I'll see if I can post it to a web
> server so people can access it.
>
> Pete.
>
>

Pete Dashwood

2007-04-11, 3:55 am

"James J. Gavan" <jgavandeletethis@shaw.ca> wrote in message
news:4c%Sh.53345$6m4.32414@pd7urf1no...
> Pete Dashwood wrote:
> If he should opt to go with ISAM2RDB to get his Table definitions - and it
> involves OCCURS - please make sure you spell out to him how to use the
> resulting 'inter-linking' tables. Aeons ago you produced similar for me -
> but without explanation how to use the 're-vamped OCCURS', I was
> stumped on how to use them !
>
What you got was a pre-release of the product.

You never asked for help.

Since then a number of customers have successfully used this tool. It comes
with a comprehensive Help file that took almost as long to develop as the
software did :-) There are also various ancilliaries and "support"
functions.

The concept of a secondary table linked to a primary one by a Foreign Key is
not exactly rocket science. It's actually pretty fundamental to Relational
Databases.

Obviously, if anyone had trouble using it I would gladly help, but I am no
longer in the business of converting ISAM files (unless someone pays me a
market rate to do so...:-)) and I'm not actually trying to sell this
software...The tool is there and I'm happy to let people use it, but I'm not
spending hours of time (which I simply don't have, at the moment) explaining
fundamental concepts and providing free support.

> Just one other thought which you might comment on. When Dave has done his
> setup with tables and definitions, for his real-world COBOL files, I can't
> think of another solution, other than generating CSV files to import to
> the Database ??? (And given any OCCURS - he may have to generate more than
> one CSV from a given COBOL file).
>

That would possibly work, but there is no need to do so. An approach I have
used in the past, successfully, is to write a COBOL program that reads the
ISAM file sequentially and simply transfers each record obtained, to the RDB
interface generated by ISAM2RDB. As I mentioned elsewhere, the tool allows
people with no RDB or SQL knowledge to access the RDB using COBOL records.
The RDB maintenance module for the table being loaded will simply INSERT all
the data. There is no need for intermediate processes or export/import; it
is direct from ISAM to RDB.

The fundamental thing here is that a COBOL program can read ISAM and "write"
RDB (within the same program). Once you write such a program you have a
template that enables the loading of all future tables from their equivalent
ISAM files.

Pete.

James J. Gavan

2007-04-11, 9:55 pm

Pete Dashwood wrote:
> "James J. Gavan" <jgavandeletethis@shaw.ca> wrote in message
> news:4c%Sh.53345$6m4.32414@pd7urf1no...
>
>
> What you got was a pre-release of the product.
>
> You never asked for help.

True. But I didn't ask for the table definitions either, which you
returned to me having used, as yet another test set, my COBOL record
copyfiles. I was supposed to know what the Table definitions were ? You
subsequently developed the Help File below which probably covered this
and other points.

I'm aware you do a good job on Help Files, so Dave shouldn't have a
problem working his way through it. For me, just academic at this stage,
as I wont be doing any more productive coding.

>
> Since then a number of customers have successfully used this tool. It comes
> with a comprehensive Help file that took almost as long to develop as the
> software did :-) There are also various ancilliaries and "support"
> functions.
>
> The concept of a secondary table linked to a primary one by a Foreign Key is
> not exactly rocket science. It's actually pretty fundamental to Relational
> Databases.
>
> Obviously, if anyone had trouble using it I would gladly help, but I am no
> longer in the business of converting ISAM files (unless someone pays me a
> market rate to do so...:-)) and I'm not actually trying to sell this
> software...The tool is there and I'm happy to let people use it, but I'm not
> spending hours of time (which I simply don't have, at the moment) explaining
> fundamental concepts and providing free support.
>
>
>
>
> That would possibly work, but there is no need to do so. An approach I have
> used in the past, successfully, is to write a COBOL program that reads the
> ISAM file sequentially and simply transfers each record obtained, to the RDB
> interface generated by ISAM2RDB. As I mentioned elsewhere, the tool allows
> people with no RDB or SQL knowledge to access the RDB using COBOL records.
> The RDB maintenance module for the table being loaded will simply INSERT all
> the data. There is no need for intermediate processes or export/import; it
> is direct from ISAM to RDB.
>
> The fundamental thing here is that a COBOL program can read ISAM and "write"
> RDB (within the same program). Once you write such a program you have a
> template that enables the loading of all future tables from their equivalent
> ISAM files.
>
Again yes to the above. But from my experience, the quick transfer is
not necessarily the best for every situation :-

- Using any new tool, (Screen to GUIs, COBOL files to DBs etc., or as
you have done, COBOL to Java, then moving on to C#), should,
I believe, trigger in your mind what advantages the new tools give you.
So as part of the design planning inevitably you think of enhanced ways
of doing things - which can lead to a slightly different, or
considerably different database from the original.

- In my case the necessity of converting 700 data sets (different
geographical locations), with some 7 files each, accumulated over a
twenty-year period, to fit a new design format. That meant in some
stages of the transfer process the 'old' records just didn't have some
fields needed in the "new" DB tables. So process in phases, having
generated error reports where necessary, stop, do some manual editing
and then move to the later phases.

- The best way of achieving this for me - Line Sequential - using a
fixed record template. Given a value of 2.345 held in pic 9(03)v9(03)
comp-3, the template outputs : ",002.345," - the result, all columns are
aligned making it very easy to swiftly scroll through records, and make
editing changes where necessary.

- Even with the above approach it is still a fiddly operation, i.e.
checking the validity of the input data. You could of course take the
'cleaned up' Line Sequential CSV and "write" to the DB as you suggest.
However given the format is a CSV - just do the one import.

Different strokes for different folks.

Jimmy

Pete Dashwood

2007-04-12, 3:55 am

"James J. Gavan" <jgavandeletethis@shaw.ca> wrote in message
news:g6hTh.61601$aG1.7987@pd7urf3no...
> Pete Dashwood wrote:
<snip>>[color=darkred]
> Again yes to the above. But from my experience, the quick transfer is
> not necessarily the best for every situation :-

It is if you're in a hurry... :-)

>
> - Using any new tool, (Screen to GUIs, COBOL files to DBs etc., or as you
> have done, COBOL to Java, then moving on to C#), should,
> I believe, trigger in your mind what advantages the new tools give you.
> So as part of the design planning inevitably you think of enhanced ways
> of doing things - which can lead to a slightly different, or
> considerably different database from the original.

Sure. Growth occurs. And when it does, we see different, and often better,
ways of doing things.

We are then faced with the dilemma of whether we should convert or scrap
what we have and go back to square one, or continue with what we (now...)
know is a sub-optimum solution. Usually a balance is struck between these
two extremes... in this case it is a conversion from ISAM to RDB; probably
the best that can done under the circumstances.

A "Relational purist" will be unhappy because the RDB is being constrained
by the original ISAM DB design (which may have also "evolved" over time),,
and therefore doesn't realise all the true benefits of RDB; someone who
LOVES ISAM and flat files will be unhappy about why something that works
fine, is being moved to a "new fangled technology" (even if it is nearly 25
years old... some people are suspicious of anything less than two decades
old...)...no-one is ever completely happy.

Given all these conflicts of perspective and interest, it is a miracle there
are any functioniong systems in the world at all...:-)

My experience is that people do the best they can; (no-one gets out of bed
in the morning and decides: "I think I'll go into work today and really
screw up everything I do...yeah, that'll make me feel better...")...
sometimes they get it wrong, sometimes they get it right, sometimes they are
constrained by other factors into compromises which are not optimal. Only
very rarely (and it is more likely if you work for yourself, and can
therefore decide what you will or will not go with) does the opportunity to
do something from scratch, using the best technology arise. (Even then, in 5
years you will realise it could have been better... :-))
>
> - In my case the necessity of converting 700 data sets (different
> geographical locations), with some 7 files each, accumulated over a
> twenty-year period, to fit a new design format. That meant in some stages
> of the transfer process the 'old' records just didn't have some fields
> needed in the "new" DB tables. So process in phases, having generated
> error reports where necessary, stop, do some manual editing and then move
> to the later phases.
>
Sounds pretty drastic...

One advantage of RDB of course, is that it is very easy to "expand the
design" and tolerate missing data as well.

> - The best way of achieving this for me - Line Sequential - using a fixed
> record template. Given a value of 2.345 held in pic 9(03)v9(03) comp-3,
> the template outputs : ",002.345," - the result, all columns are aligned
> making it very easy to swiftly scroll through records, and make editing
> changes where necessary.

This is one step away from what I suggested. Only difference, I'd have the
template write the data directly to the RDB and do the "cleanup" on the RDB,
where there are better tools available for identifying and correcting data
anomalies.

However, ANY solution that works, is a GOOD solution... :-}

(I was embarrassed when, as a student pilot, I made a pretty heavy landing
and apologised to my instructor. He was a Texan who was rarely phased by
anything, and he just drawled: "Son, any landing you walk away from is a
GOOD landing...")
>
> - Even with the above approach it is still a fiddly operation, i.e.
> checking the validity of the input data. You could of course take the
> 'cleaned up' Line Sequential CSV and "write" to the DB as you suggest.
> However given the format is a CSV - just do the one import.
>
> Different strokes for different folks.

Absolutely.

Pete.

Rick Smith

2007-04-12, 6:55 pm

"Pete Dashwood" <dashwood@removethis.enternet.co.nz> wrote in message
news:585r42F2e105nU1@mid.individual.net...
[snip]
> We are then faced with the dilemma of whether we should convert or scrap
> what we have and go back to square one, or continue with what we (now...)
> know is a sub-optimum solution. Usually a balance is struck between these
> two extremes... in this case it is a conversion from ISAM to RDB; probably
> the best that can done under the circumstances.

< http://www.microfocusworld.com/track_page.php?id=5 >
"A partner will present a session that shows how a relational
database can be used with a COBOL application using
standard COBOL I/O statements, WITHOUT any changes
to the code!"

Perhaps the best is no conversion, at all! Just upgrade to the
latest technology.

2007-04-12, 6:55 pm

In article <131sb8b2f0atpc7@corp.supernews.com>,
Rick Smith <ricksmith@mfi.net> wrote:
>
>"Pete Dashwood" <dashwood@removethis.enternet.co.nz> wrote in message
>news:585r42F2e105nU1@mid.individual.net...
>[snip]
>
>< http://www.microfocusworld.com/track_page.php?id=5 >
>"A partner will present a session that shows how a relational
>database can be used with a COBOL application using
>standard COBOL I/O statements, WITHOUT any changes
>to the code!"
>
>Perhaps the best is no conversion, at all! Just upgrade to the
>latest technology.

Mr Smith, come now... an organisation where the analyst recommends
timestamping records... errrr, rows in a database and the manager has to
turn to the UseNet for help will not, in my experience, embrace a solution
which requires Spending Money to upgrade technology or sending someone of
sufficient technical competence to benefit from the experience - as
opposed to, say, a Corner-Office Idiot - to the Royal Pacific Resort in
Orlando, FL, USA for three days.

(oh... and a rather common mis-spelling in the paragraph you quoted might
be seen, by some, as casting aspersions on the relibility of the claims)

DD

Alistair

2007-04-12, 6:55 pm

On 10 Apr, 00:39, "Pete Dashwood" <dashw...@removethis.enternet.co.nz>
wrote:
> <docdw...@panix.com> wrote in messagenews:
>
> Not sure what the point is here... we have a patently incompetent
> programmer, and a patently stupid Project Manager, neither of whom have any
> responsibility for their actions.
>
> As they are both idiots, whatever their interaction is, it is of little
> consequence. For this joke to work, it would be necessary to identify with
> one or other of the parties. This requires us to assume the same mantle of
> idiocy that they both display.
>
> Ah, now I see why SOME might find it amusing... :-)
>
> Pete

I have to take issue with your description of the Programmer as being
incompetent; he clearly answered the question posed in the most
accurate fashion possible, volunteering more information than is
strictly necessary. I wonder if there is a certain note of
defensiveness in your response?

I do, however, agree that the project manager is clearly incompetent
(at ballooning, at boy-scout preparation, at map-reading, at eliciting
information?).

;-)

Alistair

2007-04-12, 6:55 pm

On 12 Apr, 15:13, docdw...@panix.com () wrote:
> In article <131sb8b2f0at...@corp.supernews.com>,
>
>
>
>
>
> Rick Smith <ricksm...@mfi.net> wrote:
>
>
>
>
> Mr Smith, come now... an organisation where the analyst recommends
> timestamping records... errrr, rows in a database and the manager has to
> turn to the UseNet for help will not, in my experience, embrace a solution
> which requires Spending Money to upgrade technology or sending someone of
> sufficient technical competence to benefit from the experience - as
> opposed to, say, a Corner-Office Idiot - to the Royal Pacific Resort in
> Orlando, FL, USA for three days.
>
> (oh... and a rather common mis-spelling in the paragraph you quoted might
> be seen, by some, as casting aspersions on the relibility of the claims)
>
> DD

I presume that you mean me and the sleight of hand. I believe slight
of hand is only relevant in BDSM circles.

2007-04-12, 6:55 pm

In article <1176405559.394072.299600@o5g2000hsb.googlegroups.com>,
Alistair <alistair@ld50macca.demon.co.uk> wrote:
>On 12 Apr, 15:13, docdw...@panix.com () wrote:

[snip]

>
>I presume that you mean me and the sleight of hand. I believe slight
>of hand is only relevant in BDSM circles.

It may have been done with craftiness, Mr Maclean, but I am unsure whether
it was done in order to deceive... so if it offended perhaps it was a
sleightless slight.

(wonderful language, this English... how *does* anyone learn it?)

DD