How to write UDF to handle operating System I/O operations

Connectivity
rgs
Enthusiast

Re: How to write UDF to handle operating System I/O operations

Well against my better judgment here is a UDF to append to a log file named mylog in /home/tdatuser/. It is going to be appended to on each node the UDF runs on. If mylog does not exist it will create it. This is a UNIX solution.

I really think the OP should be well versed in C before doing this sort stuff and be well versed on how the system works and how it does its work. Just reading the documentation on writing a UDF is not enough, it will just get you in a whole lot of trouble quickly. That’s my opinion.

The UDF:

#define SQL_TEXT Unicode_Text
#include
#include

/* create statement:

REPLACE FUNCTION appendlog(VARCHAR(256) CHARACTER SET LATIN)
RETURNS INTEGER
LANGUAGE C
NO SQL
RETURNS NULL ON NULL INPUT
PARAMETER STYLE TD_GENERAL
EXTERNAL;

*/

void appendlog(VARCHAR_LATIN *logstring,
INTEGER *logrslt,
char sqlstate[6])
{
FILE *fd;

/* result not really used -- just set to zero */
*logrslt = 0;

/* assume there is not going to be an error */
strcpy(sqlstate, "00000");

/* open file in "/home/tdatuser/mylog" for append mode */
fd = fopen("/home/tdatuser/mylog","a");
/* validate that the open worked */
if (fd == NULL)
{
/* error opening file set the error return and exit*/
strcpy(sqlstate, "U0001");
return;
}

/* append the passed in string to "mylog" */
fwrite(logstring, 1, strlen(logstring), fd);

/* check for error on write */
if(ferror(fd))
{
/* error writing file */
strcpy(sqlstate, "U0002");
}

/* close the file */
fclose(fd);
return;
}

Sample SQL to execute UDF as a zero table select:

select appendlog('this is my test string');

From UNIX cat of ‘mylog’

cat /home/tdatuser/mylog

this is my test string

Highlighted
Enthusiast

Re: How to write UDF to handle operating System I/O operations

Thanks for the information.
Enthusiast

Re: How to write UDF to handle operating System I/O operations

It is giving the following error.

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select appendlog('this is my test string');

select appendlog('this is my test string');
*** Failure 7504 in UDF/XSP/UDM UNICA_APP_EDW1.appendlog: SQLSTATE U0001:
Statement# 1, Info =0
*** Total elapsed time was 1 second.

Please help me out.
rgs
Enthusiast

Re: How to write UDF to handle operating System I/O operations

I think at this point you need to ask someone at your organization that knows C to help you with your problem. The 7504 error is because your UDF decided to return an error that the code asked it to return. The error is “U0001”. If you look at the C code you will see that it is generated when the fopen fails. So either the directory you specified cannot be accessed by tdatuser on the open or you made some other error when you typed it in. Get a good C book, or take some classes and learn C if you want to keep pursuing this. This is not a database issue anymore. The provided UDF works on MPRAS and Linux systems.