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
deepa_r 110000Q4GN1 Post
Re: SQL Parameters in Enterprise Feed2011-12-19T17:49:23ZThis is the accepted answer. This is the accepted answer.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 100000JKUT58 Posts
Re: SQL Parameters in Enterprise Feed2011-12-19T19:20:10ZThis is the accepted answer. This is the accepted answer.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..
Stan 120000HAGM340 Posts
Re: SQL Parameters in Enterprise Feed2011-12-20T00:03:21ZThis is the accepted answer. This is the accepted answer.
- DonH 100000JKUT
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:
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.
drdamour 0600029F9D52 Posts
Re: SQL Parameters in Enterprise Feed2011-12-26T18:16:26ZThis is the accepted answer. This is the accepted answer.
- Stan 120000HAGM
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