Examples with DirectQuerySender nl.nn.adapterframework.jdbc.DirectQuerySender used on opensource projects

Example 1 with DirectQuerySender

use of nl.nn.adapterframework.jdbc.DirectQuerySender in project iaf by ibissource.

the class BrowseJdbcTable method execute.

@POST
@RolesAllowed({ "IbisDataAdmin", "IbisAdmin", "IbisTester" })
@Path("/jdbc/browse")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public Response execute(LinkedHashMap<String, Object> json) throws ApiException {
    initBase(servletConfig);
    String realm = null, tableName = null, where = "", order = null;
    Boolean rowNumbersOnly = false;
    int minRow = 1, maxRow = 100;
    for (Entry<String, Object> entry : json.entrySet()) {
        String key = entry.getKey();
        if (key.equalsIgnoreCase("realm")) {
            realm = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("table")) {
            tableName = entry.getValue().toString().toLowerCase();
        }
        if (key.equalsIgnoreCase("where")) {
            where = entry.getValue().toString().toLowerCase();
        }
        if (key.equalsIgnoreCase("order")) {
            order = entry.getValue().toString().toLowerCase();
        }
        if (key.equalsIgnoreCase("rowNumbersOnly")) {
            rowNumbersOnly = Boolean.parseBoolean(entry.getValue().toString());
        }
        if (key.equalsIgnoreCase("minRow")) {
            if (entry.getValue() != "") {
                minRow = Integer.parseInt(entry.getValue().toString());
                minRow = Math.max(minRow, 0);
            }
        }
        if (key.equalsIgnoreCase("maxRow")) {
            if (entry.getValue() != "") {
                maxRow = Integer.parseInt(entry.getValue().toString());
                maxRow = Math.min(Math.max(maxRow, 1), 100);
            }
        }
    }
    if (realm == null || tableName == null) {
        throw new ApiException("realm and/or tableName not defined.", 400);
    }
    if (maxRow < minRow)
        throw new ApiException("Rownum max must be greater than or equal to Rownum min", 400);
    if (maxRow - minRow >= 100) {
        throw new ApiException("Difference between Rownum max and Rownum min must be less than hundred", 400);
    }
    if (!readAllowed(permissionRules, tableName))
        throw new ApiException("Access to table (" + tableName + ") not allowed", 400);
    // We have all info we need, lets execute the query!
    Map<String, Object> fieldDef = new HashMap<String, Object>();
    DirectQuerySender qs;
    String result = "";
    String query = null;
    try {
        qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
        try {
            qs.setName("QuerySender");
            qs.setJmsRealm(realm);
            // if (form_numberOfRowsOnly || qs.getDatabaseType() == DbmsSupportFactory.DBMS_ORACLE) {
            qs.setQueryType("select");
            qs.setBlobSmartGet(true);
            qs.setIncludeFieldDefinition(true);
            qs.configure(true);
            qs.open();
            ResultSet rs = qs.getConnection().getMetaData().getColumns(null, null, tableName, null);
            if (!rs.isBeforeFirst()) {
                rs = qs.getConnection().getMetaData().getColumns(null, null, tableName.toUpperCase(), null);
            }
            String fielddefinition = "<fielddefinition>";
            while (rs.next()) {
                String field = "<field name=\"" + rs.getString(4) + "\" type=\"" + DB2XMLWriter.getFieldType(rs.getInt(5)) + "\" size=\"" + rs.getInt(7) + "\"/>";
                fielddefinition = fielddefinition + field;
                fieldDef.put(rs.getString(4), DB2XMLWriter.getFieldType(rs.getInt(5)) + "(" + rs.getInt(7) + ")");
            }
            fielddefinition = fielddefinition + "</fielddefinition>";
            String browseJdbcTableExecuteREQ = "<browseJdbcTableExecuteREQ>" + "<dbmsName>" + qs.getDbmsSupport().getDbmsName() + "</dbmsName>" + "<tableName>" + tableName + "</tableName>" + "<where>" + XmlUtils.encodeChars(where) + "</where>" + "<numberOfRowsOnly>" + rowNumbersOnly + "</numberOfRowsOnly>" + "<order>" + order + "</order>" + "<rownumMin>" + minRow + "</rownumMin>" + "<rownumMax>" + maxRow + "</rownumMax>" + fielddefinition + "<maxColumnSize>1000</maxColumnSize>" + "</browseJdbcTableExecuteREQ>";
            URL url = ClassUtils.getResourceURL(this, DB2XML_XSLT);
            if (url != null) {
                Transformer t = XmlUtils.createTransformer(url);
                query = XmlUtils.transformXml(t, browseJdbcTableExecuteREQ);
            }
            result = qs.sendMessage("dummy", query);
        // } else {
        // error("errors.generic","This function only supports oracle databases",null);
        // }
        } catch (Throwable t) {
            throw new ApiException("An error occured on executing jdbc query: " + t.toString(), 400);
        } finally {
            qs.close();
        }
    } catch (Exception e) {
        throw new ApiException("An error occured on creating or closing the connection: " + e.toString(), 400);
    }
    List<Map<String, String>> resultMap = null;
    if (XmlUtils.isWellFormed(result)) {
        resultMap = XmlQueryResult2Map(result);
    }
    if (resultMap == null)
        throw new ApiException("Invalid query result.", 400);
    Map<String, Object> resultObject = new HashMap<String, Object>();
    resultObject.put("table", tableName);
    resultObject.put("query", XmlUtils.encodeChars(query));
    resultObject.put("fielddefinition", fieldDef);
    resultObject.put("result", resultMap);
    return Response.status(Response.Status.CREATED).entity(resultObject).build();
}

