× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I bet you want the %Len(%Trim(<file name variable>))...it may be
including the rest of the field to its defined length.

On Tue, Feb 1, 2011 at 11:43 AM, McGovern, Sean
<Sean.McGovern@xxxxxxxxxxxx> wrote:
I get reason code 4 - The file or directory cannot be found.

I'm using a valid IFS reference, being '/home/EUISPMSM/test.csv'

Although I have used SQL on CLOB/BLOB data types within a table, I didn't know the same technique could be used to manipulate IFS files directly.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: 01 February 2011 16:00
To: Midrange Systems Technical Discussion
Subject: Re: Manipulating a CSV File on the IFS

What's the reason code? What SQL statement is giving the error?

On Tue, Feb 1, 2011 at 10:48 AM, McGovern, Sean
<Sean.McGovern@xxxxxxxxxxxx> wrote:
Birgitta,

I receive SQL0452 when running the following code on our V5R4 and V6R1 box (I've changed the IFS reference).

I'd like to this working. Could you re-visit ?

Thanks,
Sean


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: 01 February 2011 07:35
To: 'Midrange Systems Technical Discussion'
Subject: AW: Manipulating a CSV File on the IFS

Hi,

you may try to define the IFS file as CLOB_File (or DBCLOB_File or BLOB_File
depending on the data type) and access it via embedded SQL.
LOB_File data can be accessed with SQL commands or SQL scalar functions like
any character (host) variable.
Something like this:

D MyFromCLOB      S                   SQLTYPE(CLOB_File)
D MyToCLOB        S                   SQLTYPE(CLOB_File)

D ParToIFSFile    S            256A   Varying
D                                     inz('/home/Hauser/CLOBFILE.txt')
D ParFromIFSFile  S            256A   Varying
D                                     inz('/home/Hauser/CLOBFILE.txt')
D StrPos          S             10I 0
D Length          S             10I 0
 /Free
   //From CLOB File Information
   MyFromCLOB_Name = '/home/Hauser/CLOBFILE.txt';

   MyFromCLOB_NL   = %Len(MyFromCLOB_Name);

   MyFromCLOB_FO   = SQFRD;                   //Read Only



   //To CLOB File Information

   MyToCLOB_Name = '/home/Hauser/CLOBFile.txt';

   MyToCLOB_NL   = %Len(MyToCLOB_Name);

   MyToCLOB_FO   = SQFOVR;                    //Override existing file


   //Search for credit card information for example:
   Exec SQL Set :StrPos = Locate('Creditcard': :MyFromCLOB);

   //Determine the length of creditcard information (or whatever you want
to do)
   Exec SQL Set :Length = ....

   //Manipulate the data (for example)

   Exec SQL Set :MyToCLOB = Substr(:MyFromCLOB, 1, :StrPos-1)
                            Concat
                            Substr(:MyFromCLOB, :StrPos + :Length);

   If SQLCOD < *Zeros;

      Dsply SQLCOD;

   EndIf;

   *INLR = *On;
 /End-Free

SQLFRD (=2) and SQLFOVR (=32) are constant values that will be defined
integrated by the SQL Precompiler.

Not tested, because I've currently no access to an iSeries.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
Gesendet: Monday, 31. January 2011 22:11
An: Midrange Systems Technical Discussion
Betreff: Re: Manipulating a CSV File on the IFS

Scott Klement has an e-book and RGPLE code he has made available that
should get you started.

We used this approach to create several applications using .csv and .txt
files in the IFS
to exchange data with Windows servers, other iSeries on our network, and a
third party
that shipps .csv files to us.

The examples I have do not read/write IFS files directly, (we use
CPYFRMIMPF and CPYTOIMPF)
       but Scott, and others, provide examples to get you started.





"Harvell, Joel" <jbharvell@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/31/2011 01:39 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
Manipulating a CSV File on the IFS






I have a file on the AS400 IFS that has Credit Card information in the
file.  I could download the file to a file on the AS400 clear the
offending fields and put the file back on the ifs, but I would like a
more efficient way of clearing the CC information on the files.  Is
there an API or a way of using SQL to accomplish this??



Joel B. Harvell

Sr. Programmer/Analyst

Delhaize America

(704) 633-8250 x2709


**************************************************************************
This electronic message may contain confidential or privileged information
and is intended for the individual or entity named above.  If you are
not the intended recipient, be aware that any disclosure, copying,
distribution or use of the contents of this information is prohibited.
If you have received this electronic transmission in error, please notify
the sender immediately by using the e-mail address or by telephone
(704-633-8250).
**************************************************************************
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.