Symantec Access Management

Expand all | Collapse all

Badly formatted command response when using DMS API to search a user in Database

Jump to Best Answer
  • 1.  Badly formatted command response when using DMS API to search a user in Database

    Posted 03-19-2018 02:58 AM

    I am programming using DMS API to retrieve a SmDmsUser from database and from API document I should use full sql to set as the filter and use empty string as organization. And I am not using the SmDmsCursor so I am using the full sql.

    org = dmsDir.newOrganization("");

    SmDmsSearch search = new SmDmsSearch("select login from db.user_view where login= 'tom'");

    searchResult = org.search(search, SmDmsSearch.Forward);

     

    I have tried quite a few variances of the sql including:

    select login from db.user_view where login= 'tom';

    select login from db.user_view 

    from db.user_view where login= 'tom'

    select 1 from dual

    select * from db.user_view where login= 'tom'

    etc

     

    And I am always seeing the same error "Badly formatted command" from the Policy Server.

     

    Can somebody shine some light in this error?

     

    Note, I am using SiteMinder 12.5. And referring to CA SiteMinder SDK r12.52sp1 



  • 2.  Re: Badly formatted command response when using DMS API to search a user in Database

    Posted 03-19-2018 03:02 AM

    From doco :

     

    Searches - CA Single Sign-On - 12.52 SP1 - CA Technologies Documentation 

     

    Set the Search Filter for ODBC Directories

    A search of an ODBC directory is performed through a SQL query. The DMS API supports the SQL SELECT statement.

    The information you provide in the search filter depends on whether your search uses an SmDmsCursor object to provide sorting and paging operations:

    • With ODBC searches that do not pass an SmDmsCursor object to the search method, use a fully defined SQL SELECT statement in the search filter.

    • With ODBC searches that do pass an SmDmsCursor object to the search method, use a partial SQL SELECT statement in the search filter, consisting only of FROM and WHERE clauses.

    With ODBC database searches that pass an SmDmsCursor object to the search method, the DMS API constructs the complete SQL SELECT statement from various sources, as follows:

    • The FROM and WHERE clauses are taken from the filter parameter of an SmDmsSearch constructor or setFilter() method.

    • The SELECT columns portion of the query is taken from attributes specified in either of the following parameters:

      • The propertyNames parameter of setPropertyNames(). These attributes are used when an SmDmsSearch object is passed to one of the search methods in SmDmsOrganization.

      • The attrNames parameter of a getGroups() or getMembers() method.

    • The ORDER BY keywords portion is taken from the orderof the attributes you specify in the sort parameter of the SmDmsCursor constructor.

    Consider the following code fragment:

    String DIR_ROOT = "root";

    String SRCH_FILTER ="from SmGroup";

    SmDmsSearch search = new SmDmsSearch(SRCH_FILTER);

    String[] prop = {"Name", "'Group' as Class"};

    search.setPropertyNames(prop);

    Vector SortOrder = new Vector(); 

    SortOrder.add("uid");

    SmDmsCursor cursor = new SmDmsCursor(SortOrder,blockSize,false,true);

    The DMS API uses the information in the previous example to build the following SQL statement:

    SELECT Name, 'Group' AS Class FROM SmGroup ORDER BY uid ASC

    Code Source

    Portion of SQL Statement

    SRCH_FILTER parameter of SmDmsSearch constructor

    from SmGroup

    SortOrder parameter of SmDmsCursor constructor

    order by uid asc

    prop parameter of setPropertyNames()

    select Name, 'Group' as Class



  • 3.  Re: Badly formatted command response when using DMS API to search a user in Database

    Posted 03-19-2018 11:34 PM

    Thanks Ujwol for responding to my queston.

     

    I am still having the same issue of getting a "Badly formatted command" error after I add in a cursor to my search and put in prop parameters.

     

    I have also tried to change the name space to "ODBC:" instead of "LDAP:" when I connect to the directory. But this does not make any difference as well.

     

    SmUserDirectory userDir = new SmUserDirectory();
    userDir.setOid(DirectoryName);
    userDir.setNamespace(SmUserDirectory.ODBC_NAMESPACE);

    dmsApi.getDirectoryContext(userDir, smDmsConfig, dirContext);

     

    Is there any example that's working by connecting to a user directory backed by ODBC and can retrieve a user object successfully?

     

    Best Regards,

     

    Michael



  • 4.  Re: Badly formatted command response when using DMS API to search a user in Database

    Posted 03-20-2018 02:26 AM

    Sorry didn't get a chance to try this out..

    but can you try something like this ?

     

    String SRCH_FILTER = "from SmGroup";
    String DIR_ROOT = "ou=People,o=bigdir";

    SmDmsOrganization org = dmsDir.newOrganization(DIR_ROOT);

    SmDmsSearch searchNew = new SmDmsSearch(SRCH_FILTER, DIR_ROOT);
    searchNew.setScope(1);
    searchNew.setNextItem(0);
    searchNew.setMaxItems(5000);
    searchNew.setPreviousItem(0);
    searchNew.setMaxResults(5000);
    searchNew.setPropertyNames(prop_new); //new search props

    Vector SortOrder = new Vector();
    SortOrder.add(ATTR2);
    int blk = 5;
    Vector attrNames = new Vector();

    SmDmsCursor cursor = new SmDmsCursor (SortOrder, blk, false, true);
    cursor.setOffset(3);


    //-------------search forward - 1
    result = org.search(searchNew, cursor, 1);
    vsearch = searchNew.getResults()



  • 5.  Re: Badly formatted command response when using DMS API to search a user in Database

    Posted 03-20-2018 03:09 AM

    You are really good, however I am still having the issue:

     

    I have tried:

    String SRCH_FILTER = "from table_name";
    String DIR_ROOT = "";

    search = new SmDmsSearch(SRCH_FILTER, DIR_ROOT);
    search.setScope(1);
    search.setNextItem(0);
    search.setMaxItems(5000);
    search.setPreviousItem(0);
    search.setMaxResults(5000);
    search.setPropertyNames(new String[]{"loginname"}); //new search props
    Vector SortOrder = new Vector();

    SortOrder.add("loginname");
    int blk = 5;
    Vector attrNames = new Vector();

    SmDmsCursor cursor = new SmDmsCursor (SortOrder, blk, false, true);
    cursor.setOffset(3);

    org = dmsDir.newOrganization(orgRoot);

    searchResult = org.search(search, cursor, SmDmsSearch.Forward);

     

    With your example, the DIR_ROOT is using some ldap format, but I am connecting to database, according to doc it should be empty string.

     

    The following is the error I am getting:

    2018-03-20 18:06:58,138 [http-nio-8081-exec-1] ERROR c.a.a.i.u.********** - Search user from root [] with filter [from table_name] failed with result [[facility=4 severity=3 reason=0 status=3 message=Badly formatted command]]



  • 6.  Re: Badly formatted command response when using DMS API to search a user in Database
    Best Answer

    Posted 03-22-2018 08:42 PM

    Hi Michael,

     

    Here is what worked for me :

    Please note , I did identify multiple documentation bugs with respect to java doc for DMS API.

    Also, there seems to bug where you must specify the directory root while creating organization even if that is not relevant to ODBC search calls.

     

     

     public void searchCallsODBC(SmDmsDirectory dmsDir) {
         SmApiResult result = new SmApiResult();
         String SRCH_FILTER = "from SmGroup";
         String[] prop_new_ODBC = { "Name", "'Group' as Class" };
         String DIR_ROOT = "DUMMY_DIR_ROOT";// NOT applicable for ODBC

         try {

             String directoryType = dmsDir.getDirType();
             Log(CRLF + "Directory Type is:" + directoryType + CRLF);

             SmDmsOrganization org = dmsDir.newOrganization(DIR_ROOT);
             SmDmsSearch search = new SmDmsSearch(SRCH_FILTER, DIR_ROOT);
             search.setScope(1);
             search.setNextItem(0);
             search.setMaxItems(5000);
             search.setPreviousItem(0);
             search.setPropertyNames(prop_new_ODBC);
             search.setMaxResults(5000);

             result = org.search(search, 1); // search forward
             Vector vsearch = search.getResults();

             SmDmsSearchResultParams searchParams = (SmDmsSearchResultParams) vsearch
                  .firstElement();
             vsearch.removeElementAt(0);
             Log(CRLF + "RESULT:");
             for (int i = 0; i < vsearch.size(); ++i) {
              SmDmsObject dmsObj = (SmDmsObject) vsearch.elementAt(i);
              Log(CRLF + dmsObj );
             }

             vsearch.removeAllElements();
          
         } catch (SmApiException apiException) {
             System.out.println(apiException.getMessage());
         }
        }

     

    RESULT 

     

    >>>>>>>>>>>>>>  SiteMinder Java DMS API Sample  <<<<<<<<<<<<<<

    Policy Server IP Address.................:     shruj01-i1849.ca.com
    Agent IP Address.........................:     127.0.0.1
    Agent Name...............................:     agent_iis_01
    Agent Shared Secret......................:     siteminder

    >>>>>>>>>>>>>>>>>>>>>  Directory Context  <<<<<<<<<<<<<<<<<<<<

    Get Admin User Directories...........:     SUCCESS
    Get User Directory...................:     SUCCESS
    Directory Type is:ODBC:

    RESULT:
    Clerks
    Pilots
    Crew
    Flight Attendants
    Customers
    admin

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>  End  <<<<<<<<<<<<<<<<<<<<<<<<<<<

     

    Attaching the full source code and properties file for your reference.

     

    Let me know if you need any help.

    Regards,

    Ujwol

    Attachment(s)

    zip
    DmsApiSample.java.zip   4 KB 1 version


  • 7.  Re: Badly formatted command response when using DMS API to search a user in Database

    Posted 03-22-2018 08:45 PM

    Between the error "Badly formatted command" was coming because of empty organization DN - this is a bug , this should not be required for ODBC search calls.