Wiezewazoel
Programmer
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:
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:
This piece, you don't have to mind, its just some options and so
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!
Now you wonder, where the heck is his problem? well here:
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)
The sql statement Works just fine!
This was to try if it worked like i did to retrieve only one row of data.
Now in this last piece of code, it gives 2 same errors, i have been looking at like for hours now:
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!
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:
Code:
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:
Code:
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
Code:
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:
Code:
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)
Code:
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!
Code:
* 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.
Code:
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!