From YiPs Wiki (i powered)

XMLSERVICE: Non Ibmi Client Sql

(click to open)

Quick Page Table of Contents

Scanning…

Ruby: XMLService SQL interface …

XMLSERVICERubyDonWork

Should you wish to access DB2 table data from a non-IBMi client (and you don’t want to purchase DB2 Connect) you can use the XMLService SQL interface. You will need to set up an Apache server instance on your IBMi that has, as a minimum, the following in the httpd.conf:

Listen *:4242
ScriptAlias /cgi-bin/ /QSYS.LIB/XMLSERVICE.LIB/
<Directory /QSYS.LIB/XMLSERVICE.LIB>
  AllowOverride None
  order allow,deny
  allow from all
  SetHandler cgi-script
</Directory>

Note the Listen directive above specifies a port number and you should chose one that is not is use. You could, however, add the above directives to an existing Apache instance. If you do create a new Apache instance, to test the server from your browser, you will need to also add DocumentRoot and Directory directives to provide access to htdocs:

DocumentRoot /www/xmlservice/htdocs
ScriptAlias /cgi-bin/ /QSYS.LIB/XMLSERVICE.LIB/
<Directory /QSYS.LIB/XMLSERVICE.LIB>
  AllowOverride None
  order allow,deny
  allow from all
  SetHandler cgi-script
</Directory>
<Directory /www/xmlservice/htdocs>
  order allow,deny
  allow from all
</Directory>

The above example Apache httpd.conf configuration assumes that you have installed XMLService directly (following instructions at http://174.79.32.155/wiki/index.php/XMLService/XMLSERVICEInstall) . But if you have installed PowerRuby, change the location of the library containing the XMLService CGI program to POWER_RUBY, e.g.:

Listen *:4242
DocumentRoot /www/xmlservice/htdocs
ScriptAlias /cgi-bin/ /QSYS.LIB/POWER_RUBY.LIB/
<Directory /QSYS.LIB/POWER_RUBY.LIB>
  AllowOverride None
  order allow,deny
  allow from all
  SetHandler cgi-script
  Options +ExecCGI
</Directory>
<Directory /www/xmlservice/htdocs>
  order allow,deny
  allow from all
</Directory>

On your client, you will need to have the xmlservice Ruby gem installed. To do that visit the following link: http://174.79.32.155/wiki/index.php/XMLService/XMLSERVICERuby and download the latest XMLService. Extract that download and then, from a terminal session, use the gem install command specifying the location of the downloaded gem. For example, on my Mac I used the following:

> gem install ~/Downloads/xmlservice-1.0.1.devel_GA18/xmlservice-1.0.1.gem

The command gem list should then show xmlservice.

With the xmlservice gem installed, you can create a Ruby file similar to the following to access your data:

require 'xmlservice'
require 'net/http'

ActiveXMLService::Base.establish_connection(
  connection: "http://192.168.168.65:4242/cgi-bin/xmlcgi.pgm",
  database: 'B608E5BA',               # WRKRDBDIRE to find your system's name
  username: 'DENONCOURT',
  password: 'secret',
  install:  'XMLSERVICE',
  ctl:  '*sbmjob',
  ipc:  '/none',
  size: 15000000,
  head: '<?xml version='1.0'?>'
)
query = XMLService::I_DB2.new("SELECT * FROM xmlsvcdb/topics")
query.xmlservice
if query.xmlservice_error
  puts query.dump_all()
end
puts query.dump_inspect
query.xml_output.each do |row|
  all = ""
  row.each do |n,v|
    all << "#{n}=#{v} "
  end
  puts all
end

Note the database option of ActiveXMLService::Base.establish_connection needs to specify and name that is listed in the Relational Database Directory Entries on your IBMi. Use the Work with Relational Database Directory Entries command (WRKRDBDIRE) to list your systems entries (there probably will only be one.)

Retrieved from http://youngiprofessionals.com/wiki/index.php/XMLSERVICE/NonIbmiClientSql
Page last modified on March 03, 2014, at 02:56 PM EST