Examples with StoredProcedure org.teiid.query.sql.lang.StoredProcedure used on opensource projects

Example 1 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class TestConnectorWorkItem method testProcedureBatching.

@Test
public void testProcedureBatching() throws Exception {
    ProcedureExecution exec = new FakeProcedureExecution(2, 1);
    // this has two result set columns and 1 out parameter
    int total_columns = 3;
    // $NON-NLS-1$
    StoredProcedure command = (StoredProcedure) helpGetCommand("{call pm2.spTest8(?)}", EXAMPLE_BQT);
    command.getInputParameters().get(0).setExpression(new Constant(1));
    Call proc = new LanguageBridgeFactory(EXAMPLE_BQT).translate(command);
    ProcedureBatchHandler pbh = new ProcedureBatchHandler(proc, exec);
    assertEquals(total_columns, pbh.padRow(Arrays.asList(null, null)).size());
    List params = pbh.getParameterRow();
    assertEquals(total_columns, params.size());
    // check the parameter value
    assertEquals(Integer.valueOf(0), params.get(2));
    try {
        pbh.padRow(Arrays.asList(1));
        // $NON-NLS-1$
        fail("Expected exception from resultset mismatch");
    } catch (TranslatorException err) {
        assertEquals("TEIID30479 Could not process stored procedure results for EXEC spTest8(1).  Expected 2 result set columns, but was 1.  Please update your models to allow for stored procedure results batching.", // $NON-NLS-1$
        err.getMessage());
    }
}

Example 2 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class ComplexDocumentNode method buildComplexDocumentNode.

public static ComplexDocumentNode buildComplexDocumentNode(EdmOperation edmOperation, MetadataStore metadata, OData odata, UniqueNameGenerator nameGenerator, boolean useAlias, UriInfo uriInfo, URLParseService parseService) throws TeiidProcessingException {
    ComplexDocumentNode resource = new ComplexDocumentNode();
    FullQualifiedName fqn = edmOperation.getFullQualifiedName();
    String withoutVDB = fqn.getNamespace().substring(fqn.getNamespace().lastIndexOf('.') + 1);
    Schema schema = metadata.getSchema(withoutVDB);
    Procedure procedure = schema.getProcedure(edmOperation.getName());
    StoredProcedure storedQuery = new StoredProcedure();
    // $NON-NLS-1$
    storedQuery.setProcedureName(procedure.getFullName());
    for (int i = 0; i < procedure.getParameters().size(); i++) {
        storedQuery.setParameter(new SPParameter(i + 1, new Reference(i)));
    }
    String group = nameGenerator.getNextGroup();
    // $NON-NLS-1$
    SubqueryFromClause sfc = new SubqueryFromClause(group, storedQuery);
    resource.setGroupSymbol(new GroupSymbol(group));
    resource.setFromClause(sfc);
    resource.procedure = procedure;
    return resource;
}

Example 3 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class ExecResolver method findCommandMetadata.

/**
 * @see org.teiid.query.resolver.CommandResolver#findCommandMetadata(org.teiid.query.sql.lang.Command,
 * org.teiid.query.metadata.QueryMetadataInterface)
 */
