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.

Things I commonly do when starting a Rails project

Here’s another post from me for me. When starting a new Ruby on Rails project, I tend to do several things the same each time.

1. Setup SVN repository. Most of my projects are completely unrelated, so I usually have to setup a separate repository. The best walkthrough I’ve found is here.

2. Create development/test databases in mysql:

create database project_development;
create database project_test;

3. Setup ActiveRecord sessions:

rake db:sessions:create
rake db:migrate

(uncomment line in config/environment.rb)

config.action_controller.session_store = :active_record_store

4. Setup initial controller…something like:

script/generate controller Main index

And make it the default route (edit the file config/routes.rb)

# Install the default route as the lowest priority.
map.connect ':controller/:action/:id', :controller => 'main'

(Don’t forget to delete or rename public/index.html)

5. I almost always need user accounts. The ActsAsAuthenticated plugin works well:

script/plugin install http://svn.techno-weenie.net/projects/plugins/acts_as_authenticated
script/generate authenticated user account
rake db:migrate

Look in the newly created app/controller/account_controller.rb for a few things to move to app/controller/application.rb

6. While editing app/controller/application.rb, here are a bunch of date/time formatting shortcuts I use a lot:
ActiveSupport::CoreExtensions::Time::Conversions::DATE_FORMATS.merge!(
:date_time12 => "%m/%d/%Y %I:%M%p",
:regular => "%b %d %I:%M %p",
:date_time24 => "%m/%d/%Y %H:%M",
:pretty_date => "%B %d, %Y",
:month_year => "%B %Y",
:short_month_year => "%b %Y",
:month_day => "%b %d",
:pretty_date_time => "%B %d, %Y %I:%M %p",
:short_date => "%b %d, %Y",
:short_12 => "%b %d %I:%M %p",
:standard => "%m/%d/%Y"
)
ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS.merge!(
:pretty_date => "%B %d, %Y",
:month_year => "%B %Y",
:short_month_year => "%b %Y",
:year_only => "%Y",
:month_day => "%b %d",
:short_date => "%b %d, %Y",
:standard => "%m/%d/%Y"
)

Just add all of that to the bottom of application.rb, then use like this: object.created_at.to_s(:standard)

7. Other essential plugins include:

CalendarDateSelect

script/plugin install http://calendardateselect.googlecode.com/svn/tags/calendar_date_select

FileColumn

script/plugin install http://opensvn.csie.org/rails_file_column/plugins/file_column/trunk
mv vendor/plugins/trunk vendor/plugins/filecolumn

ValidatesEmailVeracityOf

script/plugin install http://svn.savvica.com/public/plugins/validates_email_veracity_of

DynamicSessionExp

script/plugin install http://svn.codahale.com/dynamic_session_exp/trunk
mv vendor/plugins/trunk vendor/plugins/dynamic_session_exp

8. Create a default layout for all controllers: app/views/layouts/application.rhtml (If you have a controller needing its own layout, just create one called, “controller.rhtml”) Here is a basic application.rhtml:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><%= @page_title || 'Site Name' %></title>
<%= stylesheet_link_tag 'all', :media=>'all' %>
<%= stylesheet_link_tag 'print', :media=>'print' %>
<%= javascript_include_tag :defaults %>
<%= calendar_date_select_includes %>
<!--[if lt IE 7]>
<script defer src="/javascripts/pngfix.js" type="text/javascript"></script>
<![endif]-->
</head>
<body>
<% if flash[:notice] -%>
<div class="flash_notice" id="Flash"><%= flash[:notice] %></div>
<% end -%>
<% if flash[:alert] -%>
<div class="flash_alert" id="Flash"><%= flash[:alert] %></div>
<% end -%>
<%= yield %>
</body>
</html>

9. Default public stuff (pngfix.js, latest scriptaculous, stylesheets, images, etc.)

Hostmonster on Rails

A client of mine recently moved their web hosting to Hostmonster, and I was tasked with migrating a Ruby on Rails application to the new server. I ran into a few snags.

