{"id":2906,"date":"2017-12-21T14:00:43","date_gmt":"2017-12-21T20:00:43","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=2906"},"modified":"2018-12-21T14:23:00","modified_gmt":"2018-12-21T20:23:00","slug":"how-to-overcome-the-select-top-1000-issue-with-the-java-businessobjects-sdk","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/how-to-overcome-the-select-top-1000-issue-with-the-java-businessobjects-sdk\/","title":{"rendered":"How to overcome the select top 1000 issue with the Java businessObjects SDK"},"content":{"rendered":"

the default rows to return is 1000 rows for the query builder queries in the BO SDK.\u00a0 there a few ways to manage this<\/p>\n

    \n
  1. is the easily way is to hard code the value eg “select top 20000 SI_ID from CI_SYSTEMOBJECTS”, the draw back is the system takes a performance hit, and the hard coded value does not follow the WORM (write once read many) theory. if you got to 21, 000 you would need to update your code of parameter file.<\/li>\n
  2. the better way is the lazy loading the query by the 1000’s.\u00a0 If you read 1000 per query and track the last record read then read the next 1000 and do this until there are no more records to retrieve.\u00a0 It would be totally WORM and the lazy reads helps the system recover per query.<\/li>\n<\/ol>\n

    here is an example of how this can be expanded to work for you.<\/p>\n

    public static HashMap<String, CMSUser> getMembers (IInfoStore infoStore, String userGroup) throws SDKException {<\/p>\n

    bolean fullBucket = true;<\/p>\n

    int\u00a0lastUserId =\u00a0 0;<\/p>\n

    while (fullBucket) {<\/p>\n

    IInfoBObject iinfoObjectUser = null;<\/p>\n

    try {<\/p>\n

    iinfoObjectUser\u00a0 =\u00a0infoStore.query(“select top 1000 SI_ID from CI_SYSTEMOBJECTS where CHILDREN(\\”SI_NAME=’User-Group-User'”, \\”SI_NAME=’userGroup’\\”) and SI_ID > ” + lastUserId + ” order by SI_ID ASC”);<\/p>\n

    int numOfUser = infoObjectsUsers.size();<\/p>\n

    fullBucket = (numOfUser\u00a0 ==1000?true:false);<\/p>\n

    if (numOfUser > 0) {<\/p>\n

    IUser lastUser = (IUser) infoObjUser.get(numOfUser -1);<\/p>\n

    lastUserId =\u00a0lastUser.getId();<\/p>\n

    for (int I=0; I <\u00a0numOfUser; I++) {<\/p>\n

    IUser tempInfoUser = (IUser ) infoObjects.get(I);<\/p>\n

    Date cDate = (Date) (tempInfoUser.propertities().getProperty(“SI_CREATION_TIME”) != null ?\u00a0tempInfoUser.propertities().getProperty(“SI_CREATION_TIME”).getValue: new Date(0));<\/p>\n

    }<\/p>\n

    }<\/p>\n

    } catch {<\/p>\n

    }<\/p>\n

    }<\/p>\n

     <\/p>\n

    }<\/p>\n","protected":false},"excerpt":{"rendered":"

    the default rows to return is 1000 rows for the query builder queries in the BO SDK.\u00a0 there a few ways to manage this is the easily way is to hard code the value eg “select top 20000 SI_ID from CI_SYSTEMOBJECTS”, the draw back is the system takes a performance hit, and the hard coded […]<\/p>\n","protected":false},"author":2,"featured_media":4904,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,30,28],"tags":[],"class_list":["post-2906","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-dashboards-analytics","category-bi-business-objects","category-java-hibernate"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2018\/12\/sap-bo-sdk.png","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=2906"}],"version-history":[{"count":5,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2906\/revisions"}],"predecessor-version":[{"id":4909,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2906\/revisions\/4909"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/4904"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=2906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=2906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=2906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}