I have just added the JDBC driver for MySQL to the KNIME preferences. I now want to connect to my database to use some of the data in a clustering task.
What I do:
Drag 'Database Reader' onto the workflow
Double-click the 'Database Reader' to configure it
Choose com.mysql.jdbc.Driver from the 'Database Driver' drop-down menu
Enter the URL of my database
Enter username and password
I know I have a working connection to the database, because I get information about the database when i press 'Fetch Metadata' in the 'Database Browser'. In the 'SQL Statement' box I have entered
select * from app_info;
where 'app_info' is the name of a table in my database. When i press 'OK' to go back to the workflow, the console prints an error message saying
WARN DatabaseReaderConnection PreparedStatment not support by database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';) table_9791296 WHERE 1 = 0' at line 1
WARN Database Reader com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';) table_9791296 WHERE 1 = 0' at line 1
I have no idea how the last part of that error message has been appended to my original SQL statement...at no point have I entered the statement ';) table_9791296 WHERE 1 = 0'. Does anyone know why this is happening?
As soon as you connect to the database, the node tries immediately to fetch the metadata to make the down-stream nodes configurable. We use that WHERE 0=1 to retrieve this metadata without fetching actual data. In some case, you don't want to do that. Therefore we have added a checkbox in the Database Reader which allows you to switch this feature off. Just a guess, do you have a semi-colon after your table name in the syntax? This needs to be removed.