SQL in COBOL: Need help with fetching data (more than one row) 1

[Wiezewazoel]

Hello,

I got this little issue here.

I am using a mysql database, i made the connection i can use sql.
I am using Legacyj Percobol
There is not a single problem when i for example do this:

EXEC SQL 
                Select Sum(a.duur) into :tijd
                From project p
                        Join iteratie i On p.projectID = i.projectID
                        Join taak t On i.iteratieID = t.iteratieID
                        Join activiteit a On t.taakID = a.taakID
                Where p.projectID = :prjnr
END-EXEC

What i mean is that i have no problem getting one row-data

But ofcourse you might have already guessed, i am having trouble with getting multiple rows and putting them into variables in cobol.

I have tried like alot of times so far.
The last try I have gotten from an example, which works with the cursor and "batch-arrays" or something.
The example i used for this:

The example (scroll down) with the name: arrays_cur

First of all you will want to see the starting working section and declare section:

DATA DIVISION.
WORKING-STORAGE SECTION.
 01 prjnr       pic 999.
   88 bestaat   value 1 thru 999.
 01 tijd        pic Z9v.99.
 01 keuze       pic x.
   88 fout      value 'a' thru 'm', 'o' thru 'x', 'z'.
   88 juist     value 'n', 'y'.
   88 ja        value 'y'.
   88 nee       value 'n'.
 01 keuzegeg    pic 9 value 0.
   88 ok                value 1 thru 4.
   88 prj               value 1.
   88 activiteit        value 2.
   88 medewerker        value 3.
   88 taak              value 4.
   88 nok               value 0, 5 thru 9.

This piece, you don't have to mind, its just some options and so

 01 teller              pic 9(3) COMP.
 01 aantalActen         pic 9(3) COMP.

 linkage section.
 01 projectnr           pic 9(3).
   88 geenParam         value null.
   88 welParam          value 1 thru 999.

EXEC SQL BEGIN DECLARE SECTION END-EXEC

* SQLCODE is 0 for success, 100 for no data, -1 for failure
77 SQLCODE PIC S9(3). 

* SQLSTATE is a 5 character communication code; 00xxx is success.
77 SQLSTATE PIC X(5). 

01 hetID             pic 9(3) occurs 5 times.
01 deDuur            pic 99v99 occurs 5 times.

01 hetID-IND         pic S9(4) COMP occurs 5 times.
01 deDuur-IND        pic S9(4) COMP occurs 5 times.

01 rows              pic 9(3) COMP.

EXEC SQL END DECLARE SECTION END-EXEC

PROCEDURE DIVISION USING projectnr GIVING tijd.
MAIN-PARAGRAPH.

Sorry for the dutch, but well we have to do that...they are just variables, i'll explain the most important ones:

The table is called "activiteit" which means i will keep "a log" of all the activities someone has done. So it has an ID and a duration

teller ==> counter
hetID ==> is the ID i will retrieve from the table
deDuur ==> is the duration inside a table i want to retrieve, its a decimal there
hetID-IND ==> is the indicator for hetID
deDuur-IND ==> is the indicator for deDuur
aantalActen ==> Is the amount of rows i will have or something
rows ==> Used as counter

BTW: I do NOT have sqlca.cbl or something, i don't have a clue where to find that!

Extra information: this cobol program will be called from JAVA, but i got the link worked so np, just in case u wondered why i have params entering my program and giving a param back

Now you wonder, where the heck is his problem? well here:

PERACTIVITEIT.
/        EXEC SQL 
/                Select Count(*) into :aantalActen
/                From project p
/                        Join iteratie i On p.projectID = i.projectID
/                        Join taak t On i.iteratieID = t.iteratieID
/                        Join activiteit a On t.taakID = a.taakID
/                Where p.projectID = :prjnr
/        END-EXEC

Yeah i messed around a bit, but the first comment part which has /'s in front of it, WORKS just fine! It retrieves the amount of activities inside a project (projnr)

        EXEC SQL
                DECLARE actcrs CURSOR FOR
                Select a.activiteitID, a.duur
                From project p
                        Join iteratie i On p.projectID = i.projectID
                        Join taak t On i.iteratieID = t.iteratieID
                        Join activiteit a On t.taakID = a.taakID
                Where p.projectID = :prjnr
                Order by a.activiteitID
        END-EXEC
        
        EXEC SQL
                OPEN actcrs
        END-EXEC
        
        DISPLAY "ActiviteitID                       Duur".
	DISPLAY "------------	                    ----".
	
	PERFORM TYPE-LOOP UNTIL  SQLCODE < 0.

The sql statement Works just fine!

*        EXEC SQL
*                FETCH actcrs INTO :activiteiten
*        END-EXEC
        
*        EXEC SQL 
*                Select a.activiteitID, a.duur into :hetID, :deDuur
*                From project p
*                        Join iteratie i On p.projectID = i.projectID
*                        Join taak t On i.iteratieID = t.iteratieID
*                        Join activiteit a On t.taakID = a.taakID
*                Where p.projectID = :prjnr
*                Order by a.activiteitID
*        END-EXEC
        
*        Perform Varying teller From 1 By 1 Until teller > aantalActen
*                Display "ActiviteitID: " + hetID
*                Display "Aantal uur: " + deDuur(teller)
*                Display "---"
*        End-Perform
.

This was to try if it worked like i did to retrieve only one row of data.

TYPE-LOOP.
        Move 1 to rows
        Move 1 to teller
        
        PERFORM INITIALIZE-LOOP UNTIL teller > 5
        
[red]=======>[/red]EXEC SQL Fetch
                actcrs INTO 
                        :hetID        :hetID-IND, 
                        :deDuur       :deDuur-IND
        END-EXEC.

        if SQLCODE = 100
		DISPLAY "ALL ROWS HAVE BEEN FETCHED!"
		STOP RUN
	ELSE
		PERFORM PRINT-LOOP UNTIL rows > aantalActen.
