Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: Get variable value from database.

  1. #1
    Join Date
    Jun 2017
    Posts
    25

    Default Get variable value from database.

    Greeting,

    I need to download pdf file from url stored inside a database. I use the step in this link - https://www.joyofdata.de/blog/batch-...entaho-kettle/

    Name:  Capture.jpg
Views: 1037
Size:  14.6 KB
    this is the job for the task that im doing.
    focus on the lower side of the job, i set a path for the pdf file download output, then i create a transformation to get the url and filename for the pdf. after that i pass it to a job that only have http step to download the pdf file by refering the path, filename and url from previous step.

    Name:  Capture1.PNG
Views: 991
Size:  9.0 KB
    this is the transformation to get the url and filename for the pdf which im not sure did i do it correctly or not.
    can anyone help me regarding on how to get data from database to set as variable value?

    regards,
    Rayzal

  2. #2
    Join Date
    Sep 2011
    Posts
    152

    Default

    are you getting any error? in downloading the files?



    or you just want to know how to get data from database and set that in variable ?

  3. #3
    Join Date
    Jun 2017
    Posts
    25

    Default

    Quote Originally Posted by rajeshbcrec View Post
    are you getting any error? in downloading the files?



    or you just want to know how to get data from database and set that in variable ?
    when i run the job, for http, its shown the result = false. but no error logged.

    i just want to know how to get data from database and set it in variable.

  4. #4
    Join Date
    Aug 2008
    Posts
    27

    Default

    not sure if i understood correctly, but "to get data from database to set as variable value" you can use:
    - table input step (you need to type the query and the result must be 1 row)
    - then use set variable step and assign the result from the table input into a variable.
    - after that you can use the variable in the parent job and the next transformation


  5. #5
    Join Date
    Jun 2017
    Posts
    25

    Default

    Quote Originally Posted by cetd View Post
    not sure if i understood correctly, but "to get data from database to set as variable value" you can use:
    - table input step (you need to type the query and the result must be 1 row)
    - then use set variable step and assign the result from the table input into a variable.
    - after that you can use the variable in the parent job and the next transformation
    if i have more than 1 row result?

    i read the step mention here - http://forums.pentaho.com/showthread...-from-database
    about how to use modified java script, but when i use it, the java script didnt capture the data from my db
    see screenshot below.
    Name:  Capture.PNG
Views: 1003
Size:  11.0 KB
    the transformation
    Name:  Capture2.jpg
Views: 1000
Size:  20.3 KB
    the table input preview
    Name:  Capture1.PNG
Views: 1010
Size:  16.8 KB
    the modified java script script
    Name:  Capture3.jpg
Views: 1000
Size:  26.1 KB
    the modified java script preview

  6. #6
    Join Date
    Sep 2011
    Posts
    152

    Default

    if there are many rows then set variable will give error, so for that you have to use table input in one transformation and then pass row to result and then use that in another job to set the variable. just run the job for each input row.

    Please post your java script code

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by rajeshbcrec View Post
    if there are many rows then set variable will give error
    That's not what I recall... I seem to recall that it will merrily set the variables all the way along, but the next transformation will only see the last one.

    From the script that I saw in this thread, OP is setting a blank JS Variable, and then assigning it to a PDI variable. That will always cause the variables to be blank when you run.
    If you try to get "", the HTTP step should rightly say "Ok, you've got it" even though nothing was retrieved.

  8. #8
    Join Date
    Jun 2017
    Posts
    25

    Default

    Quote Originally Posted by gutlez View Post
    From the script that I saw in this thread, OP is setting a blank JS Variable, and then assigning it to a PDI variable. That will always cause the variables to be blank when you run.
    If you try to get "", the HTTP step should rightly say "Ok, you've got it" even though nothing was retrieved.
    so how should i declare the variable in JS to point to database data?

  9. #9
    Join Date
    Jun 2017
    Posts
    25

    Default

    https://drive.google.com/open?id=0B1...2laTm85RTZ4U0k

    here is my transformation and job, can any1 point out which step did i do wrong?

  10. #10
    Join Date
    Jun 2017
    Posts
    25

    Default

    Hi again, I manage to get the variable for the url...but the variable for the filename is a failed. it didnt get the value from the table as intended.
    Besides that, only 1 file downloaded where it should be 10 pdf file as i manage to generate 10 text file that retrieved from the table.

    attached below are the screenshot of the job and transformation for your referal.

    Name:  Capture.PNG
Views: 999
Size:  28.1 KB
    Name:  Capture1.PNG
Views: 996
Size:  11.6 KB
    Name:  Capture2.PNG
Views: 1013
Size:  18.7 KB
    Name:  Capture3.jpg
Views: 1000
Size:  25.1 KB
    Name:  Capture4.jpg
Views: 996
Size:  30.7 KB

    please help me...need to settle this job by today and proceed with creating a control file for the pdi job to run.

    Regards,
    Rayzal

  11. #11
    Join Date
    Sep 2011
    Posts
    152

    Default

    I have modified your jobs, please run and let me know if it works.
    Attached Files Attached Files

  12. #12
    Join Date
    Jun 2017
    Posts
    25

    Default

    Quote Originally Posted by rajeshbcrec View Post
    I have modified your jobs, please run and let me know if it works.
    it's work perfectly. Thanks a lot

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.