[Seasar-user:16024] Re: [S2JDBC-Gen] Exe-Sql について

あきら [E-MAIL ADDRESS DELETED]
2008年 10月 17日 (金) 10:24:04 JST


あきらです

> Oracleの場合、/で区切るというのは対応しているつもりです。
> 私の環境では/で区切られて動作します。

statementDelimiter に"/"を指定すれば正常に動作しています

statementDelimiter : ":"
blockDelimiter : "/"

上記の場合 /と;の両方で区切られてしまい失敗

statementDelimiter : "/"
blockDelimiter : "/"

両方 / にすれば動きました
途中までデフォルトの設定で実行していて、途中で/の区切りにすると
過去のSQLも全部/にしないといけないと思ってもがいていました

ちなみに

CREATE trigger "BI_TEST_TABLE"
  before insert on "TEST_TABLE"
  for each row
begin
    select "TEST_TABLE_SEQ".nextval into :NEW.ID from dual;
end;
/

こんなSQLでやっぱりデータベースでtrigger作ろうとしたところ

CREATE trigger "BI_TEST_TABLE" ... (略) ... :NEW.ID from dual
end

この2行になってendでエラーがでてしまいました

あと行単位処理なので

select 1 from dual;select 2 from dual;

の場合、2つ目の2のセレクトは実行されないんですね。。。
(普通ですがドキュメントに明記したほうが安全かもしれません)

以下簡単なトレースです
上記までの動きが意図した動きであれば見る必要はないと
思います。

============================================
db/test.sql
トリガーを作るためにテーブルを作成(DROPは多重実行時のため追加)
============================================
DROP table "TEST_TABLE"
/

CREATE table "TEST_TABLE" (
    "ID"         NUMBER(8),
    constraint  "TEST_TABLE_PK" primary key ("ID")
)
/

DROP sequence "TEST_TABLE_SEQ"
/

CREATE sequence "TEST_TABLE_SEQ"
/

CREATE trigger "BI_TEST_TABLE"
  before insert on "TEST_TABLE"
  for each row
begin
    select "TEST_TABLE_SEQ".nextval into :NEW.ID from dual;
end;
/

============================================
org/seasar/extension/jdbc/gen/internal/sql/SqlFileReader.java
二箇所にデバッグ出力をつけました
============================================
    public String readSql() {
        if (endOfFile) {
            return null;
        }
        try {
            if (reader == null) {
                reader = createBufferedReader();
            }
            SqlBuilder builder = new SqlBuilder();
            readLineLoop: for (;;) {
                lineCount++;
                tokenizer.addLine(reader.readLine());
System.out.println( ToStringBuilder.reflectionToString(tokenizer,
ToStringStyle.MULTI_LINE_STYLE).toString() );
                builder.notifyLineChanged();
                nextTokenLoop: for (;;) {
                    builder.build(tokenizer.nextToken(), tokenizer.getToken());
                    if (builder.isTokenRequired()) {
                        continue;
                    } else if (builder.isLineRequired()) {
                        break nextTokenLoop;
                    } else if (builder.isCompleted()) {
                        break readLineLoop;
                    }
                    throw new IllegalStateException("builder");
                }
            }
 System.out.println( ToStringBuilder.reflectionToString(builder,
ToStringStyle.MULTI_LINE_STYLE).toString() );
            return builder.getSql();
        } catch (IOException e) {
            throw new IORuntimeException(e);
        }
    }

============================================
antの実行ログ
============================================
 [exec-sql] DEBUG 2008-10-17 10:08:02,811 [main]