Example 2 with DirectQuerySender

use of nl.nn.adapterframework.jdbc.DirectQuerySender in project iaf by ibissource.

the class ExecuteJdbcQuery method execute.

@POST
@RolesAllowed({ "IbisTester" })
@Path("/jdbc/query")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public Response execute(LinkedHashMap<String, Object> json) throws ApiException {
    initBase(servletConfig);
    String realm = null, resultType = null, query = null, queryType = "select", result = "", returnType = MediaType.APPLICATION_XML;
    Object returnEntity = null;
    for (Entry<String, Object> entry : json.entrySet()) {
        String key = entry.getKey();
        if (key.equalsIgnoreCase("realm")) {
            realm = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("resultType")) {
            resultType = entry.getValue().toString().toLowerCase();
            if (resultType.equalsIgnoreCase("csv")) {
                returnType = MediaType.TEXT_PLAIN;
            }
            if (resultType.equalsIgnoreCase("json")) {
                returnType = MediaType.APPLICATION_JSON;
            }
        }
        if (key.equalsIgnoreCase("query")) {
            query = entry.getValue().toString();
            if (query.toLowerCase().indexOf("select") == -1)
                queryType = "other";
        }
    }
    if (realm == null || resultType == null || query == null) {
        throw new ApiException("Missing data, realm, resultType and query are expected.", 400);
    }
    // We have all info we need, lets execute the query!
    try {
        DirectQuerySender qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
        try {
            qs.setName("QuerySender");
            qs.setJmsRealm(realm);
            qs.setQueryType(queryType);
            qs.setBlobSmartGet(true);
            qs.configure(true);
            qs.open();
            result = qs.sendMessage("dummy", query);
            if (resultType.equalsIgnoreCase("csv")) {
                URL url = ClassUtils.getResourceURL(this, DB2XML_XSLT);
                if (url != null) {
                    Transformer t = XmlUtils.createTransformer(url);
                    result = XmlUtils.transformXml(t, result);
                }
            }
            qs.close();
        } catch (Throwable t) {
            throw new ApiException("An error occured on executing jdbc query: " + t.toString(), 400);
        }
    } catch (Exception e) {
        throw new ApiException("An error occured on creating or closing the connection: " + e.toString(), 400);
    }
    if (resultType.equalsIgnoreCase("json")) {
        if (XmlUtils.isWellFormed(result)) {
            returnEntity = XmlQueryResult2Map(result);
        }
        if (returnEntity == null)
            throw new ApiException("Invalid query result.", 400);
    } else
        returnEntity = result;
    return Response.status(Response.Status.CREATED).type(returnType).entity(returnEntity).build();
}

Example 3 with DirectQuerySender

use of nl.nn.adapterframework.jdbc.DirectQuerySender in project iaf by ibissource.

the class ExecuteJdbcQueryExecute method executeSub.

public ActionForward executeSub(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
    // Initialize action
    initAction(request);
    DynaActionForm executeJdbcQueryExecuteForm = (DynaActionForm) form;
    String form_jmsRealm = (String) executeJdbcQueryExecuteForm.get("jmsRealm");
    String form_queryType = (String) executeJdbcQueryExecuteForm.get("queryType");
    String form_resultType = (String) executeJdbcQueryExecuteForm.get("resultType");
    String form_query = (String) executeJdbcQueryExecuteForm.get("query");
    DirectQuerySender qs;
    String result = "";
    try {
        qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
        try {
            qs.setName("QuerySender");
            qs.setJmsRealm(form_jmsRealm);
            qs.setQueryType(form_queryType);
            qs.setBlobSmartGet(true);
            qs.configure(true);
            qs.open();
            result = qs.sendMessage("dummy", form_query);
            if ("csv".equalsIgnoreCase(form_resultType)) {
                URL url = ClassUtils.getResourceURL(this, DB2XML_XSLT);
                if (url != null) {
                    Transformer t = XmlUtils.createTransformer(url);
                    result = XmlUtils.transformXml(t, result);
                }
            }
        } catch (Throwable t) {
            error("error occured on executing jdbc query", t);
        } finally {
            qs.close();
        }
    } catch (Exception e) {
        error("error occured on creating or closing connection", e);
    }
    StoreFormData(form_query, result, executeJdbcQueryExecuteForm);
    if (!errors.isEmpty()) {
        saveErrors(request, errors);
        return (new ActionForward(mapping.getInput()));
    }
    // Successfull: store cookie
    String cookieValue = "";
    cookieValue += "jmsRealm=\"" + form_jmsRealm + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "queryType=\"" + form_queryType + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "resultType=\"" + form_resultType + "\"";
    // separator
    cookieValue += " ";
    // TODO: fix workaround to avoid http error 500 on WAS (line separators in query cookie)
    String fq = Misc.replace(form_query, System.getProperty("line.separator"), " ");
    cookieValue += "query=\"" + fq + "\"";
    Cookie execJdbcCookie = new Cookie(AppConstants.getInstance().getProperty("WEB_EXECJDBCCOOKIE_NAME"), cookieValue);
    execJdbcCookie.setMaxAge(Integer.MAX_VALUE);
    log.debug("Store cookie for " + request.getServletPath() + " cookieName[" + AppConstants.getInstance().getProperty("WEB_EXECJDBCCOOKIE_NAME") + "] " + " cookieValue[" + new StringTagger(cookieValue).toString() + "]");
    try {
        response.addCookie(execJdbcCookie);
    } catch (Throwable e) {
        log.warn("unable to add cookie to request. cookie value [" + execJdbcCookie.getValue() + "]");
    }
    log.debug("forward to success");
    return (mapping.findForward("success"));
}

Example 4 with DirectQuerySender

use of nl.nn.adapterframework.jdbc.DirectQuerySender in project iaf by ibissource.

the class BrowseJdbcTableExecute method executeSub.

