Informix

 View Only
  • 1.  INFORMIX -> INSERT data to a table and file both

    Posted Mon January 30, 2023 09:25 AM
    Dear Friends,

    IDS 14.10.FC6 

    We need to INSERT data to a table and the same time we need the same data to write to a file.

    We cannot do this activity from the application end since there are different types of applications doing the INSERT operation.

    So, we have to run this activity from DB end only.

    Thanks !

    Best Regards,

    ------------------------------
    Indika Jinadasa
    ------------------------------


  • 2.  RE: INFORMIX -> INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 09:43 AM
    Edited by Art Kagel Mon January 30, 2023 09:46 AM
    Indika:

    One would think that one could simply create an external table SAME AS the original table with the desired format (you have a choice between DELIMITED, FIXED field, and INFORMIX (binary data) format. Then create an INSERT trigger on the original table that copies the inserted data to the external table.
    Unfortunately each INSERT to an external table truncates the file. If it is a multi-row insert those rows all get inserted to the file, but a subsequent insert, even from the same session, will truncate the file first and overwrite it. So this will not work.

    You could install the UTL_FILE datablade extension (see the Database Extension manual or search online) which contains the following functions:
    • FCLOSE procedure - Closes a specified file.
    • FCLOSE_ALL procedure - Closes all open files.
    • FFLUSH procedure - Flushes unwritten data to a file.
    • FOPEN function - Opens a file.
    • GET_LINE procedure - Gets a line from a file.
    • NEW_LINE procedure - Writes an end-of-line character sequence to a file.
    • PUT procedure - Writes a string to a file.
    You could, again, create an INSERT trigger on the table in which you call a stored procedure that builds a string or several strings from the inserted columns and writes the string out to a file. You could format the string(s) as a delimited, fixed field, XML, or JSON record for consumption by whatever application needs to read it.

    Another alternative would be to write your own database extension using "C" or Java to create a function to open a file in append mode from the BEFORE section of the trigger, format and write a row of data from the ON EVERY ROW section, and to close the file in the AFTER section of the trigger. It would not be difficult to do.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: INFORMIX -> INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 09:49 AM
    Indika:

    Just thought of a third alternative method:

    Write to an MQ table in the insert trigger on the original table and have the MQ server pass the data to an MQ consumer you write that puts the data into a file.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------