Tweet
Yeah, I know it can seem pretty pointless to create a CL program that does what a CL command already does. The point is to show some basic working examples of various APIs. They're reasonably simple in ILE CL at V5R4 and later, and they can be effective in RPG or COBOL.
I thought about writing this after the how to receive data in dynamic file field? thread a couple weeks ago. It's still not clear exactly what that thread was after, but it made me wonder how easy it would be to do something like what that thread seemed to want to do.
I chose to write it as a direct replacement of CPYFRMIMPF over a common .CSV streamfile because those are easy for us to create with CPYTOIMPF. For my test, I used the QIWS/QCUSTCDT file to create a .CSV named '/home/mydir/myqcustcdt.csv'. I also used CRTDUPOBJ to create a duplicate PF named 'MYLIB/NEWCUSTCDT'.
As written, this program reads the .CSV and INSERTs each line into MYLIB/NEWCUSTCDT. Because it uses the i 6.1 RUNSQL command, it compiles at i 6.1. By commenting that command out or by modifying it to use some RUNSQL-like utility command, it compiles at V5R4. (You could also use code like the SQL/CL sample thread to do any SQL directly. Better would probably be to do it all in RPG or COBOL and use embedded SQL.)
Note: I use 112-byte source files for ILE CL. I didn't check line lengths in this, but you might to put this into a source file with a matching length, at least temporarily.
In my testing, I edited lines in my .CSV to add some embedded quotes (apostrophes). For example, I had this line:
Both the LSTNAM and the STREET column values in the .CSV now have embedded single-quotes. That lets the program demonstrate how easy it is to double them up. The provided code does it in a few lines that would be even simpler in RPG.
Most of the APIs used are C library functions; some CODE400 members have used all or most of those before.
One useful API that isn't commonly used is the _TESTRPL MI builtin. It's used to replace all double-quotes with single-quotes in each line from the .CSV file. It's quick and pretty painless. Another that's fairly uncommon is the memmove() C library function. It helps make the process of doubling embedded apostrophes easy, though if you're not familiar with it, it might take some time reading its documentation, and maybe watching in debug, to learn how and why it works.
Create a .CSV and change the &fnmi variable value to match. Ensure there is a matching database file to be the target of the RUNSQL INSERT. Compile and run the program.
If it doesn't work, post back here so we can check any bugs that might show up.
Tom
I thought about writing this after the how to receive data in dynamic file field? thread a couple weeks ago. It's still not clear exactly what that thread was after, but it made me wonder how easy it would be to do something like what that thread seemed to want to do.
I chose to write it as a direct replacement of CPYFRMIMPF over a common .CSV streamfile because those are easy for us to create with CPYTOIMPF. For my test, I used the QIWS/QCUSTCDT file to create a .CSV named '/home/mydir/myqcustcdt.csv'. I also used CRTDUPOBJ to create a duplicate PF named 'MYLIB/NEWCUSTCDT'.
As written, this program reads the .CSV and INSERTs each line into MYLIB/NEWCUSTCDT. Because it uses the i 6.1 RUNSQL command, it compiles at i 6.1. By commenting that command out or by modifying it to use some RUNSQL-like utility command, it compiles at V5R4. (You could also use code like the SQL/CL sample thread to do any SQL directly. Better would probably be to do it all in RPG or COBOL and use embedded SQL.)
Note: I use 112-byte source files for ILE CL. I didn't check line lengths in this, but you might to put this into a source file with a matching length, at least temporarily.
Code:
/* +
Sample of CL replacing CPYFRMIMPF. The actual command can be a +
better choice, but sometimes you'll want some custom processing. +
By making minor changes, this process can change for different +
delimiters or even to add, remove or change the sequence of values. +
It could detect and report errors, either in the import file or in +
the INSERT operation. It could simultaneously print or it could +
simply output the constructed INSERT statements. +
Note: +
If you prefer to use open()/read()/close() instead of the +
related '_C_IFS*' C library functions, you can. You'll need to +
determine your own method of reading individual lines rather +
than using the built-in ability of _C_IFS_fgets() to read a +
line-at-a-time rather than reading a supplied number of bytes. +
*/
pgm
dcl &@null *ptr
dcl &fnmi *char 255 value( '/home/mydir/myqcustcdt.csv' )
dcl &fname *char 256
dcl &omode *char 32
dcl &@fdi *ptr
dcl &@ln *ptr
dcl &EOF *lgl value( '0' )
dcl &buf *char 256
dcl &obuf *char 256
dcl &szBuf *int value( 256 )
dcl &szO *int value( 256 )
dcl &@obuf *ptr
dcl &@pi *ptr
dcl &@po *ptr
dcl &limit *int value( 256 )
dcl &lB *int value( 255 )
dcl &QQ *char 1 value( '"' )
dcl &Q *char 1 value( '''' )
dcl &x00 *char 1 value( x'00' )
dcl &X25 *char 1 value( x'25' )
dcl &ZERO *int value( 0 )
dcl &ONE *int value( 1 )
dcl &Chr *int value( 125 ) /* ' */
dcl &@Chr *ptr
dcl &rc *int
dcl &errLocn *char 10 value( ' ' )
/* For SUBR GetErrNo */
dcl &@errno *ptr
dcl &@strerror *ptr
dcl &errno *int stg( *BASED ) basptr( &@errno )
dcl &strerror *char 257 stg( *BASED ) basptr( &@strerror )
dcl &szErrStr *int value( 1 )
dcl &errstr *char 257 value( ' ' )
/* +
We open our streamfile, read each line, format an output buffer to +
match an INSERT VALUES() list and execute an immediate INSERT with +
it. When EOF is reached, we close the file and return. +
The C library functions are actually procedures with names prefixed +
with "_C_IFS_*" as can be seen in the QSYSINC H file IFS member. +
Basic documentation for '_C_IFS_fopen' would be found by looking at +
the C library documentation for fopen(). +
*/
/* Open the import file... */
chgvar &fname ( &fnmi *tcat &x00 )
chgvar &omode ( 'r, crln=y' *cat &x00 )
callprc '_C_IFS_fopen' ( &fname &omode ) rtnval( &@fdi )
if ( &@fdi *eq &@null ) do
chgvar &errLocn 'OPEN'
callsubr GetError
return
enddo
/* Loop through each line and process them... */
dountil ( &EOF )
callprc '_C_IFS_fgets' ( &buf ( &limit *byval ) ( &@fdi *byval ) ) +
rtnval( &@ln )
if ( &@ln *eq &@null ) do
callprc '_C_IFS_feof' ( ( &@fdi *byval ) ) rtnval( &rc )
if ( &rc *eq 0 ) do
chgvar &errLocn 'FGETS'
callsubr GetError
enddo
chgvar &EOF ( '1' )
enddo
else do
callsubr PrcIFSLine
callprc 'strlen' ( &oBuf ) rtnval( &lB )
runsql sql( +
'insert into mylib/newcustcdt values(' *bcat +
%sst( &obuf 1 &lB ) *bcat +
')' +
) +
commit( *NONE )
monmsg ( cpf0000 mch0000 sql0000 )
enddo
enddo
/* Close the import file... */
callprc '_C_IFS_fclose' ( ( &@fdi *byval ) ) rtnval( &rc )
return
/* ----------------------------------------------------------------- */
/* +
Each line has been read into a buffer with a null-terminator. We +
use strlen() to get the string length. We check if the file had +
only LF as the line terminator or it had CRLF. The LFs have been +
replaced with null chars, but any CRs are still there. We replace +
those with nulls and adjust the length. The buffer is copied to an +
output work buffer that has been initialized to all x'00' so that +
we'll have nulls anywhere after the end. We then do our handling of +
embedded apostrophes. Finally, we use the _TESTRPL MI builtin to +
change any double-quotes (&QQ) to single-quotes (&Q). +
*/
/* Process each IFS streamfile line... */
subr PrcIFSLine
callprc 'memset' ( &obuf (&ZERO *byval) (&szBuf *byval) )
callprc 'strlen' ( &buf ) rtnval( &lB )
/* If CR is part of the buffer, set it to \0 and reduce length by 1... */
if ( %sst( &buf &lB 1 ) *eq &x25 ) do
chgvar %sst( &buf &lB 1 ) &x00
chgvar &szBuf ( &lB - 1 ) /* CR */
enddo
else do
chgvar &szBuf &lB /* no CR */
enddo
/* Copy the input buffer to an output work buffer... */
chgvar %sst( &obuf 1 &szBuf ) %sst( &buf 1 &szBuf )
chgvar &@obuf %addr( &obuf )
/* Check for embedded apostrophes... */
callsubr ChkApost
/* Convert double-quotes to apostrophes... */
callprc 'strlen' ( &oBuf ) rtnval( &lB )
callprc '_TESTRPL' ( &oBuf ( &lB *byval ) &QQ &Q ( &ONE *byval ))
rtnsubr rtnval( 0 )
endsubr
/* ----------------------------------------------------------------- */
/* +
memchr() is the useful part in this SUBR. It locates a desired +
character and sets a pointer to it. We use that pointer in the +
memmove() proc to know our copy start position. We use a second +
pointer addressed one byte over as the destination. When memmove() +
finishes, it returns a pointer that we use to start looking for the +
next apostrophe. Each search returns either an address or a null +
pointer. As soon as a null is returned, we know there are no more +
apostrophes in the rest of the area. +
*/
/* Check for apostrophe... */
subr ChkApost
/* Initialize a pointer and a size... */
chgvar &@pi &@oBuf
chgvar &szO &szBuf
/* Priming test for the DOWHILE... */
callprc 'memchr' ( ( &@pi *byval ) ( &Chr *byval ) ( &szO *byval ) ) +
rtnval( &@Chr )
/* For each apostrophe, double it up and test for a next one... */
dowhile ( &@Chr *ne &@null )
callsubr DblApost
callprc 'memchr' ( ( &@pi *byval ) ( &Chr *byval ) ( &szO *byval ) ) +
rtnval( &@Chr )
enddo
rtnsubr rtnval( 0 )
endsubr
/* ----------------------------------------------------------------- */
/* +
memmove() is the useful part in this SUBR. It handles overlaps in +
the source and destination areas in a slick way. In this case: +
xxxxxxx'yyyyyy'zzzzzz becomes... +
ssssssssssssss +
dddddddddddddd +
xxxxxxx''yyyyyy'zzzzzz +
When it's most useful is when src and dest overlap and the copy is +
in the opposite direction, i.e., shift to the left. However, all we +
care is that we pick up the address at the apostrophe and copy it +
all one byte right. Result is a doubled apostrophe. +
*/
/* Double any apostrophes... */
subr DblApost
/* Initialize pointers and sizes... */
chgvar &@po &@Chr
chgvar %ofs( &@po ) ( %ofs( &@po ) + 1 )
chgvar &szO ( %ofs( &@po ) - %ofs( &@oBuf ) )
chgvar &szO ( &szBuf - &szO )
/* Use memmove() to pick up the buffer beginning at the apostrophe and copy */
/* the remainder over one byte to the right, effectively duplicating the */
/* single quote and shifting everything beyond one byte right... */
callprc 'memmove' ( ( &@po *byval ) ( &@Chr *byval ) ( &szO *byval )) +
rtnval( &@pi )
/* The returned pointer is shifted an extra byte to get past where we are. */
/* The next time through the outer loop, we search from the new point... */
chgvar %ofs( &@pi ) ( %ofs( &@pi ) + 1 )
rtnsubr rtnval( 0 )
endsubr
/* ----------------------------------------------------------------- */
/* Get error info... */
subr GetError
callprc '_C_IFS_ferror' ( ( &@fdi *byval ) ) rtnval( &rc )
callsubr GetErrNo
callprc '_C_IFS_clearerr' ( ( &@fdi *byval ) )
sndpgmmsg msg( 'Err <' *cat &errLocn *bcat &errstr *tcat '>' )
rtnsubr rtnval( 0 )
endsubr
/* ----------------------------------------------------------------- */
/* Get error detail info... */
subr GetErrNo
callprc '__errno' rtnval( &@errno )
callprc 'strerror' ( ( &errno *byval ) ) rtnval( &@strerror )
callprc 'strlen' ( &strerror ) rtnval( &szErrStr )
chgvar &errstr %sst( &strerror 1 &szErrStr )
rtnsubr rtnval( 0 )
endsubr
/* ----------------------------------------------------------------- */
endpgm
Code:
389572,"O'Stev's","K L","2 Snow's Pass","Denver","CO",80226,400,1,58.75,1.50
Most of the APIs used are C library functions; some CODE400 members have used all or most of those before.
One useful API that isn't commonly used is the _TESTRPL MI builtin. It's used to replace all double-quotes with single-quotes in each line from the .CSV file. It's quick and pretty painless. Another that's fairly uncommon is the memmove() C library function. It helps make the process of doubling embedded apostrophes easy, though if you're not familiar with it, it might take some time reading its documentation, and maybe watching in debug, to learn how and why it works.
Create a .CSV and change the &fnmi variable value to match. Ensure there is a matching database file to be the target of the RUNSQL INSERT. Compile and run the program.
If it doesn't work, post back here so we can check any bugs that might show up.
Tom