BPM, Workflow, and Case

BPM, Workflow, and Case

Come for answers. Stay for best practices. All we’re missing is you.

 View Only

  • 1.  BPM - SQL like query syntax

    Posted Thu December 24, 2020 11:53 AM

    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
    ------------------------------



  • 2.  RE: BPM - SQL like query syntax
    Best Answer

    Posted Thu December 24, 2020 09:04 PM

    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
    ------------------------------

    Original Message


  • 3.  RE: BPM - SQL like query syntax

    Posted Fri December 25, 2020 02:08 AM

    Hello Andrew

    Thanks for your detailed answer! 



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

    Original Message