Is it possible to write to a database physical file within a CL program, using data created through a variable e.g. current date value. I wish to write the &MSGDTA variable to a file for future processing.
PGM
DCLF FILE(BGUNDRY/TESTDUMM)
DCL VAR(&ACTYEAR) TYPE(*CHAR) LEN(2)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(9)
DCL VAR(&QDATE) TYPE(*CHAR) LEN(6)
DCL VAR(&DAY) TYPE(*CHAR) LEN(2)
DCL VAR(&MONTH) TYPE(*CHAR) LEN(2)
DCL VAR(&YEAR2003) TYPE(*CHAR) LEN(4) +
VALUE('2003')
DCL VAR(&YEAR2004) TYPE(*CHAR) LEN(4) +
VALUE('2004')
RTVSYSVAL SYSVAL(QDATE) RTNVAR(&QDATE) /* DDMMYY */
CHGVAR VAR(&ACTYEAR) VALUE(%SST(&QDATE 5 2))
IF ((&ACTYEAR) *EQ '03') THEN(GOTO CALC1)
ELSE (GOTO CALC2)
CALC1: CHGVAR VAR(&DAY) VALUE(%SST(&QDATE 1 2))
CHGVAR VAR(&MONTH) VALUE(%SST(&QDATE 3 2))
CHGVAR VAR(&MSGDTA) VALUE('D' *CAT &YEAR2003 *CAT &MONTH *CAT &DAY)
GOTO SNDMSG
CALC2: CHGVAR VAR(&DAY) VALUE(%SST(&QDATE 1 2))
CHGVAR VAR(&MONTH) VALUE(%SST(&QDATE 3 2))
CHGVAR VAR(&MSGDTA) VALUE('D' *CAT &YEAR2004 *CAT &MONTH *CAT &DAY)
SNDMSG: SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&MSGDTA)
ENDPGM
***************** End of data **************************
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I use the following trick when I need data file manipulation from CL.
1. Write a RPG program called EXECSQL that executes dynamic SQL statements.
2. Write a command called EXECSQL to pass a SQL statement to the program.
Now, you can use a CL command like this:
chgvar &insert ('insert into yourfile values(''' || &MSGDTA || ''')'
execsql stmt(&insert)
Full source is at
http://search400.techtarge
ShalomC
to store small pieces of information u can use the data areas. these objects r easy to handle within the range of a short cl program.
to create a data area:
CRTDTAARA DTAARA(YourLib/YourDataAre
to get the stored value:
RTVDTAARA DTAARA(YourLib/YourDataAre
to store a value:
CHGDTAARA DTAARA(YourLib/YourDataAre
note that in this example m using a data area defined as a *char nine bytes long, u can define it almost as long as u want (think about performance) and read specific sections of it.
again, for small pieces of data, within a cl, this is the easiest way.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept shalomc comment as answer
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
ShalomC
EE Cleanup Volunteer
Just saw this thread and thought to add a comment or two...
RPG isn't required for an EXECSQL function; a QM query works fine. Also, in a pinch, CL can call the QLGSORT API -- by supplying the "records" in an input buffer, the CL program can have them written by the API to the sort output file; works perfectly well when you don't have a HLL compiler handy. The QLGSORTIO API can return records if needed.
(The UIM APIs allow you to do screen I/O without using DCLF for a display file. The QSPCRTSP and QSPPUTSP APIs plus user space APIs give spooled print output.)
Tom
ShalomC:
Since that ought to be done anyway even if the RPG program is used, I'm not clear on the point. By using QM to manage those permissions, you avoid giving them to everyone through an uncontrolled RPG program (or complicating the RPG program to include the logic to duplicate what QM does for you).
If such permissions were to be managed by granting authority to the RPG program, the same authority could be granted to the CL that executes the STRQMQRY command.
Note that my comment was only to point out that those of us who cannot afford a HLL compiler can still get the task done. I have no RPG compiler, but CL (and REXX and UIM and most APIs) are available wherever OS/400 runs. Lowest common denominator? Yes, no doubt. But pretty much guaranteed available.
Tom
Shalom:
My version of RUNSQL has always had a REXX CPP simply because all the others I saw used CL or RPG. I don't recommend it due to the overhead, but I thought it illustrated an alternative. I love REXX too.
My _major_ complaint is that it never was given compiler status under OS/400. Too bad... lots of very cool stuff can be done, including embedded SQL and UIM handling of screens (plus all the APIs available to OPM CL). The embedded SQL doesn't even require the SQL Dev Kit!
I really wish more would use REXX/400 and start demanding enhancement. After all this time, there is still neither an ObjectREXX nor a NetREXX available for OS/400. Pity.
Tom
p.s. I've been looking at STRQM profile management because of this thread. At least on the V5R1 system I've been running some tests on, the statement restrictions are _not_ being applied. I.e., I have myself limited to SELECT and DELETE, but can send INSERTs into my QM query with no errors. Yow. Time to verify with IBM what the real deal is.
Hey Tom,
I just found my own version of the REXX generic SQL routine.
It does not support regular SELECT statements, but only action statements.
I had plan to add SQL syntax verification, but never got to it.
Command:
EXECSQL: CMD PROMPT('Execute SQL from CLP ')
PARM KWD(STMT) TYPE(*CHAR) LEN(4000) MIN(1) +
CASE(*MIXED) PROMPT('SQL command')
PARM KWD(COMMIT) TYPE(*CHAR) RSTD(*YES) DFT(*CHG) +
SPCVAL((*CHG UR) (*NONE NC) (*ALL RS) +
(*UR UR) (*NC NC) (*RC RC) (*RR +
RR) (*RS RS) ) PROMPT('Commitment Control')
REXX:
/* By Shalom Carmel, 2001 */
/* This REXX script is the CPP of EXECSQL command */
/* It's purpose is the arbitrary execution of SQL code in a CLP program */
/* */
/* */
signal on error name command_error
signal on failure name command_error
signal on syntax name command_error
parse arg lower 'STMT(' QUOTE_SQL_statement ')'
parse arg 'COMMIT(' CommitControl ')'
select
when CommitControl='*CHG' then Commitment='UR'
when CommitControl='*UR' then Commitment='UR'
when CommitControl='*ALL' then Commitment='RS'
when CommitControl='*RS' then Commitment='RS'
when CommitControl='*RC' then Commitment='RC'
when CommitControl='*RR' then Commitment='RR'
when CommitControl='*RR' then Commitment='RR'
when CommitControl='*NONE' then Commitment='NC'
when CommitControl='*NC' then Commitment='NC'
otherwise Commitment='NC'
end
interpret 'SQL_statement= ' QUOTE_SQL_statement
ADDRESS EXECSQL
'EXECSQL SET TRANSACTION ISOLATION LEVEL ' Commitment
'EXECSQL ' SQL_statement
exit
/*************************
/* command_error : ERROR & FAILURE condition trap */
/*************************
/* The RC variable contains the actual error message from the AS400 system */
/* This routine can be customized for the specific implementation */
command_error:
parse source _system _start _srcmbr _srcfile _srclib
/* Available SQL information:
SQLCODE
SQLERRMC
SQLERRP
SQLERRD.1
SQLERRD.2
SQLERRD.3
SQLERRD.4
SQLERRD.5
SQLERRD.6
SQLSTATE */
errmsg = _srcmbr 'from' _srclib'/'_srcfile 'has failed: SQLCODE ' SQLCODE
ADDRESS COMMAND
'SNDPGMMSG MSGID(cpf9899) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*info) '
'SNDPGMMSG MSGID(cpf9897) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*escape) ' ,
'MSGDTA('''errmsg''')'
exit
A possible toy for playing with SQL syntax checking:
SYNSQL: CMD PROMPT('Syntax check SQL statement')
PARM KWD(SQL) TYPE(*CHAR) LEN(512) MIN(1) +
EXPR(*YES) VARY(*YES) PROMPT('SQL statement')
...and:
pgm ( +
&pSQL +
)
dcl &pSQL *char 514
dcl &SQL *char 256
dcl &RecLen *char 4
dcl &NbrRec *char 4
dcl &Options *char 256
dcl &SQLStmI *char 512
dcl &LenSQLStmI *char 4
dcl &NbrRecPrc *char 4
dcl &Error *char 8
dcl &RecLen_D *dec ( 5 0)
dcl &LenErrI *dec ( 5 0)
dcl &SQLMsgF *char 10
dcl &SQLMsgFLIB *char 10
dcl &LENMsgI *dec ( 5 0)
dcl &MsgID *char 7
dcl &SQLSTATE *char 5
dcl &LenMsgTxt *dec ( 3 0)
dcl &MsgTxt *char 256
dcl &LenRplTxt *dec ( 3 0)
dcl &RplTxt *char 256
chgvar &SQL %sst( &pSQL 3 512 )
chgvar %bin( &RecLen 1 4 ) %bin( &pSQL 1 2 )
chgvar %bin( &NbrRec 1 4 ) ( 1 )
chgvar %bin( &Options 1 4 ) ( 0 )
chgvar %bin( &LenSQLStmI 1 4 ) ( 512 )
call QSQCHKS ( +
&SQL +
&RecLen +
&NbrRec +
'*NONE' +
&Options +
&SQLStmI +
&LenSQLStmI +
&NbrRecPrc +
&Error +
)
chgvar &LenErrI %bin( &LenSQLStmI )
chgvar &SQLMsgF %sst( &SQLStmI 1 10 )
chgvar &SQLMsgFLIB %sst( &SQLStmI 11 10 )
chgvar &LenMsgI %bin( &SQLStmI 25 4 )
chgvar &MsgID %sst( &SQLStmI 53 7 )
chgvar &SQLSTATE %sst( &SQLStmI 60 5 )
if ( &SQLSTATE *ne '00000' ) do
chgvar &LenMsgTxt %bin( &SQLStmI 65 4 )
chgvar &MsgTxt %sst( &SQLStmI 69 &LenMsgTxt )
chgvar &LenRplTxt %bin( &MsgTxt 1 2 )
chgvar &RplTxt %sst( &MsgTxt 3 &LenRplTxt )
sndpgmmsg msgid( &MsgID ) +
msgf( &SQLMsgFLIB/&SQLMsgF ) +
msgdta( %sst( &MsgTxt 1 &LenMsgTxt )) +
msgtype( *INFO )
enddo
return
endpgm
Plenty of enhancements can be made, but maybe it'll help on your way.
Tom
Business Accounts
Answer for Membership
by: murphey2Posted on 2003-04-24 at 09:14:22ID: 8389688
Hi barrygg,
I'm almost sure that ther is no option to write a file directly from CL, I needed this more than once in the past years, and searched for it in manuals and internet, but all solutions that I found is writing a simple 5 line RPG program or download some 400/tool that tells you that it can write a record from CL, but actualy including a rpg or cobol or whatevr program that will do it for you.
I don't know if you are familiair with RPG but a RPG program that wil do it can be simple as this:
*************** Beginning of data **************************
FMYFILE O F 256 DISK A
C *ENTRY PLIST
C PARM MSG 256
C EXCPT
C SETON LR
OMYFILE EADD
O MSG
****************** End of data **************************
you can call it with call PGM(MYPROG) PARM(&MSGDTA)
I realize this is not the most beautiful way, but you only have to define a file "MYFILE" and that's it.
Of cause you can extend it with external described or diferent length etc. but this is the idea!!
Have Fun