Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Trouble using ? to Insert info into SQL Stmt

  1. #1
    Join Date
    Mar 2006
    Posts
    683

    Default RE: Trouble using ? to Insert info into SQL Stmt

    So if I understand this correct. your transaction looks like


    file input -> select values -> table input -> rest of transaction



    you said that the file input is reading the right values.
    did you make use that the "select values" has some fields in it for it to select?
    did you also make sure the "the paremeters to use:" in the table input point to the right fields that are selected in the select values?



    also you can preview the any step by select the step then hitting preview (the like man with a 'p' by his head) and that will show you what it's going to spit out.



    as well as checking the "right click menu" and selecting "show inputs" and "show outputs" and another way to check things is to his the 'check mark' in the tool bar that'll make sure things are being used correctly.



    Just a couple of suggestions,



    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

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

    Default Trouble using ? to Insert info into SQL Stmt

    Attachment: Table Input XML.ktr (Posted by AC, set to Plain text format)

    Hi All,

    I am having troubles making this concept listed below to work.

    "Specify the input step name where we can expect information to come from.
    This information can then be inserted into the SQL statement. The locators
    where we insert information is indicated by ? (question marks)."

    I have verified that the "Get System Info" is indeed passing the correct value to my "Select values" step by outputting it to a text file, looks good. My problem is I then have a "Table input" step which has the "Select values" step as input but I keep getting an error. It is a simple number being passed into the SQL stmt. This is what the SQL is like :

    SELECT
    c.cust_id,
    ? load_key
    FROM
    "8100_USER".customer c

    So if I was getting what I expected from Select values" step the SQL to be executed would become:

    SELECT
    c.cust_id,
    1 load_key
    FROM
    "8100_USER".customer c

    However this is what the log is sayimng my errors are:

    2006/05/05 11:10:06 - Spoon - Transformation opened.
    2006/05/05 11:10:16 - Spoon - Excecuting transformation [Lookup_Test]...
    2006/05/05 11:10:16 - Lookup_Test - Dispatching started for transformation [Lookup_Test]
    2006/05/05 11:10:16 - Lookup_Test - Nr of arguments detected: 10
    2006/05/05 11:10:17 - Database lookup.0 - Connected to database...
    2006/05/05 11:10:17 - Text file output.0 - Opening output stream in default encoding
    2006/05/05 11:10:17 - Spoon - Started.
    2006/05/05 11:10:17 - Get System Info.0 - Starting to run...
    2006/05/05 11:10:17 - Get System Info.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0
    2006/05/05 11:10:17 - Select values.0 - Starting to run...
    2006/05/05 11:10:17 - Database lookup.0 - Starting to run...
    2006/05/05 11:10:17 - Database lookup.0 - Connected to database...
    2006/05/05 11:10:17 - Select values 2.0 - Starting to run...
    2006/05/05 11:10:17 - Text file output.0 - Starting to run...
    2006/05/05 11:10:17 - Table input.0 - Starting to run...
    2006/05/05 11:10:17 - Dummy (do nothing).0 - Starting to run...
    2006/05/05 11:10:17 - Select values.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0
    2006/05/05 11:10:17 - Select values 2.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0
    2006/05/05 11:10:17 - Database lookup.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0
    2006/05/05 11:10:17 - Table input.0 - ERROR : Unexpected error in '-' : java.lang.NullPointerException
    2006/05/05 11:10:17 - Table input.0 - Finished reading query, closing connection.
    2006/05/05 11:10:17 - Table input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1
    2006/05/05 11:10:17 - Text file output.0 - Finished processing (I=0, O=2, R=1, W=0, U=0, E=0
    2006/05/05 11:10:17 - Lookup_Test - ERROR : Errors detected!
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Get System Info
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Select values
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Database lookup
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Select values 2
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Text file output
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Table input
    2006/05/05 11:10:17 - Lookup_Test - Looking at step: Dummy (do nothing)
    2006/05/05 11:10:17 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0
    2006/05/05 11:10:17 - Spoon - The transformation has finished!!

    I would like to thank all in advance for helping.

    ~kent

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

    Default RE: Trouble using ? to Insert info into SQL Stmt

    A piece of XML of the TableInput step would help to understand what it is you're doing.
    I AM woried about the NullPointerException in the Table input step and we should check this. However, perhaps that's something that has been fixed already. What version are you on?


    Thanks,



    Matt

  4. #4
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Trouble using ? to Insert info into SQL Stmt

    Attachment: Table Input XML.ktr 1) "Get System Info" is taking cmd line arg "LOADKEY" which is being passed in as string
    2) "Select values" is taking that "LOADKEY" as input and using "META DATA" adjusting to become Integer
    3) "Table input" is the sql I've shown and is taking input from "Select values" step.
    4) "Text file output" is pushing out the results to just to look at data and get passed this error.


    Thanks for your help ... I do appreciate the hard work!



    :-)



    I've incl. the xml



    ~kent

  5. #5
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Trouble using ? to Insert info into SQL Stmt

    Whoops version in 2.3.0


    Thanks



    ~kent

  6. #6
    Join Date
    Sep 2005
    Posts
    1,403

    Default RE: Trouble using ? to Insert info into SQL Stmt

    I removed the ? and the input for the "Table input" step and the db will return the rows. That was using DB2 UDB 8.2 as my source.


    Next I switched the connection to be my Target which is Oracle 9i and it returned the rows too. (Note I already had some customers in the target customer table, this was a test of sorst). I then adjusted the Oracle as source "table input" to use the ? and get input values for LOADKEY again from me "Select values" step ... and IT WORKED!



    So I noticed that I have the db name for my DB2 source in quotes as the db name begins with numerics :



    "8100_USER".customer c



    Do you think that is throwing off the the parsing step which replaces the ? with the input value?



    Thanks



    ~kent

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

    Default RE: Trouble using ? to Insert info into SQL Stmt

    I don't know enough about DB2 UDB to answer that question. It should work fine with the double quotes though. That's what they are there for.
    I know that specifying the ? parameters in the select normally works fine.
    It's the standard java way of specifying a prepared statement, no extra treatment is being done to the SQL by Kettle.

    Matt

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.