help with tables
in [Cobol]
|
Prev: oracle10g SQLBEX giving 114 with Oracle Dynamic SQL Method 4(ora9 works well)
Next: compile+link Fujitsu Linux
From: Anonymous on 1 Feb 2008 05:35 In article <29n2q3p0caoh922pmsenajrl1niln3j4m3(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Thu, 31 Jan 2008 17:38:23 +1300, "Pete Dashwood" ><dashwood(a)removethis.enternet.co.nz> >wrote: > >> >> >>"Robert" <no(a)e.mail> wrote in message >>news:jof2q31shvbu6d9ia1i7gelptabq9slahe(a)4ax.com... >>> On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick" >>> <Frank.Swarbrick(a)efirstbank.com> >>> wrote: >>> >>>>-------------------------------------------------------------------- >>>> SQL0060W The "COBOL" precompiler is in progress. >>>> 19 SQL0008N The token "occurs" found in a host variable >>>> declaration is not valid. >>>> 41 SQL4943W The number of host variables in the INTO clause >>>> is not the same as the number of items in the SELECT >>>> clause. SQLSTATE=01503 >>> >>> The manual says DB2 doesn't allow a simple SELECT to return multiple rows. >>> You must do the >>> SELECT in a cursor declaration and read the table with a single FETCH, as >>> illustrated by >>> Frederico. >>> >>> Thanks for the experiment. >>> >> >>While I don't claim the same level of SQL knowledge that you have >>demonstrated, Robert, I have managed to use it successfully for a number of >>years. >> >>Obviously you CAN return multiple rows from a query if you can process a >>result set, however, in embedded SQL I've always used the cursor and FETCHed >>it. > >Many Cobol programmers think it's not possible to get a result set, >except by FETCHing one >row at a time. They regard a database as VSAM on drugs. Ummmmm... many COBOL programmers I know work with DB2, Mr Wagner; didn't you mention something about what the manual said regarding this in another post? (I have, certainly, seen DB2 installations where the database is treated in programs as though it were VSAM... and then a few of the 'power users' get access and training in QMF (training for the programmers usually consisted of 'The managers went to the classes... and they slept; here's a manual and remember, we're trying to keep things as much in The Old Way as possible') and rather... pretty confrontations could result.) DD
From: Robert on 1 Feb 2008 08:52 On Thu, 31 Jan 2008 10:26:34 +0000 (UTC), docdwarf(a)panix.com () wrote: >In article <gfl2q319rd84spnb10q3f9huuvjq2254f9(a)4ax.com>, >Robert <no(a)e.mail> wrote: >>On Thu, 31 Jan 2008 01:37:24 +0000 (UTC), docdwarf(a)panix.com () wrote: >> >>>In article <bav1q3t26jjt26156j9eh4lpp1ivtot9a0(a)4ax.com>, >>>Robert <no(a)e.mail> wrote: >> >>>>>That's similar to a way I have seen things done on mainframes, yes... but >>>>>a way that would not pass muster in more than a few shops where I've >>>>>worked and would have gotten you laughed out of Prod Implementation >>>>>reviews. >>>> >>>>I've never seen a Prod Implementation review. I've only seen reviews >>>>BEFORE testing began. >>>>Sounds like you're putting untested rewrites into production. >>> >>>That might be due to the fact that it is something you admit to being >>>outside of your experience, Mr Wagner; a Prod Implementation review can >>>require a programmer to submit test results. >> >>In my experience, test results are checked by TEST TEAMS, who run pair, >>integration, >>system, regression and performance tests. Each test case is evidenced by >>a document >>showing expected versus actual, and signed in blood. > >Now think of expanding your experience, Mr Wagner... and imagine that the >TEST TEAM (caps original) makes a copy of their documentation... and >returns it (in exchange for a signature, of course), to the programmer... Tests plans and results, along with all other project documentation, reside in a framework repository that's accessible to everyone. There is no reason to print them out. >who brings this stack o' foolscap along when he sits down with other folks >at a Prod Implementation meeting. Our meetings are held in cyberspace using NetMeeting or similar. Participants are all over the world; they are not sitting in a room. Moreover, the development team is often disbanded before the project goes to production. There is no reason for a programmer to be the project's advocate; that's the job of project managers. >There... that wasn't so difficult, was it? A wonderful world it is, that >has New Things - even from decades past! - to learn in it. Sounds to me like Old Things. Are your code reviews done with hardcopy marked up with highlighters, 1970s style? Ours are done with file comparison programs such as Beyond Compare.
From: Anonymous on 1 Feb 2008 09:42 In article <mkj3q3pmtvmn64pjnhk0s3mfcgplas1hcs(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Thu, 31 Jan 2008 10:26:34 +0000 (UTC), docdwarf(a)panix.com () wrote: > >>In article <gfl2q319rd84spnb10q3f9huuvjq2254f9(a)4ax.com>, >>Robert <no(a)e.mail> wrote: >>>On Thu, 31 Jan 2008 01:37:24 +0000 (UTC), docdwarf(a)panix.com () wrote: >>> >>>>In article <bav1q3t26jjt26156j9eh4lpp1ivtot9a0(a)4ax.com>, >>>>Robert <no(a)e.mail> wrote: >>> >>>>>>That's similar to a way I have seen things done on mainframes, yes... but >>>>>>a way that would not pass muster in more than a few shops where I've >>>>>>worked and would have gotten you laughed out of Prod Implementation >>>>>>reviews. >>>>> >>>>>I've never seen a Prod Implementation review. I've only seen reviews >>>>>BEFORE testing began. >>>>>Sounds like you're putting untested rewrites into production. >>>> >>>>That might be due to the fact that it is something you admit to being >>>>outside of your experience, Mr Wagner; a Prod Implementation review can >>>>require a programmer to submit test results. >>> >>>In my experience, test results are checked by TEST TEAMS, who run pair, >>>integration, >>>system, regression and performance tests. Each test case is evidenced by >>>a document >>>showing expected versus actual, and signed in blood. >> >>Now think of expanding your experience, Mr Wagner... and imagine that the >>TEST TEAM (caps original) makes a copy of their documentation... and >>returns it (in exchange for a signature, of course), to the programmer... > >Tests plans and results, along with all other project documentation, >reside in a framework >repository that's accessible to everyone. There is no reason to print them out. Mr Wagner, in 'more than a few shops where I have worked' such things were not always available; that was the reason to print them out. > >>who brings this stack o' foolscap along when he sits down with other folks >>at a Prod Implementation meeting. > >Our meetings are held in cyberspace using NetMeeting or similar. >Participants are all over >the world; they are not sitting in a room. Not everyone's meetings were what your meetings are, Mr Wagner... you do realise that 'where I have worked' might encompass a portion of space/time that's more than a few decades past, don't you? > >Moreover, the development team is often disbanded before the project >goes to production. >There is no reason for a programmer to be the project's advocate; that's >the job of >project managers. > >>There... that wasn't so difficult, was it? A wonderful world it is, that >>has New Things - even from decades past! - to learn in it. > >Sounds to me like Old Things. In that you admit to being unfamiliar with them, Mr Wagner, then despite being decades old they are, by definition (http://m-w.com/dictionary/new, 2 a (2)), new. > >Are your code reviews done with hardcopy marked up with highlighters, >1970s style? >Ours are done with file comparison programs such as Beyond Compare. I've said nothing about how things are, Mr Wagner... that is why I stated 'where I have worked', not 'on my current site'. DD
From: Frank Swarbrick on 1 Feb 2008 13:42 >>> On 1/30/2008 at 8:25 PM, in message <med2q3hfb6vi8gbtvf436o07sr4k1jg0n5(a)4ax.com>, Robert<no(a)e.mail> wrote: > On Wed, 30 Jan 2008 21:55:18 +0000, Frederico Fonseca > <real-email-in-msg-spam(a)email.com> > wrote: > >>The following is allowed on V9, but on Z/OS. >>> >> WORKING-STORAGE SECTION. >> EXEC SQL >> INCLUDE SQLCA >> END-EXEC >> EXEC SQL >> BEGIN DECLARE SECTION >> END-EXEC >> 01 table2-area. >> 05 table2-entry. >> 10 t2-name pic x(30) occurs 100. >> 10 t2-dept pic x(04) occurs 100. > > Was that necessary? DB2, like Oracle, expands a Cobol group name into a > list of host > variables by using the next lower level, which should be elementary. In > other words: > > 05 table2-entry occurs 100. > 10 t2-name pic x(30). > 10 t2-dept pic x(04). > > into :table2-entry > is expanded to > into :t2-name, :t2-dept > > I don't know whether DB2 is smart enough to treat :t2-name as an array. > Oracle is. > If not, would :table2-entry.t2-name work? > >> EXEC SQL >> END DECLARE SECTION >> END-EXEC >> EXEC SQL >> DECLARE CURSOR-X CURSOR FOR >> select name, dept >> from test.table2 >> where name in (select distinct name from test.table1) >> END-EXEC. > > A cursor need not be declared in the data division, only before the > first reference to it. > I prefer to put the declaration just above the fetch, with the open and > close further > down. I prefer cursors to be in the data division, because they are declarations and not 'action' statements. By putting them in the procedure division one who doesn't know any better (say, me a year ago) could think that the declaration is actually doing something. Now I could conceivable see it immediately *above* the paragraph. Funny thing is that in VSE you *have* to put it in the procedure division. So I've taken to putting all of my DECLARE CURSOR statements immediately following the PROCEDURE DIVISION line. Bugs me, but hey... Not that I don't see your point, that it could be nice to have the cursor declaration near the FETCH. I'm just concerned about the confusion it may cause to less knowledgeable programmers. >>...... >> PROCEDURE.... >> exec sql >> FETCH NEXT CURSOR-X >> into :T2-NAME, :T2-DEPT >> for 100 rows >> end-exec >> >>It seems that Windows/Unix versions do not allow multiple fetch/insert >>rows. Pitty. > > Hard to believe. I think the non-singleton select caused the error. You're welcome to try it yourself, or at least provide an example that you think would work and I could try it. Frank
From: Frank Swarbrick on 1 Feb 2008 13:56
>>> On 1/30/2008 at 8:25 PM, in message <jof2q31shvbu6d9ia1i7gelptabq9slahe(a)4ax.com>, Robert<no(a)e.mail> wrote: > On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick" > <Frank.Swarbrick(a)efirstbank.com> > wrote: > >>-------------------------------------------------------------------- >> SQL0060W The "COBOL" precompiler is in progress. >> 19 SQL0008N The token "occurs" found in a host variable >> declaration is not valid. >> 41 SQL4943W The number of host variables in the INTO clause >> is not the same as the number of items in the SELECT >> clause. SQLSTATE=01503 > > The manual says DB2 doesn't allow a simple SELECT to return multiple > rows. You must do the > SELECT in a cursor declaration and read the table with a single FETCH, > as illustrated by > Frederico. > > Thanks for the experiment. Still no love. DB2 does not like the OCCURS. Here's my latest test: IDENTIFICATION DIVISION. PROGRAM-ID. tabtest. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SPECIAL-NAMES. CONSOLE IS CONSOLE . DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 table2-area. 05 table2-entry occurs 1 to 100 depending on sqlerrd(3). * 05 table2-entry. 10 t2-name pic x(30). 10 t2-dept pic x(04). EXEC SQL END DECLARE SECTION END-EXEC 77 ERRLOC PIC X(80) VALUE SPACES. exec sql declare t2_curs cursor for select name, dept from test.table2 where name in (select distinct name from test.table1) end-exec PROCEDURE DIVISION. CONTINUE. MAIN. CALL 'connect' perform open-t2-curs PERFORM get-into-table perform close-t2-curs PERFORM DB-DISCONNECT STOP RUN. get-into-table. compute sqlerrd(3) = length of table2-area / length of table2-entry exec sql fetch t2_curs into :table2-entry end-exec CALL 'checkerr' USING SQLCA ERRLOC display 'There are ' sqlerrd(3) ' departments.' exit. open-t2-curs. exec sql open t2_curs end-exec exit. close-t2-curs. exec sql close t2_curs end-exec exit. DB-DISCONNECT. MOVE 'DB-DISCONNECT' TO ERRLOC EXEC SQL CONNECT RESET END-EXEC CALL 'checkerr' USING SQLCA ERRLOC EXIT. END PROGRAM tabtest. And the results: DB20000I The SQL command completed successfully. prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN yes EXPLSNAP yes LINE MESSAGES FOR tabtest.sqb ------ -------------------------------------------------------------------- SQL0060W The "COBOL" precompiler is in progress. 19 SQL0008N The token "occurs" found in a host variable declaration is not valid. 51 SQL0306N The host variable "TABLE2-ENTRY" is undefined. SQL0095N No bind file was created because of previous errors. SQL0091W Precompilation or binding was ended with "3" errors and "0" warnings. I also tried this and got the same error 05 table2-entry occurs 100. Now of course you can remove the occurs, but then you're stuck with fetching one row at a time. I see no way to do a multi row (at once) fetch using DB2/LUW. More thoughts would be welcome. Frank |