Highlighted
Bart1 Absent Member.
Absent Member.
928 views

Can you declare more than 1 DYNAMIC_SQL cursor?

Jump to solution

Using DYNAMIC.CBL from \NETEXPRESS\Examples\Net Express IDE\ODBCESQL as an example, I have written a program to test dynamic SQL against a SQL Server Database.  I was wondering if there is a way to declare more than 1 DYNAMIC_SQL cursor?  

I first coded and tested retrieving data from 1 table using the following cursor:

EXEC SQL
   DECLARE VCURS CURSOR FOR DYNAMIC_SQL
END-EXEC

Then, I added a second cursor to retrieve data from another table in the same database:

EXEC SQL
   DECLARE VCURS CURSOR FOR DYNAMIC_SQL
END-EXEC

EXEC SQL
   DECLARE cursortest CURSOR FOR DYNAMIC_SQL
END-EXEC

However, it fails compile with the following error: "ES0100 Duplicate cursor name: cursortest"

I am using NetExpress 5.1 version f.110.0022 and SQLServer 2012.

0 Likes
1 Solution

Accepted Solutions
Not applicable

RE: Can you declare more than 1 DYNAMIC_SQL cursor?

Jump to solution
DYNAMIC_SQL is not a keyword, it is just the name of the SQL statement within the declare cursor and this name has to be unique as well as the cursor name. So in your example the second declare should be:
declare cursortest cursor for dynamic_sql_test
3 Replies
Not applicable

RE: Can you declare more than 1 DYNAMIC_SQL cursor?

Jump to solution
DYNAMIC_SQL is not a keyword, it is just the name of the SQL statement within the declare cursor and this name has to be unique as well as the cursor name. So in your example the second declare should be:
declare cursortest cursor for dynamic_sql_test
Bart1 Absent Member.
Absent Member.

RE: Can you declare more than 1 DYNAMIC_SQL cursor?

Jump to solution
Thank you, this was an excellent response!
0 Likes
Bart1 Absent Member.
Absent Member.

RE: Can you declare more than 1 DYNAMIC_SQL cursor?

Jump to solution
I have one more question that I think I know the answer to, but, I will ask anyway.

Can the cursor name or statement name be a host variable?
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.