Integrating legacy Visual Basic app with Rails

What is the best way to import data from legacy external sources into our Rails app and trigger certain tasks automatically in our rails app ? A very difficult question to answer and it varies in every case.

Well, here was our challenge – The earlier process was that the new entries were being dumped into a table using VBA code and then from our Rails admin portal, we had to click a button to process these entries and load them into a new table. A very painful and manual process. To eliminate the manual process, the idea now was to trigger the migration process straight from the VBA code ie. once new entries are loaded into the from_table, our rails system should be able to kickstart the migration job and transfer them to to_table.

The first solution that came to our mind was to write database level triggers to move the data from the from_table to to_table but there were ActiveRecord callbacks in our application which affected three other tables. So rewriting all that code at the database level did not seem right.

The second solution that came to our mind was to move the code from VBA to ruby, so that we could automate the whole process. But then there was some processing going on in the VBA code before creating the row and due to business constraints, we had to work with VBA code (let sleeping lions lie). Point taken.

The next solution to have a cron job running every 30 mins was rejected by the client. He did not want to wait for that long to see the data migrated. We could have written a cron job running every 1 min, but that would have created many processes. And since the migration sometimes took more than 1 minute (depending on the data), it would have been a dangerous possibility to have 2 processes parallely doing the same thing.

Now, the big question was how to pass a message from VBA script directly to our rails app?

With a little help from the database, we designed an unorthodox solution which was easy to develop and got the job done. There might be a better and cleaner solution and we are all ears, but I am proud of what we have cooked up. So here is the recipe.

So lets divide the task into 2 main parts

  1. Write a rake task to watch a table named “triggers” and trigger whenever new entry is created in this table
  2. Make sure that this rake task is monitored by monit so that we don’t need to start/stop/monitor this task manually

Step 1

The VBA script created a entry in triggers table with end_time as null
insert into triggers set start_time = current_timestamp
Our rake task was basically an infinite loop where we checked that if there is an entry in triggers table with end_time nil, then we trigger the ruby function to migrate entries.
desc "Migrate new entries"
task :monitor_entries => :environment do
  `echo "#{}" > #{Rails.root}/log/`
  `echo "#{Process.ppid}" > #{Rails.root}/log/monitor_entries.ppid`
    trigger_entries = Trigger.where(end_time: nil)
    if trigger_entries.length > 0
      results = FromTable.process
      trigger_entries.each {|trigger| trigger.update_attribute(:end_time, }
      sleep 30


The lines for storing pid and ppid were important, so that we could use them in our monit script.

Step 2

Now it was time to make changes to `project.monitrc` file so that we did not have to monitor this background rake task.
check process job_trigger
  with pidfile /path/to/log/
    start program = "/bin/bash -c 'export rvm_path=/path/to/.rvm; . $rvm_path/bin/rvm; cd /path/to/rails/app; bundle exec rake monitor_entries'"
    stop program = "/bin/bash -c 'kill $(cat /path/to/log/'"
We also added the below line to the to `:launch` block in mina deploy script, so that this task gets started on its own after every deployment.
queue "sudo monit restart job_trigger"
Thankfully everything went to plan. Would love to hear your ideas to improve this recipe 🙂
This entry was posted in Ruby and tagged , , . Bookmark the permalink.

2 Responses to Integrating legacy Visual Basic app with Rails

  1. If ‘Trigger’ is a model inside your app, can’t we use an after_create callback?

    • Abhishek says:

      The VBA script will create an entry in triggers table, because they are the ones who decide when the migration job runs. And since the creation of the entry in triggers table is not happening through the rails app, ActiveRecord callbacks will not fire.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s