Ruby STRSQL

PowerRuby Tutorial

This tutorial will create a Rails version of old green screen STRSQL (Start SQL Interactive Session) utility. Most IBM i people use STRSQL on IBM i, but if you have not, simply sign on to your IBM i 5250 and enter STRSQL on the command line. Other products duplicate the STRSQL support as a client/server application, but our Rails version will stay completely on the IBM i server and use any device with a browser as interface.

This tutorial uses a new IBM i technology, PowerRuby, to fast path our way through our web STRSQL application. a web-based STRSQL application. If you are unfamiliar with Ruby on Rails, please see the following PowerRuby tutorials for instruction on PowerRuby installation and PowerRuby Rails fast path CL utilities:

What is PowerRuby? PowerRuby is a privately held business which has partnered with IBM to provide a formal port of the Ruby language and the Rails framework including a native database driver that communicates directly with DB2 on IBM i without the need for any other proxies (i.e., MySQL, JDBC, ODBC).

PowerRuby is currently available in a free community version. Additional features under development will take the Ruby and Rails environments to the next level and be included in a licensed commercial version of PowerRuby.

Rails Web Version STRSQL

This tutorial will use the IBM i command line to edit, test, and deploy a step-by-step Rails web version of the IBM i STRSQL application instead of using the PowerRuby CL-based fast Rails helpers (SETPOWRBY, RAILSNEW, RAILSSVR). This manual approach will provide a better understanding of the Rail plumbing which should make it easier for you to develop PowerRuby applications.

A Ruby gem is simply a community contribution to the Ruby project. In this case IBM authored both ibm_db gem for DB2 access and xmlservice gem for IBM i access to native objects (*CMD, *PGM, *SRVPGM, DB2 objects, etc.). We will be creating our web STRSQL using PowerRuby included IBM gem technologies:

Rails commands used:

  • rails new strsql --skip-sprockets --skip-bundle
    • bundle install --local
  • rails generate model
    • rake db:migrate
  • rails generate controller
  • rails server -p 4242
    • Ctrl-C to shutdown server

Rails STRSQL Step-By-Step

This tutorial will use the IBM i PASE command line to edit, test, and deploy a step-by-step Rails web version of the IBM i STRSQL application. A simplified interface application will be created without using asset pipeline (rails sprockets / coffeescript), thereby the tutorial remains focused on IBM i specific topics.

Web GUI designers and Rails asset pipeline development requires a native JavaScript engine to process and package CoffeeScript/JavaScript. As of this writing, IBM i does not provide a native JavaScript engine, therefore Rails commands dealing with asset pipeline or sprockets may fail. However, with a simple web search you can find web pages which explain the concept of precompiling assets, developing JavaScript assets on laptop and deploying on an IBM i system.

start now …

Step 0) Design web STRSQL

A quick tour of STRSQL will define our web STRSQL requirements. We will simplify interface of web STRSQL to use HTML, but we will keep basic functions found in the green screen version.

Figure 1. STRSQL requirements match green screen.

STRSQL
STRSQL

→ STRSQL enables SQL statements to create tables, insert data, and delete data.

→ STRSQL SQL select statements also fetch data rows for display

→ STRSQL records user history, allowing recall of previous SQL statements


Step 1) Command line and editor

As a Linux user, I prefer a ssh terminal connected to my IBM i server(lp0364d). This tutorial is not about ssh. However, please note ssh -X option allows me to run a graphical AIX editor on IBM i (SSH setup). YIPs site provides a copy of nedit binary on wiki page Fun with QSH, call qp2term and RPG (SHELL_use-1.0.3.zip). Feel free to substitute your favorite remote attached editor, but i am going with native IBM i PASE technology. If you choose not to use ssh terminal, you may enter Rails commands using 5250 CALL qp2term .

Figure 2. Choose a terminal for command line Ruby.

ssh
ssh -X me@myibmi

→ bash - preferred shell for Linux geeks (YIPS OS binary download)

→ export PATH/LIBPATH - select PowerRuby version of Ruby/Rails (SETPOWRBY)

→ cd /www/apachedft/htdocs - create application in DocumentRoot


Step 2) Create Rails application

We need to create our Rails skeleton application. We are using a few options with rails new to help IBM i stay within the basic PowerRuby distribution. PowerRuby rails utility rails new will create a new directory strsql/ with the full Rails skeleton application sub-directories and “get started” code.

Figure 3. Command rails new strsql.

rails new
> cd /www/apachedft/htdocs

> rails new strsql —skip-sprockets —skip-bundle

→ (--skip-sprockets) - option ignore asset pipeline

→ (--skip-bundle) - option avoid bundle (next step)


Step 3) Edit Gemfile

We wish to use DB2 for i, not sqlite3 (default), so we need to edit Gemfile create by rails new and replace sqlite3 with ibm_db (DB2 for i). Also, we are not using the many options for Rails asset pipeline (JavaScript, etc.), so comment out these directives to avoid unwanted processing errors later in the tutorial and to simplify this example.

Figure 4. Edit Gemfile to add ibm_db.

bundle install
> cd /www/apachedft/htdocs/strsql

> e Gemfile

→ ibm_db - use DB2, not sqlite3

→ comment - comment out additional options (JavaScript, etc.)


Step 4) Bundle install

