Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: MS Access DBLookup using ReplicationID

  1. #1
    Join Date
    Mar 2009
    Posts
    11

    Post MS Access DBLookup using ReplicationID

    Problem:
    I want to write data to an Access (2003) target and try in the first place to do a lookup (in target table). The table uses the famous GUID, which is data type Number with fieldproperty ReplicationID.

    Error Message is: "De numerieke waarde ligt buiten het bereik bij kolomnummer 1 (PatnID)",
    which means: "Numeric value is out of bounds".

    This is because of the numerical value: (for example)
    "{8A13E63E-C2B5-44B9-B692-730E2372CFD6}"

    Below the complete LOG. I hope someone can give me a help on:
    - How to read such a table?
    - How to Insert record in such a table using ?


    PDI version:
    I'm using Spoon Kettle version: 3.2.0M1

    2009/03/10 19:50:05 - Excel Input.0 - Starting to run...
    2009/03/10 19:50:05 - Add constants.0 - Starting to run...
    2009/03/10 19:50:05 - Excel Input.0 - Opening openFile #0 : E:\Kettle\Fysio\zitting_dag.xls
    2009/03/10 19:50:05 - Text file output.0 - Starting to run...
    2009/03/10 19:50:05 - Database lookup.0 - Starting to run...
    2009/03/10 19:50:05 - Fysio_lkpup - Transformation has allocated 5 threads and 4 rowsets.
    2009/03/10 19:50:05 - Add sequence.0 - Starting to run...
    2009/03/10 19:50:05 - Excel Input.0 - Get sheet #0.0
    2009/03/10 19:50:05 - Excel Input.0 - Get line #2 from sheet #0.0
    2009/03/10 19:50:05 - Excel Input.0 - Read line with 4 cells
    2009/03/10 19:50:05 - Excel Input.0 - Converted line to row #2 : [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000]
    2009/03/10 19:50:05 - Excel Input.0 - Get sheet #0.0
    2009/03/10 19:50:05 - Excel Input.0 - Out of index error: move to next sheet!
    2009/03/10 19:50:05 - Add sequence.0 - Read row #1 : [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000], [DefaultValue]
    2009/03/10 19:50:05 - Add sequence.0 - Wrote row #1 : [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000], [DefaultValue]
    2009/03/10 19:50:06 - FysioAdministratie - Setting preparedStatement to [SELECT PatnID, NaamGdat FROM Patn WHERE BSN = ? ]
    2009/03/10 19:50:06 - Add constants.0 - Wrote row 1 : [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000]
    2009/03/10 19:50:06 - Database lookup.0 - Checking row: [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000], [DefaultValue], [1]
    2009/03/10 19:50:06 - Database lookup.0 - Field [bsn] has nr. 2
    2009/03/10 19:50:06 - Database lookup.0 - Got row from previous step: [2009/03/10 00:00:00.000], [Cattel], [136064292], [1970/06/15 00:00:00.000], [DefaultValue], [1]
    2009/03/10 19:50:06 - Database lookup.0 - Added 1 values to lookup row: [136064292]
    2009/03/10 19:50:06 - Excel Input.0 - No more files to be processed! (1 files done)
    2009/03/10 19:50:06 - Excel Input.0 - Signaling 'output done' to 1 output rowsets.
    2009/03/10 19:50:06 - Excel Input.0 - Finished processing (I=0, O=0, R=0, W=1, U=1, E=0)
    2009/03/10 19:50:06 - Add constants.0 - Signaling 'output done' to 1 output rowsets.
    2009/03/10 19:50:06 - Add constants.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/03/10 19:50:06 - Add sequence.0 - Signaling 'output done' to 1 output rowsets.
    2009/03/10 19:50:06 - Add sequence.0 - Finished processing (I=0, O=0, R=1, W=1, U=1, E=0)
    2009/03/10 19:50:06 - Database lookup.0 - ERROR (version 3.2.0-M1, build 10101 from 2009-01-29 11.22.08 by buildguy) : Because of an error, this step can't continue:
    2009/03/10 19:50:06 - Database lookup.0 - ERROR (version 3.2.0-M1, build 10101 from 2009-01-29 11.22.08 by buildguy) : Couldn't get row from result set
    2009/03/10 19:50:06 - Database lookup.0 - ERROR (version 3.2.0-M1, build 10101 from 2009-01-29 11.22.08 by buildguy) : [Microsoft][ODBC Microsoft Access-stuurprogramma]De numerieke waarde ligt buiten het bereik bij kolomnummer 1 (PatnID)
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    OK, GUIDs. It's seriously overkill but I guess the use of Access kinda discloses a certain ... behavior.

    GUIDs are in fact not numbers but Strings (Global Unique Identifiers). So if Kettle says they are numbers we are in fact looking at a Kettle bug that needs to get fixed.

    So file a bug report with more details on the source data type and the data type as reported by Kettle.

    Thanks in advance,
    Matt

Tags for this Thread

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.