Conversion of data & associated logic from ISAM to RDB

We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
2000.  The concepts/examples we are sing 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

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 sing 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

"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 sing 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)
>
Fair enough.

> 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.

Yes, this was fairly normally practice once upon a time. I remember using it
in 1978 on the first IBM 3790 deployment in the UK. This was to be a
"distributed application" that was to be "Networked" (Cutting edge stuff at
the time... We were given a very thick Assembler manual and told to learn
it. A w 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.

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

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 sing
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 sing 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

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 sing
> 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 sing 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

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 sing
> 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 sing 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.

<snip>

So you have gone searching David, but did your search take the following
into account  :-

- Google Search, enter with the hyphen, "SQL-Examples" - will give you
54K hits

- The N/E V4.0 on-line book regarding SQL - Database Access
http://supportline.microfocus.com/s...ndx.ht
m


- From the IDE top Menu Bar ----> Tools ----> Open ESQL Assistant,
which lets you model SQL queries and run them, and get results, WITHOUT
compiling. Once your test works, then you can copy/paste the proven SQL
statement into a COBOL program and compile. (At first sight this tool
looks complicated, but it isn't. Having set up a dummy DB table, have
the patience to read through the text and then start experimenting with
ESQL Assistant - you will be pleasantly surprised).

- Last and by no means least - sign up for the free Micro Focus Forum.
Post under Net Express, ensuring "SQL" appears in your message title -
and you should get help from an M/F lady specializing in DB support.

Regardless of the above, it will be worth your while to get a paperback
'How to ...." on DBs and SQL.

Jimmy

"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 sing 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.

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 sing
>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 sing 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

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