We wish to avoid unwanted automatic gem version updates from Rails http://rubygems.org , so we will use a local option with bundle install. Examining Gemfile.lock file after bundle install, we see our application gem dependencies are PowerRuby locked for any deployments. Of course PowerRuby is our IBM i team guardian with a PowerRuby installation, so our Rails applications are likely to run out of box on other PowerRuby machines.

Figure 5. Command bundle install.

bundle install
> cd /www/apachedft/htdocs/strsql

> bundle install —local

→ (--local) - option ignore rubygems.org updates


Step 5) Edit database.yml

We wish to use DB2 for i for our Rails database, so we must remove all sqlite3 (default), in favor of DB2 for i profile information. This tutorial uses an IBM i user profile named. A user profile name of DB2 is not a requirement for Rails applications. your profile name could be any on your IBM i system. The PowerRuby RAILSNEW command will ask you for a profile you wish to use and update database.yml on create, but as promised, this is what is happening under the covers.

Figure 6. Edit database.yml user id, password and options.

database yml
> cd /www/apachedft/htdocs/strsql

> e config/database.yml &

→ remove everything (SQLite3), insert DB2 for i


There are many ibm_db options for the database.yml file, and thanks to DB2 Connect 10.5, most will work remotely from your laptop connected to a DB2 for i server. DB2 Connect is alicensed program product that needs to be purchased. For production usage, the DB2 Connect Unlimited Edition for System i packaging typically offers the best terms for IBM i customers. Contact your local IBM representative or business partner for pricing information. For more information on this product, see the following Web site: DB2 Connect. A trial DB2 Connect license file for evaluation purposes can be obtained by sending an email to: rmahendr@us.ibm.com

Additionally, DB2 for i system naming mode, starting with IBM i 7.1, accepts both SQL naming schema.table and system naming LIB/FILE, so Open Source software like Ruby/Rails has a higher probability of working with IBM i traditional applications dependent on the *LIBL (library list) behavior. However, PowerRuby team has not tested Rails under all possible IBM i options, so it is perhaps best to simply use defaults for your Rails application.

The follow is a list of possible DB2 values in your database.yml, IBM i specific values labeled ‘ibm_i_’ are intended for DB2 for i, only one value is allowed on a single line.

Figure 7. List of possible database.yml options.

development:
  schema: BOB
  app_user: bob
  account: bob
  application: bob1
  workstation: bobws 
  ibm_i_naming: system, sql (default sql)
  ibm_i_libl: BOB QTEMP (CHGLIBL)
  ibm_i_curlib: BOB (CHGCURLIB)
  ibm_i_sort_seq: job, system (default system)
  ibm_i_isolation: none, ur, cs, rs, rr 
  ibm_i_date_fmt: iso, usa, eur, jis, dmy, mdy, ymd, jul, job
  ibm_i_time_fmt: iso, usa, eur, jis, hms
  ibm_i_date_sep: slash, dash, period, comma, blank, job
  ibm_i_time_sep: colon, period, comma, blank, job
  ibm_i_decimal_sep: period, comma, job
  ibm_i_query_goal: first, all
  <<: *db2profile

YIPs ibm_db versions include database.yaml password encryption ability, details of encrypt support are not fully tested, therefore support may change in ibm_db gem. However, if you chose to use encrypt support today, here are the new ibm_db APIs.

  • pwd_key = ActiveRecord::Base.ibm_db_generate_key()
  • pwd_enc = ActiveRecord::Base.ibm_db_generate_password(password,pwd_key)
    • sample program ibm_db download test/IBMi/samples/genpassword.rb

You have multiple choices for database.yml:

  • open passwords one database.yml
    db2profile: &db2profile
      adapter: ibm_db
      database: "*LOCAL"
      username: DB2
      password: MYPWD
    development:
      <<: *db2profile
    test:
      <<: *db2profile
    production:
      <<: *db2profile
    
    Notes:
      - typical Rails yaml file
      - password open/clear password
    
  • everything in one database.yml
    db2profile: &db2profile
      adapter: ibm_db
      database: "*LOCAL"
      username: DB2
      pwd_enc: "alkqap/Ao7ACWwizSQ2JvZ86+s0yR5FdDmIU68JuQv4=%0A"
      pwd_key: "YIPS4321AAAAAAAAAAAAAAA132424245"
    development:
      <<: *db2profile
    test:
      <<: *db2profile
    production:
      <<: *db2profile
    
    Notes:
      - encrypted password, but key in the same file
      - pwd_key = ActiveRecord::Base.ibm_db_generate_key()  (or your 32 characters)
      - pwd_enc = ActiveRecord::Base.ibm_db_generate_password(password,pwd_key)
    
  • separate yaml files.
    • database.yml
      db2profile: &db2profile
        adapter: ibm_db
        database: "*LOCAL"
        username: DB2
        pwd_yaml: /my/safe/path/password.yml
      development:
        <<: *db2profile
      test:
        <<: *db2profile
      production:
        <<: *db2profile
      
      Notes
       - encrypted password and key in separate files  (limit access)
       - pwd_yaml path to password yaml file
      
    • password.yml
      key_yaml: /my/really/safe/path/key.yml
      DB2:
        pwd_enc: "alkqap/Ao7ACWwizSQ2JvZ86+s0yR5FdDmIU68JuQv4=%0A"
      FLINROCK:
        pwd_enc: "alkqap/Ao7ACWwizSQ2Jvrdt+s0yR5FdDmIU68JuQv4=%0A"
      SLATER:
        pwd_enc: "alkqap/Ao7A123izSQ2Jvrdt+s0yR5FdDmIU68JuQv4=%0A"
      
      Notes:
       - admin many profiles, where key is username from database.yml (DB2 this case)
       - key_yaml path to key yaml file
       - pwd_enc = ActiveRecord::Base.ibm_db_generate_password(password,pwd_key)
      
    • key.yml
      pwd_key: "YIPS4321AAAAAAAAAAAAAAA132424245"
      
      Notes:
       - pwd_key used both generation/runtime
       - pwd_key = ActiveRecord::Base.ibm_db_generate_key()  (or your 32 characters)
      

