Skip to Content

PLEASE NOTE!

DI Commander has been embedded to DI Construction Kit, which supports also MS SQL 2008 and SAP B1 8.8. You can download DI Construction Kit with documentation and full source code from these sites:

The initial version of DIC (previously known as the B1 Turbo Command Host) was released in July 2007. Our web server logs indicate that since then, DIC release 1.0 has been downloaded ca 400 times and DIC release 1.3 ca 600 times. Considering the (small) size of the global B1 developer community, I’m very happy with these figures. Based on the feedback and questions received, it seems that at least some people who downloaded DIC have found it useful.

There were lots of good development requests/ideas in the feedback to the previous releases. I’ve tried to respond to as many of the development requests as possible.

Changes in the user interface

There are not so many changes in the user interface of DIC 2.0 – it’s almost the same as before. Perhaps the most visible change is the new cheat sheet tab that hopefully will make life a bit easier for occasional DIC users. The cheat sheet allows you to quickly look up the object shortcuts provided by DIC.

Cheat Sheet

Comments on the login process

The active sessions list now contains also the B1 company name, so that it is easier to keep track about which session is connected to which database (although it is always a good idea to name the session handle so that you recognize it).

Based on some of the questions I got, it seems I had not sufficiently explained the purpose of the “DB Direct” checkbox. If this field is checked, you can directly type the “physical” name of the SQL Server database. For instance, the physical name of the US Demo database is usually SBODemo_US.
The DB Direct feature often allows logging in even when DI API fails to retrieve the list of databases from the server. I suppose it’s got something to do with Windows-level authentication but I don’t know the details. Perhaps someone at SAP could explain this better.

New object manipulation functions

I initially thought I had all relevant document/object manipulation methods covered with add(), update(), and remove(). However, as I realized from the feedback, I had totally forgotten Cancel and Close. Well, the new release has these covered:

cancel(objectref)
close(objectref)

All the functions related to manipulating business objects (add(), update(), remove(), cancel(), close()) used to return a numeric flag: 0 for success, other values for failure. All of these now return a boolean value (true or false). This makes the scripts a bit more elegant, compact and certainly more readable.
For instance, if you wrote something like this in version 1.3:

if(add(myitem)==0):
  print "OK"

…it should now be written as:

if(add(myitem)):
  print "OK"

…or if you want to be verbose:

if(add(myitem)==True):
  print "OK"

Fault code handling

As the function calls no longer return a numeric error code, I also added helper functions for retrieving the last error code and error description.

geterror() – returns a string that contains both the error code and description
geterrorcode() – returns the error code
geterrordescription() – returns the error description

Transaction handling

Transaction handling was available in the previous versions of DIC, but it had to be called using the rather clumsy DI function calls.

Now, a new transaction can be initiated with a single function call:
starttransaction()

Ending a transaction can also be done with a single function call:
commit()  – commits the transaction
rollback() – rolls back the transaction

Enumeration shortcuts

DIC now has shorcut handles to all of the enumeration values defined in DI API.
For instance, SAPbobsCOM.YesNoEnum.tYES can now be referred to as tYES.
Likewise, SAPbobsCOM.BoAccountTypes.at_Expenses can be referred to as at_Expenses.

The shortcut handles have all the same quirks and typos as DI API does. For instance, a BoCardTypes object that points to Leads is called cLid.

These shortcuts have also been made available with lowercase and uppercase versions.
Thus, cLid, clid and CLID are all valid handles to the enum value SAPbobsCOM.BoCardTypes.cLid.

There is of course a slight risk that if you name your own variable with one of these enumeration names, the handle to the enumeration value will be overwritten.

For instance:

>> clid=2
…is a perfectly valid variable assignment in DIC, but then you can no longer use the “clid” handle to the cLid CardType object.

If you are familiar with DI API, you can check the documentation for all the enumeration types and their uses. You can also get a complete list of the available values directly in DIC by typing:

>>list(enumvalues)

Releasing COM objects

When calling any of the following functions: add(),close(), remove(), update(), cancel(), the call will also release the COM object. Additionally, you can call release(object) any time to release a COM object.

If you wish not to release the COM object, you can use the standard function calls, for instance i.Add() instead of add(i).

Important as .NET Automatic Garbage collection does not handle COM objects.

Enhanced browsing

The real workhorse of DIC is the browse() function, as it takes away a lot of the pain related to iteration with DI API. In DIC 2.0, the browsing feature has been further enhanced and some problems present in the earlier releases have been fixed.

Merging sql resultsets with DI API business objects

