Hello,

I have to use like operator in SQL Server Query. But I couldn't find any examples of using the "like" operator and SQLParameter() together. This query will be used frequently and needs to be fast. I want to benefit from the blessings of the Query Plan Caching.

I'm not sure how to write "name like '%?%'" or "name like ?"... 

You can see code lines:

tw.local.searchCriteria = "EDBA00F9";

tw.local.sqlStatement="select code, name, taxtNo from Suppliers";
if(tw.local.searchCriteria !=""){
	tw.local.sqlStatement+=" where name like '%?%'  ";
	tw.local.parameters = new tw.object.listOf.SQLParameter();
	tw.local.parameters[0]= new tw.object.SQLParameter();
	tw.local.parameters[0].value= tw.local.searchCriteria; 
	//tw.local.parameters[0].type= "vharchar";
	//tw.local.parameters[0].mode="IN";
}else 
	tw.local.parameters=null;
tw.local.sqlStatement+="  order by name asc";
tw.local.dataSourceName=tw.env.CUSTOM_DB_DATASOURCE;
tw.local.maxRows=200;
tw.local.returnType = "Suppliers";

Error message:

com.lombardisoftware.component.common.workflow.WorkflowProcessItemException: An exception occurred in activity "Execute" of "Service Flow" with name "Get Suppliers List". Task instance id "Task.559". Details: "The index 1 is out of range.".

.

.RestHelper W Unexpected database exception

Thank you!

------------------------------
Mehmet Selim ARLI
Consultant
Aksis
Istanbul AE
+905309556707
------------------------------

In case you ever need to check out exactly how to do this, the DB connector is just a wrapper to the Java Prepared Statement. Part of this is that if you set it up right, doing things like adding quotes around values is not required. So you really want your statement to be something like (ignoring your if logic)

tw.local.sqlStatement = "select code, name, taxNo from Suppliers where name like ?";

You would then modify the tw.local.searchCriteria to be "%EDBA00F9%" as the value. If this didn't work you'd have to write a different query for each possible style of "like" that you could do, of which there is pretty much an infinite number. By this I mean you'd have to write a different query for "EDBA00F9%", "%EDBA00F9", "%EDBA00F9%" etc. By allowing you to control the value of the thing that is being matched to, the Prepared Statement is far more reusable.

------------------------------
Andrew Paier
------------------------------

Hello Andrew

Thanks for your detailed answer!