Step 6–0) Try web server (introduction)

So far, we completed rails new house keeping administration, edited Gemfile and database.yml. Therefore, we now have enough Rails application to check our web environment. For the web server component, we have many options in Rails living within IBM i Apache.

Most Rails applications are deployed in proxy/reverse configuration, meaning, you start/stop a Rails application independently of your main web site. Non-Apache web servers exist to start/stop/mange Rails applications as child jobs, but these web servers largely hide proxy/reverse configuration (nice for administrators, challenging for developers to understand). This tutorial is using IBM i Apache, so it will follow traditional Rails proxy/reverse configuration and start the Rails web server independent of the Apache web server.

  • IBM i Apache start.
    STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)
    
  • Rails webrick server start.
    cd /www/apachedft/htdocs/strsql
    rails server -p 4242
    (Ctrl-C to shutdown server)
    

Note: We could choose to deploy Rails application as Apache FastCGI similar to PHP on i (Zend Server), but FastCGI is not the Rails way. Not to mention you will have trouble finding information on using Rails and FastCGI together.

PowerRuby RAILSNEW/RAILSSVR … Rails issues dealing with the IBM i Apache largely disappear using PowerRuby smart IBM i wizards (RAILSNEW/RAILSSVR). Essentially, RAILSNEW/RAILSSVR is Apache DocumentRoot agnostic, create your Rails application anywhere in IFS and RAILSNEW creates an Apache configuration without need of DocumentRoot. RAILSNEW creates an Apache configuration (httpd.conf), which simply passes all web traffic directly to your independently started Rails application (RewriteRule/ProxyPassReverse). PowerRuby RAILSSVR of course, manages day to day administration aspects start/stop your Rails applications (rails server -p 4242), and RAILSSVR could be used stand alone to batch-submit the application in this tutorial.

  • RAILSNEW no DocumentRoot issues
    httpd.conf:
    Listen *:10022
    RewriteEngine On
    RewriteRule ^(.*) http://127.0.0.1:4242/$1 [P]
    ProxyPassReverse / http://127.0.0.1:4242/
    

RAILSNEW/RAILSVR is a great way to get on line quickly with Rails applications. However, when you try to fit a Rails application into an existing web site, or want other ways of configuring your site, you may need a better understanding of the Rails experience.

Step 6–1) Try web server (simple site detour)

Our tutorial goal, add a Rails application to an exiting site (next step), but we will take a quick look at a simple one Rails application site.

In previous steps, we created our Rails application in Apache sub-directory /www/apachedft/htdocs/strsql, to avoid issues with Apache sub-directories, we simply modify our Apache DocumentRoot to fit our one Rails application site /www/apachedft/htdocs/strsql
/www/apachedft/httpd.conf:
Listen *:10022

# DocumentRoot /www/apachedft/htdocs
DocumentRoot /www/apachedft/htdocs/strsql

# Simple configuration (like RAILSNEW)
RewriteEngine On
RewriteRule ^(.*) http://127.0.0.1:4242/$1 [P]
ProxyPassReverse / http://127.0.0.1:4242/
Now we start our Rails application
cd /www/apachedft/htdocs/strsql
rails server -p 4242
(Ctrl-C to shutdown server)
And we start our Apache server — order start not important
STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)

Figure 8. First STRSQL welcome screen.

welcome try

http://lp0364d:10022

Technically speaking as Apache proxy/reverse forwards a request http://lp0364d:10022, HTTP “get route” seen by your Rails application contains “/”. There is no Apache sub-directory issues, because we changed DocumentRoot to /www/apachedft/htdocs/strsql, so Rails just proxy/reverse works.


Note: We are essentially mimicking RAILSNEW/RAILSVR by removing all Apache DocumentRoot sub-directory issues, in fact, we could move the Rails application out of /www/apachedft/htdocs and start in another directory and all would continue to work.

Step 6–2) Try web server (existing site)

When you create an IBM i Apache instance, DocumentRoot is set /www/instance/htdocs, this enables a ‘/’ root directory container for your many web application running the web site htdocs/index.html, htdocs/wiki, htdocs/strsql, etc., to wit, keeps web traffic contained to /www/instance/htdocs child sub-directories to avoid hacker anarchy access to non-web parts of your machine. So, let’s add the new application we have been building in sub-directory htdocs/strsql.

