Portlet Factory, Version 6.1.2
Schema generation limitations with SELECT statements
When using the SQL Call builder to execute a SELECT statement, there are two scenarios in which the SELECT statement as entered in the SQL Statement input cannot be used to generate an XML schema for the returned result set. The first scenario involves SQL Statement inputs that contain one or more indirect references. Because the builder generates an XML schema for the result set at model regeneration time, the builder needs to know the final form of the SELECT statement at regeneration. However, an indirect reference in the SQL Statement input implies that some part of the SELECT statement will not be determined until runtime. As a result, the SQL Call builder does not allow a SQL Statement input with indirect references to be used for schema generation. You can work around this limitation by selecting From Alternate SQL Statement as the value of the Schema Generation input. Then enter into the Alternate SQL input a copy of your SELECT statement that does not contain any indirect references. When configured in this way, the SQL Call builder will use the content of the Alternate SQL input to generated the XML schema at regen time and use the content of SQL Statement at model execution (runtime).
The second scenario involves SQL Statement inputs that use positional parameters that correspond to the JDBC column types of ARRAY, BLOB, CLOB, DATE, DISTINCT, JAVA_OBJECT, OTHER, REF, and STRUCT. At regeneration time the SQL Call builder generates an XML schema for the SELECT's result set by generating and examining a small result set that has the same columns as the result set that will be generated at execution. The builder attempts to create sample values for each positional parameter before generating this sample result set. However, it is not possible for the builder to create sample values for the types listed. The solution to this problem is to select From Alternate SQL Statement as the value of the Schema Generation input. Then enter into the Alternate SQL input a copy of your SELECT statement that does not contain any positional parameters.
Parent topic: SQL Call builder
Library | Support |