Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Highlighted
pavlli Contributor.
Contributor.
574 views

Unknown number of host variables in embedded SQL

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

Tags (2)
0 Likes
6 Replies
The COBOL Kid Absent Member.
Absent Member.

RE: Unknown number of host variables in embedded SQL

Hi,

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?

Sincerely,

The COBOL Kid

0 Likes
pavlli Contributor.
Contributor.

RE: Unknown number of host variables in embedded SQL

I got error: There is an SQL syntax error near ':' :-(
0 Likes
The COBOL Kid Absent Member.
Absent Member.

RE: Unknown number of host variables in embedded SQL

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

Sincerely,

The COBOL Kid

0 Likes
pavlli Contributor.
Contributor.

RE: Unknown number of host variables in embedded SQL

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

0 Likes
The COBOL Kid Absent Member.
Absent Member.

RE: Unknown number of host variables in embedded SQL

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

Sincerely,

The COBOL Kid

0 Likes
Not applicable

RE: Unknown number of host variables in embedded SQL

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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.