private void findCommandMetadata(Command command, TempMetadataStore discoveredMetadata, QueryMetadataInterface metadata) throws QueryMetadataException, QueryResolverException, TeiidComponentException {
    StoredProcedure storedProcedureCommand = (StoredProcedure) command;
    StoredProcedureInfo storedProcedureInfo = null;
    try {
        storedProcedureInfo = metadata.getStoredProcedureInfoForProcedure(storedProcedureCommand.getProcedureName());
    } catch (QueryMetadataException e) {
        // $NON-NLS-1$
        String[] parts = storedProcedureCommand.getProcedureName().split("\\.", 2);
        if (parts.length > 1 && parts[0].equalsIgnoreCase(metadata.getVirtualDatabaseName())) {
            try {
                storedProcedureInfo = metadata.getStoredProcedureInfoForProcedure(parts[1]);
                storedProcedureCommand.setProcedureName(parts[1]);
            } catch (QueryMetadataException e1) {
            }
        }
        if (storedProcedureInfo == null) {
            throw e;
        }
    }
    storedProcedureCommand.setUpdateCount(storedProcedureInfo.getUpdateCount());
    storedProcedureCommand.setModelID(storedProcedureInfo.getModelID());
    storedProcedureCommand.setProcedureID(storedProcedureInfo.getProcedureID());
    storedProcedureCommand.setProcedureCallableName(storedProcedureInfo.getProcedureCallableName());
    // Get old parameters as they may have expressions set on them - collect
    // those expressions to copy later into the resolved parameters
    Collection<SPParameter> oldParams = storedProcedureCommand.getParameters();
    boolean namedParameters = storedProcedureCommand.displayNamedParameters();
    // of relying on all default values of all optional parameters.
    if (oldParams.size() == 0 || (oldParams.size() == 1 && storedProcedureCommand.isCalledWithReturn())) {
        storedProcedureCommand.setDisplayNamedParameters(true);
        namedParameters = true;
    }
    // Cache original input parameter expressions.  Depending on whether
    // the procedure was parsed with named or unnamed parameters, the keys
    // for this map will either be the String names of the parameters or
    // the Integer indices, as entered in the user query
    Map<Integer, Expression> positionalExpressions = new TreeMap<Integer, Expression>();
    Map<String, Expression> namedExpressions = new TreeMap<String, Expression>(String.CASE_INSENSITIVE_ORDER);
    int adjustIndex = 0;
    for (SPParameter param : oldParams) {
        if (param.getExpression() == null) {
            if (param.getParameterType() == SPParameter.RESULT_SET) {
                // If this was already resolved, just pretend the result set param doesn't exist
                adjustIndex--;
            }
            continue;
        }
        if (namedParameters && param.getParameterType() != SPParameter.RETURN_VALUE) {
            if (namedExpressions.put(param.getParameterSymbol().getShortName(), param.getExpression()) != null) {
                throw new QueryResolverException(QueryPlugin.Event.TEIID30138, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30138, param.getName()));
            }
        } else {
            positionalExpressions.put(param.getIndex() + adjustIndex, param.getExpression());
        }
    }
    storedProcedureCommand.clearParameters();
    int origInputs = positionalExpressions.size() + namedExpressions.size();
    /*
         * Take the values set from the stored procedure implementation, and match up with the
         * types of parameter it is from the metadata and then reset the newly joined parameters
         * into the stored procedure command.  If it is a result set get those columns and place
         * them into the stored procedure command as well.
         */
    List<SPParameter> metadataParams = storedProcedureInfo.getParameters();
    List<SPParameter> clonedMetadataParams = new ArrayList<SPParameter>(metadataParams.size());
    int inputParams = 0;
    int optionalParams = 0;
    int outParams = 0;
    boolean hasReturnValue = false;
    boolean optional = false;
    boolean varargs = false;
    for (int i = 0; i < metadataParams.size(); i++) {
        SPParameter metadataParameter = metadataParams.get(i);
        if ((metadataParameter.getParameterType() == ParameterInfo.IN) || (metadataParameter.getParameterType() == ParameterInfo.INOUT)) {
            if (ResolverUtil.hasDefault(metadataParameter.getMetadataID(), metadata) || metadataParameter.isVarArg()) {
                optional = true;
                optionalParams++;
            } else {
                inputParams++;
                if (optional) {
                    optional = false;
                    inputParams += optionalParams;
                    optionalParams = 0;
                }
            }
            if (metadataParameter.isVarArg()) {
                varargs = true;
            }
        } else if (metadataParameter.getParameterType() == ParameterInfo.OUT) {
            outParams++;
        /*
            	 * TODO: it would consistent to do the following, but it is a breaking change for procedures that have intermixed out params with in.
            	 * we may need to revisit this later
            	 */
        // optional = true;
        // optionalParams++;
        } else if (metadataParameter.getParameterType() == ParameterInfo.RETURN_VALUE) {
            hasReturnValue = true;
        }
        SPParameter clonedParam = (SPParameter) metadataParameter.clone();
        clonedMetadataParams.add(clonedParam);
        storedProcedureCommand.setParameter(clonedParam);
    }
    if (storedProcedureCommand.isCalledWithReturn() && !hasReturnValue) {
        throw new QueryResolverException(QueryPlugin.Event.TEIID30139, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30139, storedProcedureCommand.getGroup()));
    }
    if (!namedParameters && (inputParams > positionalExpressions.size())) {
        // $NON-NLS-1$ //$NON-NLS-2$
        throw new QueryResolverException(QueryPlugin.Event.TEIID30140, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30140, inputParams, inputParams + optionalParams + (varargs ? "+" : ""), origInputs, storedProcedureCommand.getGroup()));
    }
    // Walk through the resolved parameters and set the expressions from the
    // input parameters
    int exprIndex = 1;
    HashSet<String> expected = new HashSet<String>();
    if (storedProcedureCommand.isCalledWithReturn() && hasReturnValue) {
        for (SPParameter param : clonedMetadataParams) {
            if (param.getParameterType() == SPParameter.RETURN_VALUE) {
                Expression expr = positionalExpressions.remove(exprIndex++);
                param.setExpression(expr);
                break;
            }
        }
    }
    for (SPParameter param : clonedMetadataParams) {
        if (param.getParameterType() == SPParameter.RESULT_SET || param.getParameterType() == SPParameter.RETURN_VALUE) {
            continue;
        }
        if (namedParameters) {
            String nameKey = param.getParameterSymbol().getShortName();
            Expression expr = namedExpressions.remove(nameKey);
            // With named parameters, have to check on optional params and default values
            if (expr == null) {
                if (param.getParameterType() != ParameterInfo.OUT) {
                    param.setUsingDefault(true);
                    expected.add(nameKey);
                    if (!param.isVarArg()) {
                        expr = ResolverUtil.getDefault(param.getParameterSymbol(), metadata);
                    } else {
                        // zero length array
                        List<Expression> exprs = new ArrayList<Expression>(0);
                        Array array = new Array(exprs);
                        array.setImplicit(true);
                        array.setType(param.getClassType());
                        expr = array;
                    }
                }
            }
            param.setExpression(expr);
        } else {
            Expression expr = positionalExpressions.remove(exprIndex++);
            if (param.getParameterType() == SPParameter.OUT) {
                if (expr != null) {
                    boolean isRef = expr instanceof Reference;
                    if (!isRef || exprIndex <= inputParams + 1) {
                        // for backwards compatibility, this should be treated instead as an input
                        exprIndex--;
                        positionalExpressions.put(exprIndex, expr);
                    } else if (isRef) {
                        // mimics the hack that was in PreparedStatementRequest.
                        Reference ref = (Reference) expr;
                        // may be an out
                        ref.setOptional(true);
                    /*
	                		 * Note that there is a corner case here with out parameters intermixed with optional parameters
	                		 * there's not a good way around this.
	                		 */
                    }
                }
                continue;
            }
            if (expr == null) {
                if (!param.isVarArg()) {
                    expr = ResolverUtil.getDefault(param.getParameterSymbol(), metadata);
                }
                param.setUsingDefault(true);
            }
            if (param.isVarArg()) {
                List<Expression> exprs = new ArrayList<Expression>(positionalExpressions.size() + 1);
                if (expr != null) {
                    exprs.add(expr);
                }
                exprs.addAll(positionalExpressions.values());
                positionalExpressions.clear();
                Array array = new Array(exprs);
                array.setImplicit(true);
                array.setType(param.getClassType());
                expr = array;
            }
            param.setExpression(expr);
        }
    }
    // Check for leftovers, i.e. params entered by user w/ wrong/unknown names
    if (!namedExpressions.isEmpty()) {
        throw new QueryResolverException(QueryPlugin.Event.TEIID30141, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30141, namedExpressions.keySet(), expected));
    }
    if (!positionalExpressions.isEmpty()) {
        throw new QueryResolverException(QueryPlugin.Event.TEIID31113, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31113, positionalExpressions.size(), origInputs, storedProcedureCommand.getGroup().toString()));
    }
    // Create temporary metadata that defines a group based on either the stored proc
    // name or the stored query name - this will be used later during planning
    String procName = storedProcedureCommand.getProcedureName();
    List tempElements = storedProcedureCommand.getProjectedSymbols();
    boolean isVirtual = storedProcedureInfo.getQueryPlan() != null;
    discoveredMetadata.addTempGroup(procName, tempElements, isVirtual);
    // Resolve tempElements against new metadata
    GroupSymbol procGroup = new GroupSymbol(storedProcedureInfo.getProcedureCallableName());
    procGroup.setProcedure(true);
    TempMetadataID tid = discoveredMetadata.getTempGroupID(procName);
    tid.setOriginalMetadataID(storedProcedureCommand.getProcedureID());
    procGroup.setMetadataID(tid);
    storedProcedureCommand.setGroup(procGroup);
}

