Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Variables in SQL step in Job

  1. #1
    Join Date
    May 2007
    Posts
    128

    Default Variables in SQL step in Job

    Any clues on this error? Just trying to update a logging table with values specified.


    Code:
    2009/07/07 12:04:01 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : An error occurred executing this job entry : 
    2009/07/07 12:04:01 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Couldn't execute SQL: UPDATE AUDIT.AUDITTABLEPROCESSING SET TableFinalRowCount = ${TableFinalRowCount}, ErrorRowCount = ${ErrorRowCount}, ExtractRowCount1 = ${ExtractRowCount1}, ExtractRowCount2 = ${ExtractRowCount2}, ExtractCheckValue1 = ${ExtractCheckValue1}, ExtractCheckValue2 = ${ExtractCheckValue2}, NumRecords_INSERT = ${NumRecords_INSERT}, NumRecords_UPDATE = ${NumRecords_UPDATE}, NumRecords_DELETE = ${NumRecords_DELETE}, SuccessfulProcessingInd = 'Y'  WHERE TableProcessID = ${TableProcessID}
    2009/07/07 12:04:01 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : 
    2009/07/07 12:04:01 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unrecognized SQL escape 'tablefinalrowcount' at line position 79.

  2. #2
    Join Date
    Feb 2009
    Posts
    321

    Default

    mm weird....try with '${my_variable}'

  3. #3
    Join Date
    May 2007
    Posts
    128

    Default

    Code:
    2009/07/07 14:08:08 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : An error occurred executing this job entry : 
    2009/07/07 14:08:08 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Couldn't execute SQL: UPDATE AUDIT.AUDITTABLEPROCESSING SET TableFinalRowCount = ${my_variable}, ErrorRowCount = ${ErrorRowCount}, ExtractRowCount1 = ${ExtractRowCount1}, ExtractRowCount2 = ${ExtractRowCount2}, ExtractCheckValue1 = ${ExtractCheckValue1}, ExtractCheckValue2 = ${ExtractCheckValue2}, NumRecords_INSERT = ${NumRecords_INSERT}, NumRecords_UPDATE = ${NumRecords_UPDATE}, NumRecords_DELETE = ${NumRecords_DELETE}, SuccessfulProcessingInd = 'Y'  WHERE TableProcessID = ${TableProcessID}
    2009/07/07 14:08:08 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : 
    2009/07/07 14:08:08 - Audit -- Update TableProcessing - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unrecognized SQL escape 'my' at line position 63.
    Last edited by elgabito; 07-07-2009 at 02:11 PM.

  4. #4
    Join Date
    Feb 2009
    Posts
    321

    Default

    add simple quotes

  5. #5
    Join Date
    May 2007
    Posts
    128

    Default

    Thank you very much - that seems to have corrected that issue.

    Now it just seems to not be passing any variables for some reason (NULLS)


    edit: to follow up - I see in logging that it is setting the variables correctly. The scope is set to "parent job" - however when I use them in the SQL step in the parent job - they come out as empty strings:

    Code:
    341: 2009/07/08 08:59:23 - Set PkgExecKey.0 - Set variable ${PkgExecID} to value [39]
    476: 2009/07/08 08:59:24 - Set Variables.0 - Set variable ${Y_TableProcessKey} to value [32]
    1217: 2009/07/08 08:59:24 - SetExtract1.0 - Set variable ${ExtractRowCount1} to value [219]
    1249: 2009/07/08 08:59:24 - SetExtract2.0 - Set variable ${ExtractRowCount2} to value [219]
    2157: 2009/07/08 08:59:25 - SetNumRecordsINSERT.0 - Set variable ${NumRecords_INSERT} to value [0]
    2165: 2009/07/08 08:59:25 - SetCheckValue1.0 - Set variable ${ExtractCheckValue1} to value [219]
    2216: 2009/07/08 08:59:25 - SetNumRecordsERROR.0 - Set variable ${NumRecords_ERROR} to value [0]
    2232: 2009/07/08 08:59:25 - SetNumRecordsUpdated.0 - Set variable ${NumRecords_UPDATE} to value [0]
    2353: 2009/07/08 08:59:25 - Set FinalRowCount.0 - Set variable ${TableFinalRowCount} to value [220]
    
    
    
    2009/07/08 08:59:25 - DW_DEV1 - UPDATE AUDIT.AUDITTABLEPROCESSING SET TableFinalRowCount = '', ErrorRowCount = '', ExtractRowCount1 = '', ExtractRowCount2 = '', ExtractCheckValue1 = '', ExtractCheckValue2 = '', NumRecords_INSERT = '', NumRecords_UPDATE = '', NumRecords_DELETE = '', SuccessfulProcessingInd = 'Y'  WHERE TableProcessID = ''
    Last edited by elgabito; 07-08-2009 at 09:04 AM.

  6. #6
    Join Date
    Feb 2009
    Posts
    321

    Default

    mmm try with "valid in the root job" or "valid in the java virtual machine"

  7. #7
    pstoellberger Guest

    Default

    i had some problems with the variable scope as well
    e.g it works fine in kitchen/pan/spoon but not on carte. had to use "valid in the JVM"

    like hernanthiebaut said.. maybe valid in root job is enough

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

    Default

    Additionally you can't reliably set variables in the same transformation as where you use them

    Regards,
    Sven

  9. #9
    pstoellberger Guest

    Default

    exactly, like the "set variables" step says: never set and use variables in the same transformation

    i usually have a set variables transformation in the beginning of my job and use it only after this transformation is being executed

  10. #10
    Join Date
    May 2007
    Posts
    128

    Default

    I am using it in a transformation where the variable was set in a prior, earlier transformation (same job). Not sure what I'm doing wrong I just can't get it to work at all.

  11. #11
    pstoellberger Guest

    Default

    increase the scope to the java virtual machine
    if it doesn't work with that its something else

  12. #12
    Join Date
    May 2007
    Posts
    128

    Default

    Still having this issue - now on a different transformation (never solved the first one). The Transformation sets the variable - i confirm that it is set properly in the logs - but then empty strings seem to get passed into the sql statement.

    I have tried valid in the parent job, valid in the JVM - nothing seems to work. On the SQL step I have:

    Code:
    insert into profile_etl (min_id,max_id) values('${start_id}','${stop_id}')
    I have tried both with and without single quotes.

    Any thoughts? Is this a bug? I have no idea why it's not working.
    Attached Images Attached Images   

  13. #13
    Join Date
    Feb 2009
    Posts
    321

    Default

    gabyto...set the variable with the same name, remove quotes and '${}'
    the step is asking the field (this contains the value) and the name of variable(new field)

    example

    field variable

    max_id MAX_ID VALID IN THE JAVA VIRTUAL MACHINE....

    try this and tell us later....

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.