Highlighted
Absent Member.
Absent Member.
1737 views

OpenESQL and EXEC ADO

Jump to solution

Hi, I'm facing this thing I don't know what to call it -a bug or whatsoever- I have this code which is self explanatory! I am receiving rows form the database using the EXEC ADO statements in order to bind the dataset to a GridView using WebForms so whenever I call this program I will get the same result of the queries -inside the cursor- which in all-customers section but, when I change the cursor name I get the appropriate result of each. I know that OpenESQL is a pre-compiler but even though it behavior is to compile SQL before compiling the code, and the generate the appropriate native code why doesn't it override my cursor a.k.a custoemrscursor since I will only access one section of the code each time the program is called due to the nature of the EVALUATE statement? 

 

Here is the code:

      

program-id. CustomersReports as "BusinessLogicModel.BusinessLogic.CustomersReports".


      

data division.

      

working-storage section.

      

01  customers-dataset    type    System.Data.DataSet.

      

      

exec sql include sqlca end-exec.

      

      

linkage section.

      

01 report-name      is      string.

      

01 report-term      is      string.

      

01 result-set       type    System.Data.DataSet.


      

procedure division using report-name, report-term returning result-set.

      

   

      

    copy 'DatabaseConnection'.

      

   

      

    evaluate report-name

      

        when 'all-customers'

      

            perform all-customers

      

        when 'customers-by-country'

      

            perform customers-by-country

      

    end-evaluate.

      

   

      

    goback.

 

      

      

all-customers.

      

    exec sql declare customerscursor dataset cursor for

      

        select customer_id, customer_name, country

      

        from customer

      

    end-exec.

      

   

      

    exec sql

      

        open customerscursor

      

    end-exec.

      

   

      

    exec ado get cursor customerscursor into :customers-dataset

      

        rename datatable as allcustomers

      

    end-exec.

      

   

      

    set result-set to customers-dataset::Copy.

      

   

      

    exec sql

      

        close customerscursor

      

    end-exec.

      

   

      

    exec sql

      

        disconnect current

      

    end-exec.

      

      

customers-by-country.

      

    exec sql declare customersbycountry dataset cursor for

      

        select *

      

        from customer

      

        where country = :report-term

      

    end-exec.

      

   

      

    exec sql

      

        open customersbycountry

      

    end-exec.

      

    exec ado get cursor customersbycountry into :customers-dataset

      

        rename datatable as customersbycountry

      

    end-exec.

      

   

      

    set result-set to customers-dataset::Copy.

      

   

      

    exec sql

      

        close customersbycountry

      

    end-exec.

      

   

      

    exec sql

      

        disconnect current

      

    end-exec.

      

      

   

      

end program CustomersReports.

0 Likes
2 Solutions

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Ahmet,

I am assuming that the code posted here is the version that works correctly because the cursor names are different in each paragraph, is that correct?

Cursor names must be unique within a program. In fact if you declare a cursor with the same name more than once then you should get an error from the precompiler ES0100-Duplicate cursor name.

The precompiler goes through the source code looking for blocks of code between EXEC SQL and END-EXEC and it does not take into account the logic of your evaluate statements.

If you want to define a cursor once and then reference it using different select statements then you would need to use dynamic sql.

Example:

   exec sql
       declare customerscursor dataset cursor for stmt1
   end-exec

all-customers.

    move "select customer_id, customer_name, country from customer" to prep
    exec sql
       prepare stmt1 from :prep
    end-exec

    exec sql
       open customerscursor
    end-exec
...

customers-by-country.

    move "select * from customer where country = ?" to prep

    exec sql
       prepare stmt1 from :prep
    end-exec

    exec sql
       open customerscursor using :report-term
    end-exec

...

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Yes, you do not have to do anything special to support dynamic SQL.

Please see the docs that cover this here:

There is also a recording that covers using dynamic SQL with OpenESQL here:

Thanks.

View solution in original post

0 Likes
3 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi Ahmet,

I am assuming that the code posted here is the version that works correctly because the cursor names are different in each paragraph, is that correct?

Cursor names must be unique within a program. In fact if you declare a cursor with the same name more than once then you should get an error from the precompiler ES0100-Duplicate cursor name.

The precompiler goes through the source code looking for blocks of code between EXEC SQL and END-EXEC and it does not take into account the logic of your evaluate statements.

If you want to define a cursor once and then reference it using different select statements then you would need to use dynamic sql.

Example:

   exec sql
       declare customerscursor dataset cursor for stmt1
   end-exec

all-customers.

    move "select customer_id, customer_name, country from customer" to prep
    exec sql
       prepare stmt1 from :prep
    end-exec

    exec sql
       open customerscursor
    end-exec
...

customers-by-country.

    move "select * from customer where country = ?" to prep

    exec sql
       prepare stmt1 from :prep
    end-exec

    exec sql
       open customerscursor using :report-term
    end-exec

...

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

Thank you very much this pretty much answers my question, nevertheless, would this code work as is on the same project properties? i.e. do I need any config changes?

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Yes, you do not have to do anything special to support dynamic SQL.

Please see the docs that cover this here:

There is also a recording that covers using dynamic SQL with OpenESQL here:

Thanks.

View solution in original post

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.