Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Escaping single quotes in Pentaho SQL data source

  1. #1
    Join Date
    Jun 2017
    Posts
    18

    Default Escaping single quotes in Pentaho SQL data source

    I have the following SQL datasource:

    Code:
    exec [dbo].[CREATE_EXCEL]  'select * from cf.Revenue where iou_latam_head = ''''Tushar Parikh'''''
    If I have hardcoded the query in the Pentaho CDE datasource, my stored procedure works and creates an excel.

    I cannot however, replace this string with a variable to make it dynamic.

    Code:
    exec [dbo].[CREATE_EXCEL] ${query}
    I have tried to put the variable query, the following values:


    • select * from cf.Revenue where iou_latam_head = ''''Tushar Parikh''''
    • 'select * from cf.Revenue where iou_latam_head = ''''Tushar Parikh'''''
    • select * from cf.Revenue where iou_latam_head = ''Tushar Parikh''

    Nothing works...

    Please your guidance.

  2. #2
    Join Date
    Nov 2011
    Posts
    1,229

    Default

    What's in the server logs?
    Pedro Vale
    --
    CTools Product Development
    http://www.webdetails.pt

  3. #3
    Join Date
    Jun 2017
    Posts
    18

    Default

    If I put 'select * from cf.Revenue where iou_latam_head = ''''Tushar Parikh''''' in the variable, the log shows error at Tushar
    If i put
    select * from cf.Revenue where iou_latam_head = ''''Tushar Parikh'''' in the variable, the excel is empty

  4. #4
    Join Date
    Nov 2011
    Posts
    1,229

    Default

    In the second scenario, can you check what are the parameter values for the SP when the request gets to SQL Server? Using the SQL Server Profiler should give you that info
    Pedro Vale
    --
    CTools Product Development
    http://www.webdetails.pt

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.