Examples with ResultSetMap com.dexels.navajo.adapter.sqlmap.ResultSetMap used on opensource projects

Example 1 with ResultSetMap

use of com.dexels.navajo.adapter.sqlmap.ResultSetMap in project navajo by Dexels.

the class MultipleValueQuery method evaluate.

@Override
public final Object evaluate() {
    JDBCMappable sql = evaluateQuery();
    ArrayList result = new ArrayList();
    try {
        ResultSetMap[] resultSet = sql.getResultSet();
        if (resultSet.length > 0) {
            for (int i = 0; i < resultSet.length; i++) {
                result.add(resultSet[i].getColumnValue(Integer.valueOf(0)));
            }
        }
    } catch (Exception e) {
        sql.kill();
        throw new TMLExpressionException(this, "Fatal error: " + e.getMessage(), e);
    } finally {
        try {
            sql.store();
        } catch (Exception e1) {
            logger.error("Error: ", e1);
        }
    }
    return result;
}

Example 2 with ResultSetMap

use of com.dexels.navajo.adapter.sqlmap.ResultSetMap in project navajo by Dexels.

the class JDBCMap method getResultSet.

protected ResultSetMap[] getResultSet(boolean updateOnly) throws UserException {
    ResultSet rs = null;
    try {
        if (resultSet == null) {
            rs = getDBResultSet(updateOnly);
        }
        if (debug) {
            Access.writeToConsole(myAccess, "SQLMAP, QUERY HAS BEEN EXECUTED, RETRIEVING RESULTSET\n");
        }
        if (rs != null) {
            int columns = 0;
            ResultSetMetaData meta = null;
            try {
                meta = rs.getMetaData();
                columns = meta.getColumnCount();
            } catch (Exception e) {
                throw new UserException(-1, "Error retrieving metadata / columncount", e);
            }
            List<ResultSetMap> dummy = new ArrayList<>();
            int index = 1;
            rowCount = 0;
            try {
                while (rs.next()) {
                    ResultSetMap rm = new ResultSetMap();
                    for (int i = 1; i < (columns + 1); i++) {
                        String param = meta.getColumnLabel(i);
                        int type = meta.getColumnType(i);
                        Object value = null;
                        final Object strVal = rs.getObject(i);
                        if ((strVal != null && !rs.wasNull()) || type == Types.BLOB) {
                            value = SQLMapHelper.getColumnValue(rs, type, i);
                        }
                        rm.addValue(param.toUpperCase(), value);
                    }
                    dummy.add(rm);
                }
                rowCount++;
                index++;
            } catch (Exception e) {
                if (debug) {
                    Access.writeToConsole(myAccess, "batch mode did not provide a fully baked result set, sorry.\n");
                    Access.writeToConsole(myAccess, "SQL exception is '" + e.toString() + "'\n");
                    logger.warn("Some sql problem: ", e);
                }
                rs.close();
                rs = null;
                resetAll();
            }
            if (debug) {
                Access.writeToConsole(myAccess, "GOT RESULTSET. Size: " + dummy.size() + " indexcounter says: " + index + "\n");
            }
            resultSet = dummy.toArray(new ResultSetMap[] {});
            rowCount = resultSet.length;
        }
    } catch (SQLException sqle) {
        logger.error("SQL Problem: ");
        AuditLog.log("SQLMap", sqle.getMessage(), Level.SEVERE, (myAccess != null ? (myAccess != null ? myAccess.accessID : "unknown access") : "unknown access"));
        throw new UserException(-1, sqle.getMessage(), sqle);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace(Access.getConsoleWriter(myAccess));
            }
            rs = null;
        }
        this.resetAll();
    }
    return resultSet;
}

Example 3 with ResultSetMap

use of com.dexels.navajo.adapter.sqlmap.ResultSetMap in project navajo by Dexels.

the class MergePDFsFromDatasource method evaluate.

@SuppressWarnings("unchecked")
@Override
public Object evaluate() throws TMLExpressionException {
    if (getOperands().size() != 7) {
        throw new TMLExpressionException("Invalid number of operands.");
    }
    // List items contains all the ids of the document
    ArrayList<String> items = null;
    if (getOperand(0) != null) {
        if (getOperand(0) instanceof ArrayList) {
            items = (ArrayList<String>) getOperand(0);
        } else if (getOperand(0) instanceof String) {
            items = (ArrayList<String>) Arrays.stream(((String) getOperand(0)).split(";")).map(o -> o.toString()).collect(Collectors.toList());
        }
    }
    int transactionContext = -1;
    if (getOperand(1) != null && getOperand(1) instanceof Integer) {
        transactionContext = ((Integer) getOperand(1)).intValue();
    }
    String datasource = null;
    if (getOperand(2) != null && getOperand(2) instanceof String) {
        datasource = (String) getOperand(2);
    }
    String username = null;
    if (getOperand(3) != null && getOperand(3) instanceof String) {
        username = (String) getOperand(3);
    }
    String tableId = null;
    if (getOperand(4) != null && getOperand(4) instanceof String) {
        tableId = (String) getOperand(4);
    }
    String objectType = null;
    if (getOperand(5) != null && getOperand(5) instanceof String) {
        objectType = (String) getOperand(5);
    }
    String binaryColumnName = null;
    if (getOperand(6) != null && getOperand(6) instanceof String) {
        binaryColumnName = (String) getOperand(6);
    }
    // Max in operator in oracle takes 1000 ites, but we need more ::
    int numOfItems = items.size();
    String queryPart = "";
    if (numOfItems == 1) {
        queryPart = " in " + items.toString().replace("[", "('").replace("]", "')").replace(" ", "");
    } else {
        int startPosition = 0;
        int endPosition = 999;
        if (startPosition + endPosition >= numOfItems) {
            endPosition = numOfItems - 1;
        } else {
            endPosition = startPosition + 999;
        }
        while (endPosition <= numOfItems && startPosition != endPosition) {
            queryPart = queryPart + " or " + tableId + " in " + items.subList(startPosition, endPosition).toString().replace(",", "','").replace("[", "('").replace("]", "')").replace(" ", "");
            startPosition = endPosition;
            if (endPosition + 999 >= numOfItems) {
                endPosition = numOfItems;
            } else {
                endPosition = startPosition + 999;
            }
        }
        queryPart = queryPart.substring(4).substring(tableId.length());
    }
    String query = "select * FROM document where (" + tableId + queryPart + ") AND objectType  = '" + objectType + "'";
    JDBCMappable sql = null;
    ArrayList<Object> result = new ArrayList<>();
    try {
        sql = JDBCFactory.getJDBCMap(getAccess());
        if (transactionContext != -1) {
            sql.setTransactionContext(transactionContext);
        } else {
            sql.setDatasource(datasource);
            sql.setUsername(username);
        }
        sql.setQuery(query);
        System.out.println(query);
        ResultSetMap[] resultSet = sql.getResultSet();
        if (resultSet.length > 0) {
            for (int i = 0; i < resultSet.length; i++) {
                result.add(resultSet[i].getColumnValue(0));
            }
        }
        int dataPosition = -1;
        // We got them all, now MERGE :D
        if (result.size() > 0) {
            // First find DATA position
            for (int i = 0; i < resultSet[0].getValuesSize(); i++) {
                if (resultSet[0].getColumnName(i).equalsIgnoreCase(binaryColumnName)) {
                    dataPosition = i;
                    break;
                }
            }
            // Then combine
            try {
                PDFMergerUtility merger = new PDFMergerUtility();
                File tempFile = File.createTempFile("pdfmerge", "pdf");
                String fileName = tempFile.getCanonicalPath();
                merger.setDestinationFileName(fileName);
                // Logic to short by items.
                for (String item : items) {
                    for (int i = 0; i < resultSet.length; i++) {
                        if (resultSet[i].getColumnValue(tableId).toString().equals(item)) {
                            // FOUND
                            merger.addSource(((Binary) resultSet[i].getColumnValue(dataPosition)).getFile());
                            break;
                        }
                    }
                }
                merger.mergeDocuments();
                Binary resultPDF = new Binary(new File(fileName), false);
                tempFile.delete();
                return resultPDF;
            } catch (IOException e) {
                throw new TMLExpressionException(this, e.getMessage(), e);
            }
        }
    } catch (Exception e) {
        sql.kill();
        throw new TMLExpressionException(this, "Fatal error: " + e.getMessage() + ", query = " + query, e);
    } finally {
        sql.kill();
    }
    return null;
}

Example 4 with ResultSetMap

use of com.dexels.navajo.adapter.sqlmap.ResultSetMap in project navajo by Dexels.

the class SPMap method getResultSet.

