OpenESQL and EXEC ADO

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.

  • Verified Answer

    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

    ...

  • 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?

  • Verified Answer

    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.