Hello, As we all know, KNIME doesn't support concurrent queries to the same database (unless the JDBC driver allows concurrent queries on the same connection - mine doesn't), because it uses one connection object only.
Has anybody tried using a Java Snippet node to connect to a database, execute a statement and return the results? I think this could be a solution to circumvent this problem, but I don't want to reinvent the wheel...
Best wishes,
Michal
A rather crude (but working) code example (takes "statement" column as parameter, returns up to three String columns):
// system imports
import org.knime.base.node.jsnippet.expression.AbstractJSnippet;
import org.knime.base.node.jsnippet.expression.Abort;
import org.knime.base.node.jsnippet.expression.Cell;
import org.knime.base.node.jsnippet.expression.ColumnException;
import org.knime.base.node.jsnippet.expression.TypeException;
import static org.knime.base.node.jsnippet.expression.Type.*;
import java.util.Date;
import java.util.Calendar;
import org.w3c.dom.Document;
// Your custom imports:
import java.sql.*;
import java.util.*;
// system variables
public class JSnippet extends AbstractJSnippet {
// Fields for input columns
/** Input column: "statement" */
public String c_statement;
// Fields for output columns
/** Output column: "error" */
public String out_error;
/** Output column: "out1" */
public String[] out_out1;
/** Output column: "out2" */
public String[] out_out2;
/** Output column: "out3" */
public String[] out_out3;
// Your custom variables:
// expression start
public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
out_out1 = null;
out_out2 = null;
out_out3 = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//host:port/DB","user","pass");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(c_statement);
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
ArrayList<ArrayList<String>> results = new ArrayList<ArrayList<String>>();
for (int i=1;i<=columnsNumber;i++) {
results.add(new ArrayList<String>());
}
ArrayList <String> resultList1 = new ArrayList<String>();
String line="empty";
while (rs.next())
for (int i=1;i<=columnsNumber;i++) {
line=(String)rs.getString(i);
results.get(i-1).add(line);
}
out_out1 = results.get(0).toArray(new String[results.get(0).size()]);
if (columnsNumber>1)
out_out2 = results.get(1).toArray(new String[results.get(1).size()]);
if (columnsNumber>2)
out_out3 = results.get(2).toArray(new String[results.get(2).size()]);
rs.close();
stmt.close();
con.close();
} catch (ClassNotFoundException e) {
out_error = e.getMessage();
} catch (SQLException sqle) {
out_error = sqle.getMessage();
}
// expression end
}
}