Topic
  • 4 replies
  • Latest Post - ‏2011-12-26T18:16:26Z by drdamour
DonH
DonH
58 Posts

Pinned topic SQL Parameters in Enterprise Feed

‏2011-12-19T15:02:31Z |
Can I include wild cards in an SQL parameter when I create an Enterprise (JDBC) feed.
e.g. I want to be able to pass part of a word as a parameter.
The following works ok -
select from table1 where column1 like '%web%' It returns all occurrences of "web" in column1.

I would like to parameterize this and thought that something like -
select from table1 where column1 like ':%query_word%' ...would provide me with the ability to pass a 'search argument' (ie. the word "websph" ) into the feed but it doesn't seem to work. I'm sure I need to wrap something around the wildcard characters but what?

Thanks in advance
Updated on 2011-12-26T18:16:26Z at 2011-12-26T18:16:26Z by drdamour
  • deepa_r
    deepa_r
    1 Post

    Re: SQL Parameters in Enterprise Feed

    ‏2011-12-19T17:49:23Z  
    The parameter name has to start with an alphabetic character. That is the reason the query above does not work.

    To achieve the same, you may modify the query by leaving out the "%" from it.
    e.g, select from table1 where column1 like ':query_word'

    And in the parameter form, you can include the wildcards in the value (e.g %web%). Please check to this if this solution is okay for you.
  • DonH
    DonH
    58 Posts

    Re: SQL Parameters in Enterprise Feed

    ‏2011-12-19T19:20:10Z  
    Hi - thanks for the quick response.

    I tried the suggestion but it does not seem to have any effect.
    As a brief test, I created a feed using the suggested sql statement. In a mashup, this feed is visualised through a data viewer widget. I tried to send %web% from a user input widget wired to the data viewer widget but I get nothing out.
    Here's the actual code :

    SELECT * FROM "SVCSTN"."SYS_SOFTWARE_COMPONENT" where "SVCSTN"."SYS_SOFTWARE_COMPONENT"."SVCSTN_NAME"=':SvcStn' and "SVCSTN"."SYS_SOFTWARE_COMPONENT"."DESCRIPTION" like ':product'

    :SvcStn operates correctly - it gets a specific name string and finds the relevan data to get us started.
    The idea behind :product is that it attempts to match a portion of the input to data in the DESCRIPTION field but it doesn't seem to work. The user enters %web% in the user input widget but the feed does not populate the data viewer widget..

    thanks
  • Stan
    Stan
    340 Posts

    Re: SQL Parameters in Enterprise Feed

    ‏2011-12-20T00:03:21Z  
    • DonH
    • ‏2011-12-19T19:20:10Z
    Hi - thanks for the quick response.

    I tried the suggestion but it does not seem to have any effect.
    As a brief test, I created a feed using the suggested sql statement. In a mashup, this feed is visualised through a data viewer widget. I tried to send %web% from a user input widget wired to the data viewer widget but I get nothing out.
    Here's the actual code :

    SELECT * FROM "SVCSTN"."SYS_SOFTWARE_COMPONENT" where "SVCSTN"."SYS_SOFTWARE_COMPONENT"."SVCSTN_NAME"=':SvcStn' and "SVCSTN"."SYS_SOFTWARE_COMPONENT"."DESCRIPTION" like ':product'

    :SvcStn operates correctly - it gets a specific name string and finds the relevan data to get us started.
    The idea behind :product is that it attempts to match a portion of the input to data in the DESCRIPTION field but it doesn't seem to work. The user enters %web% in the user input widget but the feed does not populate the data viewer widget..

    thanks
    I can't be certain what is happening in your case but think maybe the User Input windget or data Viewer widet may need to be tweaked. When I entered the catalog URL with the parameter and clicked the checkbox 'Allow parameter to receive value' - then wired it to User Input widget sending 'Value as any Data' (value is the name of the field I created) - all works well passing '%' as part of the string (e.g. value: %1%).

    You can test if the feed acts properly outside of the widget environment by executing the URL directly and seeing if the XML returned contains the expected data. For instance, the following is the URL from my test feed taken from the DETAILS page:

    https://myHost.com:9443/mashuphub/client/plugin/generate/entryid/131f772d-e0b8-42c4-bcec-79d2281778fa/pluginid/15?id=T100

    I change it to a URL with wildcards and get back the five records expected:
    The SQL is against the SAMPLE Db so you can try it if you wish:
    select * from samples.item where itemid like ':id'
    NOTE: Using the % sign (at least the first one) in a ULR requires it to be specified in URI percent encoding (%25) for the percent sign.

    https://myHost.com:9443/mashuphub/client/plugin/generate/entryid/131f772d-e0b8-42c4-bcec-79d2281778fa/pluginid/15?id=%251%25
  • drdamour
    drdamour
    52 Posts

    Re: SQL Parameters in Enterprise Feed

    ‏2011-12-26T18:16:26Z  
    • Stan
    • ‏2011-12-20T00:03:21Z
    I can't be certain what is happening in your case but think maybe the User Input windget or data Viewer widet may need to be tweaked. When I entered the catalog URL with the parameter and clicked the checkbox 'Allow parameter to receive value' - then wired it to User Input widget sending 'Value as any Data' (value is the name of the field I created) - all works well passing '%' as part of the string (e.g. value: %1%).

    You can test if the feed acts properly outside of the widget environment by executing the URL directly and seeing if the XML returned contains the expected data. For instance, the following is the URL from my test feed taken from the DETAILS page:

    https://myHost.com:9443/mashuphub/client/plugin/generate/entryid/131f772d-e0b8-42c4-bcec-79d2281778fa/pluginid/15?id=T100

    I change it to a URL with wildcards and get back the five records expected:
    The SQL is against the SAMPLE Db so you can try it if you wish:
    select * from samples.item where itemid like ':id'
    NOTE: Using the % sign (at least the first one) in a ULR requires it to be specified in URI percent encoding (%25) for the percent sign.

    https://myHost.com:9443/mashuphub/client/plugin/generate/entryid/131f772d-e0b8-42c4-bcec-79d2281778fa/pluginid/15?id=%251%25
    i ran into this same thing, you must escape the % because % is a escape token in URI's. So %web% get's turned into %we + b + %. And it's up to the code page on the server to figure out what %we and % means.

    So you should be using the encodeURI ECMAscript function to URI sanitize your feed URL.

    i spent a lot of time trying to get something like this working:

    SELECT FOLDERNAME, Description FROM CmAcmDeployedSolution WHERE FOLDERNAME LIKE '%' + ‘:STARTSWITH’ + ‘%'

    that should work for you too, in this case the wildcarding is controlled by the query def, not the UI (it's a preference thing).



    I am Just a new Boy,
    A Stranger in this Town,
    Where are All the Good Times,
    Who's Gonna Show this Stranger Around?
    Check out our Agile ACM Catalogue: Widgets, APIs, & Components for Building Solutions