COBSQL & COBSQL2 allow you  to pass in a SQL SELECT statement using PARM1-6. These programs are identical apart from that fact that COBSQL will concatenate all the parameters without adding a blank between them and COBSQL2 will add a blank.

Note: The source for these two programs may be found in QUSRTOOLLE source file in the CPPD library.

How the program works

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

•Now COBSQL will concatenate them all into one long string whereas COBSQL2 will concatenate them with a space between each parameter.

•Having build up the SQL SELECT statement the original input parameters are cleared.

•The program then executes this string as an SQL statement

•The returned values from the SQL statement are returned into PARM1-PARM6.

Some Notes:

•You can split the input SQL string across PARM1 - PARM6 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 - also 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.

Example using the Test Harness

This example is using the Test Harness to return the fields from the Cobwebb Queue for a specific entry. Notice we have added a blank character on the front of Parameters 2 & 3 as we are using COBSQL and otherwise the statement would not work.

This would build up the SQL Statement:

SELECT TQSTS, TQPRT, TQUSR, TQTO, CAST(TQDATS AS VARCHAR(132)) FROM AFAXQ WHERE TQREF='E000003276'

Here is the result:

Using Multiple Variable Substitution

If you want to use Multiple Variable Substitution it could make your SQL statement even easier. For instance if you wanted to retrieve data from the Cobwebb General Directory and you stored the input for the three key field in variables called INPUT1, INPUT2 and INPUT3 then you could use the following string as your PARM1 into COBSQL e.g.

 SELECT  GDDATA FROM AGENDIR WHERE GDKEY1='?INPUT1?' AND GDKEY2='?INPUT2?' AND GDKEY3='?INPUT3?'

The general data field GDATA will be returned in PARM1.