First of all, I followed their tutorial.

The default rails welcome page worked fine, but I kept getting “Application Error” on everything else. The logs showed nothing. I found several message board posts pointing to the permissions on the “public” folder.  Dr. Chuck had the best article on the subject.

Dr. Chuck’s instructions fixed the problem for a month or two.  Then it stopped working again. I figured some server-side process had changed the permissions on my files. So, I set it up again. It still didn’t work.

I actually had to contact tech support. A couple of days later they responded with the solution (thankfully, this isn’t a mission-critical app). Hostmonster had recently updated to a newer version of Rails. So, I just commented out the version in the rails environment file:

#config/environment.rb
#...
#RAILS_GEM_VERSION = '1.2.5' unless defined? RAILS_GEM_VERSION

I believe that tells the application to use whatever version of rails it can find (the most recent version on the server). Since version 1.2.5 had apparently been replaced with 1.2.6, removing the declaration for 1.2.5 fixed the problem.

The part that still confuses me is, I had tried generating the application from scratch while logged into the Hostmonster server (via ssh). Doing so still generated an environment.rb file for version 1.2.5.

Of course, the other part that confuses me is why did 1.2.5 stop working?  When I update my own servers with ‘gem update’, I keep old versions available for backwards compatibility of existing rails apps. As far as I know, there is no problem doing it this way. Am I wrong?

Using RubyODBC in Rails on Linux to connect to MS SQL Server

(I really need to work on shortening my titles.)

I spent the day trying to figure out one little problem. I setup a brand new server for the Intranet at work. I set it up with Fedora 7 and started configuring it for my rails apps. Since our corporation has decided to use Microsoft SQL for all databases, I turned to the instructions on the Rails wiki to setup ODBC.

Most of it went pretty smoothly. When I was done, I launched mongrel and checked the site using http://ip_address:3000. It all worked great. So, then I setup mongrel_cluster and fired it up. No workey. I kept getting database errors saying “No data found.”

The very misleading error was hard to solve. I finally found a post which explains that there is a bug in RubyODBC which does not play well with “text” columns that are blank. So, the answer is to set all your blank fields to NULL. Well, I couldn’t figure out how to do that. (MSSQL is not my favorite.) I finally figured out to use a whole bunch of these queries in SQL query analyzer:

UPDATE tablename SET description=" " WHERE description LIKE ""

(There’s a space between the first set of quotes, but not the second.)

So, that fixed everything for now, but I also had to make sure it doesn’t happen again. I couldn’t get the default value in SQL to do what I needed, so I added a bunch of these in the model code for each problematic model:

def before_save
if self.description.blank?
self.description = ' ' #(two spaces)
end
end

Hopefully this will help somebody else.

Using the “dry_scaffold” and “dhtml_calendar” plugins with Rails 1.2.3

I spent the morning trying to get a lame little site up and running. I didn’t know it would be an issue until I couldn’t turn back. Information was sketchy, so here’s my addition to the sketchy info I had found.

Keep in mind, these instructions are only designed to help if you already had these plugins working with an older version of Rails.

The dry_scaffold plugin doesn’t seem to be supported anymore. The author seems to prefer “active_scaffold”. Nice–unless you don’t want to rewrite all your code that had previously been designed for “dry_scaffold”. Also, the older version of “Engines” I had doesn’t work with Rails 1.2.3. So, here’s the skinny:

1. upgrade Engines:

ruby script/plugin install http://svn.rails-engines.org/plugins/engines --force

(if that fails, export the svn and copy it to your vendor/plugins directory)

2. read the “UPGRADING” file included in the Engines plugin. A lot has changed. To get the two plugins I needed, I had to modify several files.

environment.rb

just before the section with your configuration for dry_scaffold, add this:

require File.join(RAILS_ROOT, "vendor", "plugins", "engines",
                    "lib", "engines", "deprecated_config_support")

also, you’ll want to get rid of your “Engines.start” statement(s).

Do the following for both plugins…Copy the init_engine.rb file to init.rb within the individual plugin’s folder.

