今回は、MySQLのデータベースを使用してみようと思う。
MySQL関係のインストールや使い方についてのホームページは多い。しかし、Visual Basic
Expressから使用するという例は それほど多いわけではなく、迷ってしまう事が多々あるように思われる。この際、自分で行ったやり方を書き留めておくのも後々役に立つのではないかと考え、筆をとってみた。基本的な
使い方は２−１にてふれたSQL
SERVER Compactへの接続と変わるものではないが、MySQLを使用している人が多いようなので 同じような内容であるが触れてみたい。なお、ついでのようになってしまうが、 実際に使用する前段階のデータベース作成の部分から述べてゆこうと思っている。
１．使用したMySQLのバージョン
|
MySQL5.0.67 |
MySQL_essential_5.0.67_Win32.msi |
|
Apache 2.0.63 |
Apache_2.0.63_Win32_X86_No_Ssl.msi |
|
PHP 5.2.7 |
PHP_5.2.7_Win32.zip |
|
PHPMyAdmin2.1.9.3 |
PHPMyAdmin_2.11.9.3_all_Langages_Utf8_Only |
|
MySQL ドライバ |
MySQL_Connecter_Net_5.2.5 |
|
MySQL ODBCドライバ |
MySQL_Connecter_ODBC_5.1.5_win32.msi |
|
パソコンのOSとしてはWindows2000を使用し、Visual Basic
2005 Express Editionにて動作確認を行っている。 |
|
|
なお、以下の説明でMySQLのパスワードはpasswordと記載している。 |
|
２．MySQLのインストール
インストールは、Windows
Vista+Apache2.0+PHP5+MySQL5.0によるWebアプリケーションの内容に従い、インストールを行なっている。
３．テーブルの作成
始めに、データベースを作成するが、専用のユーザーを作成する部分から始める。コマンドプロンプトから
MYSQL -u root -p と入力。パスワードを聞かれるのでパスワードを入れてMySQLをまず立ち上げる。
次に、データベースとしてtanpinデータベースを作成する。
CREATE DATABESE tanpin;
SHOW DATABESES;
SET NAMES SJIS;
ここまでで、新しいデータベースを作成し tanpinデータベースが作成されているのは確認出来た。次にユーザーを作成する。
GRANT ALL PRIVILEGES ON *.* TO hanbai@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
ユーザーhanbaiがこれで作成されている筈なので、一旦MySQLを \q にて終了させて 再度起動しユーザーhanbaiにパスワードpasswordにて接続できるかどうかを確認する。
\q
MYSQL -u hanbai -p
password
USE tanpin;
SET NAMES SJIS;
テーブルとしては右の表のような商品のマスターテーブルを作成する。
以下のように入力してみる。
CREATE TABLE syotbl
(
tancode CHAR(13),
grpcode INTEGER,
bmncode INTEGER,
kancode CHAR(10),
syoname VARCHAR(60),
vdrcode INTEGER,
genka DECIMAL(9,2),
baika DECIMAL(7,0),
yyyymmdd DECIMAL(8,0),
kubun INTEGER,
PRYMARY KEY(tancode)
);
続いて、SHOW TABLES; と SHOW FIELDS FROM syotbl; コマンドを用い正しくデータが作成された事を確認する。
しかし、作成されたテーブルは思うようでは無く、以下の問題があった。
・INTEGERで大きさを指定すると、思ったよりも大きなエリアが確保される。−−SMALLINT かMEDIUMINTを用いる。
・列にNOT NULL制約が登録されていない。−−テーブル作成時に指定。
・列の初期値がNULLになってしまう。（NOT NULL制約をかけると）−−DEFAULT値を指定する。
・符号つきの列になってしまう−−UNSIGNEDを指定する。
これらの内容を加味するのであるが、ものの本には「ALTER TABLEは使わないほうが良い」旨書かれているので、一度テーブルを削除し
DROP TABLE IF EXISTS syotbl;
再度作成しなおした。以下に最終的に入力した内容を記載する。
CREATE TABLE syotbl
(
tancode CHAR(13) NOT NULL DEFAULT '', （''は'を2個続けて入力しています。kancodeも同様です）
grpcode SMALLINT UNSIGNED NOT NULL DEFAULT '0',
bmncode MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',
kancode CHAR(10) NOT NULL DEFAULT '',
syoname VARCHAR(60),
vdrcode MEDIUMINT UNSIGNED NOT NULL DEFAULT '0',,
genka DECIMAL(9,2) NOT NULL DEFAULT '0',
baika DECIMAL(7,0) NOT NULL DEFAULT '0',
yyyymmdd DECIMAL(8,0) NOT NULL DEFAULT '0',
kubun SMALLINT UNSIGNED NOT NULL DEFAULT '0',
PRYMARY KEY(tancode)
);
作成されたテーブルをPHPMyAdminで見て、ほぼ思い通りのテーブルが作成出来たことを確認した。
４．テーブルのテキストファイルからの作成
上のように書くのは簡単だが、実際には何度もスペルミスがあって 正しく打ち込むのは結構しんどいものだ。いくら↑キーや↓キーで前に入力した行を呼び出せるとしても それでもミスが続くと正直「またかよ」と思いたくなる。PHPMyAdminでやれば手間いらずで間違いがあってもすぐ直せるので良いのだが、ここは以降の事を考えると 是非ともテキストファイルからのテーブル作成を覚えておきたい。
やり方は簡単で、上に示した内容を一度（秀丸エディターなどの）ラインエディターで入力する。
次に、このファイルをmksyotbl.sqlという名称で登録した。（勿論名称は自由で、拡張子もSQLでなくても良いみたいだ）
ここで注意したいのは、このテキストファイルをどこに保存するかだ。
MySQLの特性なのかもしれないが、このファイルは「MySQLのホームデレクトリに無ければならない」ようだ。この記述も難しくて判断に苦しむが 要するに「コマンドプロンプト」を起動された時のカレントデレクトリにあれば良いらしい。つまり コマンドプロンプトからMySQLを起動する前に カレントデレクトリを必要なホルダーに移動させておくという事になる。私は面倒だし、すでにMYSQLは起動しているので、直接該当ホルダーに保存して処理している。
DROP TABLE IF EXISTS syotbl; にて 一度テーブルを削除し
\． mksyotbl.sql を入力。PHPMyAdminで テーブルが作成されているのを確認。
\q でMySQLを終了した。（なお、\．とmksyotbl.sqｌの間に半角のスペースが１文字必要となるので注意する。）
５．テーブルへのデータ登録
テーブルにデータを登録する場合、LOAD DATAコマンドを用いるのが一番楽で早い。但し、データに漢字が含まれていると事は面倒で 何も気にせずLOAD
DATAコマンドを行ってしまうと、漢字が文字化けしてしまう。これは、入力元のファイルが通常はS-JIS形式となっている為なので、秀丸エディターなどのファイル保存形式を指定できるエディターを用い、ファイルのエンコードの種類をUTF-８などのUNICODE形式にすることで解決できる。
実用化する際にはバッチファイル等から起動する事を考慮するので、最初からUNICODE形式のファイルである事が望ましい。この場合はVisual Basic
2005等を用いて、はじめからUNICODE形式のファイルに書き出すような仕組みを作成しておくことになる。（このやり方については、別な
機会に書いてみようと思っている。）
今回は、ORACLEのデータを用いてMySQL用にシーケンスファイルを作成し、このデータをLOAD DATAコマンドで取り込むようにする。
具体的にはORACLEの印刷機能を利用して データを固定長のファイルに書き込む。次に、データ中のスペースを削除してかわりにコンマを挿入してやるというプログラムを作成してやる。
（私はこの部分VB6にて作成したが、面倒なら直接データを作成して取り込みするようにすれば良い。今回は大量データを入れ込むテストをかねて行いたかったのでこのような方法をとった。実際には２０万件弱のデータを入れ込んでいる。）
参考のためにSQL文を右に示しておく。このように作成したファイルをe:\gtsyotbl.sqlのように保存しておいて、SQLPLUSを起動。
@e:\gtsyotbl.sql のようにSQL文の前に＠をつけて動かす事によって固定長のe:\syotbl.txtを作成する事が出来る。（なお この部分はORACLEの機能の為、今回の内容とは無関係である）。ORACLEが悪いというわけではないが、今回のテスト環境ではLOAD
DATAコマンドの結果が非常に早かった事だけは言っておきたい。単純比較はできないが、SQL-Loaderを使った時倍近いスピードが出たようだ。SQL-Loaderのように途中経過が出ないのだが
難点なのかもしれないが 早いのが一番だと思う。
ここからは、MySQLの処理となる。以下のようにコマンドを入力した。
MYSQL -u hanbai -p
password
USE tanpin;
SET NAMES SJIS;
LOAD DATA INFILE 'shohtbl2.txt' INTO TABLE syotbl
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
\q
なお、上に示したshohtbl2.txtは レコードの余分なスペースをカンマに置き換え さらに、UTF-8形式に変換したファイルである。
場所は、MySQLをインストールしたホルダーの中にあるDATAホルダー、その下にデータベースと同じホルダーが出来ているのでこの配下に保存した。私の環境の場合は、d:\MySQL\MySQLServer
5.0\data\tanpin\syohtbl2.txt に保存しています。コマンド内でドライブ名やホルダー名を指定して取り込み出来るともっと良いのですが、そこは運用でカバーしましょう。
この後は、PHPMyAdminを利用して データがきちんと入っているかと件数は正しいかどうかという点を確認しました。
６．Connecter_Netからの接続
毎回、同じような例で申し訳ないが Visual Basic 2005 Express
Editionを起動し、フォーム中にリストボックスとボタンを入れたものを作成する。
次に行うのは、環境設定で メニューのプロジェクトから参照の追加を行う。
画面の．ＮＥＴタブの画面からMySQL.DATAを追加。ソリューションエクスプローラーの画面で全てのオブジェクトを表示させた後、参照設定の下にMySQL.Dataが存在するのを確認。
次は、お決まりのIMPORTＳコマンド。具体的なコードは、Imports MySql.Data.MySqlClient である。内容を書くと、以下のようになる。
Imports MySql.Data.MySqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handl
es
Button1.Click
Dim Cn As New MySql.Data.MySqlClient.MySqlConnection
Dim SQL As MySql.Data.MySqlClient.MySqlCommand
Dim dr As MySql.Data.MySqlClient.MySqlDataReader
'
Dim s As String
'
s = "server=localhost;user=hanbai;password=password;database=tanpin;"
Cn.ConnectionString = s
SQL = Cn.CreateCommand
SQL.CommandText = "select * from syotbl where grpcode=4"
Cn.Open()
dr = SQL.ExecuteReader()
ListBox1.Items.Clear()
While (dr.Read())
ListBox1.Items.Add(dr("tancode") & " " & dr("syoname") & " " & dr("yyyymmdd"))
End While
'
Cn.Close()
SQL.Dispose()
Cn.Dispose()
End Sub
End Class
のようになる。いつも良くわからないので戸惑う接続文字列であるが 今回は「server=localhost;user ID=hanbai;password=password;database=tanpin;」というものを作成して使用している。要するに「サーバーの位置・サーバーへ接続する名称。商品名とデータベースの名称、パスワード」が設定されれば良いみたいなので 上のに示したように設定してみる。デバックを起動して、コマンドボタンを押し下げ。画面に品名が出るのを確認した。
実際の運用では、データの読み書きが必須であるが、これらについては、別の機会に書いてみたい。