@SuppressWarnings("deprecation")
@Override
protected ResultSetMap[] getResultSet(boolean updateOnly) throws UserException {
    if (debug) {
        logger.info("TIMING SPMAP, start query... : " + update);
    }
    long start = System.currentTimeMillis();
    requestCount++;
    ResultSet rs = null;
    try {
        createConnection();
        if (con == null) {
            throw new UserException(-1, "in SQLMap. Could not open database connection [driver = " + driver + ", url = " + url + ", username = '" + username + "', password = '" + password + "']");
        }
        if (resultSet == null) {
            String spName = "";
            // Close previously open call statements:
            if (callStatement != null) {
                try {
                    callStatement.close();
                } catch (Exception e) {
                    logger.warn("Non fatal error closing statement", e);
                }
                callStatement = null;
                openCallStatements--;
            }
            if (query != null) {
                callStatement = con.prepareCall(query);
                openCallStatements++;
                if (query.indexOf("Call") != -1 && query.indexOf("(") != -1) {
                    spName = query.substring(query.indexOf("Call") + 5, query.indexOf("("));
                }
            } else {
                callStatement = con.prepareCall(update);
                openCallStatements++;
                if (update.indexOf("Call") != -1 && update.indexOf("(") != -1) {
                    spName = update.substring(update.indexOf("Call") + 5, update.indexOf("("));
                }
            }
            if (debug) {
                logger.info("callStatement = " + callStatement.toString());
            }
            if (debug) {
                logger.info("parameters = " + parameters);
            }
            if (parameters != null) {
                int spIndex = 0;
                for (int i = 0; i < parameters.size(); i++) {
                    Object param = parameters.get(i);
                    int type = ((Integer) parameterTypes.get(i)).intValue();
                    if (debug) {
                        logger.info("Setting parameter: " + param + "(" + (param != null ? param.getClass().toString() : "") + "), type = " + type);
                    }
                    if (type == INPUT_PARAM) {
                        spIndex++;
                        SQLMapHelper.setParameter(callStatement, param, i, this, this.getDbIdentifier(), this.isLegacyMode, this.debug, this.myAccess);
                    } else {
                        int sqlType = ((Integer) lookupTable.get(param)).intValue();
                        callStatement.registerOutParameter(i + 1, sqlType);
                    }
                }
            }
            if (query != null) {
                // logger.info("\nCalling query - callStatement.query()");
                rs = callStatement.executeQuery();
            // logger.info("\nCalled query");
            } else {
                // logger.info("\nCalling update - callStatement.execute()");
                callStatement.execute();
            // logger.info("\nCalled update");
            }
        }
        if (rs != null) {
            ResultSetMetaData meta = rs.getMetaData();
            int columns = meta.getColumnCount();
            ArrayList dummy = new ArrayList();
            int index = 1;
            remainCount = 0;
            while (rs.next()) {
                if ((index >= startIndex) && (index <= endIndex)) {
                    ResultSetMap rm = new ResultSetMap();
                    for (int i = 1; i < (columns + 1); i++) {
                        String param = meta.getColumnName(i);
                        int type = meta.getColumnType(i);
                        // logger.info(param + " has type " + getType(type));
                        Object value = null;
                        java.util.Calendar c = java.util.Calendar.getInstance();
                        if (rs.getString(i) != null) {
                            value = SQLMapHelper.getColumnValue(rs, type, i);
                        }
                        rm.addValue(param.toUpperCase(), value);
                    }
                    dummy.add(rm);
                    viewCount++;
                } else if (index >= startIndex) {
                    remainCount++;
                }
                rowCount++;
                index++;
            }
            resultSet = new ResultSetMap[dummy.size()];
            resultSet = (ResultSetMap[]) dummy.toArray(resultSet);
        }
    } catch (SQLException sqle) {
        AuditLog.log(sqle.getLocalizedMessage() + "/" + sqle.getSQLState(), "SPMap", sqle, Level.SEVERE, myAccess.accessID);
        throw new UserException(-1, sqle.getLocalizedMessage() + "/" + sqle.getSQLState(), sqle);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace(Access.getConsoleWriter(myAccess));
            }
            rs = null;
        }
        this.resetAll();
    }
    long end = System.currentTimeMillis();
    double total = (end - start) / 1000.0;
    totaltiming += total;
    if (debug) {
        logger.info("finished " + total + " seconds. Average query time: " + (totaltiming / requestCount) + " (" + requestCount + ")");
    }
    return resultSet;
}

Example 5 with ResultSetMap

use of com.dexels.navajo.adapter.sqlmap.ResultSetMap in project navajo by Dexels.

the class SQLMap method getResultSetMap.

public static final ResultSetMap getResultSetMap(ResultSetMetaData meta, int columns, ResultSet rs) throws Exception {
    ResultSetMap rm = new ResultSetMap();
    for (int i = 1; i < (columns + 1); i++) {
        String param = meta.getColumnLabel(i);
        int type = meta.getColumnType(i);
        Object value = null;
        value = SQLMapHelper.getColumnValue(rs, type, i);
        rm.addValue(param.toUpperCase(), value);
    }
    return rm;
}