"sqlite db import"

tokmakoff

Dear RM Developers,

I'd like to import data from an sqlite DB and I've succeeded in installing a driver for it within RM 5.0.010.
(in fact I've tried two: sqlite-jdbc-3.6.20.1.jar and sqlitejdbc-v056.jar)

However, when I make a simple process using the Stream Database operator, I am encountering the
following error:

Aug 10, 2010 1:23:56 PM WARNING: Failed to fetch database tables: java.sql.SQLException: No SQL value type found for date

After some digging, it seems that sqlite does not have a Date type.  http://www.sqlite.org/datatype3.html
"SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values"
I looked through the code and see that DatabaseHandler makes use of an Ontology to create Attributes and fails on "date".

Is there any way I can use sqlite for data input within RM, or should I just try to export the data to a DB that works out of the box?

thanks,

Andrew.

fischer

Hi,

Does your example set contain date attributes? In that case I would say, it won't work out. If it does not, I would say we should make the database reader ignore  this problem unless a date column is really needed.

Can you post a stack trace?

Best,
Simon

tokmakoff

Hi,

Sorry for the late reply (I didn't see your reply for some reason)...
Yes, our dataset includes dates (a timestamp).

Please find attached the stack trace from an import attempt:

----------------------------------------------------------------------------------------------------------------
Exception: com.rapidminer.operator.UserError
Message: Database error occurred: No SQL value type found for date
Stack trace:

  com.rapidminer.operator.io.CachedDatabaseExampleSource.createExampleSet(CachedDatabaseExampleSource.java:96)
  com.rapidminer.operator.io.AbstractExampleSource.read(AbstractExampleSource.java:52)
  com.rapidminer.operator.io.AbstractExampleSource.read(AbstractExampleSource.java:36)
  com.rapidminer.operator.io.AbstractReader.doWork(AbstractReader.java:123)
  com.rapidminer.operator.Operator.execute(Operator.java:771)
  com.rapidminer.operator.execution.SimpleUnitExecutor.execute(SimpleUnitExecutor.java:51)
  com.rapidminer.operator.ExecutionUnit.execute(ExecutionUnit.java:709)
  com.rapidminer.operator.OperatorChain.doWork(OperatorChain.java:368)
  com.rapidminer.operator.Operator.execute(Operator.java:771)
  com.rapidminer.Process.run(Process.java:899)
  com.rapidminer.Process.run(Process.java:795)
  com.rapidminer.Process.run(Process.java:790)
  com.rapidminer.Process.run(Process.java:780)
  com.rapidminer.gui.ProcessThread.run(ProcessThread.java:62)

Cause
Exception: java.sql.SQLException
Message: No SQL value type found for date
Stack trace:

  com.rapidminer.tools.jdbc.StatementCreator.registerSyntaxInfo(StatementCreator.java:143)
  com.rapidminer.tools.jdbc.StatementCreator.buildTypeMap(StatementCreator.java:127)
  com.rapidminer.tools.jdbc.StatementCreator.<init>(StatementCreator.java:97)
  com.rapidminer.tools.jdbc.StatementCreator.<init>(StatementCreator.java:92)
  com.rapidminer.tools.jdbc.DatabaseHandler.connect(DatabaseHandler.java:301)
  com.rapidminer.tools.jdbc.DatabaseHandler.getConnectedDatabaseHandler(DatabaseHandler.java:263)
  com.rapidminer.tools.jdbc.DatabaseHandler.getConnectedDatabaseHandler(DatabaseHandler.java:249)
  com.rapidminer.tools.jdbc.DatabaseHandler.getConnectedDatabaseHandler(DatabaseHandler.java:827)
  com.rapidminer.operator.io.CachedDatabaseExampleSource.createExampleSet(CachedDatabaseExampleSource.java:89)
  com.rapidminer.operator.io.AbstractExampleSource.read(AbstractExampleSource.java:52)
  com.rapidminer.operator.io.AbstractExampleSource.read(AbstractExampleSource.java:36)
  com.rapidminer.operator.io.AbstractReader.doWork(AbstractReader.java:123)
  com.rapidminer.operator.Operator.execute(Operator.java:771)
  com.rapidminer.operator.execution.SimpleUnitExecutor.execute(SimpleUnitExecutor.java:51)
  com.rapidminer.operator.ExecutionUnit.execute(ExecutionUnit.java:709)
  com.rapidminer.operator.OperatorChain.doWork(OperatorChain.java:368)
  com.rapidminer.operator.Operator.execute(Operator.java:771)
  com.rapidminer.Process.run(Process.java:899)
  com.rapidminer.Process.run(Process.java:795)
  com.rapidminer.Process.run(Process.java:790)
  com.rapidminer.Process.run(Process.java:780)
  com.rapidminer.gui.ProcessThread.run(ProcessThread.java:62)

Note also that username/password are not supported by sqlite and thus,
the mandatory nature of them (enforced by the StreamDatabase Operator)
means that I have to supply a bogus username and password to keep the
Operator happy.  Is there some way to adjust this such that the SQLite
driver is used as a flag to remove this mandatory username/password
requirment?

thanks,

Andrew.

fischer

Hi,

I have made RM ignore missing type mappings and trigger a warning rather than an exception.

It certainly won't help in case you actually want  to use timestamps. However, reading again over our conversation so far, I get the impression that it does not make a lot of sense :-)

- I have been asking whether your ExampleSet contains dates. That question was nonsense, because you are reading from the DB. I thought you were writing.
- Your answer was then "Yes". However, if SQLite does not support dates, how can your table have dates? Or are you differentiating between date and timestamp?

Anyway, could you check the latest SVN version tomorrow morning?

Best,
Simon