From: tleaders on
On Apr 4, 3:00 pm, "DaveM" <renfre...(a)xemaps.com> wrote:
> On Apr 3, 9:13 pm, docdw...(a)panix.com () wrote:
>
>
>
>
>
> > In article <1175626528.510733.311...(a)n59g2000hsh.googlegroups.com>,
>
> > DaveM <renfre...(a)xemaps.com> wrote:
> > >We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
> > >2000. The concepts/examples we are seeking 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?
>
> If I'd kept a log of every single place I've been to while seeking
> 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 seeking 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 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.
>
> 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'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.
>
> 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.
>
>
>
>
>
>
>
> > >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'?
>
> 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.
>
>
>
>
>
>
>
> > >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
>
> 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.
* 111111111122222222223333333333444444444455555555556
* 123456789012345678901234567890123456789012345678901234567890
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



From: James J. Gavan on
DaveM wrote:
> On Apr 3, 9:13 pm, docdw...(a)panix.com () wrote:
>
>>In article <1175626528.510733.311...(a)n59g2000hsh.googlegroups.com>,
>>
>>DaveM <renfre...(a)xemaps.com> wrote:
>>
>>>We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
>>>2000. The concepts/examples we are seeking 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?
>>
>
>
> If I'd kept a log of every single place I've been to while seeking
> 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 seeking 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/supportline/documentation/books/nx40/nx40indx.htm


- 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
From: Anonymous on
In article <1175713257.593859.110760(a)n59g2000hsh.googlegroups.com>,
DaveM <renfrew76(a)xemaps.com> wrote:
>On Apr 3, 9:13 pm, docdw...(a)panix.com () wrote:
>> In article <1175626528.510733.311...(a)n59g2000hsh.googlegroups.com>,
>>
>> DaveM <renfre...(a)xemaps.com> wrote:
>> >We are using Micro Focus Net Express 4.0 and Microsoft SQL Server
>> >2000. The concepts/examples we are seeking 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?
>>
>
>If I'd kept a log of every single place I've been to while seeking
>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 seeking 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 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.
>
>
>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.

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

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

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

From: DaveM on
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

From: Anonymous on
In article <1176138034.214060.257610(a)q75g2000hsh.googlegroups.com>,
DaveM <renfrew76(a)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