Curl Global Community
SQLステートメントとパラメータを組み合わせて実行可能なSQLを整形する - Printable Version

+- Curl Global Community (https://communities.curl.com)
+-- Forum: Blogs (https://communities.curl.com/forumdisplay.php?fid=17)
+--- Forum: Tech blog (https://communities.curl.com/forumdisplay.php?fid=18)
+---- Forum: Baison log (https://communities.curl.com/forumdisplay.php?fid=27)
+---- Thread: SQLステートメントとパラメータを組み合わせて実行可能なSQLを整形する (/showthread.php?tid=1096)



SQLステートメントとパラメータを組み合わせて実行可能なSQLを整形する - umemura - 07-03-2014

私は、サーバの開発で iBatis を利用することが多いのですが、
log4j を利用することで、SQLのステートメントとパラメータの両方が、
出力されるようになっています。

■出力例
yyyy-mm-dd hh:mmConfuseds,mmm DEBUG [java.sql.Connection] [http-8080-2] [********************] - {conn-NNNNNNN} Preparing Statement: SELECT * FROM EMP_MST WHERE EMP_CD = '?'
yyyy-mm-dd hh:mmConfuseds,mmm DEBUG [java.sql.PreparedStatement] [http-8080-2] [*******************] - {pstm-NNNNNN} Parameters: [A0001]

テストの際などに、サーバ処理の中で利用されたSQLを、
改めて、DBに対して直接投げてみたい、ということがよくあります。
そのためには、このステートメントとパラメータを組み合わせてSQLを作り直す必要があるのですが、
パラメータ数が多いといちいち「?」を置換して整形するのが面倒なので、
下記のようなサンプルで組み立てるようにしています。


Code:
{curl 8.0 applet}
{curl-file-attributes character-encoding = "utf8"}
{import * from CURL.DESKTOP.CLIPBOARD}
{def sql-ta:TextArea =
    {TextArea
        ||width = {make-elastic preferred-size = 1in},
        height = {make-elastic preferred-size = 1in}
    }
}


{def param-ta:TextArea =
    {TextArea
        ||width = 7in,
        height = {make-elastic preferred-size = 0.5in}
    }
}

{def comp-ta:TextArea =
    {TextArea
        ||width = 7in,
        height = {make-elastic preferred-size = 0.5in}
    }
}


{define-proc {get-type-list
                 index:int,
                 val:String,
                 params:StringArray
             }:DropdownList

    || ■CHAR
    || ■NUMBER
    || ■VARCHAR2
    || ■TIMESTAMP
    || ■DATE

    def dl =  {DropdownList
                  name = val,
                  user-data = index,
                  "文字列",
                  "数値",
                  {on ValueChanged at dl:DropdownList do
                      {switch dl.value
                       case  "文字列" do
                          set params[dl.user-data asa int] = "\'" & {dl.name.trim-clone} &  "\'"
                       else
                          set params[dl.user-data asa int] = {dl.name.trim-clone} asa String
                      }
                  }
              }

    {if {String {val.to-int}} == val then
        {dl.set-value-with-events "数値"}
    elseif  {String {val.to-double}} == val then
        {dl.set-value-with-events "数値"}
    else
        {dl.set-value-with-events "文字列"}
2014-07-03 16:24:18.554999   START:会計年月日 値確定
2014-07-03 16:24:18.563000  DATE  20140413
2014-07-03 16:24:18.570000  PAY-DV  1
[info] #2014/07/03 16:24:18.579# - [SERVICE] [START] DisPayPlnDtService.calc-pay-pln-dt
[info] #2014/07/03 16:24:18.582# - [SERVICE] INPUT-DTO = DisInCalcPayPlnDtDto
[info] #2014/07/03 16:24:18.582# - [SERVICE] PROGRAM-ID = SCRD2010
2014-07-03 16:24:18.746000   START:支払区分 値確定
2014-07-03 16:24:18.848000  DATE  20140703
2014-07-03 16:24:18.848000  PAY-DV  3
[info] #2014/07/03 16:24:18.850# - [SERVICE] [START] DisPayPlnDtService.calc-pay-pln-dt
[info] #2014/07/03 16:24:18.850# - [SERVICE] INPUT-DTO = DisInCalcPayPlnDtDto
[info] #2014/07/03 16:24:18.850# - [SERVICE] PROGRAM-ID = SCRD2010
[info] #2014/07/03 16:24:18.932# - [SERVICE] [END] DisPayPlnDtService.calc-pay-pln-dt
2014-07-03 16:24:18.933999   END:支払区分 値確定
[info] #2014/07/03 16:24:19.055# - [SERVICE] [END] DisPayPlnDtService.calc-pay-pln-dt
2014-07-03 16:24:19.065000   END:会計年月日 値確定

    }

    {return dl}
}

{def type-tbl =
    {Table
        ||height = {make-elastic preferred-size = 1pt},
        ||valign = "top",
        columns = 4}
}


{def formed-params-by-type:StringArray = {StringArray}}

{def parse-cb =
    {CommandButton
        label = "解析",
        {on Action do
            {type-tbl.clear}
            {formed-params-by-type.clear}

            {type-tbl.add "No."}
            {type-tbl.add "Column"}
            {type-tbl.add "Value"}
            {type-tbl.add "Type"}

            def split-sql = {sql-ta.value.split split-chars = '?'}

            def params = {param-ta.value.split split-chars = ','}

            {for s in params do
                {formed-params-by-type.append {s.trim-clone}}
            }


            {for s key index:int in params do

                let col:String = ""

                def tmp-ary = {split-sql[index].split}

                ||カラム名を探すため、まず。後ろから = を探す
                def find-equal-index =
                    {tmp-ary.find "=", search-direction = SearchDirection.backward}

                {if find-equal-index > -1 then

                    def find-column-index =
                        {tmp-ary.find
                            "",
                            ||空白では「ない」ものを検索
                            equality-proc = {proc {s1:String, s2:String}:bool
                                                {if s1 == s2 then
                                                    {return false}
                                                 else
                                                    {return true}
                                                }
                                            },
                            starting-index = find-equal-index - 1,
                            search-direction = SearchDirection.backward}

                    {if find-column-index> -1 then
                        set col = tmp-ary[find-column-index]
                    }

                }

                {type-tbl.add index + 1}
                {type-tbl.add col}
                {type-tbl.add s}
                {type-tbl.add {get-type-list index, s, formed-params-by-type }}

            }

        }
    }
}

{def replace-cb =
    {CommandButton
        label= "置換",
        {on Action do

            {if not sql-ta.value.empty? then
                def split-sql = {sql-ta.value.split split-chars = '?'}

                let comp-sql:String = ""

                {for s key index:int in formed-params-by-type do
                    set comp-sql = comp-sql & split-sql[index]  & s
                }

                set comp-sql = comp-sql & split-sql[split-sql.size - 1]

                set comp-ta.value = comp-sql
            }
        }
    }
}


{def copy-cb =
    {CommandButton
        label= "クリップボードへコピー",
        {on Action do

            def clip = {Clipboard.get-system-clipboard }

            {clip.set-string comp-ta.value }

        }
    }
}



{View
    title = "SQL解析",
    height = 8in,
    width  = 8in,
    hstretch? = true,
    || この{Frame}を置き換えて使用してください
    {Frame
        hstretch? = true,
        {VBox
            halign = "center",
            margin = 5pt, spacing = 3pt,
            {GroupBox
                label = "■SQL Templete",
                height = null,
                sql-ta
            },
            {HBox
                {GroupBox
                    label = "■Parameter",
                    param-ta
                },
                {GroupBox
                    label = "■Type Select",
                    {ScrollBox
                        hstretch? = true,
                        vstretch? = true,
                        {VBox
                            hstretch? = true, type-tbl, {Fill}}
                    }
                }
            },
            {HBox spacing = 5pt, parse-cb, replace-cb, copy-cb},
            {GroupBox
                label = "■Executable SQL",
                comp-ta
            }
        }
    },
    visibility = "normal",
    {on WindowClose do
        {exit}
    }
}


■使い方
①「SQL Templete」のテキストエリアにステートメントを張り付ける
②「Parameter」のテキストエリアにパラメータを張り付ける
③「解析」ボタンを押す
④必要に応じて「Type Select」に表示される型と値の組み合わせを修正する
⑤「置換」ボタンを押して、ステートメントとパラメータを整形します
⑥「Executable SQL」に出力されたSQLを利用する
 (「クリップボードへコピー」ボタンでコピー可能)


もしかすると、SQL Developer などのツールであれば、
もっとうまくやってくれる機能がありそうな気もしますが・・・。