SQLファイル(\eclipse\jnavi-db\db\test.sql)を実行します。
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=DROP table "TEST_TABLE"
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=23
 [exec-sql]   token=<null>
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=/
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=1
 [exec-sql]   token=
 [exec-sql]   type=BLOCK_DELIMITER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=DROP table "TEST_TABLE"
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=false
 [exec-sql]   lineChanged=true
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:02,889 [main] DROP table "TEST_TABLE"
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=
 [exec-sql]   pos=0
 [exec-sql]   nextPos=0
 [exec-sql]   length=0
 [exec-sql]   token=/
 [exec-sql]   type=END_OF_LINE
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=CREATE table "TEST_TABLE" (
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=27
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=    "ID"         NUMBER(8),
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=27
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=    constraint  "TEST_TABLE_PK" primary key ("ID")
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=50
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=)
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=1
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=/
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=1
 [exec-sql]   token=
 [exec-sql]   type=BLOCK_DELIMITER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=CREATE table "TEST_TABLE" (     "ID"
NUMBER(8),     constraint  "TEST_TABLE_PK" primary key ("ID") )
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=false
 [exec-sql]   lineChanged=true
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:03,357 [main] CREATE table
"TEST_TABLE" (     "ID"         NUMBER(8),     constraint
"TEST_TABLE_PK" primary key ("ID") )
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=
 [exec-sql]   pos=0
 [exec-sql]   nextPos=0
 [exec-sql]   length=0
 [exec-sql]   token=/
 [exec-sql]   type=END_OF_LINE
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=DROP sequence "TEST_TABLE_SEQ"
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=30
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=/
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=1
 [exec-sql]   token=
 [exec-sql]   type=BLOCK_DELIMITER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=DROP sequence "TEST_TABLE_SEQ"
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=false
 [exec-sql]   lineChanged=true
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:03,623 [main] DROP sequence "TEST_TABLE_SEQ"
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=
 [exec-sql]   pos=0
 [exec-sql]   nextPos=0
 [exec-sql]   length=0
 [exec-sql]   token=/
 [exec-sql]   type=END_OF_LINE
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=CREATE sequence "TEST_TABLE_SEQ"
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=32
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=/
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=1
 [exec-sql]   token=
 [exec-sql]   type=BLOCK_DELIMITER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=CREATE sequence "TEST_TABLE_SEQ"
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=false
 [exec-sql]   lineChanged=true
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:03,670 [main] CREATE sequence
"TEST_TABLE_SEQ"
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=
 [exec-sql]   pos=0
 [exec-sql]   nextPos=0
 [exec-sql]   length=0
 [exec-sql]   token=/
 [exec-sql]   type=END_OF_LINE
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=CREATE trigger "BI_TEST_TABLE"
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=30
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=  before insert on "TEST_TABLE"
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=31
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=  for each row
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=14
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=begin
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=5
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=    select "TEST_TABLE_SEQ".nextval into :NEW.ID from dual;
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=59
 [exec-sql]   token=
 [exec-sql]   type=OTHER
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=CREATE trigger "BI_TEST_TABLE"   before insert on
"TEST_TABLE"   for each row begin     select "TEST_TABLE_SEQ".nextval
into :NEW.ID from dual
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=true
 [exec-sql]   lineChanged=false
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:03,717 [main] CREATE trigger
"BI_TEST_TABLE"   before insert on "TEST_TABLE"   for each row begin
  select "TEST_TABLE_SEQ".nextval into :NEW.ID from dual
 [exec-sql] [E-MAIL ADDRESS DELETED][
 [exec-sql]   statementDelimiter=;
 [exec-sql]   blockDelimiter=/
 [exec-sql]   line=end;
 [exec-sql]   pos=0
 [exec-sql]   nextPos=1
 [exec-sql]   length=4
 [exec-sql]   token=
 [exec-sql]   type=WORD
 [exec-sql]   blockCommentStarted=false
 [exec-sql] ]
 [exec-sql] org.seasar.extension.jdbc.gen.internal.sql.SqlFileReader$[E-MAIL ADDRESS DELETED][
 [exec-sql]   tokenRequired=false
 [exec-sql]   lineRequired=false
 [exec-sql]   completed=true
 [exec-sql]   buf=end
 [exec-sql]   wordList=[]
 [exec-sql]   sqlBlockContext=org.seasar.extension.jdbc.gen.internal.dialect.OracleGenDialect$[E-MAIL ADDRESS DELETED]
 [exec-sql]   delimited=true
 [exec-sql]   lineChanged=false
 [exec-sql] ]
 [exec-sql] DEBUG 2008-10-17 10:08:03,795 [main] end
 [exec-sql] DEBUG 2008-10-17 10:08:03,826 [main] 論理的なコネクションを閉じました。tx=null


Seasar-user メーリングリストの案内