Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Trouble with SQL query - completely stumped

  1. #1
    Join Date
    Feb 2016
    Posts
    16

    Default Trouble with SQL query - completely stumped

    I want to read in a list of values, and then search a MySQL table for records containing those values. The data will populate a Table Component. Sounds simple, right? I cannot get this query to return data for the life of me, and I'm at the end of my rope. The query is:

    Code:
     select device_name from devices where ${vmlist_param}
    vmlist_param is created via a File Upload HTML input control and javacript that builds the query string. I have a separate text component where I print out the value of vmlist_param with Dashboards.GetParameterValue('vmlist_param'); its value is:

    Code:
    device_name = 'DB-DSVA01' or device_name = 'DB-DSVA02' or device_name = 'DB-DSVA03' or device_name = 'DB-DSVA04' or device_name = 'DB-DSVA06' or device_name = 'DB-prod-Template' or device_name = 'DB-test-Template' or device_name = 'LB01' or device_name = 'LB02' or device_name = 'MGMT-DSVA01' or device_name = 'MGMT-DSVA02' or device_name = 'NDB-DSVA01' or device_name = 'NDB-DSVA02' or device_name = 'NDB-DSVA03' or device_name = 'NDB-DSVA04' or device_name = 'NDB-DSVA05' or device_name = 'NDB-DSVA06' or device_name = 'NDB-DSVA07' or device_name = ''
    If I cut & paste the above string into the SQL query, the table will populate with 22 rows. TO summarize:

    1. Parameterized SQL query returns no data.
    2. Parameter looks right when output to the browser.
    3. Values perform right when manually substituted into the query.

    I am at a loss for how to fix this. Does anybody see what I am doing wrong?

    Thanks,

    -k
    Last edited by kc1234; 07-27-2017 at 02:47 PM.

  2. #2
    Join Date
    Feb 2016
    Posts
    16

    Default

    Never did get to the bottom of this, but I suspect the problem has to do with the quotes in the parameter value.

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

    Default

    For fun, try:

    mysql>SET@s='select device_name from devices where ?';
    mysql>PREPARE stmt2 FROM@s;
    mysql>SET@a= 'device_name = 'DB-DSVA01' or device_name = 'DB-DSVA02'';
    mysql>EXECUTE stmt2 USING@a;

    and determine if that works. I'm willing to bet that it doesn't.
    I can't remember if C-Tools uses prepared statements as the backing process, but PDI does...

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

    Default

    Correct, CTools use Java prepared statements, so you can't replace the whole where clause with a parameter.
    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.