RFC_READ_TABLE with Ruby and SAP::Rfc

Warning: another code sample is included in this post.

I spent a few hours this morning trying to use Piers Harding’s SAP/Rfc library for Ruby to read a table from SAP. I found several examples using other languages (Perl, VBscript, PHP, etc.), but the only Ruby example I could find reads the entire table. Figuring out how to load the “options” took some trial and error.

The idea of this example is to read the “LQUA” table in SAP which stores information about where to find a particular material in the warehouse. The whole thing is wrapped up in its own model so it can be easily called elsewhere in my Rails app:

class SapMaterial < SAP4Rails::Base
  function_module :RFC_READ_TABLE
  class << self

    def find_stock(options={})
      material = options[:material]
      return nil if material.blank?
      rfc = self.RFC_READ_TABLE
      rfc.query_table.value = "LQUA"
      rfc.delimiter.value = "|"
      rfc.options.value = ["MATNR EQ '#{material}'"]
      # optional set of fields to return from the table
      #rfc.fields.value = ['MATNR', ...]
      rfc.call()
      rfc
    end

  end
end

This is called with something like this:

stock_locations = SapMaterial.find_stock(:material=>'VOC300V')

Which yields a handy data set containing all the locations and available quantity for the DigiTech Vocalist 300 in the warehouse. This will be used as part of my new scan gun application which directs the shipping department to the various storage bins for picking large orders.

UPDATE: It seems the preferred approach is to use the new SAP Netweaver RFC library (sapnwrfc.rb). This changes the code sample a bit…

class SapMaterial < SAP4Rails::NW::Base
  function_module :RFC_READ_TABLE
  class << self

    def find_stock(options={})
      material = options[:material]
      return nil if material.blank?
      rfc = self.RFC_READ_TABLE.new_function_call
      rfc.QUERY_TABLE = "LQUA"
      rfc.DELIMITER = "|"
      rfc.OPTIONS = [{'TEXT' => "MATNR EQ '#{material}'"}]
      # optional set of fields to return from the table
      #rfc.FIELDS = [{'FIELDNAME' => 'MATNR'}, {'FIELDNAME' => ...}]
      rfc.invoke
      rfc.DATA
    end

  end
end

The trickiest part was figuring out the correct syntax for the OPTIONS and FIELDS. Pier’s documentation hints at the correct format (look under the heading ‘A Closer Look At Handling Parameters’), but I had to dig in to the table structures within the function in SAP to figure out that it was expecting ‘TEXT’ and ‘FIELDNAME’. So, if you’re using some other function, then explore the table structures to learn what the function expects to see.

Ruby: Howto convert numbers to letters

Let’s say you have a series of numbers (1,2,3,4,5…500+) and you need to convert them to letters like A,B,C,D…AA…ZZ (think Excel column headers.)

I searched and searched to find a built-in way of doing this with Ruby, but I couldn’t find it. So, I tried to write my own:

def number_to_letter(n=1)
  n.to_i.to_s(27).tr("0-9a-q", "A-Z")
end

That works great except that 1=B instead of A, so all of AA,AB,AC…AZ doesn’t work. I tried all sorts of different ideas based on the one above. Then, I stumbled on: succ.

For an Integer, it makes a lot sense: 1.succ = 2, and so on (returns the next integer). But, it also does exactly what I need for strings: “A”.succ = “B” and “Z”.succ = “AA”. So, rather than converting numbers to letters, I ended up with a block like this:

column = "A"
(1..500).to_a.each do |i|
   puts "#{i} : #{column}"
   column = column.succ
end

Teague dancing…NO!

A few years ago, we used to listen to They Might Be Giant’s album called, “NO!” all the time. I captured Teague dancing to it one day. Be sure to watch all the way through.

Asher and the mint candy

mint candyLast night I was stacking the plates in the cupboard after unloading the dishwasher. Suddenly, one of the smaller plates shattered in the middle of the stack.

Upon closer investigation, I found that the plate was one that had endured one of Asher’s experiments a few years ago.

One morning I awoke to the smell of something burning. I rushed to the kitchen to find Asher with a small plate holding a pile of black ash. Groggy and confused I asked him what it was.

“I don’t want to say,” he replied and ran into the next room.

I could tell he was upset, so I backed off from scolding him. I asked why he was so upset. The poor little guy explained that he was trying to cook a mint in the microwave. He was very upset that it didn’t turn out the way he had envisioned it.

“How long did you cook it?”

“I pushed the ‘4’ button because I’m 4.”

So, in case you’re wondering how long it takes to turn a mint into a pile of ash (and probably catch fire), four minutes will work nicely.

After two years, the charred mark on the plate had almost completely worn off before the plate spontaneously burst into a hundred pieces. The stress must’ve been too much for it. I can’t blame it, though. Asher is glad to see it go. Ever since that fateful day, we made sure to give the same plate to Asher whenever we got out the small plates…just as a reminder that he shouldn’t play with the microwave.

Welcome Back, VW

These days, everyone is miles-per-gallon-conscious. Sam recently purchased a CNG-powered Honda Civic and has been very happy with it.