Fail first attempt … We are adding a Rails application to an existing web site and we want to use the traditional Rails proxy/reverse method, therefore a simple modification to the previous example Apache configuration adding ‘/strsql’ seems logical, but does not work.
/www/apachedft/httpd.conf:
Listen *:10022

DocumentRoot /www/apachedft/htdocs
# DocumentRoot /www/apachedft/htdocs/strsql

# Simple configuration (like RAILSNEW)
# RewriteEngine On
# RewriteRule ^(.*) http://127.0.0.1:4242/$1 [P]
# ProxyPassReverse / http://127.0.0.1:4242/

# Existing web site (change config/routes.rb)
RewriteEngine on
RewriteRule ^/strsql(.*) http://127.0.0.1:4242/strsql/$1 [P]
ProxyPassReverse /strsql/ http://127.0.0.1:4242/strsql/ 
Now we start our Rails application.
cd /www/apachedft/htdocs/strsql
rails server -p 4242
(Ctrl-C to shutdown server)
And we start our Apache server —order start not important
STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)

Rails and the Apache sub-directory problem …

This tutorial is about lifting the curtain on the PowerRuby CL-based helpers and exposing Rails plumbing. There are many Ruby folks have difficulty deploying Rails applications in an existing site using Apache. The good news is that Rails can work within existing IBM i Apache sites, but you must understand the true multiple application web site landscape. As we see from the browser error, our first issue, unfortunately, Rails does not work well as an Apache sub-directory to DocumentRoot '/strsql'.

Figure 9. Error STRSQL welcome screen.

rails error

http://lp0364d:10022/strsql

Technically speaking as Apache proxy/reverse forwards a sub-directory request http://lp0364d:10022/strsql, HTTP “get route” seen by your Rails application contains sub-directory “/strsql”. Unfortunately your Rails application was started under sub-directory and assumes DocumentRoot location “/”, which results in a route mismatch Apache sent “/strsql” and you see an error in your browser.


Rails sub-directory scope fix …

Rails provides an easy work around by changing Rails application config/routes.rb to understand the sub-directory scope “/strsql” sent by Apache\\

config/routes.rb (use scope):
scope "/strsql" do
  get "welcome/index"
  root :to => 'welcome#index'
end

Simple enough fix, however, our tutorial in not yet ready to test out this fix. A STRSQL welcome page to replace the default Rails welcome page on the next step.

Later versions of Rails…

This tutorial is written generically for most any version of Rails, as you approach later versions of Rails edge documentation, you find alternative ways dealing with Rails applications in a sub-directory, feel free to try the following recommendation.

Add this line to config/environments/development.rb.
config.relative_url_root = "/strsql"
Then modify config.ru.
require ::File.expand_path('../config/environment',  __FILE__)
map Rails.application.config.relative_url_root || "/" do
  run Rails.application
end

You should be able to remove most manually entered /strsql in the tutorial.

Step 7–0) Create welcome page

We wish to replace default Rails welcome screen (simple site detour), and actually begin process of building our application. The first task is to create a welcome page. We will be viewing the default index page created by rails generate controller welcome index, so you see exactly what this Rails command generates. However, don’t worry we will modify our page to mimic STRSQL later in the tutorial.

Figure 10. Command rails generate controller.

create welcome

> cd /www/apachedft/htdocs/strsql
> rails generate controller welcome index

We find our new welcome page at app/views/welcome/index.html.erb. However, as mentioned previously, before we can try our web server we need to adjust config/routes.rb to understand our Apache sub-directory “/strsql”.

Figure 11. Edit routes.rb to add sub-directory scope.

create welcome

> cd /www/apachedft/htdocs/strsql
> e config/routes.rb
Adding scope "/strsql" do


Step 7–1) Try welcome web server

We added our welcome page app/views/welcome/index.html.erb and we fixed our scope ‘/strsql’ config/routes.rb, so our existing site Rails application will now work (repeat steps)
# rails /strsql (subdir affects rails)
RewriteEngine on
RewriteRule ^/strsql(.*) http://127.0.0.1:4242/strsql/$1 [P]
ProxyPassReverse /strsql/ http://127.0.0.1:4242/strsql/ 
Now we start our Rails application.
cd /www/apachedft/htdocs/strsql
rails server -p 4242
(Ctrl-C to shutdown server)
And we start our Apache server — order start not important.
STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)

Figure 12. Basic STRSQL command screen.

welcome try

http://lp0364d:10022/strsql

Nothing fancy, just default view, but it works!


So far …

Time to take a breath and recap our progress:

  • rails new strsql —skip-sprockets —skip-bundle
    • created our rails application in /www/apachedft/htdocs/strsql
    • option: —skip-sprockets used to avoid asset pipeline (IBM i does not have native JavaScript)
    • option: —skip-bundle used to avoid updates from http://rubygems.org
  • edit strsql/Gemfile
    • change from SQLite3 to ibm_db (DB2)
  • bundle install —local
    • bundle locked our Gemfile.lock to PowerRuby
    • option: —local avoids unwanted updates into PowerRuby (leave alone)
  • edit /strsql/db/database.yml
    • open passwords one database.yml (traditional Rails)
    • encrypted passwords/key everything in one database.yml
    • encrypted passwords/key separate yaml files
  • started web server
    • tutorial following traditional dual server Rails model proxy/reverse
    • IBM i Apache start.
      STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)
      
    • Rails webrick server start.
      cd /www/apachedft/htdocs/strsql
      rails server -p 4242
      (Ctrl-C to shutdown server)
      
    • multiple configurations possible.
      # Simple configuration (like RAILSNEW)
      # RewriteEngine On
      # RewriteRule ^(.*) http://127.0.0.1:4242/$1 [P]
      # ProxyPassReverse / http://127.0.0.1:4242/
      
      # Existing web site (change config/routes.rb)
      RewriteEngine on
      RewriteRule ^/strsql(.*) http://127.0.0.1:4242/strsql/$1 [P]
      ProxyPassReverse /strsql/ http://127.0.0.1:4242/strsql/ 
      
  • Next?
    • tutorial proceeding with example of adding Rails application to existing web site
    • no longer have to start/stop Apache, only Rails application needs to start/stop remainder of the tutorial.

