Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: MS Access Driver

  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Default MS Access Driver

    Hi,
    Can someone instruct me on using JDBC or ODBC for MS-Access? I have a 64-bit processor running Windows 7 Pro Ver 6.1 Build7601 SP1 and Pentaho 7.0.0.0-25
    When I run the Connection (ODBC) from Table Input i input the following :
    Name of the database connection: PentahoData
    Type of database to connect to: MS Access
    Type of database access to use: ODBC
    and
    Name of the ODBC DSN data source:PentahoData
    The error I get is:

    "Error connecting to database [PDIdata] rg.pentaho.di.core.exception.KettleDatabaseException:
    Error occurred while trying to connect to the database
    Driver class 'sun.jdbc.odbc.JdbcOdbcDriver' could not be found, make sure the 'MS Access' driver (jar file) is installed.
    sun.jdbc.odbc.JdbcOdbcDriver"

    There doesn’t seem to be any other choice but ODBC!!
    For JDBC ... I have tried things called DBeaver and UCanAccess, but I can't seem to get Pentaho's attention at all!!
    I very limited experience with ODBC and JDBC so be KIND!!
    Marty Habicht,
    Last edited by gutlez; 11-18-2016 at 05:58 PM.

  2. #2
    Join Date
    Aug 2006
    Posts
    287

    Default

    May want to give the access input step a try:
    http://wiki.pentaho.com/display/EAI/Access+Input

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Kettle/PDI is an enterprise tool in the Java world where JDBC is the prevalent way to access client-server database systems.
    MS Access is an end-user tool and the Jet Engine is used to access MDB files, if not ODBC.
    Sun implemented a JDBC-ODBC Bridge Driver once, but it was dropped from Java 8 after years of inadequacies - that's why you get that error message about a driver class not found.
    Since the MS Jet Engine and the ODBC driver for MS Access are no good in the Java world, JAckcess was developed.
    UCanAccess is built on top of JAckcess, but so is Microsoft-Access-Input, an Input step already recommended to you.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Mar 2014
    Posts
    5

    Default

    caralomi and marabu,

    Thank you both for your prompt reply!

    I have used the Access input already on several occasions and have had no problems. However, I need to lookup some data within an access db and therefore need to use Table Input, and that is where the trouble begins.

    1) there is only an ODBC selection on MS Access db
    2) even when I use the ODBC connection, I get the previous result.

    Is there any JDBC connection to MS Access, or do I have to change DBs?

    Since this is only a first trial that should be easy.

    Thanks,

    Marty Habicht,
    Last edited by gutlez; 11-18-2016 at 05:58 PM.

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    1. I don't understand why you can't use Microsoft-Access-Input to do lookups.
    2. Forget about ODBC if you don't have a functioning bridge driver installed.
    3. If you don't have to use MS Access, just pick a DBMS satisfying the requirements - which we don't know.
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Mar 2014
    Posts
    5

    Default

    Correct me if I am wrong but "Microsoft-Access-Input" does not have any SQL attached to it. I need SQL to get a lookup in the database, with the variable data.

    What I am doing is as follows:

    I have two disparate databases however they have common fields: Name_First, Name_Last, Name_Middle, and DateOfBirth. Both these database were collected within months of one another.

    The other fields in one of the databases is the phone number and the address.

    I want to read the one db for the common fields and then look up the data in those common fields in the second db. Then I want to make a judgement based on the lookup as to the probability that the phone number and address are correct. Under perfect condition for the average person the values should come out as one for one, however this will not always be the case.

    Can you suggest a method to do this by in Pentaho, or is it even possible? I really don't care what the initial dbs are because this is just "proof of concept"... I just chose MS Access because I thought it would be easy to work with!!


    Marty Habicht
    Last edited by gutlez; 11-22-2016 at 07:55 PM.

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Answering your questions will not help you very much, I'm afraid.

    Quote Originally Posted by Marty View Post
    Correct me if I am wrong but "Microsoft-Access-Input" does not have any SQL attached to it.
    You are right, here.

    Quote Originally Posted by Marty View Post
    I need SQL to get a lookup in the database, with the variable data.
    Wrong. Everything you want to do with SQL can also be done without.

    Quote Originally Posted by Marty View Post
    I want to read the one db for the common fields and then look up the data in those common fields in the second db.
    Of course, you could use a correlated subquery or some join if both tables were in the same database, but they are disparate you say.
    Unless the query engine supports multi-database-queries, you are out of luck here.
    So you thought about 2 Table-Input steps, one to fetch the key tuples from R1 (firstName, lastName, middleName, dateOfBirth), and one to do the lookup on R2 (firstName, lastName, middleName, dateOfBirth, phone, address).
    You know how to cascade Table-Input? You'll watch bad performance that way: One SELECT per row will bring the wrath of the SQL fathers down on you.
    Sometimes it's better to fetch all rows once and let Kettle join the streams.
    Depending on the rowset sizes, even a Stream-Lookup may be viable.

    Quote Originally Posted by Marty View Post
    Then I want to make a judgement based on the lookup as to the probability that the phone number and address are correct. Under perfect condition for the average person the values should come out as one for one, however this will not always be the case.
    A simple lookup (equality comparison) won't help here, if I understand right. String distances and fuzzy matching come to mind.

    Quote Originally Posted by Marty View Post
    Can you suggest a method to do this by in Pentaho, or is it even possible?
    I gave some hints, but your problem description is too vague to be more concrete on my side.

    Quote Originally Posted by Marty View Post
    I really don't care what the initial dbs are because this is just "proof of concept"... I just chose MS Access because I thought it would be easy to work with!!
    The POC is a QA showcase for Kettle involving SQL databases? Often HSQLDB is used, then, because nothing must be installed.
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Mar 2014
    Posts
    5

    Default

    Alright... so I'll begin again. I want to learn this "ETL Metadata Injection" which would appear to aid me in getting the desired results. It can't hurt anyways, if it doesn't!

    https://help.pentaho.com/Documentati...data_Injection

    I took this path, and completed the example in Pentaho. However, I ran into a problem with the Tx Date and Time:

    ExecutionTime;ExecutionResult;ExecutionNrErrors;ExecutionLinesRead;ExecutionLinesWritten;ExecutionLinesInput;ExecutionLinesOutput;ExecutionLinesRejected;ExecutionLinesUpdated;ExecutionLinesDeleted;ExecutionFilesRetrieved;ExecutionExitStatus;ExecutionLogText;ExecutionLogChannelId
    1204;Y;1;0;0;0;0;0;0;0;0;0;"2016/11/22 14:36:58 - inject_supplier_metadata - Dispatching started for transformation [inject_supplier_metadata]
    2016/11/22 14:36:58 - Get rows from result.0 - Finished processing (I=0, O=0, R=1, W=2, U=0, E=0)
    2016/11/22 14:36:58 - Add constants.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016/11/22 14:36:58 - Metadata Target.0 - Finished processing (I=7, O=0, R=0, W=7, U=0, E=0)
    2016/11/22 14:36:58 - Metadata Suppliers.0 - Finished processing (I=17, O=0, R=0, W=17, U=0, E=0)
    2016/11/22 14:36:58 - Get File Names.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
    2016/11/22 14:36:59 - Join Rows (cartesian product).0 - Finished processing (I=0, O=0, R=18, W=9, U=0, E=0)
    2016/11/22 14:36:59 - process_supplier_file - Dispatching started for transformation [process_supplier_file]
    2016/11/22 14:36:59 - Microsoft Excel Input.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error processing row from Excel file [C:\Users\user\Documents\Pentaho\data-integration\samples\transformations\Pentaho_Metadata_Injection_Example\data\in\supplier1\data_format_a.xlsx] : org.pentaho.di.trans.steps.excelinput.KettleCellValueException: Cannot convert field ""Tx Date and Time"" from cell on sheet 1, row 2, column 1:
    2016/11/22 14:36:59 - Microsoft Excel Input.0 - org.pentaho.di.core.exception.KettleValueException:
    2016/11/22 14:36:59 - Microsoft Excel Input.0 - v String : couldn't convert string [01/26/2016 07:24:00] to a date using format [dd/MM/yyyy HH:mm:ss] on offset location 19
    2016/11/22 14:36:59 - Microsoft Excel Input.0 - 01/26/2016 07:24:00

    Could you please tell me what I'm doing wrong so that this error will go away?

    Marty
    Last edited by gutlez; 11-22-2016 at 07:55 PM.

  9. #9
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by Marty View Post
    2016/11/22 14:36:59 - Microsoft Excel Input.0 - v String : couldn't convert string [01/26/2016 07:24:00] to a date using format [dd/MM/yyyy HH:mm:ss] on offset location 19
    The date parser was told to interpret 26 as a month which it isn't.
    Try [MM/dd/yyyy HH:mm:ss] for a change.
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Mar 2014
    Posts
    5

    Default

    Thank you marabu,


    That worked beautifully -- I tried (by mistake) to change the Excel parser instead of the Pentaho date parser... and of course that did not work. However, the minute I saw your email, I knew the problem!

    Thanks also for changing my focus to the correct way !!

    Marty
    Last edited by gutlez; 11-23-2016 at 12:28 PM.

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.