Example 4 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class ExecResolver method resolveProceduralCommand.

/**
 * @see org.teiid.query.resolver.ProcedureContainerResolver#resolveProceduralCommand(org.teiid.query.sql.lang.Command, org.teiid.query.metadata.TempMetadataAdapter)
 */
public void resolveProceduralCommand(Command command, TempMetadataAdapter metadata) throws QueryMetadataException, QueryResolverException, TeiidComponentException {
    findCommandMetadata(command, metadata.getMetadataStore(), metadata);
    // Resolve expressions on input parameters
    StoredProcedure storedProcedureCommand = (StoredProcedure) command;
    GroupContext externalGroups = storedProcedureCommand.getExternalGroupContexts();
    for (SPParameter param : storedProcedureCommand.getParameters()) {
        Expression expr = param.getExpression();
        if (expr == null) {
            continue;
        }
        for (SubqueryContainer<?> container : ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(expr)) {
            QueryResolver.setChildMetadata(container.getCommand(), command);
            QueryResolver.resolveCommand(container.getCommand(), metadata.getMetadata());
        }
        try {
            ResolverVisitor.resolveLanguageObject(expr, null, externalGroups, metadata);
        } catch (QueryResolverException e) {
            if (!checkForArray(param, expr)) {
                throw e;
            }
            continue;
        }
        Class<?> paramType = param.getClassType();
        ResolverUtil.setDesiredType(expr, paramType, storedProcedureCommand);
        // Compare type of parameter expression against parameter type
        // and add implicit conversion if necessary
        Class<?> exprType = expr.getType();
        if (paramType == null || exprType == null) {
            throw new QueryResolverException(QueryPlugin.Event.TEIID30143, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30143, storedProcedureCommand.getProcedureName(), param.getName()));
        }
        String tgtType = DataTypeManager.getDataTypeName(paramType);
        String srcType = DataTypeManager.getDataTypeName(exprType);
        Expression result = null;
        if (param.getParameterType() == SPParameter.RETURN_VALUE || param.getParameterType() == SPParameter.OUT) {
            if (!ResolverUtil.canImplicitlyConvert(tgtType, srcType)) {
                throw new QueryResolverException(QueryPlugin.Event.TEIID30144, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30144, param.getParameterSymbol(), tgtType, srcType));
            }
        } else {
            try {
                result = ResolverUtil.convertExpression(expr, tgtType, metadata);
            } catch (QueryResolverException e) {
                throw new QueryResolverException(QueryPlugin.Event.TEIID30145, e, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30145, new Object[] { param.getParameterSymbol(), srcType, tgtType }));
            }
            param.setExpression(result);
        }
    }
}