Step 8–0) Add xmlservice (Gemfile)

We wish to use the gem xmlservice to process our STRSQL commands, therefore we must alter Gemfile and bundle again. As before, bundle install ‘--local’ will lock our Gemfile.lock to PowerRuby gems, and includes PowerRuby xmlservice gem.

Figure 13. Edit Gemfile to add xmlservice

gemfile xmlservice

> cd /www/apachedft/htdocs/strsql
> e Gemfile
> bundle install —local


Step 8–1) STRSQL welcome page (view)

We need our welcome page to look like STRSQL, so that SQL statements can be submitted to xmlservice gem. For now, let’s ignore recalling previous SQL requests and add a very simple form with a text input to act as our STRSQL command line.

Figure 14. Edit view simple STRSQL command line.

welcome strsql

> cd /www/apachedft/htdocs/strsql
> e app/views/welcome/index.html.erb
STRSQL form target /strsql/xmlservice/execute
STRSQL request post text params[:command]

Any html.erb file may look intimidating at first glance, but like HTML you simply have to play a day and you see how it works.

  • erb is a mix of pseudo-html and pure html
  • erb processed by ruby, therefore ruby statements and variables can be used (not this form)
  • Ruby erb manual
Specifically this form we are hard coding REST target /strsql/xmlservice/execute, and defaulting to ‘post’ action. Rails has many other ways to accomplish the form task, but true to this tutorial we are staying close to the plumbing to aid understanding (keep it simple). The submit_tag "Execute statement" is a button on welcome form, where :action => 'execute' will route to method def execute in our xmlservice controller.
app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
  end
end
If you read the manual, you will see text_field_tag "command" will render as params[:command] in our xmlservice controller, so now we know how to transfer the STRSQL request from welcome form text input field.
app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
  @command =  params[:command]
  end
end

Step 8–2) STRSQL execute route (route)

We need to modify config/routes.rb to route our new STRSQL welcome form ‘post’ request /strsql/xmlservice/execute.

Figure 15. Edit routes.rb to new STRSQL command screen.

route welcome

> cd /www/apachedft/htdocs/strsql
> e config/routes.rb


As we learned, life in a Apache sub-directory ‘/strsql’ requires our routes to be scoped, therefore we place the target of /strsql/xmlservice/execute inside the scoped block. We know action is post via welcome form and ‘/strsql’ will be part of the scope, leaving /xmlservice/execute in routes, which we route as controller#action to our xmlservice controller xmlservice#execute on form submit.
app/views/welcome/index.html.erb:
<%= form_tag "/strsql/xmlservice/execute" do %>
<%= text_field_tag "command", 'select * from db2/animals', size: 66 %>
<br>
<%= submit_tag "Execute statement" , :action => 'execute' %>
<% end %>


config/routes.rb:
Strsql::Application.routes.draw do
  scope "/strsql" do
    get "welcome/index"
    post '/xmlservice/execute', to: 'xmlservice#execute'
    root :to => 'welcome#index'
  end
end

app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
  @command =  params[:command]
  end
end

Step 10–0) Add xmlservice (create)

We need a controller to handle xmlsrvice requests for STRSQL.

Figure 16. Command generate controller xmlservice.

create xmlservice

> cd /www/apachedft/htdocs/strsql
> rails generate controller xmlservice


Step 10–1) Add xmlservice (controller)

We need some code to handle strsql/xmlservice/execute from the welcome form.

Figure 17. Edit controller xmlservice to handle execute SQL requests.

control xmlservice

> cd /www/apachedft/htdocs/strsql
> e app/controllers/xmlservice_controller.rb


Again our welcome form will route as controller#action to our xmlservice controller xmlservice#execute on form submit.
config/routes.rb:
Strsql::Application.routes.draw do
  scope "/strsql" do
    get "welcome/index"
    post '/xmlservice/execute', to: 'xmlservice#execute'
    root :to => 'welcome#index'
  end
end

app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
    @command =  params[:command]
    ibmx = XMLService::I_DB2.new(@command)
    ibmx.xmlservice
    rows = ibmx.response.output
    @output = rows.inspect
  end
end

ActiveXMLService provides default ActiveXMLService::Base.establish_connection(“connection” => “ActiveRecord”), thereby default XMLSERVICE requests flow on your current DB2 ActiveRecord connection (I_DB2, I_PGM, I_SRVPGM, etc.). A connection to DB2 for i started when our Rails application started rails server -p 4242, and XMLSERVICE provides a stored procedure interface (iPLUG4K - iPLUG15MB), therefore def execute only need use ibmx = XMLService::I_DB2.new(@command) and it all works (simple Rails model).

