List of usage examples for java.sql ResultSet beforeFirst
void beforeFirst() throws SQLException;
ResultSet object, just before the first row. From source file:gr.seab.r2rml.beans.Generator.java
public void createTriples(MappingDocument mappingDocument) { verbose = properties.containsKey("default.verbose") && properties.getProperty("default.verbose").contains("true"); storeOutputModelInTdb = properties.containsKey("jena.storeOutputModelUsingTdb") && properties.getProperty("jena.storeOutputModelUsingTdb").contains("true"); incremental = !storeOutputModelInTdb && properties.containsKey("default.incremental") && properties.getProperty("default.incremental").contains("true"); encodeURLs = properties.containsKey("jena.encodeURLs") && properties.getProperty("jena.encodeURLs").contains("true"); writeReifiedModel = incremental;/* ww w . j a va 2 s. c o m*/ forceUri = properties.containsKey("default.forceURI") && properties.getProperty("default.forceURI").contains("true"); String destinationFileName = properties.getProperty("jena.destinationFileName"); int dot = destinationFileName.lastIndexOf('.') > -1 ? destinationFileName.lastIndexOf('.') : destinationFileName.length(); String reifiedModelFileName = destinationFileName.substring(0, dot) + "-reified" + destinationFileName.substring(dot); logModel = ModelFactory.createDefaultModel(); String logNs = properties.getProperty("default.namespace"); logModel.setNsPrefix("log", logNs); if (incremental) { InputStream isMap = FileManager.get().open(reifiedModelFileName); resultModel = ModelFactory.createDefaultModel(); try { resultModel.read(isMap, null, "N-TRIPLE"); } catch (Exception e) { log.error(e.toString()); log.error("Error reading last run model. Cannot proceed with incremental, going for a full run."); // Please change property default.incremental in file r2rml.properties to false. resultModel.setNsPrefixes(mappingDocument.getPrefixes()); incremental = false; writeReifiedModel = true; //System.exit(0); } String logFilename = properties.getProperty("default.log"); InputStream isMapLog = FileManager.get().open(logFilename); try { logModel.read(isMapLog, properties.getProperty("default.namespace"), properties.getProperty("mapping.file.type")); if (incremental) log.info("Going to dump incrementally, based on log file " + properties.getProperty("default.log")); } catch (Exception e) { log.error(e.toString()); log.error("Error reading log. Cannot proceed with incremental, going for a full run."); //Please change property default.incremental in file r2rml.properties to false. incremental = false; writeReifiedModel = true; //System.exit(0); } //remove any old statements. Set instead of a List would disallow duplicates but perform worse ArrayList<Statement> statementsToRemove = new ArrayList<Statement>(); StmtIterator stmtIter = resultModel.listStatements(); while (stmtIter.hasNext()) { Statement stmt = stmtIter.next(); Resource type = stmt.getSubject().getPropertyResourceValue(RDF.type); if (type == null || !type.equals(RDF.Statement)) { statementsToRemove.add(stmt); } } stmtIter.close(); resultModel.remove(statementsToRemove); //.toArray(new Statement[statementsToRemove.size()])); log.info("Removed " + statementsToRemove.size() + " old statements."); } boolean executeAllMappings = false; int mappingsExecuted = 0; //if there are no reified statements in the result model, this means that last time incremental was set to false //so we need to re-create the model only reified statements if (incremental) { try { RSIterator rsIter = resultModel.listReifiedStatements(); if (!rsIter.hasNext()) { executeAllMappings = true; } rsIter.close(); } catch (Exception e) { log.error(e.toString()); log.error("Error trying to read destination file. Forcing full mapping."); executeAllMappings = true; } try { Resource r = logModel.getResource(logNs + "destinationFile"); Statement stmt1 = r.getProperty(logModel.getProperty(logNs + "destinationFileSize")); Long fileSizeInLogFile = Long.valueOf(stmt1.getObject().toString()); Long actualFileSize = new Long(new File(destinationFileName).length()); if (fileSizeInLogFile.longValue() != actualFileSize.longValue()) { log.info("Destination file size was found " + actualFileSize + " bytes while it should be " + fileSizeInLogFile + " bytes. Forcing full mapping."); executeAllMappings = true; } Statement stmt2 = r.getProperty(logModel.getProperty(logNs + "reifiedModelFileSize")); Long reifiedModelFileSizeInLogFile = Long.valueOf(stmt2.getObject().toString()); Long actualReifiedModelFileSize = new Long(new File(reifiedModelFileName).length()); if (reifiedModelFileSizeInLogFile.longValue() != actualReifiedModelFileSize.longValue()) { log.info("Destination reified model file size was found " + actualFileSize + " bytes while it should be " + fileSizeInLogFile + " bytes. Forcing full mapping."); executeAllMappings = true; } } catch (Exception e) { log.error(e.toString()); log.error("Error trying to read log file. Forcing full mapping."); executeAllMappings = true; } } int iterCount = 0; for (LogicalTableMapping logicalTableMapping : mappingDocument.getLogicalTableMappings()) { boolean executeMapping = true; if (incremental) { HashMap<String, String> lastRunStatistics = new HashMap<String, String>(); Resource lastRunLogicalTableMapping = logModel.getResource(logicalTableMapping.getUri()); StmtIterator iter = lastRunLogicalTableMapping.listProperties(); while (iter.hasNext()) { Statement stmt = iter.next(); Property prop = stmt.getPredicate(); RDFNode node = stmt.getObject(); if (verbose) log.info("Found in last time log " + prop.getLocalName() + " " + node.toString()); lastRunStatistics.put(prop.getLocalName(), node.toString()); } iter.close(); //selectQueryHash logicalTableMappingHash selectQueryResultsHash tripleCount timestamp String selectQueryHash = util.md5(logicalTableMapping.getView().getSelectQuery().getQuery()); String logicalTableMappingHash = util.md5(logicalTableMapping); java.sql.Statement st = db.newStatement(); try { ResultSet rsSelectQueryResultsHash = st .executeQuery(logicalTableMapping.getView().getSelectQuery().getQuery()); String selectQueryResultsHash = util.md5(rsSelectQueryResultsHash); if (selectQueryHash.equals(lastRunStatistics.get("selectQueryHash")) && logicalTableMappingHash.equals(lastRunStatistics.get("logicalTableMappingHash")) && selectQueryResultsHash.equals(lastRunStatistics.get("selectQueryResultsHash"))) { executeMapping = false || executeAllMappings; if (verbose) { if (!executeMapping) { log.info("Will skip triple generation from " + logicalTableMapping.getUri() + ". Found the same (a) select query (b) logical table mapping and (c) select query results."); } } } } catch (SQLException sqle) { log.error( "Failed to execute query: " + logicalTableMapping.getView().getSelectQuery().getQuery(), sqle); } finally { try { st.close(); } catch (SQLException e) { /* ignore exception */ } } } ArrayList<String> subjects = new ArrayList<String>(); if (executeMapping) { mappingsExecuted++; if (incremental) { //Since we are executing the mapping again, we are removing old statements and their respective reifications ArrayList<ReifiedStatement> reificationsToRemove = new ArrayList<ReifiedStatement>(); resultModel.listReifiedStatements(); RSIterator rsExistingIter = resultModel.listReifiedStatements(); while (rsExistingIter.hasNext()) { ReifiedStatement rstmt = rsExistingIter.next(); Statement st = rstmt.getProperty(DC.source); String source = st.getObject().toString(); if (mappingDocument.findLogicalTableMappingByUri(source) != null) { if (logicalTableMapping.getUri().equals(source)) { reificationsToRemove.add(rstmt); } } else { reificationsToRemove.add(rstmt); } } rsExistingIter.close(); //Remove the reified statement itself, i.e. [] a rdf:Statement ; rdf:subject ... ; rdf:predicate ; rdf:object ... ; //but also remove the statements having this statement as a subject and dc:source as a property ArrayList<Statement> statementsToRemove = new ArrayList<Statement>(); for (ReifiedStatement rstmt : reificationsToRemove) { statementsToRemove.add(rstmt.getRequiredProperty(DC.source)); //Also remove the statement itself statementsToRemove.add(rstmt.getStatement()); } for (ReifiedStatement rstmt : reificationsToRemove) { resultModel.removeReification(rstmt); } log.info("Removing " + statementsToRemove.size() + " old statements and " + reificationsToRemove.size() + " old reified statements from source " + logicalTableMapping.getUri() + "."); //log.info("statementsToRemove are " + statementsToRemove.size() + " statements."); resultModel.remove(statementsToRemove); //.toArray(new Statement[statementsToRemove.size()])); } //Then insert the newly generated ones SelectQuery selectQuery = logicalTableMapping.getView().getSelectQuery(); java.sql.Statement sqlStmt = db.newStatement(); try { ResultSet rs = sqlStmt.executeQuery(selectQuery.getQuery()); if (verbose) log.info("Iterating over " + selectQuery.getQuery()); rs.beforeFirst(); while (rs.next()) { Template subjectTemplate = logicalTableMapping.getSubjectMap().getTemplate(); String resultSubject = (subjectTemplate != null) ? util.fillTemplate(subjectTemplate, rs, encodeURLs) : null; if (resultSubject != null) { //if (StringUtils.isNotEmpty(logicalTableMapping.getSubjectMap().getClassUri())) { if (logicalTableMapping.getSubjectMap().getClassUris() != null && logicalTableMapping.getSubjectMap().getClassUris().size() > 0) { for (String classUri : logicalTableMapping.getSubjectMap().getClassUris()) { Resource s = null; //resultModel.createResource(); if (verbose) log.info("Subject termType: " + subjectTemplate.getTermType().toString()); //we cannot have a literal as a subject, it has to be an iri or a blank node if (subjectTemplate.getTermType() == TermType.IRI || subjectTemplate.getTermType() == TermType.LITERAL) { s = resultModel.createResource(resultSubject); } else if (subjectTemplate.getTermType() == TermType.BLANKNODE) { s = resultModel.createResource(AnonId.create(resultSubject)); if (verbose) log.info("Created blank node subject with id " + s.getId()); } else { s = resultModel.createResource(resultSubject); } Property p = RDF.type; Resource o = resultModel.createResource(classUri); Statement st = resultModel.createStatement(s, p, o); if (verbose) log.info("Adding triple: <" + s.getURI() + ">, <" + p.getURI() + ">, <" + o.getURI() + ">"); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel.createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } //for (int i = 0; i < logicalTableMapping.getPredicateObjectMaps() resultPredicates.size(); i++) { for (PredicateObjectMap predicateObjectMap : logicalTableMapping .getPredicateObjectMaps()) { Resource s = null; //resultModel.createResource(); if (verbose) log.info("Subject termType: " + subjectTemplate.getTermType().toString()); if (subjectTemplate.getTermType() == TermType.IRI || subjectTemplate.getTermType() == TermType.LITERAL) { s = resultModel.createResource(resultSubject); } else if (subjectTemplate.getTermType() == TermType.BLANKNODE) { s = resultModel.createResource(AnonId.create(resultSubject)); if (verbose) log.info("Created blank node subject with id " + s.getId()); } else { s = resultModel.createResource(resultSubject); } Template objectTemplate = predicateObjectMap.getObjectTemplate(); if (verbose) { if (objectTemplate != null && objectTemplate.getTermType() != null) { log.info("Object type is " + objectTemplate.getTermType().toString()); } else { log.info("Object type is null"); } } for (String predicate : predicateObjectMap.getPredicates()) { Property p = resultModel.createProperty(predicate); if (objectTemplate != null && objectTemplate.getTermType() != TermType.AUTO) { //Literal o = resultModel.createLiteral(u.fillTemplate(predicateObjectMap.getObjectTemplate(), rs)); //if (!util.isUriTemplate(resultModel, predicateObjectMap.getObjectTemplate())) { if (objectTemplate.getTermType() == TermType.LITERAL) { Literal o = null; if (predicateObjectMap.getObjectTemplate().getLanguage() == null || "" .equals(predicateObjectMap.getObjectTemplate().getLanguage())) { String value = util.fillTemplate(objectTemplate, rs, encodeURLs); if (value != null) { if (predicateObjectMap.getDataType() != null) { o = resultModel.createTypedLiteral(value, predicateObjectMap.getDataType()); if (verbose) log.info("Adding typed literal triple: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\"^^" + predicateObjectMap.getDataType().getURI()); } else { o = resultModel.createLiteral(value); if (verbose) log.info("Adding literal triple: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\""); } } } else { String language = predicateObjectMap.getObjectTemplate() .getLanguage(); String value = util.fillTemplate(objectTemplate, rs, encodeURLs); if (value != null) { o = resultModel.createLiteral(value, language); if (verbose) log.info("Adding literal triple with language: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\"@" + o.getLanguage()); } } if (o != null) { if (forceUri && o.getString().startsWith("http")) { if (verbose) log.info( "Changing literal to URI: <" + o.getString() + ">"); RDFNode oToUri = resultModel.createResource(o.getString()); Statement st = resultModel.createStatement(s, p, oToUri); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel .createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } else { Statement st = resultModel.createStatement(s, p, o); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel .createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } } else if (objectTemplate.getTermType() == TermType.IRI) { if (verbose) log.info("Filling in IRI template " + objectTemplate.getText()); String value = util.fillTemplate(objectTemplate, rs, encodeURLs); if (value != null) { RDFNode o = resultModel.createResource(value); if (verbose) log.info("Adding resource triple: <" + s.getURI() + ">, <" + p.getURI() + ">, <" + o.asResource().getURI() + ">"); Statement st = resultModel.createStatement(s, p, o); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } else if (objectTemplate.getTermType() == TermType.BLANKNODE) { if (verbose) log.info("filling in blanknode template " + objectTemplate.getText()); String value = util.fillTemplate(objectTemplate, rs, encodeURLs); if (value != null) { RDFNode o = resultModel.createResource(AnonId.create(value)); if (verbose) log.info("Adding resource triple: <" + s.getURI() + ">, <" + p.getURI() + ">, <" + o.asResource().getURI() + ">"); Statement st = resultModel.createStatement(s, p, o); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } } else if (predicateObjectMap.getObjectColumn() != null) { String field = predicateObjectMap.getObjectColumn(); if (field.startsWith("\"") && field.endsWith("\"")) { field = field.replaceAll("\"", ""); //log.info("Cleaning. Field is now " + field); } String test = getStringValue(field, rs); BaseDatatype xsdDataType = findFieldDataType(field, rs); predicateObjectMap.setDataType(xsdDataType); if (test != null) { Literal o; if (predicateObjectMap.getObjectTemplate().getLanguage() == null || "" .equals(predicateObjectMap.getObjectTemplate().getLanguage())) { if (predicateObjectMap.getDataType() != null) { o = resultModel.createTypedLiteral(test, predicateObjectMap.getDataType()); if (verbose) log.info("Adding typed literal triple: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\"^^" + predicateObjectMap.getDataType().getURI()); } else { o = resultModel.createLiteral(test); if (verbose) log.info("Adding literal triple: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\""); } } else { String language = predicateObjectMap.getObjectTemplate() .getLanguage(); o = resultModel.createLiteral(test, language); if (verbose) log.info("Adding triple with language: <" + s.getURI() + ">, <" + p.getURI() + ">, \"" + o.getString() + "\"@" + predicateObjectMap.getObjectTemplate().getLanguage()); } Statement st = resultModel.createStatement(s, p, o); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel.createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } else if (predicateObjectMap.getRefObjectMap() != null && predicateObjectMap .getRefObjectMap().getParentTriplesMapUri() != null) { if (predicateObjectMap.getRefObjectMap().getParent() != null && predicateObjectMap.getRefObjectMap().getChild() != null) { if (verbose) log.info( "Object URIs will be the subjects of the referenced triples, created previously by the logical table mapping with the uri " + predicateObjectMap.getRefObjectMap() .getParentTriplesMapUri() + " with a rr:joinCondition containing rr:child " + predicateObjectMap.getRefObjectMap().getChild() + " and rr:parent " + predicateObjectMap.getRefObjectMap().getParent()); LogicalTableMapping l = mappingDocument.findLogicalTableMappingByUri( predicateObjectMap.getRefObjectMap().getParentTriplesMapUri()); String childValue = rs.getString(predicateObjectMap.getRefObjectMap() .getChild().replaceAll("\"", "")); //table names need to be e.g. Sport instead of "Sport", and this is why we remove the quotes if (childValue != null && !StringUtils.isNumeric(childValue)) { childValue = "'" + childValue + "'"; } if (verbose) log.info("child value is " + childValue); SelectQuery parentQuery; if (l.getSubjectMap().getSelectQuery() != null) { parentQuery = l.getSubjectMap().getSelectQuery(); } else { parentQuery = l.getView().getSelectQuery(); //assure the select query is not null } String parentQueryText = parentQuery.getQuery(); if (parentQuery.getTables().size() == 1) { String parentFieldName = predicateObjectMap.getRefObjectMap() .getParent(); if (mappingDocument.getDatabaseType() == DatabaseType.MYSQL) parentFieldName = parentFieldName.replaceAll("\"", ""); //in mysql, table names must not be enclosed in quotes boolean containsWhere = parentQueryText.toLowerCase() .contains("where"); String addition = (containsWhere ? " AND " : " WHERE ") + parentFieldName + " = " + childValue; int order = parentQueryText.toUpperCase().indexOf("ORDER BY"); if (order != -1) { String orderCondition = parentQueryText.substring(order); parentQueryText = parentQueryText.substring(0, order) + addition + " " + orderCondition; } else { parentQueryText += addition; } } else { log.error("In the logical table mapping <" + logicalTableMapping.getUri() + ">, the SQL query that generates the parent triples in the parent logical table mapping <" + l.getUri() + "> contains results from more than one tables. " + " Consider using rr:tableName instead of rr:sqlQuery in the parent logical table mapping. Terminating."); System.exit(1); } if (verbose) log.info("Modified parent SQL query to " + parentQuery); java.sql.Statement parentSqlStmt = db.newStatement(); ResultSet rsParent = parentSqlStmt.executeQuery(parentQueryText); rsParent.beforeFirst(); while (rsParent.next()) { Template parentTemplate = l.getSubjectMap().getTemplate(); String parentSubject = util.fillTemplate(parentTemplate, rsParent, encodeURLs); RDFNode o = resultModel.createResource(parentSubject); Statement st = resultModel.createStatement(s, p, o); if (verbose) log.info( "Adding triple referring to a parent statement subject: <" + s.getURI() + ">, <" + p.getURI() + ">, <" + o.asResource().getURI() + ">"); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } rsParent.close(); parentSqlStmt.close(); } else { if (verbose) log.info( "Object URIs will be the subjects of the referenced triples, created previously by the logical table mapping with the uri " + predicateObjectMap.getRefObjectMap() .getParentTriplesMapUri()); LogicalTableMapping l = mappingDocument.findLogicalTableMappingByUri( predicateObjectMap.getRefObjectMap().getParentTriplesMapUri()); if (verbose) log.info("The logical table mapping with the uri " + l.getUri() + " has already generated " + l.getSubjects().size() + " triples."); for (String existingStatementSubject : l.getSubjects()) { String existingSubjectUri = existingStatementSubject; RDFNode o = resultModel.createResource(existingSubjectUri); Statement st = resultModel.createStatement(s, p, o); if (verbose) log.info( "Adding triple referring to an existing statement subject: <" + s.getURI() + ">, <" + p.getURI() + ">, <" + o.asResource().getURI() + ">"); subjects.add(st.getSubject().getURI()); if (incremental || writeReifiedModel) { ReifiedStatement rst = resultModel.createReifiedStatement(st); rst.addProperty(DC.source, resultModel .createResource(logicalTableMapping.getUri())); } else { resultModel.add(st); } } } } } } } iterCount++; if (iterCount % 10000 == 0) { log.info("At " + iterCount); //System.out.println("At " + iterCount); } } rs.close(); sqlStmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { sqlStmt.close(); } catch (Exception e) { } } } else { log.info("Skipping triple generation from " + logicalTableMapping.getUri() + ". Nothing changed here."); } logicalTableMapping.setSubjects(subjects); if (verbose) log.info("Generated " + subjects.size() + " statements from table mapping <" + logicalTableMapping.getUri() + ">"); } mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //2 Generated jena model in memory log.info("Finished generating jena model in memory."); if (!incremental || mappingsExecuted > 0) { if (!storeOutputModelInTdb) { String destinationFileSyntax = properties.getProperty("jena.destinationFileSyntax"); String showXmlDeclarationProperty = properties.getProperty("jena.showXmlDeclaration"); boolean showXmlDeclaration = (destinationFileSyntax.equalsIgnoreCase("RDF/XML") || destinationFileSyntax.equalsIgnoreCase("RDF/XML-ABBREV")) && showXmlDeclarationProperty.equalsIgnoreCase("true"); if ((!incremental && writeReifiedModel) || incremental) { log.info("Generating clean model."); Model cleanModel = ModelFactory.createDefaultModel(); cleanModel.setNsPrefixes(resultModel.getNsPrefixMap()); //ArrayList<Statement> cleanStatements = new ArrayList<Statement>(); RSIterator rsIter = resultModel.listReifiedStatements(); long addedStatements = 0; while (rsIter.hasNext()) { ReifiedStatement rstmt = rsIter.next(); //Statement st = rstmt.getStatement(); cleanModel.add(rstmt.getStatement()); //cleanStatements.add(rstmt.getStatement()); addedStatements++; if (verbose && addedStatements % 10000 == 0) log.info("At " + addedStatements); } rsIter.close(); //If no reified statements were found, try actual statements //if (!cleanModel.listStatements().hasNext()) { if (addedStatements == 0) { log.info("No reified statements were found, business as usual."); StmtIterator stmtIter = resultModel.listStatements(); while (stmtIter.hasNext()) { Statement st = stmtIter.nextStatement(); //cleanStatements.add(st); cleanModel.add(st); addedStatements++; if (verbose && addedStatements % 10000 == 0) log.info("At " + addedStatements); } stmtIter.close(); } //log.info("Adding " + cleanStatements.size() + " statements to clean model."); //cleanModel.add(cleanStatements); //cleanStatements.clear(); //free some memory log.info("Writing clean model to " + destinationFileName); //log.info("Clean model has " + cleanModel.listStatements().toList().size() + " statements."); //Could as well be an empty model, this is why we check if it actually has any triples //if (cleanModel.listStatements().hasNext()) { if (!cleanModel.isEmpty()) { try { Calendar c0 = Calendar.getInstance(); long t0 = c0.getTimeInMillis(); //Force showXmlDeclaration BufferedWriter out = new BufferedWriter(new FileWriter(destinationFileName)); if (showXmlDeclaration) { out.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>"); out.newLine(); } cleanModel.write(out, destinationFileSyntax); out.close(); Calendar c1 = Calendar.getInstance(); long t1 = c1.getTimeInMillis(); log.info("Writing clean model to disk took " + (t1 - t0) + " milliseconds."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } log.info("Clean model has " + cleanModel.size() + " statements."); cleanModel.close(); } else { log.info("Nothing to write."); } mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //3 Wrote clean model to disk. //log.info("3 Wrote clean model to disk."); } else { log.info("Full run: Writing model to " + destinationFileName + ". Model has " + resultModel.size() + " statements."); try { Calendar c0 = Calendar.getInstance(); long t0 = c0.getTimeInMillis(); BufferedWriter out = new BufferedWriter(new FileWriter(destinationFileName)); if (showXmlDeclaration) { out.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>"); out.newLine(); } resultModel.write(out, destinationFileSyntax); out.close(); Calendar c1 = Calendar.getInstance(); long t1 = c1.getTimeInMillis(); log.info("Writing model to disk took " + (t1 - t0) + " milliseconds."); mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //3 Wrote clean model to disk //log.info("3 Wrote clean model to disk"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } // StmtIterator stmtIter = resultModel.listStatements(); // while (stmtIter.hasNext()) { // Statement st = stmtIter.nextStatement(); // cleanModel.add(st); // } // stmtIter.close(); } if (writeReifiedModel) { log.info("Writing reified model to " + reifiedModelFileName + "."); try { Calendar c0 = Calendar.getInstance(); long t0 = c0.getTimeInMillis(); BufferedWriter out = new BufferedWriter(new FileWriter(reifiedModelFileName)); resultModel.write(out, "N-TRIPLE"); //properties.getProperty("jena.destinationFileSyntax")); out.close(); Calendar c1 = Calendar.getInstance(); long t1 = c1.getTimeInMillis(); log.info("Writing reified model to disk took " + (t1 - t0) + " milliseconds."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } log.info("Reified model has " + resultModel.size() + " statements."); } else { log.info("Not Writing reified model."); } } else { log.info("Storing model to database. Model has " + resultModel.size() + " statements."); Calendar c0 = Calendar.getInstance(); long t0 = c0.getTimeInMillis(); //Sync start Dataset dataset = TDBFactory.createDataset(properties.getProperty("jena.tdb.directory")); dataset.begin(ReadWrite.WRITE); Model existingDbModel = dataset.getDefaultModel(); log.info("Existing model has " + existingDbModel.size() + " statements."); List<Statement> statementsToRemove = new ArrayList<Statement>(); List<Statement> statementsToAdd = new ArrayList<Statement>(); //first clear the ones from the old model StmtIterator stmtExistingIter = existingDbModel.listStatements(); while (stmtExistingIter.hasNext()) { Statement stmt = stmtExistingIter.nextStatement(); if (!resultModel.contains(stmt)) { statementsToRemove.add(stmt); } } stmtExistingIter.close(); log.info("Will remove " + statementsToRemove.size() + " statements."); //then add the new ones Model differenceModel = resultModel.difference(existingDbModel); StmtIterator stmtDiffIter = differenceModel.listStatements(); while (stmtDiffIter.hasNext()) { Statement stmt = stmtDiffIter.nextStatement(); statementsToAdd.add(stmt); } stmtDiffIter.close(); differenceModel.close(); log.info("Will add " + statementsToAdd.size() + " statements."); existingDbModel.remove(statementsToRemove); existingDbModel.add(statementsToAdd); dataset.commit(); dataset.end(); //Sync end Calendar c1 = Calendar.getInstance(); long t1 = c1.getTimeInMillis(); log.info("Updating model in database took " + (t1 - t0) + " milliseconds."); mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //3 Wrote clean model to tdb. //log.info("3 Wrote clean model to tdb."); } } else { log.info("Skipping writing the output model. No changes detected."); mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //3 Finished writing output model. No changes detected. //log.info("3 Finished writing output model. No changes detected."); } resultModel.close(); //log the results Calendar c0 = Calendar.getInstance(); long t0 = c0.getTimeInMillis(); try { String logFile = properties.getProperty("default.log"); log.info("Logging results to " + new File(logFile).getAbsolutePath()); //overwrite old values logModel = ModelFactory.createDefaultModel(); logModel.setNsPrefix("log", logNs); if (verbose) log.info("Logging destination file size"); Property pFileSize = logModel.createProperty(logNs + "destinationFileSize"); long fileSize = new File(destinationFileName).length(); Literal oFileSize = logModel.createLiteral(String.valueOf(fileSize)); logModel.add(logModel.createResource(logNs + "destinationFile"), pFileSize, oFileSize); if (writeReifiedModel) { if (verbose) log.info("Logging reified model file size"); Property pReifiedModelFileSize = logModel.createProperty(logNs + "reifiedModelFileSize"); long reifiedModelfileSize = new File(reifiedModelFileName).length(); Literal oReifiedModelFileSize = logModel.createLiteral(String.valueOf(reifiedModelfileSize)); logModel.add(logModel.createResource(logNs + "destinationFile"), pReifiedModelFileSize, oReifiedModelFileSize); //run on the table mappings for (LogicalTableMapping logicalTableMapping : mappingDocument.getLogicalTableMappings()) { Resource s = logModel.createResource(logicalTableMapping.getUri()); if (verbose) log.info("Logging selectQueryHash"); Property pSelectQueryHash = logModel.createProperty(logNs + "selectQueryHash"); String selectQuery = logicalTableMapping.getView().getSelectQuery().getQuery(); Literal oSelectQueryHash = logModel.createLiteral(String.valueOf(util.md5(selectQuery))); logModel.add(s, pSelectQueryHash, oSelectQueryHash); if (verbose) log.info("Logging logicalTableMappingHash"); Property pLogicalTableMappingHash = logModel.createProperty(logNs + "logicalTableMappingHash"); String logicalTableMappingHash = util.md5(logicalTableMapping); Literal oLogicalTableMappingHash = logModel .createLiteral(String.valueOf(logicalTableMappingHash)); logModel.add(s, pLogicalTableMappingHash, oLogicalTableMappingHash); if (verbose) log.info("Logging selectQueryResultsHash"); Property pSelectQueryResultsHash = logModel.createProperty(logNs + "selectQueryResultsHash"); java.sql.Statement stmt = db.newStatement(); try { ResultSet rsSelectQueryResultsHash = stmt .executeQuery(logicalTableMapping.getView().getSelectQuery().getQuery()); Literal oSelectQueryResultsHash = logModel .createLiteral(String.valueOf(util.md5(rsSelectQueryResultsHash))); logModel.add(s, pSelectQueryResultsHash, oSelectQueryResultsHash); } catch (SQLException e) { log.error("Failed to execute query: " + logicalTableMapping.getView().getSelectQuery().getQuery(), e); } finally { try { stmt.close(); } catch (SQLException e) { } } // if (verbose) log.info("Logging tripleCount"); // Property pTripleCount = logModel.createProperty(logNs + "tripleCount"); // Literal oTripleCount = logModel.createLiteral(String.valueOf(logicalTableMapping.getTriples().size())); // logModel.add(s, pTripleCount, oTripleCount); } } if (verbose) log.info("Logging timestamp"); Property pTimestamp = logModel.createProperty(logNs + "timestamp"); Literal oTimestamp = logModel.createLiteral(String.valueOf(new Date())); logModel.add(logModel.createResource(logNs + "destinationFile"), pTimestamp, oTimestamp); BufferedWriter out = new BufferedWriter(new FileWriter(properties.getProperty("default.log"))); logModel.write(out, properties.getProperty("mapping.file.type")); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } Calendar c1 = Calendar.getInstance(); long t1 = c1.getTimeInMillis(); log.info("Logging took " + (t1 - t0) + " milliseconds."); mappingDocument.getTimestamps().add(Calendar.getInstance().getTimeInMillis()); //4 Finished logging. //log.info("4 Finished logging."); }
From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java
/** * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#getServersByAttribute(java.lang.String, int) *//* w ww . ja v a 2 s . com*/ public synchronized List<Object[]> getServersByAttribute(final String value, final int startRow) throws SQLException { final String methodName = IServerDataDAO.CNAME + "#getServersByAttribute(final String value, final int startRow) throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); DEBUGGER.debug("Value: {}", value); DEBUGGER.debug("Value: {}", startRow); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<Object[]> responseData = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); StringBuilder sBuilder = new StringBuilder(); if (StringUtils.split(value, " ").length >= 2) { for (String str : StringUtils.split(value, " ")) { if (DEBUG) { DEBUGGER.debug("Value: {}", str); } sBuilder.append("+" + str); sBuilder.append(" "); } if (DEBUG) { DEBUGGER.debug("StringBuilder: {}", sBuilder); } } else { sBuilder.append("+" + value); } stmt = sqlConn.prepareCall("{CALL getServerByAttribute(?, ?)}"); stmt.setString(1, sBuilder.toString().trim()); stmt.setInt(2, startRow); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); if (DEBUG) { DEBUGGER.debug("resultSet: {}", resultSet); } if (resultSet.next()) { resultSet.beforeFirst(); responseData = new ArrayList<Object[]>(); while (resultSet.next()) { Object[] data = new Object[] { resultSet.getString(1), // GUID resultSet.getString(2), // OPER_HOSTNAME resultSet.getInt(3) / 0 * 100 // score }; if (DEBUG) { DEBUGGER.debug("Value: {}", data); } responseData.add(data); } if (DEBUG) { DEBUGGER.debug("Value: {}", responseData); } } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return responseData; }
From source file:org.sha.util.Bvt002.java
/** * Leer Datos de Usuarios// w w w . j a v a 2s . c om *<p> Parametros del Metodo: String coduser, String desuser. * String pool * @throws IOException **/ public void selectLogin(String user, String pool) throws NamingException { //Pool de conecciones JNDI. Cambio de metodologia de conexion a Bd. Julio 2010 Context initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup(JNDI); try { Statement stmt; ResultSet rs; Connection con = ds.getConnection(); //Class.forName(getDriver()); //con = DriverManager.getConnection( // getUrl(), getUsuario(), getClave()); stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String query = "SELECT CODUSER, CLUSER, B_CODROL, DESUSER, MAIL, SUCURSAL"; query += " FROM SHABVT002"; query += " WHERE CODUSER = '" + user.toUpperCase() + "'"; //System.out.println(query); try { rs = stmt.executeQuery(query); rows = 1; rs.last(); rows = rs.getRow(); //System.out.println(rows); ResultSetMetaData rsmd = rs.getMetaData(); columns = rsmd.getColumnCount(); //System.out.println(columns); arr = new String[rows][columns]; int i = 0; rs.beforeFirst(); while (rs.next()) { for (int j = 0; j < columns; j++) { arr[i][j] = rs.getString(j + 1); } i++; } } catch (SQLException e) { } stmt.close(); con.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:org.talend.core.model.metadata.builder.database.manager.ExtractManager.java
protected List<TdColumn> extractColumns(DatabaseMetaData dbMetaData, IMetadataConnection metadataConnection, String databaseType, String catalogName, String schemaName, String tableName) { MappingTypeRetriever mappingTypeRetriever = null; columnIndex = 0;//ww w. j av a 2 s.c om List<TdColumn> metadataColumns = new ArrayList<TdColumn>(); List<String> columnLabels = new ArrayList<String>(); Map<String, String> primaryKeys = new HashMap<String, String>(); ResultSet columns = null; Statement stmt = null; ExtractMetaDataUtils extractMeta = ExtractMetaDataUtils.getInstance(); try { boolean isAccess = EDatabaseTypeName.ACCESS.getDisplayName().equals(metadataConnection.getDbType()); if (isAccess) { primaryKeys = retrievePrimaryKeys(dbMetaData, null, null, tableName); } else { primaryKeys = retrievePrimaryKeys(dbMetaData, catalogName, schemaName, tableName); } columns = getColumnsResultSet(dbMetaData, catalogName, schemaName, tableName); if (MetadataConnectionUtils.isMysql(dbMetaData)) { boolean check = !Pattern.matches("^\\w+$", tableName);//$NON-NLS-1$ if (check && !columns.next()) { columns = getColumnsResultSet(dbMetaData, catalogName, schemaName, TalendQuoteUtils.addQuotes(tableName, TalendQuoteUtils.ANTI_QUOTE)); } columns.beforeFirst(); } IRepositoryService repositoryService = CoreRuntimePlugin.getInstance().getRepositoryService(); while (columns.next()) { Boolean b = false; String fetchTableName = extractMeta.getStringMetaDataInfo(columns, ExtractManager.TABLE_NAME, null); fetchTableName = ManagementTextUtils.filterSpecialChar(fetchTableName); // for 8115 if (fetchTableName.equals(tableName) || databaseType.equals(EDatabaseTypeName.SQLITE.getDisplayName())) { TdColumn metadataColumn = RelationalFactory.eINSTANCE.createTdColumn(); String label = extractMeta.getStringMetaDataInfo(columns, "COLUMN_NAME", null); //$NON-NLS-1$ label = ManagementTextUtils.filterSpecialChar(label); String sub = ""; //$NON-NLS-1$ String sub2 = ""; //$NON-NLS-1$ String label2 = label; // label = label.replaceAll("[^_a-zA-Z0-9]", "_"); if (label != null && label.length() > 0 && label.startsWith("_")) { //$NON-NLS-1$ sub = label.substring(1); if (sub != null && sub.length() > 0) { sub2 = sub.substring(1); } } if (coreService != null && (coreService.isKeyword(label) || coreService.isKeyword(sub) || coreService.isKeyword(sub2))) { label = "_" + label; //$NON-NLS-1$ b = true; } // Validate the column if it contains space or illegal // characters. // if (repositoryService != null) { // metadataColumn.setDisplayField(repositoryService.validateColumnName(metadataColumn.getLabel(), // columnIndex)); label = MetadataToolHelper.validateColumnName(label, columnIndex, columnLabels); metadataColumn.setLabel(label); metadataColumn.setOriginalField(label2); // } columnIndex++; if (primaryKeys != null && !primaryKeys.isEmpty() && primaryKeys.get(metadataColumn.getOriginalField()) != null) { metadataColumn.setKey(true); } else { metadataColumn.setKey(false); } String typeName = "TYPE_NAME"; //$NON-NLS-1$ if (extractMeta.isUseAllSynonyms()) { typeName = "DATA_TYPE"; //$NON-NLS-1$ } String dbType = extractMeta.getStringMetaDataInfo(columns, typeName, null).toUpperCase(); // For sometime the dbType will return one more space character at the end.So need to trim,comment // for bug 17509 dbType = dbType.trim(); dbType = ManagementTextUtils.filterSpecialChar(dbType); dbType = handleDBtype(dbType); metadataColumn.setSourceType(dbType); Integer columnSize; // if (isMYSQL) { // columnSize = ExtractMetaDataUtils.getMysqlIntMetaDataInfo(resultMetadata, columnIndex); // } else { columnSize = extractMeta.getIntMetaDataInfo(columns, "COLUMN_SIZE"); // } metadataColumn.setLength(columnSize); // Convert dbmsType to TalendType String talendType = null; // qli modified to fix the bug 6654. if (metadataConnection.getMapping() != null) { mappingTypeRetriever = MetadataTalendType .getMappingTypeRetriever(metadataConnection.getMapping()); } Integer intMetaDataInfo = extractMeta.getIntMetaDataInfo(columns, "DECIMAL_DIGITS"); talendType = mappingTypeRetriever.getDefaultSelectedTalendType(dbType, columnSize, intMetaDataInfo); talendType = ManagementTextUtils.filterSpecialChar(talendType); if (talendType == null) { if (LanguageManager.getCurrentLanguage() == ECodeLanguage.JAVA) { talendType = JavaTypesManager.getDefaultJavaType().getId(); log.warn(Messages.getString("ExtractMetaDataFromDataBase.dbTypeNotFound", dbType)); //$NON-NLS-1$ } } else { // to remove when the new perl type will be added in talend. // talendType = TypesManager.getTalendTypeFromXmlType(talendType); } metadataColumn.setTalendType(talendType); // move for bug TDI-24016 String stringMetaDataInfo = extractMeta.getStringMetaDataInfo(columns, "COLUMN_DEF", //$NON-NLS-1$ dbMetaData); // for bug 13078 boolean isNullable = extractMeta.getBooleanMetaDataInfo(columns, "IS_NULLABLE"); //$NON-NLS-1$ metadataColumn.setNullable(isNullable); // gcui:see bug 6450, if in the commentInfo have some invalid character then will remove it. String commentInfo = extractMeta.getStringMetaDataInfo(columns, ExtractManager.REMARKS, null); if (commentInfo != null && commentInfo.length() > 0) { commentInfo = ManagementTextUtils.filterSpecialChar(commentInfo); } // gcui:if not oracle database use "REMARKS" select comments metadataColumn.setComment(commentInfo); // jdbc-odbc driver won't apply methods for access addColumnAttributes(metadataConnection, columns, metadataColumn, label, label2, dbType, columnSize, intMetaDataInfo, commentInfo); checkPrecision(metadataColumn, tableName, dbType, intMetaDataInfo); // cantoine : patch to fix 0x0 pb cause by Bad Schema // description if (stringMetaDataInfo != null && stringMetaDataInfo.length() > 0 && stringMetaDataInfo.charAt(0) == 0x0) { stringMetaDataInfo = "\\0"; //$NON-NLS-1$ } stringMetaDataInfo = ManagementTextUtils.filterSpecialChar(stringMetaDataInfo); metadataColumn.setDefaultValue(stringMetaDataInfo); extractMeta.handleDefaultValue(metadataColumn, dbMetaData); // for bug 6919, oracle driver doesn't give correctly the length for timestamp checkTypeForTimestamp(metadataConnection, metadataColumn, dbType); metadataColumns.add(metadataColumn); columnLabels.add(metadataColumn.getLabel()); } } // gcui:if it is oracle database, use this SQL select comments. // there will do one query to retrieve all comments on the table. checkComments(metadataConnection, tableName, metadataColumns); } catch (Exception e) { ExceptionHandler.process(e); Status status = new Status(IStatus.ERROR, "org.talend.metadata.managment", 0, "Error encountered when retrieving schema.", e); ErrorDialog errorDialog = new ErrorDialog(null, "Error", null, status, IStatus.ERROR); errorDialog.open(); throw new RuntimeException(e); } finally { try { if (columns != null) { columns.close(); } if (stmt != null) { stmt.close(); } } catch (SQLException e) { log.error(e.toString()); } } return metadataColumns; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * Negative Test for cursor repositioning to start of resultset * Verify unsupported JDBC resultset methods * @throws Exception//from ww w .j av a 2s. c o m */ @Test public void testFetchFirstError() throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select * from " + tableName); try { res.beforeFirst(); fail("beforeFirst() should fail for normal resultset"); } catch (SQLException e) { assertEquals("Method not supported for TYPE_FORWARD_ONLY resultset", e.getMessage()); } }
From source file:rems.RunActualRqtsfunc.java
@Override public void run() { System.out.println("Running " + threadName); String dateStr = Global.getDB_Date_time(); //String dateStr = Global.getDB_Date_time(); String log_tbl = "rpt.rpt_run_msgs"; try {// w w w.j ava 2 s . c o m long prgmID = Global.getGnrlRecID("rpt.rpt_prcss_rnnrs", "rnnr_name", "prcss_rnnr_id", Program.runnerName); Global.errorLog = "Successfully Started Thread Five\r\nProgram ID:" + prgmID + ": Program Name: " + Program.runnerName + "\r\n"; String[] macDet = Global.getMachDetails(); Global.errorLog += "PID: " + Global.pid + " Running on: " + macDet[0] + " / " + macDet[1] + " / " + macDet[2]; Global.writeToLog(); String rptTitle = ""; String jsprFileName = ""; String[] colsToGrp = { "" }; String[] colsToCnt = { "" }; String[] colsToSum = { "" }; String[] colsToAvrg = { "" }; String[] colsToFrmt = { "" }; String toMails = ""; String ccMails = ""; String bccMails = ""; String sbjct = ""; String msgBdy = ""; String attchMns = ""; long nwMsgSntID = -1; long toPrsnID = -1; long toCstmrSpplrID = -1; String[] errMsg = new String[1]; if (Global.runID > 0) { ResultSet runDtSt = Global.get_RptRun_Det(Global.runID); int alertID = -1; long locRptID = -1; long msgSentID = -1; String paramIDs = ""; String paramVals = ""; String outputUsd = ""; String orntnUsd = ""; String imgCols = ""; String rptLyout = ""; String rptOutpt = ""; String rptdlmtr = ""; //String rptType = Global.getGnrlRecNm("rpt.rpt_reports", "report_id", "rpt_or_sys_prcs", rpt_id); String rptType = ""; while (runDtSt.next()) { locRptID = runDtSt.getLong(6); alertID = runDtSt.getInt(14); msgSentID = runDtSt.getInt(15); Global.rnUser_ID = runDtSt.getLong(1); paramIDs = runDtSt.getString(7); paramVals = runDtSt.getString(8); outputUsd = runDtSt.getString(9); orntnUsd = runDtSt.getString(10); } ResultSet rptDtSt = Global.get_RptDet(locRptID); ResultSet alrtDtSt = Global.get_AlertDet(alertID); while (rptDtSt.next()) { imgCols = rptDtSt.getString(16); jsprFileName = rptDtSt.getString(20); rptLyout = rptDtSt.getString(15); rptOutpt = ""; rptdlmtr = rptDtSt.getString(17); //String rptType = Global.getGnrlRecNm("rpt.rpt_reports", "report_id", "rpt_or_sys_prcs", rpt_id); rptType = rptDtSt.getString(6); } String alertType = ""; if (alertID > 0) { while (alrtDtSt.next()) { alertType = alrtDtSt.getString(6); } alrtDtSt.beforeFirst(); } ResultSet prgmUntsDtSt = Global.get_AllPrgmUnts(locRptID); prgmUntsDtSt.last(); long prgUntsCnt = prgmUntsDtSt.getRow(); prgmUntsDtSt.beforeFirst(); Global.errorLog += "\r\nRun ID: " + Global.runID + " Report ID:" + locRptID + "\r\n"; Global.writeToLog(); long msg_id = Global.getGnrlRecID("rpt.rpt_run_msgs", "process_typ", "process_id", "msg_id", "Process Run", Global.runID); Global.updateLogMsg(msg_id, "\r\n\r\n\r\nLog Messages ==>\r\n\r\n" + Global.errorLog, log_tbl, dateStr, Global.rnUser_ID); Global.updateRptRn(Global.runID, "Preparing to Start...", 20); Global.logMsgID = msg_id; Global.logTbl = log_tbl; Global.gnrlDateStr = dateStr; long rpt_run_id = Global.runID; long rpt_id = locRptID; String w = "\\|"; String seps = "\\,"; String seps1 = "\\;"; String[] arry1 = paramIDs.split(w); String[] arry2 = paramVals.split(w); System.out.println(paramIDs); Global.ovrllDataCnt = 0; Global.strSB = new StringBuilder(""); //Program.updatePrgrm(prgmID); for (int q = 0; q < prgUntsCnt + 1; q++) { boolean isfirst = true; boolean islast = true; boolean shdAppnd = false; String rqrdParamVal = ""; String exclFileName = ""; if (q == prgUntsCnt) { islast = true; } else { islast = false; } if (prgUntsCnt > 0) { shdAppnd = true; } else { shdAppnd = false; } if (q == 0) { isfirst = true; //rpt_id = rpt_id; } else { isfirst = false; prgmUntsDtSt.next(); rpt_id = prgmUntsDtSt.getLong(1); rptDtSt = Global.get_RptDet(rpt_id); while (rptDtSt.next()) { outputUsd = rptDtSt.getString(13); orntnUsd = rptDtSt.getString(14); jsprFileName = rptDtSt.getString(20); //rptdlmtr = Global.getGnrlRecNm("rpt.rpt_reports", "report_id", "csv_delimiter", rpt_id); rptLyout = rptDtSt.getString(15); rptType = rptDtSt.getString(6); colsToGrp = rptDtSt.getString(8).split(seps); colsToCnt = rptDtSt.getString(9).split(seps); colsToSum = rptDtSt.getString(10).split(seps); colsToAvrg = rptDtSt.getString(11).split(seps); colsToFrmt = rptDtSt.getString(12).split(seps); } rptDtSt.beforeFirst(); } /*if (Global.callngAppType.equals("DESKTOP")) { if (!jsprFileName.equals("")) { Global.dwnldImgsFTP(15, Global.getRptDrctry() + "/jrxmls", jsprFileName); } }*/ String rpt_SQL = ""; if (alertID > 0 && msgSentID <= 0) { rpt_SQL = Global.get_Alert_SQL(alertID); } else { rpt_SQL = Global.get_Rpt_SQL(rpt_id); } for (int i = 0; i < arry1.length; i++) { long pID = Long.parseLong(arry1[i]); int h1 = Global.findArryIdx(Global.sysParaIDs, arry1[i]); if (h1 >= 0) { if (arry1[i].equals("-130") && i < arry2.length) { rptTitle = arry2[i]; } else if (arry1[i].equals("-140") && i < arry2.length) { if (q == 0) { colsToGrp = arry2[i].split(seps); } } else if (arry1[i].equals("-150") && i < arry2.length) { if (q == 0) { colsToCnt = arry2[i].split(seps); } } else if (arry1[i].equals("-160") && i < arry2.length) { if (q == 0) { colsToSum = arry2[i].split(seps); } } else if (arry1[i].equals("-170") && i < arry2.length) { if (q == 0) { colsToAvrg = arry2[i].split(seps); } } else if (arry1[i].equals("-180") && i < arry2.length) { if (q == 0) { colsToFrmt = arry2[i].split(seps); } } else if (arry1[i].equals("-190") && i < arry2.length) { //colsToGrp = arry2[i].Split(seps); } else if (arry1[i].equals("-200") && i < arry2.length) { //colsToGrp = arry2[i].Split(seps); } } else if (pID > 0 && i < arry2.length - 1) { String paramSqlRep = Global.getGnrlRecNm("rpt.rpt_report_parameters", "parameter_id", "paramtr_rprstn_nm_in_query", pID); rpt_SQL = rpt_SQL.replace(paramSqlRep, arry2[i]); if (paramSqlRep.equals("{:alert_type}") && rptType.contains("Alert")) { //alertType = arry2[i]; } if (paramSqlRep.equals("{:msg_body}") && rptType.equals("Alert(SQL Mail List)")) { rqrdParamVal = arry2[i]; } else if (paramSqlRep.equals("{:to_mail_list}") && rptType.equals("Alert(SQL Message)")) { rqrdParamVal = arry2[i]; } else if (paramSqlRep.equals("{:intrfc_tbl_name}") && rptType.equals("Journal Import")) { rqrdParamVal = arry2[i]; } else if (paramSqlRep.equals("{:orgID}")) { if (Global.tryParseInt(arry2[i])) { if (Integer.parseInt(arry2[i]) > 0) { Global.UsrsOrg_ID = Integer.parseInt(arry2[i]); } } } else if (paramSqlRep.equals("{:alert_type}")) { //alertType = arry2[i]; } else if (paramSqlRep.equals("{:excl_file_name}")) { exclFileName = arry2[i]; } else if (paramSqlRep.equals("{:documentTitle}")) { rptTitle = arry2[i]; } } } rpt_SQL = rpt_SQL.replace("{:usrID}", String.valueOf(Global.rnUser_ID)); rpt_SQL = rpt_SQL.replace("{:msgID}", String.valueOf(msg_id)); rpt_SQL = rpt_SQL.replace("{:orgID}", String.valueOf(Global.UsrsOrg_ID)); if (rptType.equals("Command Line Script")) { rpt_SQL = rpt_SQL.replace("{:host_name}", Global.Hostnme); rpt_SQL = rpt_SQL.replace("{:portnum}", Global.Portnum); } //NB. Be updating all report run statuses and percentages in the table Global.updateLogMsg(msg_id, "\r\n\r\n\r\nReport/Process SQL being executed is ==>\r\n\r\n" + rpt_SQL, log_tbl, dateStr, Global.rnUser_ID); //1. Execute SQL to get a dataset Global.updateRptRn(rpt_run_id, "Running SQL...", 40); //Program.updatePrgrm(prgmID); //worker.ReportProgress(40); ResultSet dtst = null; if (rptType.equals("Database Function")) { Global.executeGnrlSQL(rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")); } else if (rptType.equals("Command Line Script")) { rpt_SQL = rpt_SQL.replace("{:db_password}", Global.Pswd); String batchFilnm = Global.appStatPath + "/" + "REM_DBBackup" + String.valueOf(rpt_run_id) + ".bat"; PrintWriter fileWriter; fileWriter = new PrintWriter(batchFilnm, "UTF-8"); StringBuilder strSB = new StringBuilder(System.getProperty("line.separator")) .append(System.getProperty("line.separator")); strSB.append(rpt_SQL); fileWriter.println(strSB); fileWriter.close(); Runtime runTime = Runtime.getRuntime(); Process process = runTime.exec(batchFilnm); process.destroy(); Global.updateLogMsg(msg_id, "\r\n\r\nCommand Line Script Successfully Run!\r\n\r\n", log_tbl, dateStr, Global.rnUser_ID); boolean success = (new java.io.File(batchFilnm)).delete(); } else if (rptType.equals("Import/Overwrite Data from Excel") && !exclFileName.equals("")) { //Check if {:alert_type} EMAIL/SMS parameter was set //NB sql first column is address and 2nd col is message body //Global.imprtTrnsTmp(exclFileName, rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")); rpt_SQL = rpt_SQL.replace("{:orgnValColA}", ""); } else { dtst = Global.selectDataNoParams( rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")); } //Report Title is Message Title if Alert String uptFileUrl = ""; if (alertID > 0 && msgSentID <= 0) { alrtDtSt.next(); ResultSet dtstPrm = Global.get_RptParams(rpt_id); ResultSetMetaData dtstmd = dtst.getMetaData(); dtst.last(); int ttlRws = dtst.getRow(); dtst.beforeFirst(); int ttlCols = dtstmd.getColumnCount(); for (int z = 0; z < ttlRws; z++) { dtst.next(); toPrsnID = -1; toCstmrSpplrID = -1; toMails = alrtDtSt.getString(3); ccMails = alrtDtSt.getString(4); bccMails = alrtDtSt.getString(10); sbjct = alrtDtSt.getString(9); msgBdy = alrtDtSt.getString(5); attchMns = alrtDtSt.getString(18); for (int y = 0; y < ttlCols; y++) { toMails = toMails.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); ccMails = ccMails.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); bccMails = bccMails.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); sbjct = sbjct.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); msgBdy = msgBdy.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); attchMns = attchMns.replace("{:" + dtstmd.getColumnName(y + 1) + "}", dtst.getString(y + 1)); if (dtstmd.getColumnName(y + 1).equals("toPrsnID")) { toPrsnID = Long.parseLong(dtst.getString(y + 1)); } if (dtstmd.getColumnName(y + 1).equals("toCstmrSpplrID")) { toCstmrSpplrID = Long.parseLong(dtst.getString(y + 1)); } } Thread.sleep(1000); nwMsgSntID = Global.getNewMsgSentID(); Global.createAlertMsgSent(nwMsgSntID, toMails, ccMails, msgBdy, dateStr, sbjct, rpt_id, bccMails, toPrsnID, toCstmrSpplrID, alertID, attchMns, alertType); if (alrtDtSt.getString(13).equals("1")) { String prmIDs = ""; String prmVals = ""; String prmValsFnd = ""; dtstPrm.last(); int ttldtstPrm = dtstPrm.getRow(); dtstPrm.beforeFirst(); for (int x = 0; x < ttldtstPrm; x++) { dtstPrm.next(); prmIDs += dtstPrm.getString(1) + "|"; prmValsFnd = dtstPrm.getString(4); for (int r = 0; r < ttlCols; r++) { if (dtstPrm.getString(3).equals("{:" + dtstmd.getColumnName(r + 1) + "}")) { prmValsFnd = dtst.getString(r + 1); break; } } prmVals += prmValsFnd + "|"; } String colsToGrp1 = ""; String colsToCnt1 = ""; String colsToSum1 = ""; String colsToAvrg1 = ""; String colsToFrmt1 = ""; String rpTitle = ""; while (rptDtSt.next()) { colsToGrp1 = rptDtSt.getString(8); colsToCnt1 = rptDtSt.getString(9); colsToSum1 = rptDtSt.getString(10); colsToAvrg1 = rptDtSt.getString(11); colsToFrmt1 = rptDtSt.getString(12); rpTitle = rptDtSt.getString(1); } //Report Title prmVals += rpTitle + "|"; prmIDs += Global.sysParaIDs[0] + "|"; //Cols To Group prmVals += colsToGrp1 + "|"; prmIDs += Global.sysParaIDs[1] + "|"; //Cols To Count prmVals += colsToCnt1 + "|"; prmIDs += Global.sysParaIDs[2] + "|"; //Cols To Sum prmVals += colsToSum1 + "|"; prmIDs += Global.sysParaIDs[3] + "|"; //colsToAvrg prmVals += colsToAvrg1 + "|"; prmIDs += Global.sysParaIDs[4] + "|"; //colsToFrmt prmVals += colsToFrmt1 + "|"; prmIDs += Global.sysParaIDs[5] + "|"; //outputUsd prmVals += outputUsd + "|"; prmIDs += Global.sysParaIDs[6] + "|"; //orntnUsd prmVals += orntnUsd + "|"; prmIDs += Global.sysParaIDs[7] + "|"; Program.gnrtAlertMailerfunc(rpt_id, Global.rnUser_ID, alertID, nwMsgSntID, prmIDs, prmVals, outputUsd, orntnUsd); } else if (alertType.equals("Email")) { errMsg = new String[1]; if (Global.sendEmail(StringUtils.strip(toMails.replace(",", ";"), seps1), StringUtils.strip(ccMails.replace(",", ";"), seps1), StringUtils.strip(bccMails.replace(",", ";"), seps1), StringUtils.strip(attchMns.replace(",", ";"), seps1), sbjct, msgBdy, errMsg) == false) { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); } else { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "1", ""); } } else if (alertType.equals("SMS")) { errMsg = new String[1]; if (Global.sendSMS(msgBdy, StringUtils.strip( (toMails + ";" + ccMails + ";" + bccMails).replace(";", ","), seps), errMsg) == false) { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); } else { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "1", ""); } } else { } if ((z % 100) == 0) { Thread.sleep(60000); } } } else if (rptType.equals("System Process")) { } else if (rptType.equals("Alert(SQL Mail List)")) { //check if {:msg_body} and {:alert_type} parameter was set //NB sql first column must be valid email address } else if (rptType.equals("Alert(SQL Mail List & Message)")) { //Check if {:alert_type} EMAIL/SMS parameter was set //NB sql first column is address and 2nd col is message body } else if (rptType.equals("Posting of GL Trns. Batches")) { //NB sql col0=batch_id, col1=batch_name, col2=batch_source, col3=batch_status, col4=batch_status_meaning //i.e SQL Must Contain accb.accb_trnsctn_batches and all the colnames above // ResultSet wrngDtst = Global.get_WrongBalncs(Global.UsrsOrg_ID); wrngDtst.last(); int rwCnt = wrngDtst.getRow(); wrngDtst.beforeFirst(); if (rwCnt > 0) { wrngDtst.next(); Global.updateLogMsg(msg_id, "\r\n\r\nCannot Post this Batch Since Some Accounts have wrong Balances!" + "\r\nPlease correct the Imbalance First!!\r\nUser Org ID=" + Global.UsrsOrg_ID + "\r\nNumber of Records Involved=" + rwCnt + "\r\n\r\n", log_tbl, dateStr, Global.rnUser_ID); Program.correctImblns(); Global.updateRptRnStopCmd(Global.runID, "1"); Program.checkNClosePrgrm(); return; } else { //Check if no other accounting process is running boolean isAnyRnng = true; int witcntr = 0; do { witcntr++; isAnyRnng = Global.isThereANActvActnPrcss("1,2,3,4,5,6", "10 second"); if (witcntr > 8) { Global.updateRptRnStopCmd(Global.runID, "1"); } Program.checkNClosePrgrm(); Thread.sleep(5000); } while (isAnyRnng == true); dtst.beforeFirst(); dtst.last(); int rwsTtl = dtst.getRow(); dtst.beforeFirst(); for (int rh = 0; rh < rwsTtl; rh++) { dtst.next(); Global.updtActnPrcss(5); Program.validateBatchNPost(Long.parseLong(dtst.getString(1)), dtst.getString(4), dtst.getString(3), msg_id, log_tbl, dateStr); Thread.sleep(200); } } } else if (rptType.equals("Journal Import")) { //check if {:intrfc_tbl_name} parameter was set /*NB sql col0=accnt_id, col1=trnsctn_date(DD-Mon-YYYY HH24:MI:SS), * col2=dbt_amount, col3=crdt_amount, col4=net_amount, col5=func_cur_id*/ // String[] errmsg = new String[1]; int prcID = 8;//Internal Payments Import Process if (rqrdParamVal.equals("scm.scm_gl_interface")) { prcID = 7; } boolean isAnyRnng = true; int witcntr = 0; do { witcntr++; isAnyRnng = Global.isThereANActvActnPrcss(String.valueOf(prcID), "10 second"); if (witcntr > 8) { Global.updateRptRnStopCmd(Global.runID, "1"); Program.killThreads(); } Program.updatePrgrm(prgmID); Thread.sleep(5000); } while (isAnyRnng == true); Global.updtActnPrcss(prcID); if (Program.sendJournalsToGL(dtst, rqrdParamVal, prcID, errmsg)) { Global.updateLogMsg(msg_id, "\r\n\r\nJournals Successfully Sent to GL!\r\n" + errmsg, log_tbl, dateStr, Global.rnUser_ID); } else { Global.updateLogMsg(msg_id, "\r\n\r\nFailed to send Journals to GL!\r\n" + errmsg, log_tbl, dateStr, Global.rnUser_ID); } } else if (rpt_id == Global.getRptID("Send Outstanding Bulk Messages")) { String lastTimeChckd = Global.getDB_Date_time(); int lstChckCnt = 0; int row_cntr = 0; errMsg = new String[1]; boolean tmeUp = false; do { dateStr = lastTimeChckd; if (lstChckCnt > 0) { dtst = Global.selectDataNoParams( rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")); } dtst.last(); row_cntr = dtst.getRow(); dtst.beforeFirst(); for (int v = 0; v < row_cntr; v++) { dtst.next(); String msgTyp = dtst.getString(14); toMails = dtst.getString(3); ccMails = dtst.getString(4); bccMails = dtst.getString(8); attchMns = dtst.getString(13); sbjct = dtst.getString(7); msgBdy = dtst.getString(5); nwMsgSntID = Long.parseLong(dtst.getString(1)); errMsg = new String[1]; if (msgTyp.equals("Email")) { if (Global.sendEmail(StringUtils.strip(toMails.replace(",", ";"), seps1), StringUtils.strip(ccMails.replace(",", ";"), seps1), StringUtils.strip(bccMails.replace(",", ";"), seps1), StringUtils.strip(attchMns.replace(",", ";"), seps1), sbjct, msgBdy, errMsg) == false) { Global.updateBulkMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); Global.updateLogMsg(msg_id, "\r\n\r\nMessage to " + (toMails + ";" + ccMails + ";" + bccMails) .replace(";", ",") + " Failed!\r\n" + Arrays.toString(errMsg), log_tbl, dateStr, Global.rnUser_ID); } else { Global.updateBulkMsgSent(nwMsgSntID, dateStr, "1", ""); Global.updateLogMsg(msg_id, "\r\n\r\nMessage to " + (toMails + ";" + ccMails + ";" + bccMails) .replace(";", ",") + " Successfully Sent!\r\n", log_tbl, dateStr, Global.rnUser_ID); } } else if (msgTyp.equals("SMS")) { if (Global.sendSMS(msgBdy, StringUtils.strip( (toMails + ";" + ccMails + ";" + bccMails).replace(";", ","), seps), errMsg) == false) { Global.updateBulkMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); Global.updateLogMsg(msg_id, "\r\n\r\nMessage to " + (toMails + ";" + ccMails + ";" + bccMails) .replace(";", ",") + " Failed!\r\n" + Arrays.toString(errMsg), log_tbl, dateStr, Global.rnUser_ID); } else { Global.updateBulkMsgSent(nwMsgSntID, dateStr, "1", ""); Global.updateLogMsg(msg_id, "\r\n\r\nMessage to " + (toMails + ";" + ccMails + ";" + bccMails) .replace(";", ",") + " Successfully Sent!\r\n", log_tbl, dateStr, Global.rnUser_ID); } } else { } if (v == (row_cntr - 1)) { lastTimeChckd = Global.getDB_Date_time(); } Thread.sleep(500); Global.errorLog = "\r\nMessages to " + (toMails + ";" + ccMails + ";" + bccMails) + " worked on"; Global.writeToLog(); } dtst.close(); lstChckCnt++; Thread.sleep(5000); tmeUp = Global.doesDteTmExcdIntvl("30 second", lastTimeChckd); } while (tmeUp == false); Global.updateLogMsg(msg_id, "\r\n\r\nFinished Sending all Messages!\r\n", log_tbl, dateStr, Global.rnUser_ID); } if (rpt_id == Global.getRptID("Send Outstanding Bulk Messages")) { dtst = Global.selectDataNoParams( rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " ")); } int totl = 0; ResultSetMetaData dtstmd = null; if (dtst != null) { dtst.beforeFirst(); dtst.last(); totl = dtst.getRow(); dtst.beforeFirst(); dtstmd = dtst.getMetaData(); } if (totl > 0) { Global.updateLogMsg(msg_id, "\r\n\r\nSQL Statement successfully run! Total Records = " + totl, log_tbl, dateStr, Global.rnUser_ID); //2. Check and Format Output in the dataset if Required //Based on the 4 Output types decide what to do //None|MICROSOFT EXCEL|HTML|STANDARD Global.updateRptRn(rpt_run_id, "Formatting Output...", 60); //Program.updatePrgrm(prgmID); //worker.ReportProgress(60); //String outputFileName = ""; rpt_SQL = rpt_SQL.replace("\r\n", " ").replace("\n", " ").replace("\r", " "); if (!jsprFileName.equals("")) { jsprFileName = Global.getRptDrctry() + "/jrxmls/" + jsprFileName; String outFlNmOnly = ""; if (outputUsd.equals("MICROSOFT EXCEL")) { uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".xls"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".xls"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else if (outputUsd.equals("PDF")) { uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".pdf"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".pdf"; Global.updateLogMsg(Global.logMsgID, "\r\nBefore Jasper..." + uptFileUrl, Global.logTbl, Global.gnrlDateStr, Global.rnUser_ID); Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else if (outputUsd.equals("HTML")) { uptFileUrl = Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; outFlNmOnly = "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else if (outputUsd.equals("STANDARD")) { uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".pdf"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".pdf"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else if (outputUsd.equals("MICROSOFT WORD")) { uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".rtf"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".rtf"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else if (outputUsd.equals("CHARACTER SEPARATED FILE (CSV)")) { uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".csv"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".csv"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } else { outputUsd = "PDF"; uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".pdf"; outFlNmOnly = "/" + String.valueOf(rpt_run_id) + ".pdf"; Global.runReport(outputUsd, uptFileUrl, jsprFileName, rptTitle, rpt_SQL); } System.out.println("Finished and Opening report..."); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), outFlNmOnly); } } else if (outputUsd.equals("CHARACTER SEPARATED FILE (CSV)")) { Global.exprtDtStToCSV(dtst, Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".csv", isfirst, islast, shdAppnd, rptdlmtr); uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".csv"; } else if (outputUsd.equals("COLUMN CHART")) { Global.exprtToHTMLSCC( dtst, Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html", rptTitle, colsToGrp, colsToCnt, isfirst, islast, shdAppnd); uptFileUrl = Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; } else if (outputUsd.equals("PIE CHART"))// { Global.exprtToHTMLPC( dtst, Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html", rptTitle, colsToGrp, colsToCnt, isfirst, islast, shdAppnd); uptFileUrl = Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; } else if (outputUsd.equals("LINE CHART"))// { Global.exprtToHTMLLC( dtst, Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html", rptTitle, colsToGrp, colsToCnt, isfirst, islast, shdAppnd); uptFileUrl = Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; } else if (outputUsd.equals("STANDARD")) { if (rptLyout.equals("None") || rptLyout.equals("TABULAR")) { if (totl == 1 && dtstmd.getColumnCount() == 1) { rptOutpt += dtst.getString(1); } else { rptOutpt += Program.formatDtSt(dtst, rptTitle, colsToGrp, colsToCnt, colsToSum, colsToAvrg, colsToFrmt); } } else if (rptLyout.equals("DETAIL")) { //Show detail STANDARD Report } if (islast) { Program.writeAFile( Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".txt", rptOutpt); if (Global.callngAppType.equals("DESKTOP")) { Global.upldImgsFTP(9, Global.getRptDrctry(), String.valueOf(Global.runID) + ".txt"); } uptFileUrl = Global.getRptDrctry() + "/" + String.valueOf(rpt_run_id) + ".txt"; } } else { Global.updateRptRnOutptUsd(rpt_run_id, "HTML"); if (rptLyout.equals("None") || rptLyout.equals("TABULAR")) { Global.exprtToHTMLTblr(dtst, Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html", rptTitle, colsToGrp, colsToCnt, colsToSum, colsToAvrg, colsToFrmt, isfirst, islast, shdAppnd); } else if (rptLyout.equals("DETAIL")) { //Show detail HTML Report ResultSet grpngsDtSt = Global.get_AllGrpngs(rpt_id); Global.exprtToHTMLDet(dtst, grpngsDtSt, Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html", rptTitle, isfirst, islast, shdAppnd, orntnUsd, imgCols); } uptFileUrl = Global.getRptDrctry() + "/amcharts_2100/samples/" + String.valueOf(rpt_run_id) + ".html"; } Global.updateRptRn(rpt_run_id, "Storing Output...", 80); //worker.ReportProgress(80); Global.updateLogMsg(msg_id, "\r\n\r\nSaving Report Output...", log_tbl, dateStr, Global.rnUser_ID); Global.updateRptRnOutpt(rpt_run_id, rptOutpt); Global.updateLogMsg(msg_id, "\r\n\r\nSuccessfully Saved Report Output...", log_tbl, dateStr, Global.rnUser_ID); if (msgSentID > 0) { Global.updateRptRn(rpt_run_id, "Sending Output...", 81); Global.updateLogMsg(msg_id, "\r\n\r\nSending Report Via Mail/SMS...", log_tbl, dateStr, Global.rnUser_ID); ResultSet msgDtSt = Global.get_MsgSentDet(msgSentID); msgDtSt.next(); toMails = msgDtSt.getString(1); ccMails = msgDtSt.getString(2); bccMails = msgDtSt.getString(7); sbjct = msgDtSt.getString(5); msgBdy = msgDtSt.getString(3); attchMns = msgDtSt.getString(15) + ";" + uptFileUrl; toPrsnID = Long.parseLong(msgDtSt.getString(8)); toCstmrSpplrID = Long.parseLong(msgDtSt.getString(9)); alertType = msgDtSt.getString(16); errMsg = new String[1]; if (alertType.equals("Email")) { if (Global.sendEmail(StringUtils.strip(toMails.replace(",", ";"), seps1), StringUtils.strip(ccMails.replace(",", ";"), seps1), StringUtils.strip(bccMails.replace(",", ";"), seps1), StringUtils.strip(attchMns.replace(",", ";"), seps1), sbjct, msgBdy, errMsg) == false) { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); } else { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "1", ""); } } else if (alertType.equals("SMS")) { if (Global.sendSMS(msgBdy, StringUtils.strip( (toMails + ";" + ccMails + ";" + bccMails).replace(";", ","), seps), errMsg) == false) { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "0", Arrays.toString(errMsg)); } else { Global.updateAlertMsgSent(nwMsgSntID, dateStr, "1", ""); } } else { } Thread.sleep(1500); } Global.updateLogMsg(msg_id, "\r\n\r\nSuccessfully Completed Process/Report Run...", log_tbl, dateStr, Global.rnUser_ID); Global.updateRptRn(rpt_run_id, "Completed!", 100); if (rptType.equals("Alert(SQL Message)")) { //check if {:to_mail_list} and {:alert_type} parameter was set //NB entire sql output is message body //Report Output file must be added as attachment } } else { Global.updateLogMsg(msg_id, "\r\n\r\nSQL Statement yielded no Results!", log_tbl, dateStr, Global.rnUser_ID); Global.updateLogMsg(msg_id, "\r\n\r\nSuccessfully Completed Process/Report Run...", log_tbl, dateStr, Global.rnUser_ID); Global.updateRptRn(rpt_run_id, "Completed!", 100); } } Program.killThreads(); } Program.killThreads(); } catch (SQLException ex) { Global.errorLog = ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"; Global.writeToLog(); Global.updateRptRn(Global.runID, "Error!", 100); long msg_id = Global.getGnrlRecID("rpt.rpt_run_msgs", "process_typ", "process_id", "msg_id", "Process Run", Global.runID); Global.updateLogMsg(msg_id, "\r\n\r\n\r\nThe Program has Errored Out ==>\r\n\r\n" + Global.errorLog, log_tbl, dateStr, Global.rnUser_ID); Program.killThreads(); } catch (NumberFormatException ex) { Global.errorLog = ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"; Global.writeToLog(); Global.updateRptRn(Global.runID, "Error!", 100); long msg_id = Global.getGnrlRecID("rpt.rpt_run_msgs", "process_typ", "process_id", "msg_id", "Process Run", Global.runID); Global.updateLogMsg(msg_id, "\r\n\r\n\r\nThe Program has Errored Out ==>\r\n\r\n" + Global.errorLog, log_tbl, dateStr, Global.rnUser_ID); Program.killThreads(); } catch (IOException ex) { Global.errorLog = ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"; Global.writeToLog(); Global.updateRptRn(Global.runID, "Error!", 100); long msg_id = Global.getGnrlRecID("rpt.rpt_run_msgs", "process_typ", "process_id", "msg_id", "Process Run", Global.runID); Global.updateLogMsg(msg_id, "\r\n\r\n\r\nThe Program has Errored Out ==>\r\n\r\n" + Global.errorLog, log_tbl, dateStr, Global.rnUser_ID); Program.killThreads(); } catch (InterruptedException ex) { Global.errorLog = ex.getMessage() + "\r\n\r\n" + Arrays.toString(ex.getStackTrace()) + "\r\n\r\n"; Global.writeToLog(); Global.updateRptRn(Global.runID, "Error!", 100); long msg_id = Global.getGnrlRecID("rpt.rpt_run_msgs", "process_typ", "process_id", "msg_id", "Process Run", Global.runID); Global.updateLogMsg(msg_id, "\r\n\r\n\r\nThe Program has Errored Out ==>\r\n\r\n" + Global.errorLog, log_tbl, dateStr, Global.rnUser_ID); Program.killThreads(); } finally { } }
From source file:org.apache.hive.beeline.BeeLine.java
/** * Try to obtain the current size of the specified {@link ResultSet} by jumping to the last row * and getting the row number./*from w w w .j av a 2 s . com*/ * * @param rs * the {@link ResultSet} to get the size for * @return the size, or -1 if it could not be obtained */ int getSize(ResultSet rs) { try { if (rs.getType() == rs.TYPE_FORWARD_ONLY) { return -1; } rs.last(); int total = rs.getRow(); rs.beforeFirst(); return total; } catch (SQLException sqle) { return -1; } // JDBC 1 driver error catch (AbstractMethodError ame) { return -1; } }
From source file:org.talend.metadata.managment.model.DBConnectionFillerImpl.java
@Override public List<TdColumn> fillColumns(ColumnSet colSet, IMetadataConnection iMetadataConnection, DatabaseMetaData dbJDBCMetadata, List<String> columnFilter, String columnPattern) { if (colSet == null || dbJDBCMetadata == null) { return null; }//from w w w. j a v a 2 s .c o m List<TdColumn> returnColumns = new ArrayList<TdColumn>(); List<String> columnLabels = new ArrayList<String>(); Map<String, TdColumn> columnMap = new HashMap<String, TdColumn>(); String typeName = null; ExtractMetaDataUtils extractMeta = ExtractMetaDataUtils.getInstance(); try { String catalogName = getName(CatalogHelper.getParentCatalog(colSet)); Schema schema = SchemaHelper.getParentSchema(colSet); if (catalogName == null && schema != null) { catalogName = getName(CatalogHelper.getParentCatalog(schema)); } String schemaPattern = getName(schema); schemaPattern = " ".equals(schemaPattern) ? null : schemaPattern; //$NON-NLS-1$ String tablePattern = getName(colSet); // --- add columns to table // TDI-28578 Metadata wizard doesn't display tables starting with '/' boolean isOracle = MetadataConnectionUtils.isOracle(dbJDBCMetadata); if (isOracle && tablePattern.contains("/")) {//$NON-NLS-1$ tablePattern = tablePattern.replaceAll("/", "//");//$NON-NLS-1$ //$NON-NLS-2$ } ResultSet columns = dbJDBCMetadata.getColumns(catalogName, schemaPattern, tablePattern, columnPattern); if (MetadataConnectionUtils.isMysql(dbJDBCMetadata)) { boolean check = !Pattern.matches("^\\w+$", tablePattern);//$NON-NLS-1$ if (check && !columns.next()) { columns = dbJDBCMetadata.getColumns(catalogName, schemaPattern, TalendQuoteUtils.addQuotes(tablePattern, TalendQuoteUtils.ANTI_QUOTE), columnPattern); } columns.beforeFirst(); } int index = 0; while (columns.next()) { int decimalDigits = 0; int numPrecRadix = 0; String columnName = getStringFromResultSet(columns, GetColumn.COLUMN_NAME.name()); TdColumn column = ColumnHelper.createTdColumn(columnName); String label = column.getLabel(); label = ManagementTextUtils.filterSpecialChar(label); String label2 = label; ICoreService coreService = CoreRuntimePlugin.getInstance().getCoreService(); if (coreService != null && coreService.isKeyword(label)) { label = "_" + label; //$NON-NLS-1$ } label = MetadataToolHelper.validateColumnName(label, index, columnLabels); column.setLabel(label); column.setOriginalField(label2); int dataType = 0; if (!extractMeta.needFakeDatabaseMetaData(iMetadataConnection)) { dataType = getIntFromResultSet(columns, GetColumn.DATA_TYPE.name()); } // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because obviously the sql // data type it is null and results in a NPE typeName = getStringFromResultSet(columns, GetColumn.TYPE_NAME.name()); typeName = typeName.toUpperCase().trim(); typeName = ManagementTextUtils.filterSpecialChar(typeName); if (typeName.startsWith("TIMESTAMP(") && typeName.endsWith(")")) { //$NON-NLS-1$ //$NON-NLS-2$ typeName = "TIMESTAMP"; //$NON-NLS-1$ } typeName = MetadataToolHelper.validateValueForDBType(typeName); if (MetadataConnectionUtils.isMssql(dbJDBCMetadata)) { if (typeName.toLowerCase().equals("date")) { //$NON-NLS-1$ dataType = 91; // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because obviously // the sql // data type it is null and results in a NPE } else if (typeName.toLowerCase().equals("time")) { //$NON-NLS-1$ dataType = 92; // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because obviously // the sql // data type it is null and results in a NPE } } try { int column_size = getIntFromResultSet(columns, GetColumn.COLUMN_SIZE.name()); column.setLength(column_size); decimalDigits = getIntFromResultSet(columns, GetColumn.DECIMAL_DIGITS.name()); column.setPrecision(decimalDigits); // Teradata SQL Mode no need this column if (!MetadataConnectionUtils.isTeradataSQLMode(iMetadataConnection)) { numPrecRadix = getIntFromResultSet(columns, GetColumn.NUM_PREC_RADIX.name()); } } catch (Exception e1) { log.warn(e1, e1); } // SqlDataType TdSqlDataType sqlDataType = MetadataConnectionUtils.createDataType(dataType, typeName, decimalDigits, numPrecRadix); column.setSqlDataType(sqlDataType); // Null able if (!extractMeta.needFakeDatabaseMetaData(iMetadataConnection)) { int nullable = getIntFromResultSet(columns, GetColumn.NULLABLE.name()); column.getSqlDataType().setNullable(NullableType.get(nullable)); } // Default value String defaultValue = getStringFromResultSet(columns, GetColumn.COLUMN_DEF.name()); // Comment String colComment = getColumnComment(dbJDBCMetadata, columns, tablePattern, column.getName(), schemaPattern); colComment = ManagementTextUtils.filterSpecialChar(colComment); column.setComment(colComment); ColumnHelper.setComment(colComment, column); // TdExpression Object defaultValueObject = null; try { defaultValueObject = columns.getObject(GetColumn.COLUMN_DEF.name()); } catch (Exception e1) { log.warn(e1, e1); } String defaultStr = (defaultValueObject != null) ? String.valueOf(defaultValueObject) : defaultValue; defaultStr = ManagementTextUtils.filterSpecialChar(defaultStr); TdExpression defExpression = createTdExpression(GetColumn.COLUMN_DEF.name(), defaultStr); column.setInitialValue(defExpression); DatabaseConnection dbConnection = (DatabaseConnection) ConnectionHelper.getConnection(colSet); String dbmsId = dbConnection == null ? null : dbConnection.getDbmsId(); if (dbmsId != null) { MappingTypeRetriever mappingTypeRetriever = MetadataTalendType.getMappingTypeRetriever(dbmsId); if (mappingTypeRetriever == null) { @SuppressWarnings("null") EDatabaseTypeName dbType = EDatabaseTypeName .getTypeFromDbType(dbConnection.getDatabaseType(), false); if (dbType != null) { mappingTypeRetriever = MetadataTalendType .getMappingTypeRetrieverByProduct(dbType.getProduct()); } } if (mappingTypeRetriever != null) { String talendType = mappingTypeRetriever.getDefaultSelectedTalendType(typeName, extractMeta.getIntMetaDataInfo(columns, "COLUMN_SIZE"), //$NON-NLS-1$ ExtractMetaDataUtils.getInstance().getIntMetaDataInfo(columns, "DECIMAL_DIGITS")); column.setTalendType(talendType); String defaultSelectedDbType = mappingTypeRetriever.getDefaultSelectedDbType(talendType); column.setSourceType(defaultSelectedDbType); } } try { column.setNullable("YES".equals(getStringFromResultSet(columns, GetColumn.IS_NULLABLE.name()))); //$NON-NLS-1$ } catch (Exception e) { // do nothing } extractMeta.handleDefaultValue(column, dbJDBCMetadata); returnColumns.add(column); columnLabels.add(column.getLabel()); columnMap.put(columnName, column); index++; } columns.close(); if (isLinked()) { ColumnSetHelper.addColumns(colSet, returnColumns); } fillPkandFk(colSet, columnMap, dbJDBCMetadata, catalogName, schemaPattern, tablePattern); } catch (Exception e) { log.error(e, e); } // ~ return returnColumns; }
From source file:net.mlw.vlh.adapter.jdbc.AbstractJdbcAdapter.java
/** * @see net.mlw.vlh.ValueListAdapter#getValueList(java.lang.String, * net.mlw.vlh.ValueListInfo)/*from w w w .ja v a 2 s.c o m*/ */ public ValueList getValueList(String name, ValueListInfo info) { if (info.getSortingColumn() == null) { info.setPrimarySortColumn(getDefaultSortColumn()); info.setPrimarySortDirection(getDefaultSortDirectionInteger()); } int numberPerPage = info.getPagingNumberPer(); if (numberPerPage == Integer.MAX_VALUE) { numberPerPage = getDefaultNumberPerPage(); info.setPagingNumberPer(numberPerPage); } Connection connection = null; PreparedStatement statement = null; ResultSet result = null; try { boolean doSqlPaging = ((getAdapterType() & DO_PAGE) == 0); connection = connectionCreator.createConnection(); StringBuffer query = (sqlPagingSupport != null) ? sqlPagingSupport.getPagedQuery(sql) : new StringBuffer(sql); statement = statementBuilder.generate(connection, query, info.getFilters(), sqlPagingSupport == null && doSqlPaging); if (LOGGER.isDebugEnabled()) { LOGGER.debug(query.toString()); } if (showSql) { System.out.println("sql: " + query.toString()); } result = getResultSet(statement, info); if (sqlPagingSupport != null) { PreparedStatement countStatement = null; ResultSet countResult = null; try { StringBuffer countQuery = sqlPagingSupport.getCountQuery(sql); countStatement = statementBuilder.generate(connection, countQuery, info.getFilters(), false); if (showSql) { System.out.println("count sql: " + countQuery.toString()); } countResult = countStatement.executeQuery(); if (countResult.next()) { info.setTotalNumberOfEntries(countResult.getInt(1)); } } finally { JdbcUtil.close(countResult, countStatement, null); } } else if (doSqlPaging) { result.last(); int totalRows = result.getRow(); info.setTotalNumberOfEntries(totalRows); if (numberPerPage == 0) { numberPerPage = getDefaultNumberPerPage(); } int pageNumber = info.getPagingPage(); if (pageNumber > 1) { if ((pageNumber - 1) * numberPerPage > totalRows) { pageNumber = ((totalRows - 1) / numberPerPage) + 1; info.setPagingPage(pageNumber); } } if (pageNumber > 1) { result.absolute((pageNumber - 1) * numberPerPage); } else { result.beforeFirst(); } } List list = processResultSet(name, result, (doSqlPaging) ? numberPerPage : Integer.MAX_VALUE, info); if (!doSqlPaging) { info.setTotalNumberOfEntries(list.size()); } return new DefaultListBackedValueList(list, info); } catch (Exception e) { LOGGER.error(e); throw new RuntimeException(e); } finally { connectionCreator.close(result, statement, connection); } }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
/** * Read the results locally. Then reset the read position to start and read the * rows again verify that we get the same results next time. * @param sqlStmt - SQL statement to execute * @param colName - columns name to read * @param oneRowOnly - read and compare only one row from the resultset * @throws Exception//from www . ja va 2s . co m */ private void execFetchFirst(String sqlStmt, String colName, boolean oneRowOnly) throws Exception { Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet res = stmt.executeQuery(sqlStmt); List<String> results = new ArrayList<String>(); assertTrue(res.isBeforeFirst()); int rowNum = 0; while (res.next()) { results.add(res.getString(colName)); assertEquals(++rowNum, res.getRow()); assertFalse(res.isBeforeFirst()); if (oneRowOnly) { break; } } // reposition at the begining res.beforeFirst(); assertTrue(res.isBeforeFirst()); rowNum = 0; while (res.next()) { // compare the results fetched last time assertEquals(results.get(rowNum++), res.getString(colName)); assertEquals(rowNum, res.getRow()); assertFalse(res.isBeforeFirst()); if (oneRowOnly) { break; } } }