SetSearchSpec & SetSearchExpr

·         SetSearchExpr and SetSearchSpec are BC methods, used to uniquely query records

·         must call both these methods before ExecuteQuery BC method

·         both are mutually exclusive, NEVER use them together

·         SetSearchSpec method is used to set the search spec in one field whereas SetSearchExpr is used to set search spec on entire BC rather level, including variuo fields of the BC

·         Valid usage for SetSearchSpec:

1.       oBC.SetSearchSpec("Name", "<> 'ABC'");

2.       oBC.SetSearchSpec("Type", "<> 'New'");                                                                            oBC.SetSearchSpec("Status", "<> 'Sold' AND [Status] <> 'Cancelled' AND [Status] <> 'Renewed'");

3.       VB Script -

oBC.SetSearchSpec "Name", "<> 'ABC' AND ([Owner] = LoginName () OR [Skill] = 'Developer') OR ([Owner] IS NULL AND [Support Group] = 'ABC')"

4.       to query for - "Abc : SetSearchSpec "Name", "'""Abc'"

5.       to search for null value - SetSearchSpec "Name",  "is NULL"

6.       to search for Ash'Wed - SetSearchSpec "Name", "'Ash''Wed'"

7.       Must follow MM/DD/YYYY to query on date fields using SetSearchSpec, irrespective of the Regional Contro Panel settings of server or client system.

·             Valid usage for SetSearchExpr:    

1.       var sStr = "Abc/Def";

var sAcc = "Äbc";

var sSearchst = "[Name] = '" + sStr + "' AND [Account] = '" + sAcc + "'" + "'  OR [Account] = '"ABC"'";

oBC.SetSearchExpr(sSearchst);

                oBC.ExecuteQuery(ForwardBackward);

            Tips:         

·         Use SetSearchExpr instead of SetSearchSpec for better Sql generation.

·         If the parameter CaseInsensitive has been set to TRUE in the configuration (.cfg) file, then bc.SetSearchSpec("NAME", "SADMIN");

·         will generate the following SQL clauses against a local database:

T2.NAME LIKE 'sa%' OR T2.NAME LIKE 'Sa%' OR T2.NAME LIKE 'sA%'

OR T2.NAME LIKE 'SA%') AND UCASE(T2.NAME) = UCASE('SADMIN'))

·         In order to force the case and check for exact match, use SetSearchExpr

                                sName= "SADMIN";

                                 oBC.SetSearchExpr("[Name] = '" + sName + "'")

                    will generate the SQL T2.NAME = 'SADMIN'

References : http://docs.oracle.com/cd/B40099_02/books/OIRef/OIRefInterfaceRef127.html#wp1012209