public ActionForward executeSub(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
    // Initialize action
    initAction(request);
    // -------------------------------
    if (isCancelled(request)) {
        log.debug("browseJdbcTable was cancelled");
        removeFormBean(mapping, request);
        return (mapping.findForward("cancel"));
    }
    // Retrieve form content
    // ---------------------
    IniDynaActionForm browseJdbcTableForm = (IniDynaActionForm) form;
    String form_jmsRealm = (String) browseJdbcTableForm.get("jmsRealm");
    String form_tableName = (String) browseJdbcTableForm.get("tableName");
    String form_where = (String) browseJdbcTableForm.get("where");
    boolean form_numberOfRowsOnly = false;
    String form_order = (String) browseJdbcTableForm.get("order");
    if (browseJdbcTableForm.get("numberOfRowsOnly") != null)
        form_numberOfRowsOnly = ((Boolean) browseJdbcTableForm.get("numberOfRowsOnly")).booleanValue();
    int form_rownumMin = ((Integer) browseJdbcTableForm.get("rownumMin")).intValue();
    int form_rownumMax = ((Integer) browseJdbcTableForm.get("rownumMax")).intValue();
    if (!form_numberOfRowsOnly) {
        if (form_rownumMin < 0) {
            form_rownumMin = 0;
        }
        if (form_rownumMax < 0) {
            form_rownumMax = 0;
        }
        if (form_rownumMin == 0 && form_rownumMax == 0) {
            form_rownumMin = 1;
            form_rownumMax = 100;
        }
        if (errors.isEmpty()) {
            if (form_rownumMax < form_rownumMin) {
                error("errors.generic", "Rownum max must be greater than or equal to Rownum min", null);
            }
        }
        if (errors.isEmpty()) {
            if (form_rownumMax - form_rownumMin >= 100) {
                error("errors.generic", "Difference between Rownum max and Rownum min must be less than hundred", null);
            }
        }
        if (!readAllowed(permissionRules, request, form_tableName)) {
            error("errors.generic", "Access to table (" + form_tableName + ") not allowed", null);
        }
    }
    if (errors.isEmpty()) {
        DirectQuerySender qs;
        String result = "";
        String query = null;
        try {
            qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
            try {
                qs.setName("QuerySender");
                qs.setJmsRealm(form_jmsRealm);
                // if (form_numberOfRowsOnly || qs.getDatabaseType() == DbmsSupportFactory.DBMS_ORACLE) {
                qs.setQueryType("select");
                qs.setBlobSmartGet(true);
                qs.setIncludeFieldDefinition(true);
                qs.configure(true);
                qs.open();
                ResultSet rs = qs.getConnection().getMetaData().getColumns(null, null, form_tableName, null);
                if (!rs.isBeforeFirst()) {
                    rs = qs.getConnection().getMetaData().getColumns(null, null, form_tableName.toUpperCase(), null);
                }
                String fielddefinition = "<fielddefinition>";
                while (rs.next()) {
                    String field = "<field name=\"" + rs.getString(4) + "\" type=\"" + DB2XMLWriter.getFieldType(rs.getInt(5)) + "\" size=\"" + rs.getInt(7) + "\"/>";
                    fielddefinition = fielddefinition + field;
                }
                fielddefinition = fielddefinition + "</fielddefinition>";
                String browseJdbcTableExecuteREQ = "<browseJdbcTableExecuteREQ>" + "<dbmsName>" + qs.getDbmsSupport().getDbmsName() + "</dbmsName>" + "<tableName>" + form_tableName + "</tableName>" + "<where>" + XmlUtils.encodeChars(form_where) + "</where>" + "<numberOfRowsOnly>" + form_numberOfRowsOnly + "</numberOfRowsOnly>" + "<order>" + form_order + "</order>" + "<rownumMin>" + form_rownumMin + "</rownumMin>" + "<rownumMax>" + form_rownumMax + "</rownumMax>" + fielddefinition + "<maxColumnSize>1000</maxColumnSize>" + "</browseJdbcTableExecuteREQ>";
                URL url = ClassUtils.getResourceURL(this, DB2XML_XSLT);
                if (url != null) {
                    Transformer t = XmlUtils.createTransformer(url);
                    query = XmlUtils.transformXml(t, browseJdbcTableExecuteREQ);
                }
                result = qs.sendMessage("dummy", query);
            // } else {
            // error("errors.generic","This function only supports oracle databases",null);
            // }
            } catch (Throwable t) {
                error("errors.generic", "error occured on executing jdbc query [" + query + "]", t);
            } finally {
                qs.close();
            }
        } catch (Exception e) {
            error("errors.generic", "error occured on creating or closing connection", e);
        }
        String resultEnvelope = "<resultEnvelope>" + "<request " + "tableName=\"" + form_tableName + "\">" + XmlUtils.encodeChars(query) + "</request>" + result + "</resultEnvelope>";
        request.setAttribute("DB2Xml", resultEnvelope);
    }
    // Report any errors we have discovered back to the original form
    if (!errors.isEmpty()) {
        StoreFormData(browseJdbcTableForm);
        saveErrors(request, errors);
        return (new ActionForward(mapping.getInput()));
    }
    // Successfull: store cookie
    String cookieValue = "";
    cookieValue += "jmsRealm=\"" + form_jmsRealm + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "tableName=\"" + form_tableName + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "where=\"" + form_where + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "order=\"" + form_order + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "numberOfRowsOnly=\"" + form_numberOfRowsOnly + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "rownumMin=\"" + form_rownumMin + "\"";
    // separator
    cookieValue += " ";
    cookieValue += "rownumMax=\"" + form_rownumMax + "\"";
    Cookie sendJdbcBrowseCookie = new Cookie(AppConstants.getInstance().getProperty("WEB_JDBCBROWSECOOKIE_NAME"), cookieValue);
    sendJdbcBrowseCookie.setMaxAge(Integer.MAX_VALUE);
    log.debug("Store cookie for " + request.getServletPath() + " cookieName[" + AppConstants.getInstance().getProperty("WEB_JDBCBROWSECOOKIE_NAME") + "] " + " cookieValue[" + new StringTagger(cookieValue).toString() + "]");
    try {
        response.addCookie(sendJdbcBrowseCookie);
    } catch (Throwable e) {
        log.warn("unable to add cookie to request. cookie value [" + sendJdbcBrowseCookie.getValue() + "]");
    }
    // Forward control to the specified success URI
    log.debug("forward to success");
    return (mapping.findForward("success"));
}

