|
|
|
SQL or Procedure Returns No Output
Technical Note 10062
Last Reviewed 07-Sep-2006
Applies To
Verastream Host Integrator version 5.5 or higher
Summary
Technical Note 10062
Last Reviewed 07-Sep-2006
Applies To
Verastream Host Integrator version 5.5 or higher
Summary
When executing a SQL query (SELECT statement) or table procedure, no output may be returned. This technical note describes how to troubleshoot and resolve this problem.
Troubleshooting No Output
In the Design Tool, run the Procedure Test and SQL Test debug tools. Step through the tests (see also Technical Note 10018 for more information).
- If the Output tab shows data but the data is discarded when the Procedure Test is completed, proceed to Situation I: Procedure Value Verification.
- If the Output tab shows data and the data is discarded by SQL Test but not the Procedure Test, proceed to Situation II: SQL Post-Fetch Filtering.
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.