Then, edit init.rb:

Comment out the line about “Engines.current.version = …” I’m not sure why, but that breaks stuff and everything works fine without it.

Finally, in your layouts in app/views/layouts, you’ll need to edit the stylesheet and javascript include tags. Here’s the new format:

<%= stylesheet_link_tag "dry_scaffold", :plugin=>"dry_scaffold" %>
<%= javascript_include_tag "dry_scaffold", "rico_corner", :plugin=>"dry_scaffold" %>

<%= stylesheet_link_tag "dhtml_calendar","calendar-blue", :plugin=>"dhtml_calendar" %>
<%= stylesheet_include_tag "dhtml_calendar","datebocks_engine","calendar","lang/calendar-en","calendar-setup", :plugin=>"dhtml_calendar" %>

I think that will do it. Hopefully this will help someone…and not be more confusing than the info out there now.

Java Developers: Standardize or Go Away

Remember that buzz word from the mid 1990’s? “Java is the new cross-platform programming language which promises to unite all computing platforms and bring about world peace.” (I’m exaggerating, but that’s about what it seemed like at the time.)

Guess what. It couldn’t deliver. It was a good way to make your application run extremely slow–especially web apps. It sort of went away for a while.

Ten years later it has a whole new set of problems (which I’ll get into in a minute). What I can’t understand is its new popularity. It still runs terribly slow, and there are plenty of other good alternatives. From a web developer’s perspective, I would choose Ruby on Rails, Perl, .NET or even the disgustingly overrated PHP over Java.

Here’s the new problem: end user requirements. Why on earth would you develop a web application with such specific client-side requirements? Time for an example.

At work I help support our 400+ employees’ computers. In the last year, we have had several Java-based web applications thrown at us from our parent corporation. So far, each of these requires a different version of the Java Runtime Environment (JRE) to be installed on the client’s PC. “Just install the latest version. It must be backwards-compatible,” you say? Not quite.

American Express and IBM provide something called Soundtrax. ADP provides eTIME and EV3. There’s one other I can’t remember. All users need eTIME. It requires JRE 1.4.2_12. HR and Payroll need to use EV3. It requires JRE 1.4.2_02, JRE 1.4.2_04, and JRE 1.4.2_06. And it won’t work if JRE 1.4.2_12 is installed. Anyone who fills out expense reports needs to use Soundtrax. It will actually work with the current JRE, so that is nice.

So ADP has two different software packages which work together, but have system requirements making them incompatible with each other. Plus, if you install the latest version of JRE, it will often search for updates. If the user clicks the message, “Updates are ready for you,” then they just lost access to both ADP programs.

I’m glad Sun can just decide, “Oh, I think we’ll stop supporting JRE 1.4.x in our current JRE.” That just doesn’t work. Sure, you can argue that the developers need to update their apps to be compatible. That isn’t happening. Sun (and IBM) need to develop a JRE which is completely compatible with all Java apps. Unfortunately, I’m sure it isn’t possible now that there’s such a mess out there. So, Java needs to die.

Meanwhile, I think we’re going to setup a separate set of terminals for each of the sites mentioned above. Employees will have to take turns to approve their timecards. This is sure to be a productive part of our workflow. Good job Java developers!

I was done, but then I remembered one more thing. Remember the cross-platform promise? Forget it. All of these will only run on Windows XP with Microsoft IE. So, I have to boot up a vmware virtual machine every couple of weeks just for this. (I’m lucky enough to run FC7 on my desktop at work.)

First Time Visitors

Seth Godin posted an idea about first time customers to different businesses. The idea is to have sign with information for first timers. It is one of those ideas that makes me think, “Duh, why didn’t I think of that.”

Anyway, I’m mostly writing this post as a note to myself to remember to do this on my online venture. Right now, of course, I’m thinking it would make a nice addition to the homepage for TheBigFork. Perhaps we can get rid of the big block of text on the home page and replace it with a simple link “First Time Here?” When clicked, it could show the first timer text inline.