Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Kettle and MS access

  1. #1
    Join Date
    Jun 2011
    Posts
    102

    Default Kettle and MS access

    Hello,
    since java 8 you can't use anymore a odbc connection in Kettle to do stuff in a MS access db, cause the jdbc-odbc driver was removed...
    So, i'm trying to make it work using the UCanAccess driver and 'Generic database' connection type in Kettle since everything i'm working on is on a MS access db.

    my setup is:
    Custom connection url: jdbc:ucanaccess://c:/tmp/pippo.accdb
    Custom driver class name: net.ucanaccess.jdbc.UcanaccessDriver

    as illustrated on UCanAccess site
    Code:
    Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");  /* often not required for Java 6 and later (JDBC 4.x) */
    Connection conn=DriverManager.getConnection("jdbc:ucanaccess://<mdb or accdb file path>",user, password);  
    // for example:  
    Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb");
    When i press the try button it pop up this error:
    Code:
    Error connecting to database [asd] : org.pentaho.di.core.exception.KettleDatabaseException: 
    Error occurred while trying to connect to the database
    
    Error connecting to database: (using class net.ucanaccess.jdbc.UcanaccessDriver)
    Implementing class
    
    
    org.pentaho.di.core.exception.KettleDatabaseException: 
    Error occurred while trying to connect to the database
    
    Error connecting to database: (using class net.ucanaccess.jdbc.UcanaccessDriver)
    Implementing class
    
    
        at org.pentaho.di.core.database.Database.normalConnect(Database.java:472)
        at org.pentaho.di.core.database.Database.connect(Database.java:370)
        at org.pentaho.di.core.database.Database.connect(Database.java:341)
        at org.pentaho.di.core.database.Database.connect(Database.java:331)
        at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
        at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2795)
        at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:598)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
        at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
        at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
        at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)
        at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137)
        at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
        at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
        at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
        at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
        at org.eclipse.jface.window.Window.open(Window.java:796)
        at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:80)
        at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47)
        at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)
        at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60)
        at org.pentaho.di.ui.trans.step.BaseStepDialog.showDbDialogUnlessCancelledOrValid(BaseStepDialog.java:779)
        at org.pentaho.di.ui.trans.step.BaseStepDialog$AddConnectionListener.widgetSelected(BaseStepDialog.java:1374)
        at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
        at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
        at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
        at org.pentaho.di.ui.trans.steps.tableinput.TableInputDialog.open(TableInputDialog.java:436)
        at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:127)
        at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:8789)
        at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:3179)
        at org.pentaho.di.ui.spoon.trans.TransGraph.mouseDoubleClick(TransGraph.java:775)
        at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
        at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
        at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
        at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
        at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1359)
        at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7990)
        at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9290)
        at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:685)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
    Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
    Error connecting to database: (using class net.ucanaccess.jdbc.UcanaccessDriver)
    Implementing class
    
        at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:587)
        at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)
        ... 55 more
    Caused by: java.lang.IncompatibleClassChangeError: Implementing class
        at java.lang.ClassLoader.defineClass1(Native Method)
        at java.lang.ClassLoader.defineClass(Unknown Source)
        at java.security.SecureClassLoader.defineClass(Unknown Source)
        at java.net.URLClassLoader.defineClass(Unknown Source)
        at java.net.URLClassLoader.access$100(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at net.ucanaccess.jdbc.DefaultJackcessOpener.open(DefaultJackcessOpener.java:28)
        at net.ucanaccess.jdbc.DBReference.<init>(DBReference.java:161)
        at net.ucanaccess.jdbc.DBReferenceSingleton.loadReference(DBReferenceSingleton.java:51)
        at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:99)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:571)
        ... 56 more
    
    Custom connection url       :jdbc:ucanaccess://c:/tmp/pippo.accdb
    Custom driver class name       :net.ucanaccess.jdbc.UcanaccessDriver
    Can any of you tell me what i'm missing?

    Thanks in advance,
    Lom
    Last edited by Lomior; 02-09-2017 at 06:20 AM.

  2. #2
    Join Date
    Sep 2015
    Posts
    4

    Default

    Hello,

    I have the same error, I leave the .jar files in ..\Pentaho\data-integration\lib but nothing

    pdi v. 6.0
    ucanaccess 4.0.3
    java 8
    however if I run console.bat from ucancaccess directory and input access file, it works fine.
    I don't know what i do wrong.

    Any suggest?
    Thanks, Dolors

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Why are you not just using the MS Access Input step?

    UCanAccess hasn't been updated since 2003 according to their SourceForge page.
    UCanAccess uses Jackcess to read Access files (just like PDI does!)
    Last edited by gutlez; 01-11-2018 at 12:29 PM.

  4. #4
    Join Date
    Sep 2015
    Posts
    4

    Default

    I'm not using MS Access Input step because i need a connection to do more steps with data MSaccess.
    I've founded a solution, I had an old version *.jar file to ucanaccess in destiny directory. I've updated it and does work the connection

    Thanks for your time,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.