While enhancing the browse functions, I also ended up another function that might make life even easier in certain kinds of tasks. The merge() function can be used to match recordset rows with DI API business functions. In order for the trick to work, the column names in the recordset must match existing fields in the object being merged.

For demonstration’s sake, I will present a script that will work in any B1 database. The script takes the 10 (alphabetically) first items in the database and creates a duplicate for each of those items. The duplicate has the same itemcode except there’s an ‘X’ in the end. The item name will be the same as in the original item. No other data is copied.

q=query("select top 10 itemcode+'X' as ItemCode, itemname as ItemName from oitm")
for row in browse(q):
  i=get(ITEM)
  merge(i,row)
  if(add(i)):
    print "Item added successfully"
  else:
   print "Item add failed"

The column name checking process is case insensitive, so in this case the ItemCode field of Items object will match any of these column names in the recordset: “ItemCode”, “Itemcode”, “itemcode”, “iTEMcODE” etc.

The merge functionality is actually quite close to what DTW does with a query-based import.

Variants of merge

INSERT

Attempts to add objects of the specified type to the database. The assumption is that they don’t exist already. If they do, the function will simply return a False.

q="select 'myItem3' as ItemCode, 'MyItemName3' as ItemName union select 'myItem4' as ItemCode, 'myItemName4' as ItemName"
for row in browse(query(q)):
  insert(ITEM,row)
UPSERT

Upsert looks up an existing object based on the resultset. If the object exists, it is updated. If not, new objects will be added to the database.

For demonstration purpose, the query in the following script just generates a simple two-record resultset. In real situation, you would retrieve the resultset from a table.

q="select 'myITEM' as ItemCode, 'myItemName' as ItemName union select 'myItem2' as ItemCode, 'myItemName2' as ItemName"
for row in browse(query(q)):
  upsert(ITEM,row)

Other sample scripts

This script simply runs the sql query and displays the value of the single column:
q=”select slpname from oslp”
for sp in browse(query(q)):
  sp

This one returns two columns:
q="select slpcode, slpname from oslp"
for sp in browse(query(q)):
  sp.Item("slpcode").Value
  sp.Item("slpname").Value
Or:
q="select slpcode, slpname from oslp"
for sp in browse(query(q)):
  for i in sp:
    i.Value

This one will browse items from oitm table:

q="select top 100 itemcode, itemname from oitm"
for i in browse(ITEM, query(q)):
   i.ItemName

This one will also show the warehouses linked to each item:


q="select top 100 itemcode, itemname from oitm"
for i in browse(ITEM, query(q)):
   i.ItemName
   for a in browse(i.WhsInfo):
     a.WarehouseCode

…and this one will show the items’ prices from each pricelist

q="select top 100 itemcode, itemname from oitm"
for i in browse(ITEM, query(q)):
   i.ItemName
   for pl in browse(i.PriceList):
   pl.PriceListName
   pl.Price
Retrieving data from another database server (or server instance)

The following script would execute the query against another database server and based on the resultset create new payment terms objects in the target B1 database.


q="select description from OPENDATASOURCE('SQLOLEDB','Data Source=MyServerSQL2005;User id=sa;Password=;').SAMPLEDB.dbo.PayTerms "
for row in browse(query(q)):
  s=get(PAYMENTTERMSTYPE)
  s.PaymentTermsGroupName=row.Item(0).Value
  if add(s):
    print "#"   
  else:
    geterror()

This one would create new shipping types objects in the target B1 database.

q="select code, description from OPENDATASOURCE('SQLOLEDB','Data Source=MyServerSQL2005;User id=sa;Password=;').SAMPLEDB.dbo.ShipTypes"
for row in browse(query(q)):
  s=get(SHIPPINGTYPE)
  s.Name=row.Item(0).Value
  s.Website=row.Item(1).Value
  if add(s):
    print "#"   
  else:
    geterror()

This one would create only such item groups that did not already exist.

q="select left(description,20) from OPENDATASOURCE('SQLOLEDB','Data Source=MyServerSQL2005;User id=sa;Password=;').SAMPLEDB.dbo.ItemGroups where description collate database_default not in (select itmsgrpnam collate database_default as description from oitb) order by code"
for row in browse(query(q)):
  s=get(ITEMGROUP)
  s.GroupName=row.Item(0).Value
  if add(s):
    print "#"   
  else:
    geterror()

Further research

This will probably be the last major version of DIC that I’ll be releasing. I will concentrate my research efforts to enhancing myBOLT and possibly to exploring the scripting possibilities that Windows PowerShell offers.

