SQL or Procedure Returns No Output

  • 7021542
  • 17-Aug-2006
  • 02-Mar-2018

Environment

Verastream Host Integrator

Situation

When executing a SQL query (SELECT statement) or table procedure, no output may be returned.

Resolution

In the Design Tool, run the Procedure Test and SQL Test debug tools. Step through the tests.

Situation I: Procedure Value Verification

When Host Integrator processes a table procedure, value verification is performed. If the procedure has a column mapped as both a filter parameter and output parameter, but these values do not match (the column value has changed during execution of the procedure), then the output data is discarded and the procedure returns no data.

Solutions

Use one of the following methods to resolve this issue.

  • Map the output parameters to the filter parameters: If the output value you want returned is identical to the value provided in the input filter, map the output parameter to the filter parameter. In the table procedure, select the output parameter, click Advanced, and then map the output parameter to the filter parameter.
  • Create different names for the parameters: In the table, create unique column names for each parameter (for example, AcctNumberIn and AcctNumberOut). In the table procedure, map one column as a filter parameter and the other column as an output parameter. The same attribute can still be mapped to each parameter.

Situation II: SQL Post-Fetch Filtering

When the Host Integrator receives an SQL query, it determines which procedure or set of procedures will be used to satisfy the query, and then executes those procedures. For SELECT statements, any resulting data that does not exactly match the WHERE clause is thrown out during a process known as post-fetch filtering. This match comparison is case sensitive.

Solutions

Use one of the following alternatives to resolve this issue:

  • Use LIKE operator: In the SQL statement WHERE clause, use the LIKE operator (instead of = operator) to disable post-fetch filtering on a per-column basis. Post-fetch filtering is not performed for LIKE expressions. (This implementation of LIKE diverges from the SQL-92 standard.)
  • Perform procedure instead of SQL: Instead of using a SQL statement, execute a procedure directly (which bypasses procedure resolution and post-fetch filtering). In the Design Tool, use Procedure Test. In your client code, use the PerformTableProcedure method.

Additional Information

Legacy KB ID

This article was originally published as Attachmate Technical Note 10062.