CODEWITHSUNDEEP
javaplsql
Minsik Park
Minsik Park

Reputation: 687

how to execute create trigger sql in java?

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;

Upvotes: 1

Views: 682

Answers (1)

Minsik Park
Minsik Park

Reputation: 687

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..

Upvotes: 1

Related Questions