Last Updated on 3 March 2020 by Roberto De Pedrini
In the meeting organized by FAQ400 on November 21, 2019 I held a presentation on the use of PowerRuby to generate Excel sheets with data pre-loaded by IBM i. I thought of getting two short articles to publish. Today we only deal with access to DB2.
My previous contribution already exists for the installation of PowerRuby. (PowerRuby CE2 – Ed.)
We only remember here that to verify the installation it is sufficient to add the POWER_SLNG library to the list of libraries and call the DSPSFWRSC command.
Scrolling through the list of installed products you should get to the resource 1PRUBY1.
The following options are necessary for the functions described in this article.
View software resources
System: XXXXX400
ID
resource Opt. Device. Description
1PRUBY1 * BASE 5001 IBM i PowerRuby (administration utilities)
1PRUBY1 * BASE 2924 IBM i PowerRuby (administration utilities)
1PRUBY1 1 5002 IBM i PowerRuby Developer Edition (irubydb)
1PRUBY1 6 5001 IBM i PowerRuby (Ruby 2.4 + Rails 5.1)
The fascinating aspect of Rails is certainly the ORM (Object-Relational Mapping) engine which is made up of the component known as ActiveRecords.
Beyond the theoretical problems of coexistence between relational model and object-oriented programming, which generate the so-called object-relational impedence mismatch (see Wikipedia, class of problems that the various ORM have tried to overcome) here we are interested – very pragmatically- see what you can do, with little effort, on IBM i.
As you known, many IBM i system commands offer the OUTPUT (* OUTFILE) option which allows us to extract the resulting content to a database file.
In the Milan presentation I had considered the DSPFFD command by applying it to a particularly interesting file: the QSYS / QADBXLFI file.
First of all, we use a good command shell (lately I got excited about the Terminal support offered by the Open Source Jupyter Lab project) and we check that both ruby and rails are accessible:
bash-4.4$ /PowerRuby/prV2R4/bin/ruby -v ruby 2.4.4p296 (2018-03-28 revision 63013)[powerpc-aix7.1] bash-4.4$ /PowerRuby/prV2R4/bin/rails -v Rails 5.1.6
We first generate a file called CLEVER with the DSPFFD command in the current library:
bash-4.4$ system "DSPFFD FILE(QSYS/QADBXLFI) OUTPUT(*OUTFILE) OUTFILE(*CURLIB/CLEVER)"
We can now become familiar with Ruby through the interactive interpreter (Interactive RuBy = irb):
bash-4.4$ /PowerRuby/prV2R4/bin/irb irb(main): 001:0>
ActiveRecord can also be used without Rails, but to request its functions it is necessary to recall its gem with the require command:
irb(main):001:0> require 'active_record' => true
Now we can define a Hash variable in which to record the configuration parameters for database access:
irb(main):002:0> conf = {:adapter => "irubydb",
irb(main):003:1 * :database => '*LOCAL',
irb(main):004:1 * :default_schema => '*LIBL',
irb(main):005:1 * :username => '*CURRENT',
irb(main):006:1 * :password => ''}
=> {:adapter => "irubydb",:database => "*LOCAL",:default_schema => "*LIBL",:username => "*CURRENT",: password => ""}
This information will be used to interact with another gem that will be implicitly loaded (the irubydb, part of the PowerRuby project).
With these settings, we will use the SQL inline (not the server mode) also benefiting from the list of current libraries and the permissions of the files belonging to the active user. The method we need (establish_connection) is – evidently – offered by a class (Base) of the ActiveRecord component that we successfully called in the previous step (require):
irb(main):007:0> conn = ActiveRecord::Base.establish_connection(conf)
Now we’re going to do a double sub-classing.
First, we derive the ApplicationRecord class from the ActiveRecord :: Base, preserving its abstract nature.
Next, we derive the Description class from the ApplicationRecord, setting the name of the table to the value ‘clever’ (setting the use of the lower case).
class ApplicationRecord<ActiveRecord::Base self.abstract_class = true end class Description<ApplicationRecord self.table_name = 'clever' end
This allows the Description class to inherit all the methods provided by ActiveRecord: the surprising thing is that we can already interact with the records of the CLEVER table:
s = Description.all()
Now with the map function we can interact with the result (supported in the variable s) by filtering only some fields (the name of the field -WHFLDI- and the description -WHFTXT-).
Of the returned array we can show only some records (here the first 3).
s.map {|h| [h.whfldi, h.whftxt]}[0..2]
In the second part of the article we will deal with the records of the CLEVER file to generate an Excel sheet with them using an additional gem that we will see how to install.
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
