"sqlite db import"

tokmakofftokmakoff Member Posts: 2 Contributor I
edited May 2019 in Help
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.
Tagged:

Answers

  • fischerfischer Member Posts: 439 Maven
    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
  • tokmakofftokmakoff Member Posts: 2 Contributor I
    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.
  • fischerfischer Member Posts: 439 Maven
    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
Sign In or Register to comment.