COBSQLM is similar to COBSQL in that it will concatenate all the parameters, without adding a blank between them, but it will allow you to return more than one record. From version 6.2.88.

Configure the SQL select statement

The '*FIRST' call allows you to specify the SQL SELECT statement that you wish to use to retrieve your data over PARM1 - 5. This would normally be called in a PAGE=*FIRST condition. This will set the SQL cursor ready to retrieve the records.

•Firstly the program trims any trailing blanks from PARM1 - PARM5.

•Now COBSQLM will concatenate them all into one long string.

Return the data

To return the data you do not need to specify any parameters (PARM6 must be blank), just call the program and the data will be passed back in PARM1-6.

Keep calling the program with blank in PARM6 to return all the records you want unless '*NOMORE' is returned in PARM1 to let you know that there are no more records to be returned.

•The data will be returned in PARM1-6 unless there are no more records to return when '*NOMORE' is returned in PARM1 or if there is an error running the SQL statement, when '*ERROR' is returned in PARM1 and the SQLSTATE value is returned in PARM2. Note: When either '*NOMORE' or '*ERROR' are returned the program is automatically ended and there is no need to call it with '*LAST' in PARM6.

End the program

The *LAST call will close the SQL cursor and end the program. This would normally be called in a PAGE=*LAST condition. It could also be called if you wanted to specify a different SQL SELECT statement to close the current cursor, and then you would call the program again with *FIRST to configure the new SELECT statement.

Some Notes:

•You can split the input SQL string across PARM1 - PARM5 as you see fit.

•You can use this to insert dynamic values from the spooled file or simply to clarify the SQL call.

•You must ensure any return values are string types or are cast to string types using CAST(VALUE_NAME AS CHAR(132))

•You can also populate your parameters with multiple Variable information - see Multiple Variable & Parameter Substitution.

•If you are accessing data from a file outside the CPPD library then you will need to either specify the library name in the SQL string e.g. "SELECT * FROM LIBRARYA/FILEB" or add the library to the list of libraries in your PPD see Calling an Exit Program for details.

•See examples of calling COBSQL from the Test Harness and using Multiple Variable Substitution for more information, as the select statements work in the same way.