I’ve been researching the possibilities of a new vehicle to replace my tiring ’95 Jetta (my fifth beloved oil burner). Sadly, the fuel economy of the latest VWs have been less-than-impressive. I’m not sure why the TDI Jetta was discontinued after the 2006 model. I had given up on VW.

This has steered my focus towards the Prius and Sam’s Civic. I’ve never really been a fan of the toy-like handling of Hondas and the Prius is just too pricey. (Anything over $20k is too high.) I’m also interested in a vehicle with more cargo room for trips to the (un-named) hardware stores, IKEA, etc. Dad’s Corvette has more trunk space than Sam’s new Civic. To make matters worse, this winter has heightened my awareness of the virtues of 4-wheel drive (something I’ve never had).

As far as fuel efficiency, I’ve set my targets on 40+ mpg. I’d also like to be able to fuel up in remote areas which aren’t equipped with CNG fueling stations.

Gather all of those requirements, and it is nearly impossible to find a vehicle on the market that fits my needs…until now…

VW Sportwagen frontIt hasn’t hit the market yet, but the new Jetta Sportwagen TDI certainly looks promising. The turbo-charged fuel injected diesel engine touts about 55 mpg on the freeway (most of my driving). The sporty tiptronic transmission and 4-motion traction system takes care of winter driving concerns (climbing out of a snow-packed ravine at Bear Lake). It has more cargo room than our Honda Odyssey and Sam’s old Pathfinder. It even has a nice rack.

Fueling up the sportwagen won’t be an issue, either. Diesel is readily available just about everywhere. (Previous TDI models have also been known to perform well on bio-diesel fuel should the need arise.)

I’m interested to see if it can tow a (very) small trailer with a snowmobile on it without too much extra effort. No, I don’t have one, but I think I need one.

I would also need to retrofit a reverse-facing seat in the way-back. Otherwise, what’s the point of having a stationwagon–right? Plus, then I could convince Laura that she needs one so she can dump her gas-guzzling Tahoe.

Some claim the tail end of the car is less visually appealing than the rest of the car. I’ll have to agree on that one. Maybe a really tall wing on back would spice it up.

The VW site does not have any information on pricing or availability, but rumors put it under $20k and the TDI version should hit the market towards the end of 2008 (as a 2009 model).

I need one.

Subversion with Active Directory Authentication via Apache

Laura–skip this one.

I recently started using Subversion at work. It has been popular enough, that several other employees have found a need for it. So, I decided I’d better figure out Active Directory authentication so I don’t end up maintaining a separate set of passwords for everyone.  It took quite a bit of trial-and-error. Here’s my Apache config for the subversion site:

<VirtualHost *:80>
ServerName svn.domain.com
DocumentRoot /var/svn/www
<Location /repos/>
DAV svn
SVNParentPath /var/svn/repos
SVNListParentPath on
AuthzSVNAccessFile /var/svn/svnaccess
AuthType Basic
AuthName "SVN Server"
AuthBasicProvider ldap
AuthzLDAPAuthoritative Off
AuthLDAPBindDN "DOMAIN\administrator"
AuthLDAPBindPassword password_for_administrator
AuthLDAPURL ldap://domain_controller:389/dc=ad,dc=domain,dc=com?sAMAccountName?sub?(objectClass=*)
Require valid-user
</Location>
</VirtualHost>

No, it is not a good idea to use your domain administrator in the config above. Do it for testing. Then, replace it with an account with read-only access in your domain.

Many of the examples on the web were geared towards non-Active Directory implementations. Those that were specific for AD still didn’t work until I removed the “cn=Users” from the first part of the ldapurl. Our users are not all part of the “Users” group. Removing this from the string means that all AD accounts can login. So, then I turned to the AuthzSVNAccessFile to fine-tune the access to the various repositories. Here is an example of that file:

[groups]
it=username1,username2
engineers=username3,username4

[:/]
*=r

[/]
*=r

[intranet:/]
@it = rw

[helpdesk:/]
@it = rw

[product_development:/]
@it = r
@engineers=rw

Have fun.

Asher and the Food Experience

A few weeks ago it was “P” day at kindergarten. The kids each brought a food item which started with the letter “P”.

When it was time to go, Asher hadn’t finished all of his food. So, he decided to take it home. After picking him up, Tina wondered what the funny smell was coming from Asher’s backpack. She found his (sideways) plate in the bag. At the bottom of the backpack she found a collection of food: pineapple, pretzels, peanuts, and of course, a pickle.

I can just imagine the thoughts running through his blonde, spikey haired head. “I have plate full of food. It is time to go home. I’ll just put this plate in my backpack.”

He’s actually a smart little guy, so you’d think he’d learn that it didn’t work very well.

Last week they had “M” day. As part of the food experience, Asher’s class was given little cups of chocolate Milk. I probably don’t need to tell the rest of the story.

This time, he was a little upset that all of his papers in his backpack were covered in chocolate milk. “This chocolate milk will taste great when I get home.  I’ll just put it in my…oops”

——————
Disclaimer: Asher is very particular about facts. I heard this story second-hand, so some of the details might not be exactly correct. Sorry if something isn’t quite right.