I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1Hi,
think about
MOVE "A" TO S-A
MOVE "B" TO S-B
...
move 'declare my-crs1 cursor for select * from TABLE where X = :S-A AND Y = :S-B' to W-SQL
exec sql .... end-exec
exec sql
open my-crs1
end-exec
fetch from cursor.
does this help?
I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1I got error: There is an SQL syntax error near ':' :-(
I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1It was only a sample for better understanding. OK. Here something that truly worked for me. Try to adapt for your problem.
01 DB-SQL PIC X(650).
01 AF-SQL1.
05 FILLER PIC X(07) VALUE "SELECT ".
05 FILLER PIC X(09) VALUE "A.ABSNR, ".
05 FILLER PIC X(08) VALUE "B.KDNR, ".
05 FILLER PIC X(10) VALUE "A.ABSTXT, ".
05 FILLER PIC X(30) VALUE "TO_CHAR(A.DATVON,'YYYYMMDD'), ".
05 FILLER PIC X(30) VALUE "TO_CHAR(A.DATBIS,'YYYYMMDD'), ".
05 FILLER PIC X(10) VALUE "A.PFFINR, ".
05 FILLER PIC X(10) VALUE "A.ABSSTAT ".
05 FILLER PIC X(05) VALUE "FROM ".
05 FILLER PIC X(30) VALUE "OFFER_MANAGEMENT.V_ABS_BAS A, ".
05 FILLER PIC X(31) VALUE "OFFER_MANAGEMENT.V_ABS_KUNDE B ".
05 FILLER PIC X(06) VALUE "WHERE ".
05 FILLER PIC X(18) VALUE "A.ABSNR = B.ABSNR ".
05 FILLER PIC X(15) VALUE "AND A.ABSNR >= ".
05 FILLER PIC X(17) VALUE ":V-ABS-BAS-ABSNR ".
05 FILLER PIC X(23) VALUE "AND B.ABSPFLKDNR = 'M' ".
05 FILLER PIC X(16) VALUE "ORDER BY A.ABSNR".
MOVE AF-SQL1 TO DB-SQL
*--> for PREPARE the AT DBO is mandatory on MF and disallowed for NX51
EXEC SQL AT DBO
PREPARE CRS8 FROM :DB-SQL
END-EXEC.
IF SQLCODE NOT = 0
IF SQLCODE NOT = 1403
PERFORM SQL-FEHLER
end-if
DISPLAY with conversion
"TPREP01: invalid cursor select " SQLCODE
end-if
*
MOVE 80000002 TO V-ABS-BAS-ABSNR
*--> for OPEN (dynamic PREPARED!) the AT DBO is mandatory on MF and disallowed for NX51
*--> (55 Herr Horn, 2011 07 14, LexDBDynProbHorn55)
EXEC SQL OPEN CRS8 using :V-ABS-BAS-ABSNR END-EXEC.
IF SQLCODE NOT = 0
IF SQLCODE NOT = 1403
PERFORM SQL-FEHLER
end-if
DISPLAY with conversion
"TPREP01: invalid cursor open " SQLCODE
end-if
*
MOVE kFlse TO fEOD
MOVE ZERO TO wFetchDone
MOVE ZERO TO wFetchTo
MOVE ZERO TO wFetchCnt
PERFORM until fEOD = kTrue
EXEC SQL
FETCH CRS8 INTO
:V-ABS-BAS-O-ABSNR
,:V-ABS-KUNDE-O-KDNR
,:V-ABS-BAS-O-ABSTXT :V-ABS-BAS-O-ABSTXT-NULL
,:V-ABS-BAS-O-DATVON :V-ABS-BAS-O-DATVON-NULL
,:V-ABS-BAS-O-DATBIS :V-ABS-BAS-O-DATBIS-NULL
,:V-ABS-BAS-O-PFFINR
,:V-ABS-BAS-O-ABSSTAT
END-EXEC
IF SQLERRD(3) = ZERO
DISPLAY "TPREP01: fetch NO DATA FOUND"
EXIT PERFORM
end-if
*
DISPLAY with conversion "TPREP01: SQLERRD3 " SQLERRD(3)
upon terminal
DISPLAY with conversion "TPREP01: SQLCODE " SQLCODE
upon terminal
DISPLAY with conversion "TPREP01: SQLSTATE " SQLSTATE
upon terminal
*
COMPUTE wFetchTo = SQLERRD(3) - wFetchDone
ADD SQLERRD(3) TO wFetchDone
PERFORM varying wFetchCur FROM 1 by 1
until wFetchCur > wFetchTo
ADD 1 TO wFetchCnt
MOVE V-ABS-BAS-O-ABSTXT-LEN (wFetchCur) TO wTextLg
DISPLAY with conversion
"TPREP01: " wFetchCnt
" abstxt-len "
V-ABS-BAS-O-ABSTXT-LEN (wFetchCur)
" abstxt-arr "
V-ABS-BAS-O-ABSTXT-ARR (wFetchCur) (1:wTextLg)
upon terminal
end-perform
*
IF SQLCODE = 1403
MOVE kTrue TO fEOD
end-if
end-perform
EXEC SQL CLOSE CRS8 END-EXEC
IF SQLCODE NOT = 0
IF SQLCODE NOT = 1403
PERFORM SQL-FEHLER
end-if
DISPLAY with conversion
"TPREP01: invalid cursor close " SQLCODE
end-if
.
I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1Thanks for example, but I think it's still doesn't solve my problem.
You are using this:
EXEC SQL OPEN CRS8 using :V-ABS-BAS-ABSNR END-EXEC.
Because I don't know how many host variables I will have in where condition. I have 5 searching criteria, and I don't know which ones user will select (and it can be selected more than one). And of course I dont' want to write all 5 * 5 possible combinations.
I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1Hmm, sounds more complex. We have one similar case which a colleague solved completely different.
He wrote a function in Oracle to accomplish this. The problem was a update of 1 to 6 fields. The COBOL program loads all 6 variables with either data or space and then calls the Oracle function providing all 6 variables. The function then can decide how to handle the input and the return. Maybe this gives you more ideas, i do not have a sample code for this.
I have this code:
move 'select * from TABLE where a = ? and b = ? and c = ? to w-sql
exec sql
open cur_read_2 using :w-a,
:w-b,
:w-c
end-exec
But this select can be variable, so I can have here for example only
... where a = ? ...
or
... where a = ? and c = ? ...
So, can I somehow setup exec-sql part dynamically, to have proper number of host-variables here? In actual case I have 7 WHERE variables and full list of combinations is possible. I suppose that I must use SQL DESCRIPTOR, but I don't know how...
#SQL#NetExpress5.1I think you must build dynamic SQL. Here is a short example, hope this helps.
Otherwise have a look in the Net Express Help, keyword "Dynamic SQL".
01 filler.
02 sql-dynamictxt pic x(2000).
*** your host variables:
02 hostvar-1 pic s9(9) comp-5.
02 hostvar-2 pic s9(9) comp-5.
02 hostvar-3 pic s9(9) comp-5.
cursor-open section.
move spaces to sql-dynamictxt
move what you need to hostvar-1
hostvar-2
hostvar-3
*** Build your dynamic SQL
*** In dynamic SQL do NOT use host variables directly,
*** instead use parameter markers => look at the ?
string
"select "
" field_a, "
" field_b, "
" field_x "
" from User.yourTablename"
" where field1 = ? "
" and field2 = ? "
" and field3 >= ? "
" order by something "
delimited by size
into sql-dynamictxt
end-string
***
declare YourCURSORname cursor for dynamic_sql
prepare dynamic_sql from :sql-dynamictxt
if sqlcode = 0
open YourCURSORname using
:hostvar-1,
:hostvar-2,
:hostvar-3
if sqlcode = 0
do what you want...
end-if
end-if
.
exit.