Step 10–2) Add xmlservice (view)

We need a view for xmlservice execute. We did not ask rails generate controller xmlservice for a view component, so we we just add a view manually in the directory app/views/xmlservice/execute.html.erb.

Figure 18. Edit view for xmlservice result execute SQL requests.

view xmlservice

> cd /www/apachedft/htdocs/strsql
> e app/views/xmlservice/execute.html.erb


Nothing fancy on the xmlservice execute view, simply dump output of xmlservice data provided by xmlservice controller in a text_area_tag. Displayed information is @command = params[:command] command passed through from welcome form, and of course data returned from xmlservice @output = rows.inspect . XMLSERVICE on IBM i only deals with XML documents, so XML document to array output was handled by xmlservice gem XMLService::I_DB2.new(@command) .
app/views/xmlservice/execute.html.erb:
<%= text_field_tag "command", @command, size: 66 %>
<%= text_area_tag 'body', @output, size: "75x10" %>

app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
    @command =  params[:command]
    ibmx = XMLService::I_DB2.new(@command)
    ibmx.xmlservice
    rows = ibmx.response.output
    @output = rows.inspect
  end
end

Step 10–2) STRSQL query animals

We now have a fully functional STRSQL, so restart Rails application.
cd /www/apachedft/htdocs/strsql
rails server -p 4242
(Ctrl-C to shutdown server)

STRSQL welcome page now appears.

Figure 19. STRSQL command line welcome page.

welcome strsql

http://lp0364d:10022/strsql


Press ‘Execute statement’ select * from db2/animals.

Figure 20. STRSQL execute simple SQL query.

strsql animals

select * from db2/animals


Step 10–3) STRSQL query syscolumns

We can do most anything with STRSQL at this point, so we will try a query against the columns meta data catalog.

Figure 21. STRSQL command line welcome page.

welcome strsql

http://lp0364d:10022/strsql


Press ‘Execute statement’ select column_default from syscolumns where NAME = 'BREED'.

Figure 22. STRSQL execute simple SQL meta data query.

strsql animals

select column_default from syscolumns where NAME = 'BREED'


So far …

Time to take a breath and recap our progress:

  • edit Gemfile
    • add xmlservicegem
    • bundle install —local
      • locked Gemfile.lock to PowerRuby (with xmlservice)
  • edit welcome page app/views/welcome/index.html.erb
    • made a crude text entry to run STRSQL requests (like green screen)
    • added a form that will post requests to /strsql/xmlservice/execute
  • edit routes config/routes.rb
    • added a ‘post’ route to handle /xmlservice/execute
  • rails generate controller xmlservice
    • generated a controller to handle /xmlservice/execute
  • edit xmlservice controller to add ‘execute’ method app/controllers/xmlservice_controller.rb
    • after calling xmlservice class variable @output has result
  • edit xmlservice view for execute to dump processed output
    • crude textarea to display provided @output variable
  • Next?
    • cleanup the STRSQL views
    • create a recall model database table to emulate STRSQL ability to retrieve previously executed SQL statements.

11–0) create History (model)

STRSQL needs a history database to enable ‘recall previous command’ similar to Sto the retrieve capability provided by the green screen STRSQL F9 function key. Rails rails generate model will build a database ‘migrate’ to enable create table db/migrate/20140131193446_create_histories.rb. Our ‘History’ table will contain an integer ID key field (automatic), and we added command field of type text command:text, where ‘text’ is DB2 type CLOB.

Figure 23. Generate model database history.

history model

> cd /www/apachedft/htdocs/strsql
> rails generate model History command:text


Rails plurals …

Please note we asked Rails rails generate model History, whereby Rails immediately asserted actual physical table name ‘Histories’ db/migrate/20140131193446_create_histories.rb. The intent of pluralization is to make your code more readable and transparent, but this convention drives some developers crazy. The following list contains the default Rails rules for plural and singular. Becoming familiar with these three conventions will go a long way toward getting comfortable with Rails.

  • Database table names: plural
    • Database table names are expected to be pluralized. For example, a table containing employee records should be named Employees.
  • Model class names: singular
    • Model class names are the singular form of the database table that they are modeling. For example, an Employee model is created based on a table named employees.
  • Controller class names: plural
    • Controller class names are pluralized, such as EmployeesController or AccountsController.

Where is my table?

DB2 physical table ‘Histories’ was not created using command rails generate model, only a migration template db/migrate/20140131193446_create_histories.rb, thereby allowing us to edit custom attributes before the actual migration. You can verify this by using the schema value specified in the database.yml file on the WRKLIB (Work With Libraries) system command. Using a traditional Rails database.yml file, the DB2 default schema will be the same as the username directive (username: DB2). Other schema options are possible by making configuration changes to the database.yml file.

11–1) edit History create (model)

Edit generated migrate file db/migrate/20140131193446_create_histories.rb to add any attributes required before actual create/migrate DB2 Histories table. Custom attribute for Histories table is not allow NULL commands by adding :null => false.

Figure 24. Edit database migrate for create table histories.

edit history

> cd /www/apachedft/htdocs/strsql
> e db/migrate/20140131193446_create_histories.rb


