Mind９βでSQLServer、Mysql、PostgreSQLにトランザクション更新（begintran ～ rollback）

はじめに

Mind９βを開発元@killyさんの御厚意により評価用としてご提供いただきましたので、Mind９βのTclとのインターフェース単語を使い、TclのODBCデータベース操作ライブラリをつかってSQLServer、Mysql、PostgreSQLに対する共通処理単語を実装しております。（SQLコマンドテキストの内容としては各RDBによって異なっていきますが、処理単語としては同じとなります。）前回はトランザクションとコミットに対応しましたので今回はロールバックを検証します。

前提条件

Windows11 Pro 22H2 22621.4169
mind version9-BETA-6

SQL Server 16.0.1000.6 Express Edition
SQL Server Management Studio 19.2.56.2
MySQL Community Server 8.3.0 winx64
mysql-workbench-community-8.0.36-winx64
mysql-connector-odbc-8.0.35-win32
postgresql-16.1-1-windows-x64
pgAdmin4↑これにはいってたやつ
psqlodbc_16_00_0000-x86

９βの言語拡張機能「評価」とは

ご関心があるようでしならば、たいへんお手数ですが、こちらの記事をご参照ください。
また、こちらの記事より「評価した値」ではなく、エラー結果を返す「評価した値０」を使用することにより、エラー情報の取得を別途、単語「参照０」を使用して行っています。

参考情報

tclDatabaseにはMysql、PostgreSQL、SQLLite固有のパッケージがありますが、今回評価しているのはtdbc:odbcというODBC接続用パッケージです。

お題のデータべース

SQLServer、Mysql、PostgreSQLでは共通のテーブル構成のデータベースを作成しています。データベース名は「日本語プログラミング言語」です。今回もそれを利用します。今回検証しているのはSQLServerです。

お題のソースコード

前回でトランザクション開始、コミット、ロールバックの単語を追加し、コマンドラインを評価して成否を得る箇所を単語化して記述を少し短くなるようにしました。今回は大きな変化はないのですが、Tcl評価の成否の０が失敗で１が成功というのをわかりやすくしました。
また、トランザクションとして各種SQLを実行している部分は単語化すれば「終わり」で処理を抜けてくるので問題ないのですが、単語化しないで一本書きしている場合に、各ステップのSQL実行が失敗した場合は後続の処理をジャンプするよう回数指定構文で１回を指定するという従来のやり方を踏襲しました。

ライブラリ

odebctcl.src

コマンドトークン設定は　コマンドラインにトークンを設定と　等価。
コマンドトークン追加は　コマンドラインにトークンを追加と　等価。
値を取得は             参照０と  等価。
TCL改行は  文字列定数     「＆ＬＦ＆」。
成功は　定数　１。
失敗は　定数　０。

接続文字列最大長は　定数　400バイト。
SQL文字列最大長は　定数　2000バイト。

改行コマンドトークン設定とは　（トークン　→　・）
    コマンドトークン設定し
    TCL改行を　コマンドトークン追加すること。

改行コマンドトークン追加とは　（トークン　→　・）
    コマンドトークン追加し
    TCL改行を　コマンドトークン追加すること。


エラーをモニタするとは　（・　→　・）
    「コマンドの評価実行に失敗しました。」を　表示し　
    「errorInfo」で　値を取得して　表示し　改行すること。

結果をモニタするとは　（結果文字列　→　・）
    表示し　改行すること。

コマンドラインをモニタするとは　（・　→　・）
    改行し
    コマンドラインを　表示し　改行し　改行すること。

コマンドラインを評価して成否を返すとは　（・　→　1:成功/0:失敗）
        成否は　変数
    コマンドラインを　評価した値０を　成否に　入れ
    捨て
    成否を　返すこと。

ODBCライブラリをロードとは　（・　→　1:成功/0:失敗）
    "package require tdbc::odbc"を　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。

