2 Replies Latest reply on Feb 10, 2013 2:17 AM by Sanjeev Gour

    Teiid UDF with String return type limiting the return value to 4000 characters

    Sanjeev Gour Novice

      We have User Defined Function in teiid that take some input params like object name, where clause etc. and returns a string as an outparam which an SQL. The metadata of this function look like this-

       

              final FunctionParameter policyId = new FunctionParameter("ID", DefaultDataTypes.STRING);

              inParamList.add(policyId);

              final FunctionParameter objectName = new FunctionParameter("OBJ_NAME", DefaultDataTypes.STRING);

              inParamList.add(objectName);

              final FunctionParameter columnName = new FunctionParameter("COLUMN", DefaultDataTypes.STRING);

              inParamList.add(columnName);

              final FunctionParameter comparator = new FunctionParameter("OPERATOR", DefaultDataTypes.STRING);

              inParamList.add(comparator);

              final FunctionParameter constValue = new FunctionParameter("VALUE", DefaultDataTypes.STRING);

              inParamList.add(constValue);

              final FunctionParameter whereClause = new FunctionParameter("WHERE_CLAUSE", DefaultDataTypes.STRING);

              inParamList.add(whereClause);

       

              final FunctionParameter outParam = new FunctionParameter("SQL", DefaultDataTypes.STRING);

       

              sqlFn.setPushDown("NEVER");

              sqlFn.setInvocationClass(UDFExectionFactory);

              sqlFn.setInvocationMethod("prepare_sql");

              sqlFn.setCategory("string");

              sqlFn.setInputParameters(inParamList);

              sqlFn.setOutputParameter(outParam);

       

      The implementation looks like this-

       

      public static String prepare_sql (final String Id,

                                                    final String objName,

                                                    final String column,

                                                    final String operator,

                                                    final String value,

                                                    final String whereClause) throws TranslatorException

          {

           

                    String preparedSql = some calculated value

                     return preparedSql;

           }

       

      This function is called like this-

       

      SELECT prepare_sql ( ID,OBJ_NAME,COLUMN,OPERATOR,VALUE,WHERE_CLAUSE ) as SQL

       

      In some cases this preparedSql becomes very large and goes beyong 4000 characters but teiid only return the first 4000 characters from it making it a bad sql.

       

      How can we tell teiid to return the full SQL and not limit to 4000?