Re: Retrieve iSeries data in Excel spreadsheet

[Al Mac Wheel <macwheel99@xxxxxxxxxx>]

You can have an SQL/400 statement inside an Excel Cell that is on a PC that 
is connected to the i/400.  I have no problem with a SELECT statement where 
the i/400 is being used to read info & put the latest story into the Excel 
at the time that it is opened, or as a result of someone changing the 
conditions of the SELECT.

Where I have a problem is if someone puts UPDATE in the Cell to have the 
Excel update data in the i/400, without the usual checks & balances on 
veracity of the data going in, and testing any new software, and then there 
are the performance issues associated with a poorly crafted query.  We have 
files with millions of records, where any software using SQL/400 or 
query/400 against those files, now run via JOBQ.  Seems to me this 
architecture could bring the 400 to its knees very rapidly.

This is not a topic that I am an expert in.
I studied it several years ago & backed away because of security concerns.

There are also 3rd party vendor solutions such as Globalsoft Executive 
Dashboard, where you have an Excel-like icon on your desk top that 
interconnects i/400 data with other kinds of computer data in a SEQUEL-like 
report, that is READ ONLY relevant to the original source data.  I am not 
familiar with the performance issues with these products.  My first 
interest was security and ease of use.

Al Macintyre


> I am not an excel data person (rpg programmer by trade), and have been
> asked to see if I can come up with a solution for a problem we have.
>
> I have an excel spreadsheet, that contains 5 columns, I need to use the
> first 4 columns as  keys to an file on the iSeries and fill the 5th column
> with a field from that file.  I would prefer that the fifth column be a
> user defined function if possible.  So in column E, we would code the
> following
>
> =get400data(a1,b1,c1,d1)
>
> and it will retrieve the record from the system and populate column E with
> the field.
>
> I know this has to be possible, I have searched and can not make heads or
> tails of what I find.  Can anyone point me in the right direction?
>
> Mike
>
> Please consider the environment before printing this email. The Donna 
> Karan Company LLC DISCLAIMER:   This e-mail is intended only for the 
> addressee(s) and may contain confidential information.    If you are not 
> the intended recipient, you are hereby notified that any use of this 
> information or dissemination, distribution or copying of this e-mail is 
> strictly prohibited.    If you have received this e-mail in error, please 
> notify the sender immediately by return e-mail and delete the original 
> message.    Thank you.