標準出力チャネル設定とは　（・　→　1:成功/0:失敗）
    「set stdout [open "CONOUT$" "w"]」を　改行コマンドトークン設定し
    「fconfigure $stdout -buffering line」を　コマンドトークン追加し
    コマンドラインを評価して成否を返すこと。

標準出力チャネルクローズとは　（・　→　1:成功/0:失敗）
    「close $stdout」を　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。


DB接続とは 	　（接続文字列　→　1:成功/0:失敗）			
    「set connectionString {」を　コマンドトークン設定し
    コマンドトークン追加し
    「}」を　改行コマンドトークン追加し
    「set db [tdbc::odbc::connection create db $connectionString]」を　コマンドトークン追加し
    コマンドラインを評価して成否を返すこと。
    
DB切断とは	（・　→　1:成功/0:失敗）
    「$db close」を　　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。

結果を取得とは 	　（・　→　結果文字列 1:成功/0:失敗）
    「set result [$stmt execute]」を　改行コマンドトークン設定し
    「$result foreach row {」を　改行コマンドトークン追加し
    「puts $stdout [dict get $row]」を　改行コマンドトークン追加し
    「}」を　改行コマンドトークン追加し
    「$result close」を　改行コマンドトークン追加し
    「$stmt close」を　改行コマンドトークン追加し
    ※コマンドラインをモニタ
    コマンドラインを　評価した値０すること。 


SQL文をセットとは （SQL文字列　→　・）
    「set stmt [$db prepare 」を　コマンドトークン設定し
    コマンドラインに空白と囲みトークンを追加し
    「]」を　改行コマンドトークン追加。 


整数のSQLパラメータをセットとは	 （プレースルダ　変数　→　・）
        プレースホルダは　文字列
        パラメータは　変数
    パラメータに　入れ
    プレースホルダに　入れ    
    「set 」を　コマンドトークン追加
    プレースホルダを　コマンドトークンを追加し
    パラメータを　コマンドラインに空白と数値トークンを追加
    TCL改行を　コマンドトークン追加すること。

文字列のSQLパラメータをセットとは （プレースホルダ　文字列　→　・）
        プレースホルダは　文字列
        パラメータは　文字列
    パラメータに　入れ
    プレースホルダに　入れ
    「set 」を　コマンドトークン追加
    プレースホルダを　コマンドトークンを追加し
    パラメータを　コマンドラインに空白と囲みトークンを追加
    TCL改行を　コマンドトークン追加すること。

    
SELECT実行とは 	　（SQL文字列　→　結果文字列 1:成功/0:失敗）
        成否は　変数
    標準出力チャネル設定し　成否に　入れ
    成否が　失敗に　等しい
    ならば　空列と　成否を　返し
    さもなければ
        SQL文をセットし　結果を取得し
        ※成否に関わらず
        標準出力チャネルクローズし　成否に　入れ
        成否が　失敗に　等しい
        ならば　捨て　捨て　空列と　成否を　返し
        つぎに
    つぎに。


結果セット取得とは 	　（・　→　結果文字列 1:成功/0:失敗）
    ※SQL文をセットしていること、または、パラメータリスト作成開始していること   
    ※コマンドラインをモニタ
    コマンドラインを評価して成否を返し　失敗に　等しい
    ならば　　空列と　失敗を　返し
    さもなければ
        標準出力チャネル設定し　失敗に　等しい
        ならば　空列と　失敗を　返し
        さもなければ
            結果を取得し
            ※成否に関わらず
            標準出力チャネルクローズし　失敗に　等しい
            ならば　捨て　捨て　空列と　失敗を　返し
            つぎに
        つぎに
    つぎに。

SQLコマンド実行とは　（・　→　1:成功/0:失敗）
    ※SQL文をセットしていること、または、パラメータリスト作成開始していること   
    「$stmt execute 」を　改行コマンドトークン追加し
    「$stmt close」を　コマンドトークン追加し
    コマンドラインを評価して成否を返すこと。

トランザクションを開始とは	 （・　→ 1:成功/0:失敗）
    「$db begintransaction」を　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。

コミットとは		 （・　→ 1:成功/0:失敗）
    「$db commit」を　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。
    
