I need to execute a script (informix code) in a .sql file for migration purposes. The thing is, I want to load it from a function to be able to use the exception, therefore being able to do a rollback in case of an error.

So, this is the code (still experimenting):

DROP FUNCTION IF EXISTS "informix".SCRIPT_MIGRATION();

CREATE FUNCTION "informix".SCRIPT_MIGRATION()
RETURNS BOOLEAN as RESULT;

    DEFINE lv_execute lvarchar(32739);
    DEFINE li_errnum, li_eisam INT;
    DEFINE lv_errtxt CHAR(200);

    ON EXCEPTION SET li_errnum, li_eisam, lv_errtxt
        ROLLBACK;
            CALL regista_log('script_migration', get_session_user(), li_errnum, lv_errtxt);
        RETURN 'f';
    END EXCEPTION;

    CALL set_isolation_level();

    BEGIN;

    LET lv_execute = 'LOAD FROM ''C:\Users\Admin\Desktop\ConstaWeb_Stuff\test.sql'' DELIMITER ''+'' INSERT INTO SCRIPT_MIGRATION_TEMP_TABLE;';

    DROP TABLE IF EXISTS SCRIPT_MIGRATION_TEMP_TABLE;

    CREATE TABLE SCRIPT_MIGRATION_TEMP_TABLE(
        STRING_TO_EXECUTE LVARCHAR(31739)
    );

    EXECUTE IMMEDIATE lv_execute;


    COMMIT; 

    RETURN 't';
END FUNCTION;


CALL SCRIPT_MIGRATION();

That's because we apparently can't execute the load command inside functions. So I'm trying to execute it. But I'm not getting it right, apparently... The objective here is to execute the script (not a sheel command script! it's an informix script, like creates, loads, unloads, drops...) on a file. I'm open to other ways of doing this.

I'm relatively new to informix so I'm sure there is still a lot I don't know about it.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.