Problem solve Get help with specific problems with your technologies, process and projects.

Defining a cursor based on user-populated input fields

I am developing an online application in COBOL DB2 on the mainframe whose cursor definition (the where clause) is dependent on what fields are populated by the user. I have 13 input fields, and a user might populate a minimum of 3 fields depending on available input. Another user might populate 6 fields. I need a solution that will allow me to define a cursor based on input fields that have been populated. In other words, can I define my cursor (the where part) after checking what fields have been populated and how, can you also give me an example? I can supply you with the format of my screen.

Basically what you are describing is the classic example for using dynamic SQL. There are multiple types and variations...

to implementing dynamic SQL statements, but for your case you should read up on and implement varying-list SELECT dynamic SQL. You can use a varying-list dynamic SELECT to explicitly prepare and execute SQL SELECT statements when you do not know in advance which columns will be retrieved by an application program. Varying-list SELECT provides the most flexibility for dynamic SELECT statements. You can change tables, columns, and predicates "on the fly."

Because everything about the query can change during one invocation of the program, the number and type of host variables needed to store the retrieved rows cannot be known beforehand. The lack of knowledge regarding what is being retrieved adds considerable complexity to your application programs.

The steps needed to code varying-list SELECT dynamic SQL will vary according to the amount of information known about the SQL beforehand. In general, though, you will need to build your SQL statement from the input given through your online screen. Only the columns for which values have been input should be built into your SQL WHERE clauses. Then you would move that SQL statement to a string variable, declare a cursor and then prepare that cursor into the SQLDA. Following that you would load the appropriate storage addresses into the SQLDA and then fetch the results from the cursor referencing the SQLDA in the FETCH statement.

I cannot teach all of the nuances of dynamic SQL in a short answer to a question in this type of forum. Keep in mind that to implement a dynamic varying-list SELECT you will need to learn about the PREPARE statement, the SQLDA, using FETCH with the SQLDA, and how to store host variable addresses in the SQLDA. Read up about this "stuff" in Chapter 23 of the IBM DB2 V7 manual named "Application Programming and SQL Guide" (SC26-9933) which can be downloaded from the IBM Web site for free.

For More Information

This was last published in December 2002

Dig Deeper on IBM DB2 management



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.