SQL Search - how to include search term in query?

[Dave_Bullet]

Any ideas?  or am I just not reading the help properly.

For example, I want to return the types and number of diagrams that have the search term in their name as follows:

select t_diagram.diagram_type, count(*)
from t_diagram
where name like '<search_term>'
group by t_diagram.diagram_type

Questions are:
1. Can I include the users search term in the query?
2. What syntax does EA expect so it can substitute the search term?

Cheers,
David.

[«Midnight»]

What exactly do you mean?

I've recently plugged one of these into an automation project, and it worked like a charm. I just treated it as plain old SQL - and didn't make any assumptions about syntax; nothing even remotely fancy - and it seemed to work right out of the box.

Are you trying to set this up as something in the UI. Sort of 'click and go?'

David

[Dave_Bullet]

Hi David,

I'm using the native EA search facility.  3 options - query builder, SQL editor or add-in.

For the SQL editor - you get a dialogue in EA where you can paste in a SQL statement.  That's great and I can put in something like

select * from t_object.

My question is - Does the EA UI apply your search term "after" the result set is retured the client and filters the resut set on any column where the term is contained:
or
can you specify the search term within your query. ie:

select * from t_object where name = :search_term

or similar.

If I do

select * from t_object

The search term never seems to be applied to the results, ie. whatever I type in the search term when I run the query just dumps the whole of the t_object table to the search window unfilterred.

Hope that makes sense.

The same issue would apply to an add-in search method.... how do you pass in the user entered search term to the add-in method call?  What syntax do you use?

Cheers,
David.

[«Midnight»]

I'll answer the last question now; I've not thought the first part out yet.

I just construct a string, which I can do at run-time, since my user needs to pick something from a list, which I use to filter. [Sometimes it is a package, other times a diagram.] The string is a normal SQL command. Something like:

Dim foo as String = "SELECT * FROM t_object WHERE Package_ID = '" & MyPackage.Package_ID.ToString & "'"

I then use this as the parameter to Repository.SQLQuery. I load the resulting string into an XML document and I'm all set.

I find this much less of a bother than trying to use RunModelSearch.

HTH, David

[Eve]

The search term can be directly substituted in your search sql for the string '<Search Term>' (without quotes).

[Dave_Bullet]

Thanks Simon - that's exactly what I wanted.  Suggest Roy etc.. add this to the help (I couldn't find it).

Thanks again - (tell your boss to give you a bonus)
David.
 

[RoyC]

I have changed the initial example SQL statement in Create Search Definitions to:

SELECT * FROM t_object WHERE NAME='<Search Term>'

Not sure if Simon meant that the quotes were not needed for the actual substituted search term, but they are need in the above statement.

Can I have a bonus too?

[Eve]

To clarify what I said,

If you ran a search with the following sql and the search term Bah

SELECT * FROM t_object WHERE NAME='Foo<Search Term>'

The actual sql that will be executed is

SELECT * FROM t_object WHERE NAME='FooBah'