Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Spoon gives error with string in execute sql step

  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Default Spoon gives error with string in execute sql step

    I have a execute sql step as follows:

    UPDATE ? SET ? = '?' WHERE CONTACT_ID = ? ;

    or
    UPDATE ? SET ? = ? WHERE CONTACT_ID = ? ;
    with pre-prepared the value



    Whenever the new column value (3rd placeholder) has string signs (single quotes ' ) in it , the step fails.

    I also tried to replace single quotes with 2 of them as in sql statement as following:
    UPDATE WH_CF_CONT SET hobby = 'bs '' abcdefghijklmnopqrstuvwxyz' WHERE CONTACT_ID = 3

    which works properly in the database. But kettle would fail with the following error

    2007/10/09 22:35:49 - Target_TeamConnect_Warehouse - UPDATE WH_CF_CONT SET hobby = 'bs '' abcdefghijklmnopqrstuvwxyz' WHERE CONTACT_ID = 3 ;
    2007/10/09 22:35:49 - Execute SQL UPDATE DATAMART TEXT.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Unexpected error in ' : be.ibridge.kettle.core.exception.KettleDatabaseException:
    2007/10/09 22:35:49 - Execute SQL UPDATE DATAMART TEXT.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : Couldn't execute SQL: UPDATE WH_CF_CONT SET hobby = 'bs '' abcdefghijklmnopqrstuvwxyz' WHERE CONTACT_ID = 3 ;
    2007/10/09 22:35:49 - Execute SQL UPDATE DATAMART TEXT.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) :
    2007/10/09 22:35:49 - Execute SQL UPDATE DATAMART TEXT.0 - ERROR (version 2.5.0, build 25002 from 2007/05/04 00:20:04) : ORA-00911: invalid character

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

    Default

    In execute SQL step the parameters markers are manually replaced, so not being used as real JDBC parameter markers. If you use quotes in your input you have to know how to escape them.

    Regards,
    Sven

  3. #3
    Join Date
    Mar 2007
    Posts
    158

    Default

    Hi all,

    It’s been great experience using kettle Integration tool. But now a days I m facing a very awkward problem.
    When I click on spoon.bat it asks me for the repository and then I click on no repository option. After that it doean’t start.
    On other machines its working fine but on a particular machine its not.
    What may be the reason?

  4. #4
    Join Date
    Apr 2007
    Posts
    21

    Default

    Sven

    I thought it might be a defect.
    How can I escape a string sign ?

    In databases it's possible by converting to double the string sign.

    Although I did it the same it still didn't work.
    It's not possible to remove the string signs completely, since they are part of the actual data.
    It seems that kettle has somehow a problem with it.

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

    Default

    It's only in the execute SQL step, why not use insert/update or update step. Those use real parameters.

    Regards,
    Sven

  6. #6
    Join Date
    Apr 2007
    Posts
    21

    Default

    I can't use those steps. Because target table name and column name are dynamically identified and parametric.
    I requested an enhancement for table name to be read from a field just like table output.

    But for now I don't have a choice other than using execute sql.

    I want to understand if there is a workaround ,or is it a defect , or is it just like this and string signs in data can not be passed in execute sql and will be like this even in the future versions.


    Thank you for your help
    Last edited by farbodf; 10-10-2007 at 03:54 PM.

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

    Default

    execute sql will replace the ? by your parameters in place... so if use the right kind of quotes around the ? and you escape the quotes as you should for your specific database it will work.

    In your case:
    - loose the ; at the end
    - use Oracle alternative quoting for a test.

    Regards,
    Sven

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

    Default

    It’s been great experience using kettle Integration tool. But now a days I m facing a very awkward problem.
    When I click on spoon.bat it asks me for the repository and then I click on no repository option. After that it doean’t start.
    On other machines its working fine but on a particular machine its not.
    What may be the reason?
    You're probably building RC2 yourself. There was a regression problem caused by a bug fix. Update and try again.

  9. #9
    Join Date
    Apr 2007
    Posts
    21

    Default

    Thanks for your advice
    I tried to remove the end of statement ";" and it seemed to work.

    But I tested if further and have both ; and single quote in the data . For simplifying , I replaced parameters with actual values and the results are

    WORKS:
    UPDATE WH SET hobby = 'A ;'' B' WHERE ID =3

    FAILS:
    UPDATE WH set hobby = 'A '' ; B' WHERE ID =3

    Both statements work in the database directly.

    Please note that in real world I don't have control over the data and also as I previously explained , I can not use update or insert/update since I set the target table through parameters.

    I still believe it's in the method which kettle interprets end of statement with the semicolons ";"

    Thanks

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

    Default

    Since the parameters are put in place in the SQL without parameters the code checking for quotes will "freak out" on your data. The problem is in Databases.execStatements().

    You can raise a JIRA request for it.

    Regards,
    Sven

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.