作ろうと思ったきっかけ
会社の基幹システムがAS/400(=IBM i)を使っていて、データの抽出を行う機会がちょくちょくあります。
これまでは、フリーソフトのA5M2を使っていて、これはこれで非常に使い勝手が良かったですが、DB2/400に保有している
テーブルや各カラムの日本語名が出ない事が、ほんのちょっとだけ不便でした。
というわけで、夏の工作がてらDB2/400のSQL実行ツールを作成しようと思い立ちました。
この記事で実装する機能
SQL入力エリアで下記の機能を実装する
①シンタックスハイライト
②オートコンプリート機能
③SQL整形機能
使ったライブラリ
①Fast Colored TextBox for Syntax Highlighting
②Autocomplete Menu
③Poor Man's T-SQL Formatter
まずはライブラリをNuGetで入手
Fast Colored TextBox for Syntax Highlighting
Autocomplete Menu
Poor Man's T-SQL Formatter
Fast Colored TextBox for Syntax Highlighting(以下、FCTB)をコントロールに配置する
FCTBはデザイナは使えないので、コードからコントロールを追加する必要があります。
配置については、Panelを使ってそのコントロールに追加するのが良いと思います。
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;
namespace WindowsFormsApplication1
{
/// <summary>
/// テスト用フォームです
/// </summary>
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 初期読込時の動作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();
sqlInputTextBox.Language = Language.SQL;
sqlInputTextBox.Dock = DockStyle.Fill;
sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません!(ToT)
sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
}
}
}
たったこれだけで、それなりの見た目の入力エリアが出来ました。
DB2への接続クラスを作成する
下記の様なClassを作成しました。キモになるのは、QSYS2.SYSTABLES
です。このテーブルには、テーブルの物理名・論理名・テキスト名などが保管されています。
他にもQSYS2.SYSCOLUMNS
というテーブルにはカラム名とそれに紐づくTEXT情報が格納されているので、工夫次第でカラム名の日本語化も可能です。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
/// <summary>
/// AS400からのデータ取得を行う
/// </summary>
class DA_AS400
{
private string ipAddress;
private string schema;
/// <summary>
/// 接続先のIPアドレスと参照するDBを取得する
/// </summary>
/// <param name="ip">IPアドレス</param>
/// <param name="db">接続するDB</param>
public DA_AS400(String ip, String db)
{
ipAddress = ip;
schema = db;
}
/// <summary>
/// SQLを実行する
/// </summary>
/// <param name="SQL">入力されたSQL</param>
/// <returns>データベースからの取得結果</returns>
private DataTable execSQL(String SQL)
{
OleDbConnection cn = new OleDbConnection();
DataTable returnTable = new DataTable();
try
{
cn.ConnectionString = "Provider=IBMDASQL;Data Source=" + ipAddress +
";User ID=READONLY;Password=READONLY;Default Collection=" + schema + ";Force Translate = 0 ";
cn.Open();
SQL = SQL + " FETCH FIRST 10000 ROWS ONLY"; //レスポンスを考慮してレコード検索上限数を設ける(初期値は10,000件)
OleDbDataAdapter da = new OleDbDataAdapter(SQL, cn);
da.Fill(returnTable);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);//例外処理は今回は適当に
}
finally
{
if (cn != null){
cn.Close();
cn.Dispose();
}
}
return returnTable;
}
/// <summary>
/// スキーマに存在するテーブル名を取得する
/// </summary>
/// <returns></returns>
public DataTable getTableName()
{
return execSQL("SELECT Trim(TABLE_TEXT) as table_text,Trim(TABLE_NAME) as table_name FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = " +
quote(schema) + " AND TABLE_TEXT <> '' ORDER BY TABLE_TYPE DESC, TABLE_NAME");
}
/// <summary>
/// 入力された文字にシングルクォートをつけて返却する
/// </summary>
/// <param name="inputText">入力文字列</param>
/// <returns>クォートを付けた文字列</returns>
private string quote(String inputText)
{
return "'" + inputText + "'";
}
}
}
オートコンプリートを設定するように、フォームを設定する。
setAutoCompleteMenuForSQLInputArea
というメソッドを作成しました。上記で取得したテーブルの一覧を、AutoComplete用の文字列として設定しています。MulticolumnAutocompleteItem
に文字配列を設定すると、マルチカラムでオートコンプリートが出力されるようになります。
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;
using AutocompleteMenuNS;
using System.Data;
namespace WindowsFormsApplication1
{
/// <summary>
/// テスト用フォームです
/// </summary>
public partial class Form1 : Form
{
FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 初期読込時の動作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
sqlInputTextBox.Language = Language.SQL;
sqlInputTextBox.Dock = DockStyle.Fill;
sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません!(ToT)
sqlInputTextBox.Font = new System.Drawing.Font("Meiryo UI", 10);//お好みで
setAutoCompleteMenuForSQLInputArea(); //ここを追加
sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
}
/// <summary>
/// オートコンプリート用のデータを設定する
/// </summary>
private void setAutoCompleteMenuForSQLInputArea()
{
AutocompleteMenuNS.AutocompleteMenu acMenu = new AutocompleteMenuNS.AutocompleteMenu();
acMenu.MinFragmentLength = 1;//何文字入れたらオートコンプリートを出力するかを設定
acMenu.MaximumSize = new System.Drawing.Size(300, 400);//オートコンプリートの最小文字数を設定
DA_AS400 da = new DA_AS400("XX.XX.XX.XX", "HOGELIB");
DataTable tableNameTable = da.getTableName();
foreach(DataRow rows in tableNameTable.Rows)
{
MulticolumnAutocompleteItem item = new MulticolumnAutocompleteItem
(new String[] {(String)rows["table_text"],(String)rows["table_name"] }, (String)rows["table_name"]);
acMenu.AddItem(item);
}
acMenu.SetAutocompleteMenu(sqlInputTextBox, null); //以前に取得したオートコンプリート用コレクションをクリア
acMenu.SetAutocompleteMenu(sqlInputTextBox, acMenu);
}
}
}
結果、テーブル名を入れると下記の様に日本語名+実際の名前の候補が選択肢として出力されるようになりました!実に簡単。
SQL整形を実装する
最後にSQLを見易く整形する機能を実装します。formatSQLという名前のボタンをクリックすると、SQLを整形するようにしています。実際に追加したのは、formatSQL_Click
というメソッドです。今回は全てデフォルトで整形していますが、プロパティの与え方で、整形の仕方は調整が可能です。
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;
using AutocompleteMenuNS;
using System.Data;
using PoorMansTSqlFormatterLib.Tokenizers;
using PoorMansTSqlFormatterLib.Parsers;
using PoorMansTSqlFormatterLib.Formatters;
namespace WindowsFormsApplication1
{
/// <summary>
/// テスト用フォームです
/// </summary>
public partial class Form1 : Form
{
FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 初期読込時の動作
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
sqlInputTextBox.Language = Language.SQL;
sqlInputTextBox.Dock = DockStyle.Fill;
sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません!(ToT)
sqlInputTextBox.Font = new System.Drawing.Font("Meiryo UI", 10);//お好みで
setAutoCompleteMenuForSQLInputArea(); //ここを追加
sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
}
/// <summary>
/// オートコンプリート用のデータを設定する
/// </summary>
private void setAutoCompleteMenuForSQLInputArea()
{
AutocompleteMenuNS.AutocompleteMenu acMenu = new AutocompleteMenuNS.AutocompleteMenu();
acMenu.MinFragmentLength = 1;//何文字入れたらオートコンプリートを出力するかを設定
acMenu.MaximumSize = new System.Drawing.Size(300, 400);//オートコンプリートの最小文字数を設定
DA_AS400 da = new DA_AS400("10.100.1.151", "THMVLIBF");
DataTable tableNameTable = da.getTableName();
foreach(DataRow rows in tableNameTable.Rows)
{
MulticolumnAutocompleteItem item = new MulticolumnAutocompleteItem
(new String[] {(String)rows["table_text"],(String)rows["table_name"] }, (String)rows["table_name"]);
acMenu.AddItem(item);
}
acMenu.SetAutocompleteMenu(sqlInputTextBox, null); //変更以前に取得したオートコンプリート用コレクションをクリア
acMenu.SetAutocompleteMenu(sqlInputTextBox, acMenu);
}
/// <summary>
/// SQL整形ボタンが押された時にSQLを整形する
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void formatSQL_Click(object sender, EventArgs e)
{
TSqlStandardTokenizer tokenizer = new TSqlStandardTokenizer();
TSqlStandardParser parser = new TSqlStandardParser();
TSqlStandardFormatter tsf = new TSqlStandardFormatter();
var tokenizedSQL = tokenizer.TokenizeSQL(sqlInputTextBox.Text);
var parsedSQL = parser.ParseSQL(tokenizedSQL);
sqlInputTextBox.Text = tsf.FormatSQLTree(parsedSQL);
}
}
}
結果
それなりにSQLが見易くなりましたね。
おまけ
ちなみに、上記で使ったTSqlStandardParser
ですが、SQLを構造化してXML化しています。
つまり下記の様なコードで、SQLに書かれた要素を抽出する事が出来ます。
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(parsedSQL.OuterXml);
XmlElement xmlel = xmlDoc.DocumentElement;
XmlNodeList nodes = xmlel.GetElementsByTagName("SelectionTarget");//SelectionTargetで、SQL内のテーブル名を抽出
var test = nodes.Item(0).InnerText;//この様な形でテーブル名を取得可能
私はこの機能を利用して、SQL中のテーブル名を抽出→カラム名のオートコンプリート候補作成機能を実装しています。
このあたりを弄りながら作成すると、DB2/400におけるSQL作成の生産性が上がるかもしれませんね。
(でもQUERYとかあるから、あんまりSQLの需要はないのかな…)