ロールバックとは	 （・　→ 1:成功/0:失敗）
    「$db rollback」を　コマンドトークン設定し
    コマンドラインを評価して成否を返すこと。

ここからトランザクションは　定数　１。
スコープ開始とは　回数指定と　等価。
スコープを抜け出すとは　打ち切りと　等価。
ここまでスコープは	繰り返すと	等価。

テスト用コード

前回の実行状態（開発言語IDの10が追加された状態）で同じINSERT文を実行します。UPDATE文の状態は初期状態に戻しておきます。結果、INSERTで主キー重複エラーが発生して失敗しますので、最初のUPDATE文の結果がロールバックされるというシナリオです。
また、トランザクションとして各種SQLを実行している部分は単語化すればエラーの場合に「終わり」で処理を抜けてくるように書けるので問題ないのですが、今回は単語化しないで一本書きしていますので、各ステップのSQL実行が失敗した場合は後続の処理をジャンプするよう回数指定構文で１回を指定するという従来のやり方を踏襲しており、ソースを読んだ感じそのようには読めないようにしています。

testodebctcl.src

"odbctcl.src"を　コンパイル。

datasourceSqlsvrは 文字列定数 「Driver={ODBC Driver 17 for SQL Server};Server=(local)\SQLEXPRESS;Database=日本語プログラミング言語;UID=sa;PWD=****;」。
datasourceMysqlは 文字列定数 「Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Port=3306;Database=日本語プログラミング言語;UID=root;PWD=****;charset=cp932」。
datasourcePgsqlは 文字列定数 「Driver={PostgreSQL UNICODE};Server=localhost;Port=5432;Database=日本語プログラミング言語;UID=postgres;PWD=****;」。


TCLのODBCでトランザクションするとは　（・　→　・）
    SELECTは　　　文字列定数　「SELECT LN.言語ID,LN.言語名,LN.よみがな,DLN.開発言語ID,DLN.開発言語名 FROM 言語名 AS LN 」続
    「LEFT JOIN 開発言語 AS DL ON LN.言語ID = DL.言語ID 」続
    「LEFT JOIN 開発言語名 AS DLN ON DLN.開発言語ID=DL.開発言語ID WHERE LN.言語ID IN (:id1,:id2)」
　　INSERT1は　　　文字列定数　「INSERT INTO 開発言語名 (開発言語ID,開発言語名) VALUES (:dgid,:dgnm)」
　　INSERT2は　　　文字列定数　「INSERT INTO 開発言語 (言語ID,開発言語ID) VALUES (:gid,:dgid)」
　　UPDATEは　　　文字列定数　「UPDATE 言語名 SET よみがな = :yomi WHERE 言語ID = :id」
　　DELETEは　　　文字列定数　「DELETE 開発言語 WHERE 言語ID = :gid AND 開発言語ID = :id」
    成否は　変数

    ODBCライブラリをロードし 成否に　入れ
    成否が　失敗に　等しい
    ならば　エラーをモニタし 終わり
    つぎに
    datasourceSqlsvrで　DB接続し　成否に　入れ
    成否が　失敗に　等しい
    ならば　エラーをモニタし　終わり
    つぎに

    SELECTで　SQL文をセットし
    "id1"と　1で　整数のSQLパラメータをセットし
    "id2"と　7で　整数のSQLパラメータをセットし
    コマンドラインをモニタ
    結果セット取得し 成否に　入れ
    成否が　失敗に　等しい
    ならば　 捨て 
            エラーをモニタし
            DB切断し　捨て　終わり
    つぎに
    結果をモニタし

    トランザクションを開始し　成否に　入れ
    成否が　失敗に　等しい
    ならば　  エラーをモニタし
        　　　DB切断し　捨て　終わり
    つぎに

    ここからトランザクションの　スコープ開始
    
        UPDATEで　SQL文をセットし
        "yomi"と　"あたらしいよみがな"で　文字列のSQLパラメータをセットし
        "id"と　7で　整数のSQLパラメータをセットし
        SQLコマンド実行し 成否に　入れ
        成否が　失敗に　等しい
        ならば　 スコープを抜け出す
        つぎに
        コマンドラインをモニタ

        INSERT1で　SQL文をセットし
        "dgid"と　10で　整数のSQLパラメータをセットし
        "dgnm"と　"Tcl/Tk"で　文字列のSQLパラメータをセットし
        SQLコマンド実行し 成否に　入れ
        成否が　失敗に　等しい
        ならば　 スコープを抜け出す
        つぎに
        コマンドラインをモニタ

        INSERT2で　SQL文をセットし
        "dgid"と　10で　整数のSQLパラメータをセットし
        "gid"と　1で　整数のSQLパラメータをセットし
        SQLコマンド実行し 成否に　入れ
        成否が　失敗に　等しい
        ならば　 スコープを抜け出す
        つぎに
        コマンドラインをモニタ

