Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: getting the output from fireToDB

  1. #1
    Join Date
    Jul 2007
    Posts
    10

    Question getting the output from fireToDB

    Hi All,

    Can any one tell me how can I get the output generated by the fireToDB into another variable.

    What I wanted to do was.

    var strConn = "dbConnection";
    var strSQL = "SELECT COUNT(*) FROM mytable_20070710";
    var xArr = fireToDB(strConn, strSQL);

    and get the result of the query ie. the number of rows in the table in to a variable.

    I tried;

    Alert(xArr),Alert(xArr[1].toString()),Alert(xArr[0].toString()) but could not get the result i wanted.

    If this is not possible to do this with javascript, plz help me with some other recommendation.

    Any how I cannot do this with the table_input because mytable name is dynamically generated and comes in the from the previous step and I found that variables are replaced only after the where clause and not in the table list.

    Eg. mytable_XXXXXXXX is not alway the same the XXXXXX comes in the from the previous step.

    CAnnot do it as select count(*) from ?

    Thnx

    Sayy

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

    Default

    var strConn = "dbConnection";
    var strSQL = "SELECT COUNT(*) FROM mytable_20070710";
    var xArr = fireToDB(strConn, strSQL);
    I've never gotten this to work myself, will check later.

    Any how I cannot do this with the table_input because mytable name is dynamically generated and comes in the from the previous step and I found that variables are replaced only after the where clause and not in the table list.

    Eg. mytable_XXXXXXXX is not alway the same the XXXXXX comes in the from the previous step.

    CAnnot do it as select count(*) from ?
    No... this is normal. Parameters just use the SQL parameters, and in any SQL language you can't bind a table. The reason for this is that the table determines the access rights e.g. and the list of columns. If you could bind it it would be a very big security hole and an optimization problem. So it's a db/jdbc restriction instead of a kettle one. You can however use variables to replace the table e.g.

    Have a look at the "samples\jobs\process all tables" example in your distribution.

    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    1

    Default

    Quote Originally Posted by sboden View Post
    I've never gotten this to work myself, will check later.
    ...
    Regards,
    Sven
    I've also tried to access values returned by the fireToDB, but
    with no success. FireToDB returns an object that should be two dimensional table. I can convert that object to an array from which I can retrieve Objects that can be converted to arrays,
    but from those arrays I still can't get the actual Strings and Integers that should have been returned from the DB. I only get another set of Objects from which I can't get out of anything meaningful (maybe it was arrays again that I got, but no sign
    of the actual values.)

    There is no other practical way to do the transformation that I am trying to do, but by the help of the fireToDB. So I would appreciate and be greatly delighted if someone could show me how the result returned by the fireToDB should actually be handled.

    Regards,

    Ferre

  4. #4
    Join Date
    May 2008
    Posts
    3

    Default

    var strConn = "blah";
    var strSQL = "SELECT IPAddress FROM asdfs";
    var xArr = fireToDB(strConn, strSQL);
    Alert(xArr[0][0]);

    Works for me.

  5. #5
    Join Date
    Apr 2008
    Posts
    20

    Default

    This worked for me as well... and solved my problem described in another post "Lookup with no parameters"

    thanks

  6. #6
    Join Date
    Mar 2008
    Posts
    22

    Default fireToDb -Can't get it to work for me

    I read the other posts but it doesn't seem to work for me. When I hit the test buttton I get the following error message pointing to my Alert message on line 23.

    Couldn't execute this script! Error on position (23:0) :
    Java class "java.lang.Object" has no public instance field or method named "0". (script#23)

    here's my code:

    var
    strConn = "dbConn";

    var
    strSQL = "Select PK_ID As FK_FEED_LISTING_ID from (SELECT PK_ID FROM FEED_LISTING WHERE " ;
    strSQL = strSQL &
    "(FK_FEED_TYPE_ID = 1) ;
    strSQL = strSQL &
    "ORDER BY FEED_FILE_DT) where rownum <=1;"

    var
    xArr = fireToDB(strConn, strSQL);

    Alert(xArr[0][0]);

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

    Default

    Missing " in your text, if it's exactly the same text as you use.

    And do you really want to use "&" for concatenation?

    Regards,
    Sven

  8. #8
    Join Date
    Mar 2008
    Posts
    22

    Default fireToDb -Can't get it to work for me

    The code given was just an abreviated sample and I intend to replace the "&" for concatenation. The problem is the error message.

    Couldn't execute this script! Error on position (23:0) :
    Java class "java.lang.Object" has no public instance field or method named "0". (script#23)

    I've tried using a simple query but the problem seems to be related to referencing the array.

    Var xArr = fireToDB(strConn, strSQL);
    Alert(xArr[0][0]);

    If I remove the "[0][0]" from the Alert line and change it to "Alert(xArr);" it works but returns a non-readable hex character. Should I have compatibility mode on? Am I running the right version 3.0.3??

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

    Default

    Check for nullness on xArr

    Regards,
    Sven

  10. #10
    Join Date
    Dec 2007
    Posts
    27

    Default

    Hello,
    we have the same problem as awoliver. On expression xArr[0][0] we also get an error with "Java class "java.lang.Object" has no public instance field or method named "0". Now we made a Database Input Step before the JavaScript step. The query works in the Database Input step, but in the following JavaScript step we cannot access the query result but only if it is a single value - not if we have several rows with several fields. Is there any other method to access the query result, which is not the here proposed xArr[0][0]?

    Thanks.

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

    Default

    It's one of those things which probably would have been better left out... check for null on the a[0][0]... you will see it's null in your case.

    Regards,
    Sven

  12. #12
    Join Date
    Apr 2009
    Posts
    8

    Exclamation

    Code:
    var strConn = "DB";
    var strSQL = "SELECT CURDATE()";
    var xArr = fireToDB(strConn, strSQL);
    Alert(xArr[0][0]);
    This works for me as long as I "Run this transformation".

    However when using "Verify this tranformation" or Modified Java Script Value's "Test script", I get this error as well:

    Code:
    Java class "java.lang.Object" has no public instance field or method named "0"

  13. #13
    Join Date
    Nov 2008
    Posts
    143

    Default

    I guess this is mostly for PDI's developers:

    Is this "fireToDB() not working on debug and script test" thing a known issue? If it is, then is is there a bug-tracker or something?
    If it isn't, how do we open one?

    Thanks in advance,

    Renato Back

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.