When to use Database Connector + Database Query and when to use Database Reader

KNIME Analytics Platform
1

Hi,

being fairly new to Knime I see two methods of getting data into Knime from an external database.

  1. Database connector + database query node
  2. Database reader node

Are there any (dis)advantages in using any of the two? Or specific usecases for using the one above the other?

Regards,

 

Geoffrey

 

2

For the example you provide there isn't much difference.  However, the connector can be useful if you wish to preform multiple queries with related requests.  In this case, the connector node would allow you to branch your SQL allowing you to pull out related data without having to write redundant SQL. 

Also, it can be useful for users that are not experts in SQL if they use the connector along with the Databse row and column filters to pull create a more targeted query.

3

Hi 

I have problem connecting to SQL Server 2012 , I have put the Jar and DLL files in the right folder C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\.  I am using Kmine 2.10.3 and JDBC4 

 

I keep getting the following error when executing the Database Reader node

ERROR     Database Reader                    Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'myUserName'.

 

DatabaseURL = jdbc:sqlserver://MyServer:1433;databaseName=DbName;

User Name = myUserName

Password = myPassWord

SQL Statement =  SELECT TOP 5 * FROM [MY_CHANNEL_DIM]

 

But when using the Java Snippet node, I can prove it that I can connect to the database just fine 

using the following code 


// system variables
public class JSnippet extends AbstractJSnippet {

// Your custom variables:
private static final NodeLogger logger = NodeLogger.getLogger(JSnippet.class);

// expression start
  public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
try
{
    String url = "jdbc:sqlserver://MyServer:1433;databaseName=DbBane;Username=myUserName;Password=myPassWord";   
    logger.error( url );
    Connection conn = DriverManager.getConnection(url);
    logger.error("connection created");
    Statement st=conn.createStatement();
    String sql="SELECT TOP 5 * FROM MY_CHANNEL_DIM";
    ResultSet rs=st.executeQuery(sql);
    while(rs.next())    {
        logger.error("Name1: " + rs.getString(1) );
        logger.error("Name2: " + rs.getString(2) );
        logger.error("Name3: " + rs.getString(3) );
        logger.error("Name4: " + rs.getString(4) );
        logger.error("Name5: " + rs.getString(5) );
    }
    if(st!=null)
        st.close();
    if(conn!=null)
        conn.close();
}
catch(SQLException sqle)
{
    logger.error("Sql Exception "+sqle);
}

I cannot get any further meaningful error message fom the log, what seems to be the problem with the Database Reader node?

 

Thanks