DI Commander 2.0 - The New Generation Scripting Tool

[Former Member]

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:

• http://www.mediafire.com/di_construction_kit
• http://tinyurl.com/4pcsccr

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.

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.