　　ここまでスコープ

　　成否が　成功に　等しい
　　ならば
        コミットし　失敗に　等しい
        ならば　エラーをモニタし
                DB切断し　捨て　終わり
        つぎに
　　さもなければ
        コマンドラインをモニタし
        「SQL実行に失敗しました。ロールバックします。」を　表示し　改行し　改行し
        ロールバックし　失敗に　等しい
        ならば　エラーをモニタし
                DB切断し　捨て　終わり
        つぎに
　　つぎに


    SELECTで　SQL文をセットし
    "id1"と　1で　整数のSQLパラメータをセットし
    "id2"と　7で　整数のSQLパラメータをセットし
    結果セット取得し 成否に　入れ
    成否が　失敗に　等しい
    ならば　 捨て 
            エラーをモニタし
            DB切断し　捨て　終わり
    つぎに
    結果をモニタし

    DB切断し　失敗に　等しい
    ならば　エラーをモニタし 終わり
    つぎに。

※ テスト用
メインとは
    コンソールを開く

    TCLのODBCでトランザクションすること。

お題のMind9βでの実行の様子

INSERTで主キー重複エラーが発生して失敗しますので、最初のUPDATE文の結果がロールバックされるというシナリオです。

set stmt [$db prepare  {SELECT LN.言語ID,LN.言語名,LN.よみがな,DLN.開発言語ID,DLN.開発言語名 FROM 言語名 AS LN LEFT JOIN 開発言語 AS DL ON LN.言語ID = DL.言語ID LEFT JOIN 開発言語名 AS DLN ON DLN.開発言語ID=DL.開発言語ID WHERE LN.言語ID IN (:id1,:id2)}]
set id1 1
set id2 7


言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 1 開発言語名 C
言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 8 開発言語名 Mind
言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 10 開発言語名 Tcl/Tk
言語ID 7 言語名 {Mind for Android} よみがな まいんどふぉーあんどろいど 開発言語ID 3 開発言語名 Java


set stmt [$db prepare  {UPDATE 言語名 SET よみがな = :yomi WHERE 言語ID = :id}]
set yomi {あたらしいよみがな}
set id 7
$stmt execute
$stmt close


set stmt [$db prepare  {INSERT INTO 開発言語名 (開発言語ID,開発言語名) VALUES (:dgid,:dgnm)}]
set dgid 10
set dgnm {Tcl/Tk}
$stmt execute
$stmt close

SQL実行に失敗しました。ロールバックします。

言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 1 開発言語名 C
言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 8 開発言語名 Mind
言語ID 1 言語名 Ｍｉｎｄ よみがな まいんど 開発言語ID 10 開発言語名 Tcl/Tk
言語ID 7 言語名 {Mind for Android} よみがな まいんどふぉーあんどろいど 開発言語ID 3 開発言語名 Java

無事にロールバック成功しました。よみがなを変えるUPDATE文の結果がキャンセルされていることがわかります。

おわりに

いかがでしたでしょうか？なにかの参考になれば幸いです。いったん検証用のライブラリコードはひととおり揃いましたので、次回は他のRDBへのトランザクションを検証します。