Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: fireToDB function

  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Default fireToDB function

    Has anyone managed to get the correct value from this function yet?

    I have seen 3 previous postings on this topic but has not yet seen the solution. I am wondering what kind of object was returned from this function?


    var strConn = "MY Connection";
    var strSQL = "SELECT COUNT(*) FROM ...";
    var xArr = fireToDB(strConn, strSQL);


    Eddie

  2. #2

    Default

    Don't do it. Use "Table Input" object.

  3. #3
    Join Date
    Sep 2006
    Posts
    10

    Default

    Why not? I can see the power in it. I just haven't figure out how to get the results out. I'm in the process of checking the source code out. It's just a matter of time to figure it out.

  4. #4
    Join Date
    Sep 2006
    Posts
    10

    Default

    Found the solution!

    Looking at the java source code has revealed that the returned value from java was an ArrayList.

    After playing with the javascripts syntax, it appears that we need something like this:

    x = xArr[0];
    value=x[0];

    From javascripts, the fireToDb function returned an ARRAY. However, each element of the array is also an ARRAY object!

    Hope that it help.


    Eddie

  5. #5

    Question Can fireToDB step to send Insert / Update SQL to DB ?

    Hi,

    One question about fireToDB step:
    Can fireToDB step to send Insert / Update statements to DB, too ?

    For instance:

    var strConn = "MY Connection";
    var strSELECT = "SELECT X, Y , Z FROM ANY_TABLE ";
    var xArr = fireToDB(strConn, strSELECT);

    var parameters = xArr[0];
    parameter1=x[0];
    parameter2=x[1];
    parameter3=x[2];

    var strINSERT = "INSERT INTO OTHER_TABLE (parameter1, parameter2, parameter3) ";
    fireToDB(strConn, strINSERT);

    Can i do that ?

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    no

    Regards,
    Sven

  7. #7
    Join Date
    Sep 2006
    Posts
    10

    Default Can fireToDB step to send Insert / Update SQL to DB ?

    I think fireToDb can sent insert/update statement to the database. There was a previous posting on this forum on this topic. Essentially, you are free to send any sql string that works with a Java JDBC connection. You need to modify the example and construct a full sql insert statement before sent to fireToDB. Parameters do not work here.

    Personally, I don't think this is the most appropriate way to use fireToDB to do something as suggested.

    I suggest the following:

    1. If the 2 tables can be accessed through the same connection. Then use a single SQL step and the statement below:

    INSERT into OTHER_TABLE (col1, col2, col3)
    select A, B, C from SOME_TABLE.

    2. If the 2 tables are from 2 separate connections, the use a SQL step and then an INSERT step.

  8. #8

    Default

    The my problem is more complex.
    I need create a dimension called Bridge Table, with hierarchy.
    In my case, i need find a set of surrogate keys (n values) that are filtereds by a set of descriptive values (like lookup). After this, i need insert the surrogate keys in bridge table. (pair of surrogate keys).
    For instance:

    1) At first, i get the set of descriptive values (like extraction):
    SELECT DSC_VALUE FROM ANY_TABLE WHERE ....

    2) Find SK filtereds by previous result (like lookup):
    SELECT SURROGATE_KEY FROM DIMENSION_X WHERE DSC_DIMENSION_VALUE IN ( ? )
    OBS: The question mark (?) must have all set of values returned in step 1. In other words, it can have n parameters (e.g. ... DSC_DIMENSION_VALUE IN ( ?, ?, ? ... ). Is it possible to do that ?

    3) Finally, i need insert the surrogate keys returned int the step 2 above (in pairs).
    Imagine that 4 Surrogate keys were returned (SK_1, SK_2, SK_3 and SK_4)
    INSERT INTO BRIDGE_TABLE (SK_1 , SK_2 );
    INSERT INTO BRIDGE_TABLE (SK_1 , SK_3 )
    INSERT INTO BRIDGE_TABLE (SK_3 , SK_4 );

    Thus, i chose Java Script entry.
    I have create some plugins for Kettle, and i also create a java script function for insert/update/delete, because fireToDB doesn't do this.

    Can anybody help me?

    Regards
    Dhiogo

  9. #9
    Join Date
    May 2006
    Posts
    4,882

    Default

    And what do you want help with? fireToDB is for selects only.

    Regards,
    Sven

  10. #10

    Default

    Sven,

    I created a other java script function called updateDB for insert/update/delete.
    I had thought that fireToDB function would did that initially, because the sample about fireToDB show:

    // Fires a SQL-Statement to a valid Pentaho / Kelttle connection
    // and returns, if possible, an array.
    // Keep in mind, it is possible to kill your Data
    // if you send a delete or truncate to your tables.
    //
    // Usage:
    // fireToDB(var,var);
    // 1: String - The Name of the Connection.
    // 2: String - The SQL to send.
    //
    // 2006-11-15

    I had to analize the code to realize that.
    But, Is there other way to resolve my problem without Java Script ?
    I'm using the version 2.5.2.

    All the best
    Dhiogo

  11. #11
    Join Date
    May 2006
    Posts
    4,882

    Default

    Output the input for your step 3) to an insert/update step?
    For the moment I don't see anything else.

    Regards,
    Sven

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

    Default

    Why would these things not be possible with normal transformations?
    The whole goal of having an ETL tool is to avoid the use of complex programs.

    Just wondering...

    Matt

  13. #13

    Default

    All steps (in my example) are developed only with Java Script.
    A doubt:
    Is it possible to pass one or more parameters with only one (?) question mark ? (in my example ... SELECT SURROGATE_KEY FROM DIMENSION_X WHERE DSC_DIMENSION_VALUE IN ( ? ) ). I don't know the exact quantity.
    If the answer is no, Is my step 2 poss�*ble to be made with any other kettle'step?

    Thanks,
    Dhiogo
    Last edited by Dhiogo; 11-01-2007 at 11:49 AM.

  14. #14
    Join Date
    Sep 2006
    Posts
    10

    Default

    I think Matt has a point. This thing should be possible with simple transformations.

    First, the 1& 2 step could be done with a single query:

    SELECT SURROGATE_KEY FROM DIMENSION_X WHERE DSC_DIMENSION_VALUE IN ( SELECT DSC_VALUE FROM ANY_TABLE WHERE .... )

    This SQL construct should work on most servers (not sure about mysql)!

    Second, i think that on the pairs front, Dhiogo probably want something like:
    INSERT INTO BRIDGE_TABLE (SK_1 , SK_2 );
    INSERT INTO BRIDGE_TABLE (SK_2 , SK_3 )
    INSERT INTO BRIDGE_TABLE (SK_3 , SK_4 );

    The pairs could be quite easily construct from a simple Java scripts step using the "prev_row" object as in the example. Then a Insert/Update step would finish the job.

    Eddie

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

    Default

    The "IN LIST" operation can be done by removing the IN LIST from the query and by doing a Merge Join in a transformation.
    And I bet that the pair generation can be done by a "Normaliser" step.
    As usual, YMMV, but I highly doubt that lot of JavaScript is needed here ;-)

    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.