Example 5 with DirectQuerySender

use of nl.nn.adapterframework.jdbc.DirectQuerySender in project iaf by ibissource.

the class JobDef method cleanupDatabase.

private void cleanupDatabase(IbisManager ibisManager) {
    Date date = new Date();
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String formattedDate = formatter.format(date);
    List<String> jmsRealmNames = new ArrayList<String>();
    for (Configuration configuration : ibisManager.getConfigurations()) {
        List<JobDef> scheduledJobs = configuration.getScheduledJobs();
        for (JobDef jobdef : configuration.getScheduledJobs()) {
            if (jobdef.getLocker() != null) {
                String jmsRealmName = jobdef.getLocker().getJmsRealName();
                if (!jmsRealmNames.contains(jmsRealmName)) {
                    jmsRealmNames.add(jmsRealmName);
                }
            }
        }
    }
    for (IAdapter adapter : ibisManager.getRegisteredAdapters()) {
        if (adapter instanceof Adapter) {
            PipeLine pipeLine = ((Adapter) adapter).getPipeLine();
            if (pipeLine != null) {
                for (IPipe pipe : pipeLine.getPipes()) {
                    if (pipe instanceof IExtendedPipe) {
                        IExtendedPipe extendedPipe = (IExtendedPipe) pipe;
                        if (extendedPipe.getLocker() != null) {
                            String jmsRealmName = extendedPipe.getLocker().getJmsRealName();
                            if (!jmsRealmNames.contains(jmsRealmName)) {
                                jmsRealmNames.add(jmsRealmName);
                            }
                        }
                    }
                }
            }
        }
    }
    for (Iterator iter = jmsRealmNames.iterator(); iter.hasNext(); ) {
        String jmsRealmName = (String) iter.next();
        setJmsRealm(jmsRealmName);
        DirectQuerySender qs;
        qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
        qs.setJmsRealm(jmsRealmName);
        String deleteQuery;
        if (qs.getDatabaseType() == DbmsSupportFactory.DBMS_MSSQLSERVER) {
            deleteQuery = "DELETE FROM IBISLOCK WHERE EXPIRYDATE < CONVERT(datetime, '" + formattedDate + "', 120)";
        } else {
            deleteQuery = "DELETE FROM IBISLOCK WHERE EXPIRYDATE < TO_TIMESTAMP('" + formattedDate + "', 'YYYY-MM-DD HH24:MI:SS')";
        }
        setQuery(deleteQuery);
        qs = null;
        executeQueryJob(ibisManager);
    }
    List messageLogs = new ArrayList();
    for (IAdapter iadapter : ibisManager.getRegisteredAdapters()) {
        Adapter adapter = (Adapter) iadapter;
        PipeLine pipeline = adapter.getPipeLine();
        for (int i = 0; i < pipeline.getPipes().size(); i++) {
            IPipe pipe = pipeline.getPipe(i);
            if (pipe instanceof MessageSendingPipe) {
                MessageSendingPipe msp = (MessageSendingPipe) pipe;
                if (msp.getMessageLog() != null) {
                    ITransactionalStorage transactionStorage = msp.getMessageLog();
                    if (transactionStorage instanceof JdbcTransactionalStorage) {
                        JdbcTransactionalStorage messageLog = (JdbcTransactionalStorage) transactionStorage;
                        String jmsRealmName = messageLog.getJmsRealName();
                        String expiryDateField = messageLog.getExpiryDateField();
                        String tableName = messageLog.getTableName();
                        String keyField = messageLog.getKeyField();
                        String typeField = messageLog.getTypeField();
                        MessageLogObject mlo = new MessageLogObject(jmsRealmName, tableName, expiryDateField, keyField, typeField);
                        if (!messageLogs.contains(mlo)) {
                            messageLogs.add(mlo);
                        }
                    }
                }
            }
        }
    }
    for (Iterator iter = messageLogs.iterator(); iter.hasNext(); ) {
        MessageLogObject mlo = (MessageLogObject) iter.next();
        setJmsRealm(mlo.getJmsRealmName());
        DirectQuerySender qs;
        qs = (DirectQuerySender) ibisManager.getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
        qs.setJmsRealm(mlo.getJmsRealmName());
        String deleteQuery;
        if (qs.getDatabaseType() == DbmsSupportFactory.DBMS_MSSQLSERVER) {
            deleteQuery = "DELETE FROM " + mlo.getTableName() + " WHERE " + mlo.getKeyField() + " IN (SELECT " + mlo.getKeyField() + " FROM " + mlo.getTableName() + " WITH (rowlock,updlock,readpast) WHERE " + mlo.getTypeField() + " IN ('" + JdbcTransactionalStorage.TYPE_MESSAGELOG_PIPE + "','" + JdbcTransactionalStorage.TYPE_MESSAGELOG_RECEIVER + "') AND " + mlo.getExpiryDateField() + " < CONVERT(datetime, '" + formattedDate + "', 120))";
        } else {
            deleteQuery = "DELETE FROM " + mlo.getTableName() + " WHERE " + mlo.getTypeField() + " IN ('" + JdbcTransactionalStorage.TYPE_MESSAGELOG_PIPE + "','" + JdbcTransactionalStorage.TYPE_MESSAGELOG_RECEIVER + "') AND " + mlo.getExpiryDateField() + " < TO_TIMESTAMP('" + formattedDate + "', 'YYYY-MM-DD HH24:MI:SS')";
        }
        qs = null;
        setQuery(deleteQuery);
        setQueryTimeout(900);
        executeQueryJob(ibisManager);
    }
}