SQL_QUERY

LANSA Composer

SQL_QUERY

This activity queries a database using an SQL SELECT statement and receives the results into processing sequence variable lists.

This activity does not support the use of SQL parameter markers (usually designated by a question mark) in the SQL statement.  If you need the SQL statement to be variable (for example, selection values in a WHERE clause), then you must dynamically construct the SQL statement to include the variable values as constants.

For more information about the SQL database activities, refer to the description of the SQL_CONNECT activity.  For a list of supplied working (*) examples using the SQL database activities, refer to:

Example Processing Sequences using the SQL database activities

INPUT Parameters:

SQLHANDLE : Required

This parameter must specify the connection handle value that identifies the SQL connection upon which this activity should operate.  The connection handle value is returned by the SQL_CONNECT activity.

SQLQUERY : Required

This parameter must specify the SQL SELECT statement that will execute on the target database for the query.  The following are examples of SQL SELECT statements that might be specified in this parameter:

select distinct bchnum from tutordh

select bchnum, ordnum, cusnum, orddat from tutordh
where bchnum = '12345687890'

Since processing sequence variables are restricted to 256 characters, a single value may not be sufficiently large to hold some queries.  For this reason, this parameter is defined as a variable list.  This means that you can split your long SQL statement into more than one part and provide the parts in this parameter using a variable list.  If you do this, then the SQL_QUERY activity will re-assemble them into a single statement before execution.

SQLMAXROWS : Optional

This parameter may specify a maximum number of rows to be returned.  This guards against the possibility of SELECT statements that select much more data than was intended.

If not specified, a default value of 100 (one hundred) is used.  Remember that the SQL database activities are not intended and not usually suitable for high-throughput, high-volume database operations.  If you expect that your query will return a large number of records, then you should possibly consider an alternate implementation, such as using a Transformation Map.

OUTPUT Parameters:

SQLROWS :

Upon successful completion, this parameter will contain the actual number of rows returned by the query.

SQLCOLUMN1
SQLCOLUMN2


SQLCOLUMN25

These output lists will contain a list of the values for the corresponding column for each row selected by the query.

You should specify the name of a variable list that will contain the values for each column used in your query, up to a maximum of 25.

You may specify fewer output lists than in your query, however, if you specify more, then a run-time error will occur, for example

ERROR – Descriptor index not valid, or
ERROR – The index 10 is out of range