1

I want to set trigger though java.

So I used ScriptRunner supplied by ibatis.

but JDBC seperates query by semicolon, so PL/SQL that makes trigger doesn't be aware entirely.

Do you have any idea to resolve this problem??

(I am using PostgreSQL DB)

this is my code

URL ddlUrl = ClassLoader.getSystemClassLoader().getResource( "com/puzzledata/pa/schema/origin/ddl.sql");
URL baseDataUrl = ClassLoader.getSystemClassLoader().getResource( "com/puzzledata/pa/schema/origin/basedata.sql");

File[] files = new File[2];
files[0] = new File( ddlUrl.getPath());
files[1] = new File( baseDataUrl.getPath());

for( File f : files) {
    conn = DriverManager.getConnection( URL, USER_NAME, PASSWORD);//some method to get a Connection
    ScriptRunner runner= new ScriptRunner(conn);
    InputStreamReader reader = new InputStreamReader(new FileInputStream(f));
    runner.runScript(reader);
    reader.close();
    conn.close();

}

this is error when execute PL/SQL

   16:32:10.611 ERROR jdbc.sqltiming [] - [ : ]2. Statement.execute(create OR REPLACE function msg_bigsync() returns trigger AS $bigsync_trg$
    BEGIN
        IF NEW.state = 'Processing' THEN
            UPDATE PDMessage set state='Processing', message = NEW.content, modifiedat=NEW.executedat WHERE jobid = NEW.jobid
) FAILED! create OR REPLACE function msg_bigsync() returns trigger AS $bigsync_trg$
    BEGIN
        IF NEW.state = 'Processing' THEN
            UPDATE PDMessage set state='Processing', message = NEW.content, modifiedat=NEW.executedat WHERE jobid = NEW.jobid
 {FAILED after 8 msec}

this is PL/SQL

  create OR REPLACE function msg_bigsync() returns trigger AS $bigsync_trg$
    BEGIN
        IF NEW.state = 'Processing' THEN
            UPDATE PDMessage set state='Processing', message = NEW.content, modifiedat=NEW.executedat WHERE jobid = NEW.jobid;
        END IF;
        RETURN NULL;
    END;
$bigsync_trg$ LANGUAGE plpgsql;

1 Answer 1

1

I Found answer by myself

I need to execute entire script not line by line.

there is an option ScriptRunner.class to set runner.setSendFullScript(true) and It work fully.

I guess it is better to make User can set this option by parameter of method..

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.