Closed kadler closed 6 years ago
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
ActiveRecord is designed badly with reguard to required working with database stored procedures.
I agree.
Everything worked to perfection in this issue.
I agree. It was great to hear your thought process. I learned from it.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Issue closed as customer does not want to participate.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
ActiveRecord is not a requirement. ActiveRecord for program API call feels like a round peg in a square hole. The only reason we should consider ActiveRecord's participation is to capitalize on the connection to the DB.
Ok by me. We have a itoolkit that 'uses' the ActiveRecord 'connection' as transport. Aka, if only ActiveRecord connection is needed, we already have itoolkit for the task (+/- performance, new json, etc.).
ActiveRecord for program API call feels like a round peg in a square hole.
Clarify request (my view real problem), i consider 'square peg' in 'auto-magic' discussion is ActiveRecord missing support for call stored procedures with parameters. ActiveRecord is designed badly with reguard to required working with database stored procedures. Especially a problem for IBM i, RPG has real business value easily unlocked had ActiveRecord understood stored procedures parms (it does not). Bluntly, ActiveRecord bad design for stored procedure usage (no if/but will change my mind).
You ask, we explore, we vote
Everything worked to perfection in this issue.
Anyway, anything db2sock can be done (within reason). This is simply a great case for Open Source db2sock. Nobody wasted significant time. To wit, customer requested 'auto-magic'. i demonstrated 'on paper' a workable design in db2sock exactly as ActiveRecord wants (query/result set). The customer looks at said design 'hack' and says 'no thanks'.
Cool! Everything worked perfectly.
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Specifically, originator of this request wants to auto-magically make ActiveRecord call RPG without EVER leaving comfortable confines of ActiveRecord.
Note the original request stated the following:
I would like to be able to simply - almost auto-magically be able to send input/output parameters to a program on the iseries from a Ruby on Rails application.
ActiveRecord is not a requirement. ActiveRecord for program API call feels like a round peg in a square hole. The only reason we should consider ActiveRecord's participation is to capitalize on the connection to the DB.
Also, forgot to mention, new toolkit allows for compiled calls to RPG programs (see db2sock / toolkit / proc-user).
I didn't realize this was a feature. Very cool.
If we want to align ourselves with what others are doing (successfully) then we need to consider how people are using Swagger.io for an approach to API documentation, composition, and execution. I don't have formal thoughts to convey (yet) concerning Swagger, though I have implemented it on IBM i with Node.js.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Very well. Vote is no. Going once ...
I will leave issue open for a day, so speak now if you want or hold you peace for 'auto-magic' ActiveRecord.
kadler
commented
6 years ago Original comment by Becky Short (Bitbucket: bshort-gannett, GitHub: Unknown).
Tony - Thank you so much for all of your thinking out loud on this topic. After doing a litmus test with management - there doesn't seem to be the appetite I originally thought there would be to pursue this topic any further. Again I appreciate your time, effort and energy into this. I think it could be something good but I do not think there is a commitment all around to help support your efforts from our side of the equation. I hope that we can work on something in the future.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
So, I am not feeling a big warm yes on this idea folks. I suggest the 'hack' will do exactly 'auto-magic" pure ActiveRecord like a big comfy chair. However, you-the-consumer have to commit to wanting this hack before i build.
Do I hear a big yes or a big no???
BTW -- I am no big hurry. We can discuss alternative as long as you want. If you want to start reaping benefits of 'auto-magic' ActiveRecord call RPG, ball is in your court.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
I'm really an IBM newb so will defer on if this is even possible.
Everybody is new. Don't let newb stop you from asking things. Only way you get what you need is to ask.
Witness, we are talking about a hack that is so radical it makes experienced ActiveRecord people like Aaron a bit 'concerned'. Hey! All just code to me. Impossible is a matter of too much work, so i then say no. I really don't mind bending any architecture. Also, I feel questions should have some answer beyond 'i said so' (typical architecture people). Do the easy thing to fit the task at hand (with experience as guide of course).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Migrations are a wonderful thing and are used in scenarios where Rails can fully dictate the end entity. Specifically, Rails knows fully how to create/alter/update/delete a table. Rails doesn't know how to do that with RPG, nor do I believe we want to do that, at least not just for Rails though maybe for all itoolkits (read more in n1).
So, i suspect need for 'alter migration' for a called existing RPG program will occur exactly once. To wit, very likely any ActiveRecord call to existing RPG program/srvpgm will be designed/mapped once in json and will never change (two million years ... IBM i still running ... IBM i rusts out ... people have left the earth for new home ... etc.).
If we were going to be "compiling" definitions to an end format (to get better throughput), I think we'd be better off creating an RPG program on the fly and storing it for future consumption; like how IceBreak(n1) does it.
I don't think so. Again, looks to be a great big violation of 'auto-magic'. We enter a world of compilers, makefiles, tools, ... lions, tigers, bears ... oh my! Not to mention, doing nothing for original problem of ActiveRcord inability to pass parameters to call anything.
Also, forgot to mention, new toolkit allows for compiled calls to RPG programs (see db2sock / toolkit / proc-user). Aka, proc-user allows the user to compile any type of call they want and the routing will simply by-pass the normal toolkit (long dynamic way). This will significantly improve the performance when required. However, do not advise use proc-user much (sparingly), we should focus/work on performance dynamic toolkit formats, json, xml, bson, etc. to help every use ( ... don't throw slow baby toolkit out with bath water before baby toolkit is even born ...).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
It "feels like" the storing of a definition of an RPG interface in DB2 is just one more layer to maintain and keep track of. Specifically, when the RPG interface/parms change then I need to not only change the RPG program but also do a Rails migration and make sure the "magic" going on under the covers didn't screw anything up. In short, I'd rather just change a Ruby class where I store my RPG call definitions. This also makes it easy for source change management.
Well, I think no.
Specifically, originator of this request wants to auto-magically make ActiveRecord call RPG without EVER leaving comfortable confines of ActiveRecord. Essentially, my opinion, adding any additional gems that 'supplement' Rails gem will be violating 'auto-magic'.
More used car selling ...
More to point, using this hack, ActiveRecord developers are doing next to nothing beyond adding a table that represents a RPG call. In fact, nothing could be easier than simply letting Rails do everything 'table' thinking including finding the column names (parameter names), column type (parameter type), etc. More, all Rails operations are exactly in the ActiveRecord play book (N)ew (initial describe record), (U)pdate (call RPG), (find) (get parm results)., etc.
kadler
commented
6 years ago kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
I typed the above before seeing your newest responses this morning. Does your new approach require DB2 LUW on the laptop?
Yes.
I am not following how it communicates with DB2 on IBM i.
However, idea with simulated IBM i RPG response records (stored in DB2 LUW), is NO communication with IBM i. That is, IBM i is nowhere on reachable network (you are in a plane 30,000 ft), and just doing ActiveRecord work with local DB2 LUW.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
I have one question I guess I'm not clear on. How would this be significantly better than extending ibm_db to support AREL and making an exec_query call similar to how the SQLSERVER adapter does it?
I do not use AREL. However, quick look, this appears to address building 'more complex' queries. Quick look, this does not address passing parameters to stored procedures. Definitely not IN/OUT parameters. Last, has no ability to intercept a 'query' and make direct RPG toolkit call (not stored procedure at all). I am happy to be wrong. In fact, if i have misread the 'quick look' AREL, please demostrate how you would call a RPG program without using a stored procedure (aka, no toolkit).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
So i don't forget ... slightly off topic, but concern original poster. Toolkit calling existing stored procedures (in this hack context)
Background ... Basically, hard working IBM i folks already made time/effort to create stored procedure wrappers around RPG programs. Essentially, context of hack, means customer did most/part work for a toolkit call to RPG dynamically. However, 'passing parameters' to fancy customer stored procedure remains a mismatch of calling any stored procedure in ActiveRecord (doesn't work).
Toolkit ... already has built-in json based db2 driver. Only trick is 'describe' record will not be json 'pgm" template call, but instead a 'db2' template call.
Example:
#!bash
hi = HelloDsToolCall.new(
parm'',
retn:'',
tooltype:'json'
tooltemplate:'
{"query":[{"stmt":"call BOBLIB/MYSP(?)"},
{"parm":[{"value":"Jones"}]},
{"fetch":[{"rec":"all"}]}]}
]}
"
)Note: Work for toolkit json db2 built-in db2 driver is not complete, aka, no json tests in db2sock/tests/json to cut/paste yet. Yes, I know exactly how to do this, so not to worry (things take time).
kadler
commented
6 years ago kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
So, perfectly clear, row/col<>parm 'hack' ONLY works on IBM i using db2sock locally (libdb400.a). . . . Aaron, I am especially interested in your thoughts about no toolkit idea.
If we sunset ruby-itoolkit then we need to make sure its replacement has the foundational features that people use, namely the ability to call RPG from Ruby in a 2-tier situation (over HTTP). Said another way, we can't go backwards and lose important features.
Concerning storing call interfaces in DB2 tables, I am going to think out loud for a moment... Migrations are a wonderful thing and are used in scenarios where Rails can fully dictate the end entity. Specifically, Rails knows fully how to create/alter/update/delete a table. Rails doesn't know how to do that with RPG, nor do I believe we want to do that, at least not just for Rails though maybe for all itoolkits (read more in n1).
It "feels like" the storing of a definition of an RPG interface in DB2 is just one more layer to maintain and keep track of. Specifically, when the RPG interface/parms change then I need to not only change the RPG program but also do a Rails migration and make sure the "magic" going on under the covers didn't screw anything up. In short, I'd rather just change a Ruby class where I store my RPG call definitions. This also makes it easy for source change management.
If we were going to be "compiling" definitions to an end format (to get better throughput), I think we'd be better off creating an RPG program on the fly and storing it for future consumption; like how IceBreak(n1) does it.
n1: I only have a high-level understanding of how this works in IceBreak. Imagine a situation similar to how a make build in RPG works, where it only compiles members that have changed. What if the same was done for the various IBM i toolkits so they didn't need to go through DB2 to make the call to RPG?
Occurred to me your laptop (laptop db2), could simply add a few 'expected return' results in laptop local migrate database (hellods_toolcalls).
I typed the above before seeing your newest responses this morning. Does your new approach require DB2 LUW on the laptop? I am not following how it communicates with DB2 on IBM i.
kadler
commented
6 years ago Original comment by Becky Short (Bitbucket: bshort-gannett, GitHub: Unknown).
I think you missed your calling as a stand up comedian. That being said I am waiting for my Ruby on Rails expert to weigh in here. He was out on Friday but will be back today - but works out of PHX so they are 2 or 3 hours behind us. Have patience grasshopper. Your answer will come.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Question remains. Will you use this completely radical new support if i build it??? Your turn to speak. Hey, is this microphone on??? ... testing, testing???
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Personally, I think developing on a laptop is beyond crazy ... no RPG ... no LIBL ... yucky. However, I do my best to help people work through remote development on laptop.
Mmm ... maybe spoke too soon ...
This IBM i only hack may actually make development on a laptop much easier. Basically, on IBM i, ActiveRecord toolkit RPG calls will appears as simple select/result set (hack on IBM i libdb400.a is fully Rails ActiveRecord compliant). Occurred to me your laptop (laptop db2), could simply add a few 'expected return' results in laptop local migrate database (hellods_toolcalls). Thereby, your laptop version of Rails application could 'update/find' simulated responses from your RPG program call.
On laptop, fool your Rails ActiveRecord application example ...
#!bash
> rails generate migration CreateHelloDs parm:text retn:text tooltype:text tooltemplate
> rake db:migrate ... same as on IBM i, the table is create
(N)ew ... hi = HelloDsToolCall.new(parms)
... same describe 1st record, but not used ( o hack on laptop).
(N)ew ... hi = HelloDsToolCall.new(simulated RPG response parms 2)
... only laptop, add records simulate RPG toolkit call response 2
(N)ew ... hi = HelloDsToolCall.new(simulated RPG response parms 3)
... only laptop, add records simulate RPG toolkit call response 3
> (U)date - HelloDsToolCall.update(2, :parm => '{"ds":...}'
... on laptop, change records for 'simulated' calls (2).
(F)ind - hi = HelloDsToolCall.find(1)
...both IBM i/laptop 'describe' toolkit empty record (1).
...Not used on the laptop.
(F)ind - hi = HelloDsToolCall.find(2)
... laptop simulated response 2 RPG call
(F)ind - hi = HelloDsToolCall.find(3)
... laptop simulated response 3 RPG call
Note: On IBM i find(2-n) will simply return last update call to the real IBM i RPG program.
Well, how about that? A happy accident. I don't even develop on laptop and this IBM i hack will essentially make simulating Rails ActiveRecord RPG calls on laptop trivial (dead easy).
... from laptop development to crazy easy laptop development
Future (maybe) ... as mentioned in another issues (ODBC issue), thought about introducing a MySql federation layer over IBM i libdb400.a. Again, not perfect match, but may be able to develop using MySql most things on laptop and simply run on federation layer on IBM i.
BTW -- Yeah, I know, ain't your fathers IBM i project. Careful what you ask for, with db2sock, you may just get it.
kadler
commented
6 years ago Original comment by Becky Short (Bitbucket: bshort-gannett, GitHub: Unknown).
Our lead Ruby on Rails developer - Jerry - is out today. I'm sure he will be able to answer your questions when he gets back on Monday.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
radical plus plus
A little more on technical explication.
(C)reate ... To be clear, 'rake migrate' will actually create a table in current library. The 'trick' is to map your RPG parameters as closely to actual as possible for the ActiveRecords supported type ( ibm_db-2.5.15 types above). In case of complex aggregates, RPG dcl-ds structures, db2sock will choose a clob json rendering of the in/out data (example hellods above). Otherwise, int is :int, packed is :decimal, so on, matching rails (forgive doing from Rails memory).
#!bash
===
create -- create db2 table named "hellods_toolcalls" (current library)
===
> rails generate migration CreateHelloDs parm:text retn:text tooltype:text tooltemplate
class CreateHelloDsToolCall < ActiveRecord::Migration
def change
create_table :hellods_toolcalls do |t|
t.text :parm, :null => false
t.text :retn, :null => false
t.string :toolformat, :null => false
t.text :tooltemplate, :null => false
end
end
end
> rake db:migrate (N)ew ... hi = HelloDsToolCall.new(parms) is the only record in the entire file. Contains column for toolformat='json' (xml, csv, etc.), and, column tooltemplate='{pgm:...}' that describes the parameters and matching names to the columns (see example above)
(U)date - HelloDsToolCall.update(1, :parm => '{"ds":...}' is intercepted by libdb400.a to call the program (described first recod tooltemplate). NO record is changes in the actual datbase file (hellods_toolcalls).
(F)ind - hi = HelloDsToolCall.find(1) retrieves data from last (U)pdate. Technically, call program result is cached as long as statement/cursor is open by db2sock (libdb400.a).
BTW -- Future ... For audit purpose (logging), we could choose new option to record data of any (U)pdate call. Perhaps even dump result into another clob. (For you Big Brother types with auditors watching).
Ok, well, i gave all i can think of at this point. Please think through 'to use' or 'not to use'. Post you ideas, comments, etc. I don't want to write all 'hack' code if folks do not want a new undiscovered country (hack d2sock libdb400.a).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Aaron, I am especially interested in your thoughts about no toolkit idea.
This db2sock intercept at low level driver (libdb400.a) really for all those abstraction APIs like ActiveRecord. The idea to completely remove 'all knowledge' about a native call from said lagauge abstraction. To wit, ActiveRecord thinks CRUD, but db2sock (libdb400.a) routes insert, update, select, (delete?), request to low level 'toolkit' mapping (cmd, shell, srvpgm, etc.).
More ... We worked on Ruby/Rails itoolkit together. Works ok. Even cool syntax with DSL stuff (you guys wanted). Made a few errors itoolkit by omission (ie, no multi-request). However, good, itoolkit runs over any protocol (db2, rest, etc. via xmlservice). This same itoolkit could also run all same protocols using json in db2sock (supports db2 local/remote, rest nginx/apache fastcgi, etc.).
Mmmm ... i am just not 100% on the idea yet ... your input appreciated ... (taking weekend off starting afternoon).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
crazy idea makes it possible to interface in/out parameters from Rails to ibm i with any HLL .... count me in!
Cool! So, want to hear from a few others before i write the rest of the code in db2sock. A radical departure from two gems ibm_db2 and itoolkit. I suspect itoolkit almost goes away completely, but you need a json parser for complex parameters (dcl-ds hello_ds_t).
There is a hitch ... only works on IBM i
So, perfectly clear, row/col<>parm 'hack' ONLY works on IBM i using db2sock locally (libdb400.a). To wit, litmis/db2sock is Open Source IBM i driver. Aka, we can do mostly anything with db2sock, including expanding CLI/ODBC architecture to task at hand like ActiveRecord calling stored procedures (pirates we may be).
Anyway ... to be clear ... my 'radical' change to IBM i db2sock does not mean all of IBM will change every DB2 driver on other platforms. Basically, no way for LUW remote to 'intercept' the hack required CLI/ODBC APIs (SQLPrepare/SQLExecute, SQLExecDirect, SQLFetch, etc.),
Understand clearly, this will only work on IBM i via db2sock locally. Bluntly, for you laptop development people, you will not be able to develop Rails applications on your laptop, then 'deploy' to IBM i (see author note).
Author note
I always use a chroot on IBM i for development. See litmis/ibmichroot project. Personally, I think developing on a laptop is beyond crazy ... no RPG ... no LIBL ... yucky. However, I do my best to help people work through remote development on laptop. Witness ODBC issue to see db2sock bend over backward to try to help remote LUW. Again, personally, i think development for IBM i on laptop is just nuts. However, I do use my laptop editor (gedit), and scp to transfer files to my IBM i chroot for run (my hypocrisy knows no bounds).
kadler
commented
6 years ago Original comment by Becky Short (Bitbucket: bshort-gannett, GitHub: Unknown).
I will have to let my Ruby on Rails counterpart comment on most of this. But if your crazy idea makes it possible to interface in/out parameters from Rails to ibm i with any HLL .... count me in! If whatever you can do to simplify this interaction would be most helpful to us ... but probably lots and lots of others who want to access back end databases with front end applications not native to ibm i!
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
I must ask, following idea too radical for anyone to use??? If db2sock builds it will you come (use it)???
Today hack previous posts (coding right now)
Today, I am only playing around with a hacked 'select'. The input and output are full json documents (see previous post). This will work, but from 'pretty' ActiveRecord point of view we end up with one implicit accessor pileojson.
class HelloDsToolCall < ActiveRecord::Base
end
# implicit single accessor for 'json' result set (single row, single column)
hi.implicitjson = '{big pile of json returned from call}'
I have not finished code, because following 'radical idea' has occurred to me (last night).
Mmmm ... crazy? ... world without toolkits, ActiveRecord, ZF2 (php), etc. Bum, bum, bum, bum, bum (Also sprach Zarathustra, Op. 30 - Strauss).
We 'could' invent a world where IBM i RPG/anything is called directly from ActiveRecord (far beyond 'json', although used). Use dbs2sock (libdb400.a) as an artful 'hack' to make normal CLI/ODBC processing to map row/column to called program parm(s) and return data. Mmmm ... allow normal ActiveRecord processing of migrate/create table into a library to set the 'template' matching RPG parameters.The only requirement is toolcall (or TOOLCALL, ToolCall) must appear in create database name, so db2sock (libdb400.a) will know operators like insert, select, update are for calling RPG/anything.
Forgive any syntax errors ... example below is a bit complex using ds structures (ie. text == clob == ds structure json representation), but it looks promising. I suspect ActiveRecord db2sock 'hack' works much better for rails model accessors when RPG program 'sane' simple/primary types smallint, int, decimal, numeric, decfloat, float, double, real, timestamp, datetime, time, date, etc. Anyway, Hal/me (2010), says following seems possible for any scripting language abstraction depends on a column name mapping (rails column name <-> accessor name).
#!bash
===
RPG
===
dcl-ds hello_ds_t qualified based(Template);
hello char(128);
end-ds;
dcl-pr helloDS likeds(hello_ds_t);
hello likeds(hello_ds_t);
end-pr;
===
create -- create db2 table named "hellods_toolcalls" (current library)
===
> rails generate migration CreateHelloDs parm:text retn:text tooltype:text tooltemplate
class CreateHelloDsToolCall < ActiveRecord::Migration
def change
create_table :hellods_toolcalls do |t|
t.text :parm, :null => false
t.text :retn, :null => false
t.string :toolformat, :null => false
t.text :tooltemplate, :null => false
end
end
end
> rake db:migrate
# rails to sql pseudo (bad memory --- forgive)
# create hellods_toolcalls (parm clob, retn clob, toolformat varchar(255), tooltemplate clob)
- parm is a clob ... 1st parameter mapping ds structure (length of 1 megabyte is assumed)
- retn is a clob ... return json mapping ds structure
- toolfromat is a varchar ... "json" format description (or "xml", "csv", etc.)
- tooltemplate is a clob ... holds description
===
add program description/template record (one time only)
===
hi = HelloDsToolCall.new(
parm'',
retn:'',
tooltype:'json'
tooltemplate:'
{"pgm":[{"name":"HELLOSRV", "lib":"DB2JSON", "func":"HELLODS"},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm"}]},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}
]}
"
)
# rails to sql pseudo (bad memory --- forgive)
# insert into hellods_toolcalls (parm, retn, tooltype, tooltemplate)
# ('','','json','{"pgm": ... }')
===
update -- program call
===
HelloDsToolCall.update(1, :parm => '{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}}')
# rails to sql pseudo (bad memory --- forgive)
# update hellods_toolcalls set parm='{"ds":...}'
===
select -- retrieve program call data
===
hi = HelloDsToolCall.find(1)
# rails to sql pseudo (bad memory --- forgive)
# SELECT * FROM hellods_toolcalls WHERE ( id = 10) LIMIT 1
# You should be able to simply access the data (rails accessor methods)
hi.parm # json for input hello_ds_t
hi.retn # return json for hello_ds_t
Types from ibm_db-2.5.15
case field_type
# if +field_type+ contains 'for bit data' handle it as a binary
when /for bit data/i
:binary
when /smallint/i
:boolean
when /int|serial/i
:integer
when /decimal|numeric|decfloat/i
:decimal
when /float|double|real/i
:float
when /timestamp|datetime/i
:timestamp
when /time/i
:time
when /date/i
:date
when /vargraphic/i
:vargraphic
when /graphic/i
:graphic
when /clob|text/i
:text
when /xml/i
:xml
when /blob|binary/i
:binary
when /char/i
:string
when /boolean/i
:boolean
when /rowid/i # rowid is a supported datatype on z/OS and i/5
:rowid
endOriginal comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Aka, json for CMD chglibl, then PGM cmypgm all in one call.
Agreed. I and my customers make use of that on a regular basis (in Node.js).
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
I would leverage Ruby's DSL capabilities to produce something elegant
Cool! We should remember to allow multiple 'actions' in one call like other toolkits (python, node). Aka, json for CMD chglibl, then PGM cmypgm all in one call. Thanks for input.
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Aaron, you ok with this idea???
Yes, thumbs up from me.
I am mostly interested in seeing db2sock succeed (very excited, actually).
The syntax is semantics that can be determined later; specifically, I would leverage Ruby's DSL capabilities to produce something elegant (see below). Under the covers we can massage it to work with ActiveRecord. I am hoping we can do the same with the Node.js iToolkit (though Javascript doesn't have pretty DSL capabilities) and Python.
itoolkit.call do
lib :MYLIB
pgm :PGM01
error :fast
parms do
char :inchara, 1
char :incharb, 1
dec :indec1, 7, 4
dec :indec2, 12, 2
struct :inds1, 1 do
char :dschara, 1
char :dscharb, 1
dec :dsdec1, 7, 4
dec :dsdec2, 12, 2
end
end
endOriginal comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Mmm ... maybe easier. One parameter of json input, one clob result set clob out (also json). Therein, ActiveRecord class name be anything contain keywords 'toolkitcall' (any case).
#!bash
===
RPG SRVPGM
===
dcl-ds hello_ds_t qualified based(Template);
hello char(128);
end-ds;
dcl-pr helloDS likeds(hello_ds_t);
hello likeds(hello_ds_t);
end-pr;
===
ActiveRecord (whatever language)
===
BigBobIBMiToolkitCall.where(
{
p1='{"pgm":[{"name":"HELLOSRV", "lib":"DB2JSON", "func":"HELLODS"},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm"}]},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}
]}'
)
# select * from BigBobIBMiToolkitCalls
# where p1='{"pgm":[{"name":"HELLOSRV", "lib":"DB2JSON", "func":"HELLODS"},
# {"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm"}]},
# {"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}'
===
run (tests today)
===
Should result something like this for toolkit in db2sock.
> test1000_sql400json32 ../json/j0110_srvpgm_hello_ds
input(5000000):
{"pgm":[{"name":"HELLOSRV", "lib":"DB2JSON", "func":"HELLODS"},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm"}]},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}
]}
output(127):
{"script":[{"pgm":["HELLOSRV","DB2JSON","HELLODS",
{"parm":[{"char":"Hello World"}]},
{"retn":[{"char":"Hello World Again"}]}]}]}
result:
success (0)Mmm ... think i will sleep on it before writing the code.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Aaron, you ok with this idea??? I kinda wanted a 'real user' of ActiveRecotrd to thumbs up this idea ... but ... i am having trouble waiting to write the code.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
politics ...
Oh yeah, for those admin types that do not know me. I work with Jesse and Kevin on IBM i Open Source at Rochester lab. We plan release db2sock with other Open Source (when complete). Until release, you will be able to find my test pre-compiled binaries posted on Yips.
Note: Geeks like me use PASE chroot, so we never muck up the root machine. See Aaron if you want to learn how to chroot on IBM i. Be careful if you install this libdb400.a in your root machine. The db2sock README.md is explicit about saving old libdb400.a (do not miss the step).
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Maybe I can talk one of you guys into testing Rails for me (lost my IBM i with PowerRuby)???
You can sign up for a free spaces.litmis.com account. See signup instructions here (select the PowerRuby environment instead of the Node.js one).
If you prefer not to do that then I can also test for you.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Maybe, I will just write db2sock code, let you play around with Rails.
Example:
#!bash
===
RPG SRVPGM
===
dcl-ds hello_ds_t qualified based(Template);
hello char(128);
end-ds;
dcl-pr helloDS likeds(hello_ds_t);
hello likeds(hello_ds_t);
end-pr;
===
ActiveRecord (whatever language)
===
Hellosrv_helloDs_toolkit_srvpgm.where(
{
p1='{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi"}},{"name":"parm"}]}',
p2='{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}',
lib='DB2JSON'
}
)
# select * from Hellosrv_helloDs_toolkit_srvpgms
# where p1='{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi"}},
# {"name":"parm"}]}'
# and
# where p2='{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},
# {"name":"retn","by":"return"}]}'
# and
# where lib='DB2JSON'
I think use keywords like lib='MYLIB' for a few non-parameter things.
===
run (tests today)
===
Should result something like this for toolkit in db2sock.
> test1000_sql400json32 ../json/j0110_srvpgm_hello_ds
input(5000000):
{"pgm":[{"name":"HELLOSRV", "lib":"DB2JSON", "func":"HELLODS"},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm"}]},
{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi back"}},{"name":"retn","by":"return"}]}
]}
output(127):
{"script":[{"pgm":["HELLOSRV","DB2JSON","HELLODS",
{"parm":[{"char":"Hello World"}]},
{"retn":[{"char":"Hello World Again"}]}]}]}
result:
success (0)
I am assuming ActiveRecord will 'eat ' weird class name (hope), Hellosrv_helloDs_toolkit_srvpgm. If not, we may need some sort of CamelCase junk to make Rails shut up.
Anyway ...
I will use php, but really should not matter what language (or framework, cough Rails). Maybe I can talk one of you guys into testing Rails for me (lost my IBM i with PowerRuby)???
Unless i hear protest, probably be running in a day or so ... i will post back here.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
BTW -- I modified previous post multiple times. I am designing on fly here. This looks promising to keep ActiverRecord happily fooled into calling IBM i code on the machine and returning json.
Better, json work already almost complete (libtkit400.a). Please feel free check out source/tests/json and see all wild and complex 'ds' array calls we can do right now.
beyond crazy to just plain nuts
We have ideas to add additional 'toolkit' interfaces beyond json for 'big data'. These may actually be something binary, perhaps bson. Such, may also boast performance, but, well, i never promise until i see it actually run.
Anyway to close Rails thought, if we did return bson (future, no promise), seems same day different data for ActiveRecord, aka, a 'blob' (possible bson), instead of a 'clob' (json above) returned.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
To avoid confusion, this post conversation is something potentially new in db2sock, aka, not current itoolkit (Aaron mentions "RPG and RPG; Talking At Last").
more details (devil in the details) ...
Again, this is NOT the current itoolkit. This is only possibility in new db2sock project.
connect (all that) ...
So, basic 101 any language 'select stmt' driver flow. All 'connect stuff' happens before actual prepare/execute (or exec). The db2 extension driver can allocate connection/statement per normal calling DB2.
prepare (or exec) ...
However in db2sock (libdb400.a), we can pre-parse any prepare statement looking for '_toolkit_suffix', thereby 'mark' the DB2 statement as 'toolkit use only' while pasring the name of pgm (and/or function, etc.). Next, when normal flow of processing prepare/execute (or exec) occurs, db2sock simply routes CLI API request to toolkit module (libtkit400.a).
bind parameter ...
That is, when we 'bind parameter' on hstmt, we will be adding a parameter to the toolkit call. Simple enough, but we run into our first problem. DB2, database understands table column description/data type via 'create table' .
#!bash
create table(bob integer, sally varchar(32) )
select bob, sally from table
bob is integer
sally is varchar(32)In our 'toolkit fooling' case we must find a way to pass type of parameter along for parameter data. If we get too fancy describing the data type, ActiveRecord may pre-parse reject our 'hacking' before db2sock gets a chance to help out. So, my 'best guess' at 'fooling select' across any abstraction like ActiveRecord, is to stay very close to normal query select.
#!bash
select *
from mypgm_toolkit_pgm
where parm1='{"s":{"name":"parm1", "type":"128a", "value":"Hi there"}'
and where parm2='{"s":{"name":"parm2", "type":"128a", "value":"Hi again"}'
and where parm3='{"ds":[{"s":{"name":"char", "type":"128a", "value":"Hi there"}},{"name":"parm3"}]}'If we use 'json' to describe actual data along with value, we have all the needed data to make the call to the target program (mypgm this case).
bind parameter ...
Above example is literal parameters (json). This would also work for parameter markers ('?'), during the bind parameter CLI call.
execute
This 'json' is so close to the currently running 'json toolkit', that we may be able to simply call new toolkit API SQL400Json(clob). Bingo, already works.
fetch ...
At fetch time, we need only push data back as single row/column 'json'
#!bash
{
"script": [{
"pgm": ["RAINBOW", "DB2JSON", {
"aint8": 2
}, {
"aint16": 3
}, {
"aint32": 4
}, {
"aint64": 5
}, {
"auint8": 6
}, {
"auint16": 7
}, {
"auint32": 8
}, {
"auint64": 9
}, {
"afloat": 6.66
}, {
"adouble": 7.777
}, {
"apacked": 8.88
}, {
"azoned": 9.99
}, {
"achar": "A+1"
}, {
"avarchar2": "B+1"
}, {
"avarchar4": "C+1"
}, {
"abin4": 5
}, {
"abin9": 3
}, {
"ahex1": "0002"
}, {
"ahex2": "0003"
}, {
"ahex3": "0004"
}, {
"ahex4": "0005"
}, {
"ahex5": "00000006"
}, {
"ahex6": "00000007"
}, {
"ahex7": "00000008"
}, {
"ahex8": "00000009"
}, {
"ahex9": "0000000A"
}, {
"abuf12": "F0F1F2F3F4F5F6F7F8F9F4F2"
}, {
"adateusa": "01/07/2014"
}]
}]
}Bingo. Easy as pie ...
Mmmm ... why does this sound so easy to me ??? There must be some horrible chunk i am missing. Anyone see the Achilles heel???
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Anyway, a Rails compatible itoolkit exists today. However, itoolkit support is also 'bolt on' to ActiveRecord (my opinion).
I like the idea of using the raw/direct ibm_db object to invoke db2sock and obtain back a json object.
How to I ensure the toolkit exist on our ibm i server right now?
If you installed PowerRuby you should already have it. See this slide of my "RPG and RPG; Talking At Last" presentation.
kadler
commented
6 years ago Original comment by Becky Short (Bitbucket: bshort-gannett, GitHub: Unknown).
Sorry I thought I was signed in when I created the issue. I'm logged in now to create this comment. I am going to forward this discussion thread on to other who will be able to ask better Ruby on Rails questions to keep us moving forward. So you will likely start seeing comments from some new names. How to I ensure the toolkit exist on our ibm i server right now?
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Ruby iToolkit repo ...
I answered same time as Aaron. Yes, we do like when you have a profile name so that you can be addressed by other than Mr/Ms Anon Y Mous.
Anyway, a Rails compatible itoolkit exists today. However, itoolkit support is also 'bolt on' to ActiveRecord (my opinion). Therefore, I am seriously considering db2sock obfuscated statement/cursor support using _toolkit_suffix, to fool ActiveReord into thinking any IBM i call is just another 'query' with a returning result-set (fetch).
Yep, I may be crazy Aaron, but at the moment it seems possible in db2sock.
kadler
commented
6 years ago Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).
Ruby on Rails application ... almost auto-magically be able to send input/output
Cool. Perhaps possible to add Rails API thinking into db2sock APIs to mask complexity
Background - Rails ActiveRecord not suitable for calling stored procedures
Ruby vs. Rails.
Q1: Can Ruby call IBM i stored procedures?
A1: Yes, using ibm_db. Simple question. Simple answer. Simple Ruby code.
Q2: Can Rails call IBM i stored procedures?
A2: No. Rails ActiveRecord wants SQL style select/fetch (result-set). Some 'hacking' data mapper stored proc ideas Rails community, but, my opinion, not exactly elegant ActiveRecord.
Possible work around IBM i DB2 ... using cleaver DB2 technique, you can remain within beautiful Rails (select/result set). In a nutshell, use DB2 UDTFs/views to modify current evil API stored procedures (RPG programs), into Rails friendly beautiful SELECT and result sets. Here is the great Birgitta Hauser article on 'how to' UDTFs (with beautiful view).
db2sock ... almost auto-magically be able to send input/output parameters to a program on the iseries from a Ruby on Rails application.
Ok, let's not be bound by conventional CLI/ODBC architecture thinking. Open Source db2sock wants creative API solutions to fit job at hand, so let's talk ...
Currently, db2sock has basics for 'toolkit' in 'json' format. The API is smple SQL400Json(json clob). The toolkit clob support could be expanded to 'another' format. Rails case we need something like 'auto-magically' transforming a select request into toolkit call w/parms, then push output into result set for collection by AciveRecord. Basically, ActiveRecord (aka, orb behind the curtain), can not tell difference between a regular query select/fetch and a toolkit select/fetch.
Mmmm ... thinking out loud ... simple matter of programming ...
Yes, perhaps limit toolkit calls specific to 'select' to subset of ActiveRecord and the resulting SQL. Something like adding a keyword forming ActiveRecord resulting SQL select statement, such as '_toolkit_suffix" (below). This would notify db2sock '_toolkit_suffix' statement/cursor was special 'toolkit' and any additional CLI/ODBC API using handle would route to 'toolkit' for SQLDescribeCol(hstmt), SQLFetch(hstmt), so on.
#!bash
'select rpgpgm_toolkit_pgm(?,?,?,?)' - PGM call
'select rpgsrvpgm_myfunc_toolkit_srvpgm(?,?,?,?)' - SRVPGM call
'select rpgsrvpgm_myfunc_toolkit_cmd(?)' - CMD call
... mmm ... seems completely possible to do in db2sock, and, Rails ActiveRecord would be obliviously happy (Wizard Of OZ --- don't look behind the db2sock curtain).
Ok, i will think about it.
kadler
commented
6 years ago Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).
Are you aware of the Ruby iToolkit repo? It facilitates Ruby calling RPG.
Also, if possible please log in and post under your profile.
Original report by Anonymous.
Hi Tony,
I would like to be able to simply - almost auto-magically be able to send input/output parameters to a program on the iseries from a Ruby on Rails application.