0 Replies Latest reply on Jun 24, 2015 7:26 AM by Dharmendra Prasad

    Defaults in Stored Procedure

    Dharmendra Prasad Newbie

      Hi,

       

      I wrote a Procedural Relation as below:

       

      CREATE VIRTUAL PROCEDURE GetContact (id varchar, firstName varchar default 'null')
      RETURNS (type varchar(100), title varchar(100))
      AS
      BEGIN
      ...
      END
      


      I have a View named ContactView on this procedure and it works fine. Now I am looking for making following queries on the View:

       

      1. SELECT * FROM ContactView WHERE id = 1 and firstName = 'xyz';
      2. SELECT * FROM ContactView WHERE id = 1;
      3. SELECT * FROM ContactView WHERE firstName = 'abc';

       

      The first query seems to work fine. For the second and the third I get an error mentioned below:

      org.teiid.api.exception.query.QueryPlannerException: TEIID30270 No valid criteria specified for procedure parameter Contact.GetContact.firstName Sending error to client CrP+bNYZ7eaA.0
       org.teiid.api.exception.query.QueryPlannerException: TEIID30270 No valid criteria specified for procedure parameter Contact.GetContact.id Sending error to client CrP+bNYZ7eaA.0
      


      My assumption was that TEIID will honor the default value, but it didn't seem to do so. I also looked into the code for RulePlanProcedures' execute method (line number 116) and discovered that the code which might have handled the defaults is commented. See below:

      /*try {
           defaultValue = ResolverUtil.getDefault(symbol, metadata);
        } catch (QueryResolverException qre) {
             //Just ignore
        }*/
      

       

       

      I believe this was done with a good intention. If possible, please provide me with an alternative where I can make all the above three queries and similar queries successful.

       

      Thanks,

      Dharmendra