Example 5 with StoredProcedure

use of org.teiid.query.sql.lang.StoredProcedure in project teiid by teiid.

the class AccessNode method openInternal.

private void openInternal() throws TeiidComponentException, TeiidProcessingException {
    // TODO: support a partitioning concept with multi-source and full dependent join pushdown
    if (subPlans != null) {
        if (this.evaluatedPlans == null) {
            this.evaluatedPlans = new HashMap<GroupSymbol, SubqueryState>();
            for (Map.Entry<GroupSymbol, RelationalPlan> entry : subPlans.entrySet()) {
                SubqueryState state = new SubqueryState();
                RelationalPlan value = entry.getValue();
                value.reset();
                state.processor = new QueryProcessor(value, getContext().clone(), getBufferManager(), getDataManager());
                state.collector = state.processor.createBatchCollector();
                this.evaluatedPlans.put(entry.getKey(), state);
            }
        }
        BlockedException be = null;
        for (SubqueryState state : evaluatedPlans.values()) {
            try {
                state.collector.collectTuples();
            } catch (BlockedException e) {
                be = e;
            }
        }
        if (be != null) {
            throw be;
        }
    }
    /*
		 * Check to see if we need a multi-source expansion.  If the connectorBindingExpression != null, then 
		 * the logic below will handle that case
		 */
    if (multiSource && connectorBindingExpression == null) {
        synchronized (this) {
            // the description can be obtained asynchly, so we need to synchronize
            VDBMetaData vdb = getContext().getVdb();
            ModelMetaData model = vdb.getModel(getModelName());
            List<String> sources = model.getSourceNames();
            // make sure that we have the right nodes
            if (this.getChildCount() != 0 && (this.sourceNames == null || !this.sourceNames.equals(sources))) {
                this.childCount--;
                this.getChildren()[0] = null;
            }
            if (this.getChildCount() == 0) {
                sourceNames = sources;
                RelationalNode node = multiSourceModify(this, connectorBindingExpression, getContext().getMetadata(), sourceNames);
                RelationalPlan.connectExternal(node, getContext(), getDataManager(), getBufferManager());
                this.addChild(node);
            }
        }
        this.getChildren()[0].open();
        return;
    }
    // Copy command and resolve references if necessary
    if (processingCommand == null) {
        processingCommand = command;
        isUpdate = RelationalNodeUtil.isUpdate(command);
    }
    boolean needProcessing = true;
    if (this.connectorBindingExpression != null && connectorBindingId == null) {
        this.connectorBindingId = (String) getEvaluator(Collections.emptyMap()).evaluate(this.connectorBindingExpression, null);
        VDBMetaData vdb = getContext().getVdb();
        ModelMetaData model = vdb.getModel(getModelName());
        List<String> sources = model.getSourceNames();
        String replacement = this.connectorBindingId;
        if (!sources.contains(this.connectorBindingId)) {
            shouldExecute = false;
            if (command instanceof StoredProcedure) {
                StoredProcedure sp = (StoredProcedure) command;
                if (sp.returnParameters() && sp.getProjectedSymbols().size() > sp.getResultSetColumns().size()) {
                    throw new TeiidProcessingException(QueryPlugin.Event.TEIID30561, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30561, command));
                }
            }
            return;
        }
        if (!(command instanceof StoredProcedure || command instanceof Insert)) {
            processingCommand = (Command) command.clone();
            MultiSourceElementReplacementVisitor.visit(replacement, getContext().getMetadata(), processingCommand);
        }
    }
    do {
        Command atomicCommand = nextCommand();
        if (shouldEvaluate) {
            needProcessing = prepareNextCommand(atomicCommand);
            nextCommand = null;
        } else {
            needProcessing = RelationalNodeUtil.shouldExecute(atomicCommand, true);
        }
        if (needProcessing) {
            registerRequest(atomicCommand);
        }
    // We use an upper limit here to the currency because these commands have potentially large in-memory value sets
    } while (!processCommandsIndividually() && hasNextCommand() && this.tupleSources.size() < Math.max(Math.min(MAX_CONCURRENT, this.getContext().getUserRequestSourceConcurrency()), this.getContext().getUserRequestSourceConcurrency() / 2));
}