.

INITIALIZE-LOOP.
	   MOVE "NULL" to hetID(teller).
	   MOVE "NULL" to deDuur(teller).
	   MOVE -1 to hetID-IND(teller).
	   MOVE -1 to deDuur-IND(teller).
	   ADD 1 TO teller.
.

Now in this last piece of code, it gives 2 same errors, i have been looking at like for hours now:

Reserved word or improper variable in place of SQL indicator variable.

I have marked a red arrow where he gives the error ("=======>")!

I have not a single clue where the error could be! because I don't think i'm using improper var or reserved word there :s

Please if you don't understand something about it, because its dutch or something else, do not fear to ask!

 

[Glenn9999]

SQL databases require atomic data values to work with. The values you are using in your FETCH statement are not.

01 hetID             pic 9(3) occurs 5 times.
01 deDuur            pic 99v99 occurs 5 times.

01 hetID-IND         pic S9(4) COMP occurs 5 times.
01 deDuur-IND        pic S9(4) COMP occurs 5 times.

Treat each record individually in your logic and don't try to treat them as a group. Also, check the definitions of the non-indicator variables - they do not match up to any common database definition.

01 hetID             pic 9(3).
01 hetID-IND         pic S9(4) COMP.
01 deDuur            pic 99v99.
01 deDuur-IND        pic S9(4) COMP.
01 EOF-CODE          PIC X VALUE 'N'.

* declare and open cursor here, yadda yadda.
PERFORM 1000-LOAD-DATA UNTIL EOF-CODE = 'Y'.
* close cursor here.

1000-LOAD-DATA
    EXEC SQL Fetch
         actcrs INTO 
             :hetID        :hetID-IND, 
             :deDuur       :deDuur-IND
    END-EXEC.
    IF SQLCODE = 100
       MOVE 'Y' to EOF-CODE
    else
       PERFORM PRINT-LOOP
    END-IF.

Or some such thing.

 

[Wiezewazoel]

Ah it works perfectly! really big thx

yea i guess i "did" have to work like i do one row at a time

and about the definitions ... i know they aint correct:

the "hetID" (ID - unique - not null) is an INT(5) inside database so that would make it 9(5) right? but the problem is...it always shows the leading zero's...if i work with Z(5) or something, then it get messed up. I didn't want to cause more problems

the "deDuur" (not unique - not null) is a Decimal(5,2) inside the database, they said i should make it COMP-2 or something but again, same problem...damn leading zero's

ofcourse i have forgotten how to get rid of those zero's without having an ugly layout like:
Before:
number 1: 001
After:
number 1: 1

but the main problem was I think, that i didn't understand how he went to the following row in the thing i used.

But your example shows it where...the perform right?

but still its weird code to me...nowhere a counter, etc etc

It works great, big big thx, i looked at that like for a whole day yesterday.

Greets,
wieze

 

[Glenn9999]

INT(5) inside database so that would make it 9(5) right? but the problem is...it always shows the leading zero's...if i work with Z(5) or something, then it get messed up. I didn't want to cause more problems

This may be a specific mysql definition (I've seen it in none of the documentation I've encountered).

9(5) is a text representation of numeric values. Z(5) is text representation of numeric values with the zeros suppressed. To go from a numeric to a display value (which is what Z(5) is), simply move the value from one variable to another.

Decimal(5,2) inside the database, they said i should make it COMP-2 or something but again, same problem...damn leading zero's

PIC S9(3)v99 COMP-3. To display, deal with it as was described above. PIC ZZ9.99 will do it.

but the main problem was I think, that i didn't understand how he went to the following row in the thing i used.

1000-LOAD-DATA is a paragraph performed in a loop. With SQL you don't know how many rows will be returned (unless you check before-hand - you really shouldn't though) - so the SQLCODE of +100 is the control variable. The loop keeps processing until you run out of records.

 

[Wiezewazoel]

ah thanks,

by the way you don't happen to know a way to return a table back to a program?

I thought of an idea like this, in my Working-Storage Section:

 01 activiteiten occurs 1 to 10000 depending on teller.
   04 activiteitID      pic 9(5).
   04 activiteitDuur    pic 999v99.

so I made a counter (here: "teller") that with each feth, it adds 1.
and then I would place the fetched data each time into this array.
and give back "activiteiten" at

PROCEDURE DIVISION USING projectnr GIVING tijd, activiteiten.

i would leave the "tijd" there because well my program has 4 functions, which 3 are similar output
so at option 1 it gives the whole duration ("tijd") of a project.
and option 2 to 4 it gives for each activity, employee, task the duration. but these 3 all have same size ID, and same size duration, so i can recycle that.

but for some reason it doesn't allow me? it sais it isn't expecting an identifier there. so i tried to put only "activiteiten" there, but then it sais the same so i conclude i can't give back an array?

 

[Glenn9999]

Well, you have two problems with what you're trying to do:

1) Your table is defined as an ODO, which means COBOL needs to know what "teller" is, along with the table.
2) You can't address a table without having a subscript in it as well.

Group the values if you insist on passing back all the data - in fact grouping is best for more than two or three data times in a subprogram. But it is best to just work one record at a time and call it done when it comes to SQL tables, depending on what the precise requirements are.

01  Group-data.
    04  teller              pic s9(5) binary.
    04 activiteiten occurs 1 to 10000 depending on teller.
       08 activiteitID      pic 9(5).
       08 activiteitDuur    pic 999v99.

 

[Wiezewazoel]

oh yeah, thx! it worked alright!

in java i just had to split it up again!

which worked fine ... really thx...now the program is finished nad i know how to do it all

big thx