Re: FTPAPI
Marco Facchinetti <marco.facchinetti-kthxv0ud/[email protected]>
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <CAMsgu4qiMJGXzKYCqnu=N+tezgFL1GGtY9d0JD4pX5x2E2eL6w@mail.gmail.com> |
Very nice! Thank you. Where can I find information about CLOB_FILE? Best regards -- Marco Facchinetti Mr S.r.l. Tel. 035 962885 Cel. 393 9620498 Skype: facchinettimarco Il giorno mer 17 dic 2025 alle ore 06:22 Birgitta Hauser < [email protected]> ha scritto: > It is even easier if you use a CLOB_FILE: > > DCL-S YourClobFile SQLTYPE(Clob_File); > > Exec SQL DECLARE C1 CURSOR ....; > > YourClobFile_Name = '/home/Dir1/Dir2/YourIFSFile.txt'; //IFS File Name > YourClobFile_NL = %Len(%Trim(YourClobFile_Name)); //IFS File Name > Length (attention _NAME is CHAR(255) i.e. fixed length!) > YourClobFile_FO = SQFOVR; > //File Operation: SQFOVR is an constant that is automatically included > > Exec SQL Open C1; > ... > Exec SQL Fetch Next C1 into :YourClobFile; > ... > YourClobFile_FO = SQFAPP; > //Before the next fetch change the file operation to append SQFAPP > Exec SQL Fetch Next C1 into :YourClobFile; > > A CLOB_FILE variable is converted by the SQL Precompiler into a Data > Structure, containing the following subfields: > VariableName_NAME (IFS File Name) > VariableName_NL (IFS File Name Length) > VariableName_FO (File Operation: SQFCRT - Create New IFS file / SQFOVR - > Create New IFS File and Replace an existing one / SQFAPP - Add text to an > IFS File) > VariableName_DL (Data Length: Output only) > > You only have to populate the Data Structure subfields ... and with the > FETCH into your CLOB_FILE (not Datastructure subfields) the data is written > to the IFS file. > > Mit freundlichen Grüßen / Best regards > > Birgitta Hauser > Modernization – Education – Consulting on IBM i > Database and Software Architect > IBM Champion since 2020 > > "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!" > "Train people well enough so they can leave, treat them well enough so they > don't want to. " (Richard Branson) > "Learning is experience … everything else is only information!" (Albert > Einstein) > > > -----Original Message----- > From: RPG400-L <[email protected]> On Behalf Of Marco > Facchinetti > Sent: Tuesday, 16 December 2025 22:34 > To: RPG programming on IBM i <[email protected]> > Subject: Re: FTPAPI > > Hi Greg, store a timestamp before FTPAPI invocation and when is done store > a > new one. > > Dcl-s NulInd int(5) inz(0) dim(5); > Dcl-s i int(10); > Dcl-s l int(10); > Dcl-s nFileOut varchar(200) inz(''); > Dcl-s LineOut varchar(2000); > Dcl-s TsBegin timestamp; > Dcl-s TsEnd timestamp; > > Dcl-Ds wSql qualified; > Ts timestamp; > Txt varchar(1024); > JobName varchar(28); > End-Ds; > > EXEC SQL SET OPTION COMMIT = *NONE; > > TsEnd = %timestamp(); > TsBegin = TsEnd - %minutes(5); > > exec SQL DECLARE C1 CURSOR FOR > SELECT MESSAGE_TIMESTAMP, MESSAGE_TEXT , QUALIFIED_JOB_NAME > FROM TABLE (QSYS2.JOBLOG_INFO('*')) > WHERE MESSAGE_TEXT IS NOT NULL > AND MESSAGE_TIMESTAMP BETWEEN :TsBegin AND :TsEnd; exec sql Open > C1; for i = 1 to 100000; > exec sql fetch C1 into :wSql:NulInd; > if sqlcod <> 0; > leave; > EndIf; > if nFileOut = ''; > wSql.JobName = %scanrpl('/':'_':wSql.JobName); > nFileOut = '/tmp/' + wSql.JobName + %char(%timestamp()) + '.log'; > EndIf; > LineOut = %char(wSql.Ts) + ' - ' + wSql.Txt; > exec sql CALL QSYS2.IFS_WRITE_UTF8(PATH_NAME => :nFileOut, > LINE => :LineOut, > OVERWRITE => 'APPEND', > END_OF_LINE => 'LF'); > > EndFor; > > exec sql Close C1; > > *inlr = *on; > Return; > > HTH > > > -- > Marco Facchinetti > > Mr S.r.l. > > Tel. 035 962885 > Cel. 393 9620498 > > Skype: facchinettimarco > > > Il giorno mar 16 dic 2025 alle ore 20:07 Greg Wilburn < > [email protected]> ha scritto: > > > And.. I'm not sure how to put the code below in an rpg program with > > "exec sql" > > > > The filename and path would always be variable. > > > > > > I'm not 100% sold on using the joblog - it's way to verbose for what I > > want. I would like to just see the FTP commands and results (i.e. > > like you would see in an interactive session) > > > > -----Original Message----- > > From: RPG400-L <[email protected]> On Behalf Of > > Marco Facchinetti > > Sent: Tuesday, December 16, 2025 12:04 PM > > To: RPG programming on IBM i <[email protected]> > > Subject: Re: FTPAPI > > > > With Sql it's very easy: > > > > BEGIN > > CALL QSYS2.IFS_WRITE( > > PATH_NAME *=>* '/tmp/logsample', > > LINE *=>* '', > > FILE_CCSID *=>* *1200*, > > OVERWRITE *=>* 'REPLACE', > > END_OF_LINE *=>* 'NONE' > > ); > > FOR SELECT (*CHAR*(MESSAGE_TIMESTAMP) || ' - ' || MESSAGE_TEXT) AS > > LOGLINE > > FROM TABLE ( > > QSYS2.JOBLOG_INFO('216955/MYUSER/MYJOB') > > ) > > DO > > CALL QSYS2.IFS_WRITE( > > PATH_NAME *=>* '/tmp/logsample', > > FILE_CCSID *=>* *1200*, > > LINE *=>* LOGLINE, > > OVERWRITE *=>* 'APPEND', > > END_OF_LINE *=>* 'CRLF' > > ); > > END FOR; > > END; > > > > HTH > > -- > > Marco Facchinetti > > > > Mr S.r.l. > > > > Tel. 035 962885 > > Cel. 393 9620498 > > > > Skype: facchinettimarco > > > > > > Il giorno mar 16 dic 2025 alle ore 16:16 Greg Wilburn < > > [email protected]> ha scritto: > > > > > I use Scott's LIBFTP (FTPAPI) occasionally instead of an FTP Script > > > when exchanging data. > > > > > > I would like to have a simple "text" log on the IFS for each FTP > session. > > > With a script, I override OUTPUT to a PF in QTEMP, then using > > > CPYTOIMPF > > to > > > create the text file. > > > (As CYA, I typically have DIR or LS in the script before I get and > > > remove > > > files) > > > > > > With FTPAPI, the logging can go to the joblog by enabling FTP Logging. > > > So, I'm considering using CPYSPLF to send the entire joblog to the > IFS. > > > Just wondering if anyone has done something similar? > > > > > > TIA, > > > Greg > > > [Logo]< > > > > https://protect.checkpoint.com/v2/r01/___https://www.totalbizfulfillment.com > > /___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzMDhkYTQ0Y2Y > > 2OTAzYzY2Mjo3OjFmMDQ6MzdjZWRjMjcxOTU1NmMyYWEyOTJhMjU0NTJlOTBiZTEyNjA1NGFiMTc > yMGVlZDMzOWZjYjU2Y2UzZjY1YzVmYTpwOlQ6Rg> > > Greg Wilburn > > > Director of IT > > > 301.895.3792 ext. 1231 > > > 301.895.3895 direct > > > [email protected]<mailto:gwilburn@totalbizfulfillment > > > .com > > > > > > 1 Corporate Dr > > > Grantsville, MD 21536 > > > > > https://protect.checkpoint.com/v2/r01/___www.totalbizfulfillment.com__ > > _.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzMDhkYTQ0 > > Y2Y2OTAzYzY2Mjo3OjE3MzA6Y2RjMjcwM2Q2NmRlYzFjZjE0MGY1NWQ1ZDM4NDZkZjM2Nj > > VjM2NhN2M0MGI5MmQ2M2RlNTYwZjdiMDA3MWM2MDpwOlQ6Rg > > < > > https://protect.checkpoint.com/v2/r01/___http://www.totalbizfulfillmen > > t.com___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzM > > DhkYTQ0Y2Y2OTAzYzY2Mjo3OmUxODY6NjgyMTU2Nzk0MTBiMzQ2ZDc1MWMwMGMzZGI3MjZ > > iYzUzYWFkOWNlYTgwYzI3ZDQyMzRmMmZhYjE5NTk1YzA0YTpwOlQ6Rg > > > > > > -- > > > This is the RPG programming on IBM i (RPG400-L) mailing list To post > > > a message email: [email protected] To subscribe, > > > unsubscribe, or change list options, > > > visit: > > https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/ma > > ilman/listinfo/rpg400-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE > > 0YmZlMWIxMjFhNTMzMDhkYTQ0Y2Y2OTAzYzY2Mjo3OmY2YmU6NjdlMjI4ZDdkZjhiODI2Y > > mZkYjIyYjkzOTc1Y2ZjMmM0NjUyMWFiOTMyNDU1NjJmMjRlODU1Y2I4MDk5MjE0OTpwOlQ > > 6Rg > > > or email: [email protected] > > > Before posting, please take a moment to review the archives at > > https://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/ > > rpg400-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNT > > MzMDhkYTQ0Y2Y2OTAzYzY2Mjo3OjFiN2I6MTZmNmM2YjUwMmI3NDFjMGIwMDYyZTJkNjJj > > Y2ZhOTAwM2JmZGVjZTQwNTYzZTRiZjViZDU3MmEyYmU3YjJiMTpwOlQ6Rg > > . > > > > > > Please contact [email protected] for any subscription > related > > > questions. > > > > > > > > -- > > This is the RPG programming on IBM i (RPG400-L) mailing list > > To post a message email: [email protected] > > To subscribe, unsubscribe, or change list options, > > visit: > > > > https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/ > > listinfo/rpg400-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjF > > hNTMzMDhkYTQ0Y2Y2OTAzYzY2Mjo3OmRiNmQ6MWY0OWUzZjRkNzVkMjQxNWNmNWExZGYzY2E5Mjh > <https://protect.checkpoint.com/v2/r01/___https://lists.midrange.com/mailman/listinfo/rpg400-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzMDhkYTQ0Y2Y2OTAzYzY2Mjo3OmRiNmQ6MWY0OWUzZjRkNzVkMjQxNWNmNWExZGYzY2E5Mjh> > iMjQxMTIzNjE3ZDIwNmIyMDA3OWRiY2VkYTVhMTgyMDM0ODpwOlQ6Rg > > or email: [email protected] > > Before posting, please take a moment to review the archives > > at > > > > https://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/rpg400 > > -l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzMDhkYTQ0Y2 > > Y2OTAzYzY2Mjo3OmVjNzE6YTk4MWYzYTI1NTllNGVlYjhlODBlMzIxMzZiOWQ0M2Y2Y2Y1ODVmZT > <https://protect.checkpoint.com/v2/r01/___https://archive.midrange.com/rpg400-l___.YzJ1OnRvdGFsYml6ZnVsZmlsbG1lbnQxOmM6bzplYTE0YmZlMWIxMjFhNTMzMDhkYTQ0Y2Y2OTAzYzY2Mjo3OmVjNzE6YTk4MWYzYTI1NTllNGVlYjhlODBlMzIxMzZiOWQ0M2Y2Y2Y1ODVmZT> > ZlMDUxZDdkYmM4MDU4ZTBhYzNiNzFhMTpwOlQ6Rg > > . > > > > Please contact [email protected] for any subscription related > > questions. > > > > > > > > Greg Wilburn > > Director of IT > > 301.895.3792 ext. 1231 > > -- > > This is the RPG programming on IBM i (RPG400-L) mailing list > > To post a message email: [email protected] > > To subscribe, unsubscribe, or change list options, > > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l > > or email: [email protected] > > Before posting, please take a moment to review the archives > > at https://archive.midrange.com/rpg400-l. > > > > Please contact [email protected] for any subscription related > > questions. > > > > > -- > This is the RPG programming on IBM i (RPG400-L) mailing list > To post a message email: [email protected] > To subscribe, unsubscribe, or change list options, > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l > or email: [email protected] > Before posting, please take a moment to review the archives > at https://archive.midrange.com/rpg400-l. > > Please contact [email protected] for any subscription related > questions. > > -- > This is the RPG programming on IBM i (RPG400-L) mailing list > To post a message email: [email protected] > To subscribe, unsubscribe, or change list options, > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l > or email: [email protected] > Before posting, please take a moment to review the archives > at https://archive.midrange.com/rpg400-l. > > Please contact [email protected] for any subscription related > questions. > > -- This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: [email protected] To subscribe, unsubscribe, or change list options, visit: https://lists.midrange.com/mailman/listinfo/rpg400-l or email: [email protected] Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l. Please contact [email protected] for any subscription related questions.