I’m therefore submitting DIC to SDN with full source code attached. Feel free to use it in any way you see suitable. 

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. Davinder Singh
    Greetings,

    First off, thank you very much for this tool, its been very helpful in most of my implementations.

    I just have an issue I was hoping you could help me out on.

    I am able to edit and update the GL Account (Chart Of Accounts) with a sample code below:

    for gl in browse(CHARTOFACCOUNT, query(“SELECT *  FROM OACT WHERE AcctCode =’102520′”)): 
      gl.Details=”Testing batch.” 
      update(gl)

    Now I have a client with 130 000 GL codes created accidentally, would it be possible to use the DI Commander to remove these GL codes (with no transactions)?

    (0) 
    1. Davinder Singh
      I’m guessing since the SDK for the ChartOfAccount object does not support remove, that we’re unable to do so even via the DI Commander?
      (0) 
      1. Henry Nordström Post author
        Hi Davinder,

        You’re right. The DI Commander is just a layer on top of DI API. If you want to do something in the B1 database and you can’t do it with DI API, you will not be able to do it with DI Commander either.

        (0) 
  2. Jose Montanez
    I have this message when I try to unzip the DI Commander 2 zip file:

    “…\Download\DI_Commander_20.zip: unexpected end of file”

    I am using Winrar 3.60, is there another download link for this program?

    Thanks in advance,

    Jose Montanez

    (0) 
  3. Jacques Spies
    How would one access records from child tables?

    Roughly, I’m trying to do the following….

    result = browse(DELIVERYNOTE, query(“SELECT DocEntry FROM ODLN” ))

    for dln in result:
         for line in list(dln.Lines):
              print line
     
    Thanks in advance

    (0) 
    1. Henry Nordström Post author
      Hi Jacques,

      Simply replace the “list” command with “browse”:

      for line in list(dln.Lines):
      ==>
      for line in browse(dln.Lines):

      The list command is a standard Python command and it cannot handle the subitems of B1 business objects, which are not “real” enumerables. That’s why we need to use the special “browse” command to access the subitems.

      (0) 
      1. Jacques Spies
        # Thanks for the quick response… having fun
        # combining Python and SAP
        # Stuck again though. I’m trying to find all
        # DeliveryNote lines that are open and set their
        # status to closed:

        sql = “””SELECT ODLN.DocEntry FROM ODLN INNER JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry where DLN1.LineStatus <> ‘C'”””

        for dln in browse(DELIVERYNOTE, query(sql)):
        for line in browse(dln.Lines):
        print line.LineStatus,”->”
        line.LineStatus = SAPbobsCOM.BoStatus.bost_Close
        print line.LineStatus
        # update(dln) # — this returns False
        # update(line) # — throws COMException
        print “,”
        #update(dln)# this returns false

        # I’ve even tried pulling out another closed line
        # and using it’s value, no luck

        (0) 
        1. Henry Nordström Post author
          Hi Jacques,

          Do you actually have such deliverynotes that are closed on header level but have non-closed rows? If everything is ok, that should never happen.

          If you just want to close all lines in a deliverynote that is currently open, you could do it by passing the handle to the delivery note as a parameter to the “close” function:

          close(dln)

          At the moment – as far as I know – there is unfortunately no such function in DI API that would allow us to close a single document line.

          (0) 
  4. George Mamacos
    Hi Henry,

    I’m attempting the above, with not much luck, here’s my code:

    for bp in browse(BUSINESSPARTNER, query(“SELECT CardCode FROM OCRD”)):
      for line in browse(bp.Addresses): 
        remove(line)

    Where am I making an error?

    Thanks in advance,
    George

    (0) 
    1. George Mamacos
      PS: I see there is no remove method in DI Api for this “line” object, so this is impossible until such time.

      Thanks for a fantastic tool anyways – I hope SAP take it onboard in some way, to empower us to work efficiently via this business layer, which where before we worked very efficiently via the data layer…

      Regards,
      George

      (0) 
  5. Andy Grootens
    Hey there,

    Great tool this DIC. I had to close 6000 orders in batch. This is how I did it. Maybe you’ll find it usefull:

    for myorder in browse(ORDER, query(“SELECT ORDR.DocEntry FROM ORDR WHERE ORDR.DOCNUM BETWEEN 600001 AND 700099”)): 
      myorder.Cancel()

    (0) 
    1. Andy Grootens
      That was cancel of course. Here’s closing:

      for myorder in browse(ORDER, query(“SELECT ORDR.DocEntry FROM ORDR WHERE ORDR.DOCNUM BETWEEN 500053 AND 500055”)): 
        myorder.Close()

      (0) 

Leave a Reply