11–2) create/migrate History (model)

After editing custom attributes in Histories migration, we will generate the actual physical DB2 table using command rake db:migrate.

Figure 25. Create database table histories.

migrate history

> cd /www/apachedft/htdocs/strsql
> rake db:migrate


If we made a mistake in our schema design, we can recreate using rake db:migrate:redo.

Figure 26. Recreate database table histories.

redo history

> cd /www/apachedft/htdocs/strsql
> rake db:migrate:redo


There are many rake options, see command line rake -T. Here’s a list of some more popular commands below. I didn’t have time to test all options with DB2 for i.
rake db:create            - creates the database for the current env
rake db:create:all        - creates the databases for all envs
rake db:drop              - drops the database for the current env
rake db:drop:all          - drops the databases for all envs
rake db:migrate           - runs migrations for the current env that have not run yet
rake db:migrate:up        - runs one specific migration
rake db:migrate:down      - rolls back one specific migration
rake db:migrate:status    - shows current migration status
rake db:migrate:rollback  - rolls back the last migration
rake db:forward           - advances the current schema version to the next one
rake db:seed              - (only) runs the db/seed.rb file
rake db:schema:load       - loads the schema into the current env's database
rake db:schema:dump       - dumps the current env's schema (and seems to create the db as well)
rake db:setup             - runs db:schema:load, db:seed
rake db:reset             - runs db:drop db:setup
rake db:migrate:redo      - runs (db:migrate:down db:migrate:up) 
                            or (db:migrate:rollback db:migrate:migrate) 
                            depending on the specified migration
rake db:migrate:reset     - runs db:drop db:create db:migrate

Migration ID establishes up/down: 

> cd /www/apachedft/htdocs/strsql
> rake db:migrate:status
database: *LOCAL
 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             ********** NO FILE **********
   up     20131017214312  ********** NO FILE **********
   up     20131017214541  ********** NO FILE **********
   up     20131118211937  ********** NO FILE **********
   up     20140131193446  Create histories

After migration, you can verify the table exists on the server by executing the DSPFD (Display File Description) command on a 5250 emulator command line.

12–1) save History commands (xmlservice)

Our xmlservice controller already has a functional STRSQL, so all we need do is track History records with History.create(:command => @command).
app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
    @command =  params[:command]
    ibmx = XMLService::I_DB2.new(@command)
    ibmx.xmlservice
    rows = ibmx.response.output
    @output = rows.inspect
    # add to history
    History.create(:command => @command)
  end
end

12–2) display STRSQL history (welcome)

Initially, we will simply display the STRSQL statement history on the STRSQL welcome page.

Figure 27. STRSQL welcome screen with simple command history.

welcome history

http://lp0364d:10022/strsql

Nothing fancy, just default list, but it works!


Here is our welcome view and controller code, augmented with history of SQL statements.
app/views/welcome/index.html.erb:
<%= form_tag "/strsql/xmlservice/execute" do %>
<%= text_field_tag "command", 'select * from db2/animals', size: 66 %>
<br>
<%= submit_tag "Execute statement" , :action => 'execute' %>
<br>
<%= text_area_tag 'body', @output, size: "75x10" %>
<% end %>

app/controllers/welcome_controller.rb:
class WelcomeController < ApplicationController
  def index
    # display history
    @output = ""
    History.find_each do |row|
      @output += row.command + "\n"
    end
  end
end

13) Fancy history recall

At this point we have covered basics of view/controller, so we will run quickly through STRSQL history recall.

CSS was added to the welcome form to look more like a STRSQL green screen, but took some design liberty to modernize recall previous statement to be a simple mouse click on history line for a better GUI user experience.

Figure 28. STRSQL welcome with web GUI history.

welcome history

http://lp0364d:10022/strsql

> mouse click history line to recall (f9++)
> Execute Statement - run statement
> Refresh History - refresh history list
> Clear History - clear history database

We replaced text_area_tag tag with and easy to use line-by-line select_tag and added a small bit of JavaScript in welcome STRSQL :onchange =>'changeValue("previous","command"); to move any clicked history item into the text filed for Execute Statement. Ruby erb has a very nice select_tag feature options_for_select(@output), which allows us to pass an Array to populate options for our select, therefore we altered welcome controller to pass an array instead of String used for removed text_area_tag.
app/controllers/welcome_controller.rb:
class WelcomeController < ApplicationController
  def index
    # display history
    @output = Array.new
    History.find_each do |row|
      @output << row.command
    end
  end
end

We added submit_tag buttons to our form_tag /strsql/xmlservice/execute:

  • Execute Statement - run statement (original)
  • Refresh History - refresh history list
  • Clear History - clear history database
