Creating Dynamic SQL With LIKE

, ,
1

I tried to use a regular SelectSQL adapter service to write a dynamic query but it wouldn’t work. I want the query to add LIKE ‘%%’. So anything I pass in will be a contains query instead of a direct equal. When you add like in the where clause in SelectSQL it creates a LIKE ‘Variable’. Which is not what I need.

So I switched to try to create dynamic sql and it looks like it works yet I get an error. This is the sql statement in the DynamicSQL adapter.

[

SELECT [recordid]
      ,[wmservice]
      ,[servicestack]
      ,[javastackdump]
      ,[errormsg]
      ,[contact]
      ,[businessunit]
      ,[severity]
      ,[datecreated]
  FROM [PxErrorLog]
where wmservice like '%${serviceName}%'

Then I run it with “t” as the serviceName input variable. When I run it through I get a ServiceException that says Invalid paramter binding. When I look at the SQL generated it looks fine??

Could not run 'getErrorRecordsByServicesName'.
 
com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service PxErrorReport.adapterService:getErrorRecordsByServicesName.
[ADA.1.316] Cannot execute the SQL statement "SELECT [recordid]
      ,[wmservice]
      ,[servicestack]
      ,[javastackdump]
      ,[errormsg]
      ,[contact]
      ,[businessunit]
      ,[severity]
      ,[datecreated]
  FROM [PxErrorLog]
where wmservice like '%t%'". "
(07009/0) [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s)."
[Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
2

In your query, instead of using a variable such as ‘t’, could you not put a question mark in the query to signify input, then define an input variable on the adapter?

I would use a custom SQL.

So, for example: select col1, col2, from table1 where col1 like ‘%?%’

Would this work?

_brett.