Embedded SQL in a C program on IBM i.

Quick post about my experiences and hopefully a guide to others who are starting to look at the possibilities of embedding SQL in C Programs.

First note: Not a lot of samples or good tutorials out there.
Second note: Remember to use the CRTSQLCI command and not the CRTCMOD etc commands
Third note: CRTSQLCI creates a module, so then need to run the CRTPGM/CRTSRVPGM commands to create run-able object.
Forth note: Remember to add FOR UPDATE to the select statement if you need a cursor to allow updates/deletes.

So the documentation provided by IBM is very extensive, but it is a tad difficult to find what you need from it. I spent a whole day just getting this very simple program to work. The problem arose because of a need from a client that has an huge IFS tree structure which had to be audited and repaired, they have over 2.6 million objects (*DIR/*STMF) in a single tree structure. The normal process of repairing audit failures had a number of problems because of the depth of the subtrees, basically saving each object would not work because the save of a directory would save all of its objects and sub directories at once, having some directories which have subtrees in the hundreds of directories was not going to make this easy. Also the total size of one of the top directories is about 21GB with all its subdirectories.

So we needed to come up with a process that would take the results of an audit against the directory and allow a directory by directory rebuild on the target system. Another major issue is the very poor save and restore process on the IBM i, what you saved is not what is left after a restore correctly completes (missing attributes and settings!) so we had to allow the entire structure to be deleted on the target before we started to rebuild it. When we looked at the contents of the file on the source system we had to do some filtering of the records, we wanted to only save and restore the directory object and its non directory objects. We did look at a Logical file to do the filtering but that ended badly and trying to move into the 21st century means we should embrace SQL technology where possible.

This is the SQL statement which gave us the desired results when run interactively.
SELECT * FROM ifsaudf WHERE ATTR like 'd%' and SYS = 'SRC' and FTYPE = 'M' ORDER BY PATH

Our program would need to run that request and then be able to build a synch request and delete the record once it had successfully been actioned. The following is the program we created.
[code lang=”text”]
#include <stdio .h>
#include <stdlib .h>
#include <string .h>
#include <sqlca .h>

/* IFS Audit failures */
#pragma mapinc("ifsaud","HA4I71/IFSAUDF(*ALL)","both")
#include "ifsaud"
HA4I71_IFSAUDF_IFSREC_both_t IFS_Aud_Rec;

int main(int qrgc, char **argv) {
char outp[5002];
char cmd[5002];

EXEC SQL
INCLUDE SQLCA;
EXEC SQL DECLARE C1 SCROLL CURSOR FOR
SELECT * FROM ifsaudf WHERE ATTR like ‘d%’ and SYS = ‘SRC’ and FTYPE
= ‘M’ ORDER BY PATH FOR UPDATE;
EXEC SQL OPEN C1;
EXEC SQL WHENEVER NOT FOUND GO TO done1;
do {
EXEC SQL FETCH C1 INTO :IFS_Aud_Rec;
if((IFS_Aud_Rec.PATHLEN > 0) && (memcmp(IFS_Aud_Rec.PATH," ",4) != 0)){
memset(&IFS_Aud_Rec.PATH[IFS_Aud_Rec.PATHLEN],’\0’,1);
printf("%d %sn",IFS_Aud_Rec.PATHLEN,IFS_Aud_Rec.PATH);
sprintf(cmd,"SYNCIFS PATH(‘%s’) SUBTREE(%s)",IFS_Aud_Rec.PATH,
argv[1]);
if(system(cmd) == 0) {
EXEC SQL
DELETE FROM IFSAUDF WHERE CURRENT OF C1;
if(SQLCODE != 0)
printf("SQLCODE = %dn",SQLCODE);
}
else
printf("%sn",cmd);
}
}while(SQLCODE==0);
done1:
EXEC SQL CLOSE C1;
exit(1);
}
[/code]

So that is our program which will read through the audit file, sort and filter the records, issue a command using the record content and then delete the record if the command is successfully actioned. Next step will be to add some additional error checking a message sending into the program but for now I think it shows some important elements of embedding SQL in a C program.

Chris…