Welcome erb also changed :action => 'execute' to :name => 'execute', allowing much simpler coding of multiple button logic in the execute controller.
app/views/welcome/index.html.erb:
<head>
<style>
body { background-color:green; color:white; }
select {width:550px; border-style:none;}
option {border-style:none; }
input[type="text"] {width:535px;}
input[type="submit"] {background-color:green; color:white; border-style:none; text-decoration:underline; }
</style>
<script type="text/javascript">
function changeValue(id1,id2)
{
  here = document.getElementById(id1);
  there = document.getElementById(id2);
  if (here.selectedIndex >= 0) {
    there.value = here.options[here.selectedIndex].value;
  }
}
</script>
</head>
<body>
<%= form_tag "/strsql/xmlservice/execute" do %>
STRSQL (Enter new SQL statment or highlight previous statement)
<br>
<%= select_tag 'previous', options_for_select(@output), size: 10, :onchange =>'changeValue("previous","command");' %>
<br>
<%= text_field_tag "command", ''%>
<br>
Action: <%= submit_tag "Execute Statement" , :name => 'execute' %>
<%= submit_tag "Refresh History", :name => 'refresh' %>
<%= submit_tag "Clear History", :name => 'clear' %>
<% end %>
</body>
We updated our xmlservice controller to handle multiple button names execute, clear, and refresh.
app/controllers/xmlservice_controller.rb:
class XmlserviceController < ApplicationController
  def execute
    if !params[:execute].nil?
      if params[:command].strip == ""
        refresh
      else
        run
      end
    elsif !params[:clear].nil?
      clear
      refresh
    elsif !params[:refresh].nil?
      refresh
    end
  end
  def run
    @command =  params[:command]
    ibmx = XMLService::I_DB2.new(@command)
    ibmx.xmlservice
    rows = ibmx.response.output
    @output = rows.inspect
    # add to history
    History.create(:command => @command)
  end
  def clear
    History.delete_all
  end
  def refresh
    redirect_to :back
  end
end
Our config/routes.rb remains the same for post '/xmlservice/execute', to: 'xmlservice#execute', simply included for completeness.
config/routes.rb:
Strsql::Application.routes.draw do
  # get "welcome/index" -- rails generate controller moved into scope
  scope "/strsql" do
    get "welcome/index"
    post '/xmlservice/execute', to: 'xmlservice#execute'
    root :to => 'welcome#index'
  end
end

14) Fancy execute display

The tutorial leaves the task of creating a fancier execute display to the reader, but at least we did go green.

Figure 29. STRSQL simple execute SQL display screen.

welcome history

http://lp0364d:10022/strsql/xmlservice/execute


To give you a head start, we changed view execute (erb), adding :disabled for no edit allowed, and link_to "Back" to return to welcome screen after execution of the statement.
app/views/xmlservice/execute.html.erb:
<head>
<style>
body { background-color:green; color:white; }
input[type="text"] {width:535px;}
a{color:white;}
</style>
</head>
<body>
<%= text_field_tag "command", @command, disabled:true %>
<br>
<%= text_area_tag 'body', @output, size: "75x10", disabled:true %>
<br>
<%= link_to "Back", "/strsql" %>
</body>

Summary

This tutorial used the IBM i command line to edit, test, and deploy a step-by-step Rails web version of IBM i STRSQL using Ruby gems ibm_db gem for DB2 access and xmlservice gem for IBM i access to native objects.

The tutorial used Rails commands to create web STRSQL.
* rails new strsql --skip-sprockets --skip-bundle

  • bundle install --local
  • rails generate model
    • rake db:migrate
  • rails generate controller
  • rails server -p 4242
    • Ctrl-C to shutdown server

We introduced configuration proxy relationship of IBM i Apache and independently started Rails applications.

  • /www/apachedft/httpd.conf
    Listen *:10022
    DocumentRoot /www/apachedft/htdocs
    # Existing web site (change config/routes.rb)
    RewriteEngine on
    RewriteRule ^/strsql(.*) http://127.0.0.1:4242/strsql/$1 [P]
    ProxyPassReverse /strsql/ http://127.0.0.1:4242/strsql/ 
    
    • config/routes.rb
      Strsql::Application.routes.draw do
        # get "welcome/index" -- rails generate controller moved into scope
        scope "/strsql" do
          get "welcome/index"
          post '/xmlservice/execute', to: 'xmlservice#execute'
          root :to => 'welcome#index'
        end
      end
      
  • IBM i Apache start.
    STRTCPSVR SERVER(*HTTP) HTTPSVR(APACHEDFT)
    
  • Rails webrick server start.
    cd /www/apachedft/htdocs/strsql
    rails server -p 4242
    (Ctrl-C to shutdown server)
    
We introduced Rails model, view, controller, and configuration, within directory geometry established by rails commands.
cd /www/apachedft/htdocs/strsql

Configuration (rails new strsql):
./Gemfile                                       -- gems PowerRuby application (xmlservice, ibm_db)
./config/database.yml                           -- database userid, password and other options
./config/routes.rb                              -- HTTP GET, POST routes to sub-directory /strsql

Controller (rails generate controller):
./app/controllers/welcome_controller.rb         -- STRSQL welcome command control with history model recall
./app/controllers/xmlservice_controller.rb      -- STRSQL xmlservice execute SQL controller with history record

View (rails generate controller default views):
./app/views/welcome/index.html.erb              -- STRSQL welcome command screen with SQL history recall
./app/views/xmlservice/execute.html.erb         -- STRSQL execute SQL display screen

Model (rails generate model):
./db/migrate/20140131193446_create_histories.rb -- create/migrate histories table (rake db:migrate)
./app/models/history.rb                         -- history model (class History < ActiveRecord::Base)

The manual approach was intended to provide a better understanding of developing PowerRuby applications.

Happy Rails to your IBM i from PowerRuby!