Hi,
I tried using the SQLite connector + DB Query Reader to navigate through the Photo library database on my Mac.
For those who use Photos, here is the simple query I tried:
SELECT * FROM ZPERSON
But, I am getting the following error:
WARN DB Query Reader 2:8 Error during fetching data from the database: java.lang.IllegalStateException: java.sql.SQLFeatureNotSupportedException
Yet, it I do this, it works fine:
SELECT zfullname, zfacecount FROM ZPERSON
It works perfectly fine.
Also, using the “*” operator on other tables worked fine.
What to do ?
I guess it comes from the jdbc, but I don’t know what to do.
Also, using a program like DB Browser for SQLite let me execute the afore mentioned query without a problem.
Hi,
I can’t even open the Photos library with the SQLite Connector. Which version of macOS are you on and can you share your workflow?
Kind regards
Alexander
Hi diyoyo,
do you see any further information in the KNIME log regarding the SQLFeatureNotSupportedException e.g. the complete stack trace? This might help to detect the problem. It would also help to know the data type that SQLite reports when querying the column. To get it use the DB Table Selector node and the Custome Query option to enter your query. Then execute the node and have a look at the DB Spec tab output port view. It should show the DB Type, DB Type Name and the corresponding KNIME type.
Attached you can find a workflow that writes a png images into a binary column into SQLite and then reads them back.
Bye
Tobias SQLite_Read_Write_BLOB.knwf (50.6 KB)
I use Catalina. In fact, I had to enter the full path manually, since it was not possible to go to the package contents of the photo library using the File CHooser.
Also, I had to add Knime to the list of authorized software for the Photos database, in my macOs settings.
Here’s the workflow.ApplePhotos.knwf (27.9 KB)
Here is the log. Indeed, it seems to be a problem of BLOB.
2020-03-17 22:48:26,177 : ERROR : SwingWorker-pool-4-thread-1 : : DefaultDBRowOutput : DB Query Reader : 0:33 : Exception reading data row: java.sql.SQLFeatureNotSupportedException
org.knime.core.data.convert.map.MappingException: java.sql.SQLFeatureNotSupportedException
at org.knime.database.datatype.mapping.DBCellValueProducerFactory.lambda$0(DBCellValueProducerFactory.java:80)
at org.knime.core.data.convert.map.DefaultExternalToKnimeMapper.map(DefaultExternalToKnimeMapper.java:122)
at org.knime.database.agent.reader.impl.DefaultDBRowOutput.next(DefaultDBRowOutput.java:182)
at org.knime.database.agent.reader.impl.DefaultDBRowOutput.next(DefaultDBRowOutput.java:1)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.lambda$0(DBViewPreviewPanel.java:472)
at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:149)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.getDataTable(DBViewPreviewPanel.java:479)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.doInBackgroundWithContext(DBViewPreviewPanel.java:409)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.doInBackgroundWithContext(DBViewPreviewPanel.java:1)
at org.knime.core.util.SwingWorkerWithContext.doInBackground(SwingWorkerWithContext.java:106)
at javax.swing.SwingWorker$1.call(SwingWorker.java:295)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at javax.swing.SwingWorker.run(SwingWorker.java:334)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLFeatureNotSupportedException
at org.sqlite.jdbc4.JDBC4ResultSet.unused(JDBC4ResultSet.java:347)
at org.sqlite.jdbc4.JDBC4ResultSet.getBlob(JDBC4ResultSet.java:388)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.DatabasePlugin.lambda$54(DatabasePlugin.java:399)
at org.knime.database.datatype.mapping.DBCellValueProducerFactory.lambda$0(DBCellValueProducerFactory.java:78)
… 15 more
2020-03-17 22:48:26,217 : WARN : AWT-EventQueue-0 : : DBViewPreviewPanel : DB Query Reader : 0:33 : Error during fetching data from the database: java.lang.IllegalStateException: java.sql.SQLFeatureNotSupportedException
java.util.concurrent.ExecutionException: java.lang.IllegalStateException: java.sql.SQLFeatureNotSupportedException
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at javax.swing.SwingWorker.get(SwingWorker.java:602)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.doneWithContext(DBViewPreviewPanel.java:420)
at org.knime.core.util.SwingWorkerWithContext.done(SwingWorkerWithContext.java:163)
at javax.swing.SwingWorker$5.run(SwingWorker.java:737)
at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.run(SwingWorker.java:832)
at sun.swing.AccumulativeRunnable.run(AccumulativeRunnable.java:112)
at javax.swing.SwingWorker$DoSubmitAccumulativeRunnable.actionPerformed(SwingWorker.java:842)
at javax.swing.Timer.fireActionPerformed(Timer.java:313)
at javax.swing.Timer$DoPostEvent.run(Timer.java:245)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Caused by: java.lang.IllegalStateException: java.sql.SQLFeatureNotSupportedException
at org.knime.database.agent.reader.impl.DefaultDBRowOutput.next(DefaultDBRowOutput.java:185)
at org.knime.database.agent.reader.impl.DefaultDBRowOutput.next(DefaultDBRowOutput.java:1)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.lambda$0(DBViewPreviewPanel.java:472)
at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:149)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.getDataTable(DBViewPreviewPanel.java:479)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.doInBackgroundWithContext(DBViewPreviewPanel.java:409)
at org.knime.database.node.component.sqleditor.fragment.preview.DBViewPreviewPanel$2.doInBackgroundWithContext(DBViewPreviewPanel.java:1)
at org.knime.core.util.SwingWorkerWithContext.doInBackground(SwingWorkerWithContext.java:106)
at javax.swing.SwingWorker$1.call(SwingWorker.java:295)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at javax.swing.SwingWorker.run(SwingWorker.java:334)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.knime.core.data.convert.map.MappingException: java.sql.SQLFeatureNotSupportedException
at org.knime.database.datatype.mapping.DBCellValueProducerFactory.lambda$0(DBCellValueProducerFactory.java:80)
at org.knime.core.data.convert.map.DefaultExternalToKnimeMapper.map(DefaultExternalToKnimeMapper.java:122)
at org.knime.database.agent.reader.impl.DefaultDBRowOutput.next(DefaultDBRowOutput.java:182)
… 13 more
Caused by: java.sql.SQLFeatureNotSupportedException
at org.sqlite.jdbc4.JDBC4ResultSet.unused(JDBC4ResultSet.java:347)
at org.sqlite.jdbc4.JDBC4ResultSet.getBlob(JDBC4ResultSet.java:388)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.connection.wrappers.AbstractResultSetWrapper.getBlob(AbstractResultSetWrapper.java:689)
at org.knime.database.DatabasePlugin.lambda$54(DatabasePlugin.java:399)
at org.knime.database.datatype.mapping.DBCellValueProducerFactory.lambda$0(DBCellValueProducerFactory.java:78)
… 15 more
Hi,
unfortunately I cannot reproduce this, as I always get the error “The database has been closed”. Do you know a way around that?
Kind regards
Alexander
Hi,
I did some further googling and found this issue on GitHub: https://github.com/xerial/sqlite-jdbc/issues/135. It seems like reading blobs like that is not supported by the SQLite JDBC driver, sorry.
Kind regards
Alexander
I don’t know a way around your “database has been closed”. Have you given Knime the right to access Photos in Mac?
As for the JDBC issue, is there a way in Knime to silent fail when it comes to Blob retrieval, and still display the other columns using the ‘*’ operator?
Or do I have to do a bit like my current workflow, ie listing the columns, and focusing only on the ones that are really of interest to me, or even just filtering out those that have blobs in them?
Hi,
KNIME does not appear in the list of apps that want to access photos. Anyways, I think the problem is the JDBC driver for SQLite, which simply does not support the action the node is trying to perform. Unfortunately I don’t know any way around that. Maybe there are alternative drivers out there?
Kind regards
Alexander
you can try DB Column Filter node with Type Selection option to always exclude BLOB columns while in DB Table Selector node leaving select * from table_name SQL statement.
I’ll try @ipazin suggestion. As for permissions, just for going fully in-depth, maybe you can setup a workflow that just opens a file (any file, not the db) from within the photo library package. It should bring a prompt from your Mac to give Knime the correct permission. At least that’s what happened for me.
