How to create a stored procedure to take varargs as IN parameters using java code (no DDL)

[deveshmishra]

I need to create a stored procedure which takes a parameter of variable length. Following is what I am doing but to not avail.

        final Procedure saveUsersProc = metadataFactory.addProcedure("save_user_details");
        
        ProcedureParameter param = new ProcedureParameter();
        param.setName("userInfo");
        param.setType(ProcedureParameter.Type.In);
        param.setDatatype(metadataFactory.getDataTypes().get(TypeFacility.RUNTIME_NAMES.STRING));
        param.setProcedure(saveUsersProc);
        
        saveUsersProc.getParameters().add(param);
        param.setPosition(saveUsersProc.getParameters().size()); 
        param.setVarArg(true);
        
        
        metadataFactory.addProcedureResultSetColumn("status",
                     TypeFacility.RUNTIME_NAMES.STRING,
                     saveUsersProc);

If I execute CALL save_user_details('a','b'), I get error saying CALL save_user_details('a','b'), TEIID30140 Incorrect number of parameters specified on the stored procedure save_user_details - expected 1 but got 2

Thanks in advance for help.

[shawkins]

That looks like it should work and does work on latest.  What version are you on?  You can also replace all of the parameter creation with

ProcedureParameter param = metadataFactory.addProcedureParameter("userInfo", TypeFacility.RUNTIME_NAMES.STRING, ProcedureParameter.Type.In, saveUsersProc);

param.setVarArg(true);

I do see an issue though when calling the vararg procedure with no arguments.

Steve

[deveshmishra]

Thanks Steve. I am on 8.1. Apparently it does not work, it works for Functions though. I will try to write a test case and provide. Thanks

[shawkins]

The vararg flag on a procedure parameter was added at the metadata level initially to support ddl definition of vararg functions.  Vararg procedure handling was not added until 8.2 with http://issues.jboss.org/jira/browse/TEIID-2111

Steve

[deveshmishra]

Thanks Steve. I shall upgrade to latest teiid / use ddl. Thanks