Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    VA,USA
    Posts
    8

    Post Unanswered: SP output to Flat Files

    Hi,
    I wanted to create a External Function/Procedure, which can output the stored proceure variable to a flat file. I am having a AIX box, and trying to create a C program as follow:

    #include<stdio.h>
    FILE *fp;
    void output_file(char filename[30],char str[150])
    {
    fp = fopen(filename,"a+");
    fprintf(fp,"%s",str);
    fclose(fp);
    }

    and compiling it using xlc -c FileOutput.c
    The FileOutput.o file I am copying in the $DB2HOME/functions Dir.
    and then creating a Stored procedure as follows:

    CREATE PROCEDURE AKUMAR.FILE_OUTPUT
    (IN FIELPATH VARCHAR(40),
    IN FILETEXT VARCHAR(60)
    )
    SPECIFIC AKUMAR.SQL020611091730470
    RESULT SETS 1
    READS SQL DATA
    EXTERNAL NAME 'FileOutput.o!output_file'
    LANGUAGE C
    PARAMETER STYLE GENERAL
    NOT DETERMINISTIC
    FENCED
    CALLED ON NULL INPUT
    NO DBINFO
    PROGRAM TYPE SUB;

    But this is not Working. Can any one of you through some light where I am going wrong. According to me it should work.

    regards,
    Arvind

  2. #2
    Join Date
    Jul 2002
    Posts
    4
    Arvind,

    Sorry that I'm not offering any help.

    We are thinking of writing output to a flat file from a SP, mainly for problem diagnosis purposes. Therfore I'm very interested to know if you managed to get this sorted out.

    Regards

    Leo

  3. #3
    Join Date
    May 2002
    Posts
    27
    I'm not good with this, but is it not right that we just change the Stored Procedure compiler into the C compiler then code based on the C language, thus reducing the call from your stored procedure to an external program?

  4. #4
    Join Date
    Jul 2002
    Posts
    4
    Ken_Ken,
    The reason to write the output frop SP is because the SP is performing other SQLs, and I need to write the output when exceptions are returned from the DB.

    Regards
    Leo

  5. #5
    Join Date
    May 2002
    Posts
    27
    i get your point, but from the code shown by ARVIND_TCS, he created a SP from db2 which calls the C function created from C, did i get to understand the SP right?

    what i'm suggesting it incorporate the C function within the Stored Procedure so it reduces the calling of an external program from SP.

  6. #6
    Join Date
    Jul 2002
    Posts
    4
    I may need to clarify the terms and make sure we are on the same ground.

    When we create a SP in DB2, we are just defining the SP in DB2 catalog. This is basically defining SP attributes (eg fenced etc.). And also defining the program (in this case C) via the EXTERNAL to be run when the SP is called. The program will then have both SQLs, and C codes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •