基礎プログラム~JVデータ挿入編②~
⑤JV-Dataを読み込んでレコードを挿入する
ここからはフォームの作成とJV-Linkの初期化が済んでいることが前提になります。ボタンを押すとreadJvDataを呼び出してJV-Dataの読み込みを開始し、カラム名や挿入する内容を指定したパラメータをArrayListに追加していき、最後はそのArrayListを渡して挿入していく流れになっています。
Public Class DataSetupForm Private tokuumaData As JV_TK_TOKUUMA '特別登録情報構造体 Private Const DATA_SPEC_TOKUBETSU_TOROKU As String = "TOKU" Private Sub btnGetJvData_Click(sender As Object, e As EventArgs) Handles btnGetJvData.Click readJvData(DATA_SPEC_TOKUBETSU_TOROKU, 2, "20160101000000") End Sub 'パラメータを一括でセットするためのメソッド Public Sub setAllParams(paramList As ArrayList, columnName As String, dataType As MySql.Data.MySqlClient.MySqlDbType, maxLength As Integer, value As String) Dim params As New DbParams params.columnName = columnName params.dataType = dataType params.maxLength = maxLength params.value = value paramList.Add(params) End Sub 'JV-Dataを読み込むためのメソッド Private Sub readJvData(dataSpec As String, jvOption As Long, fromTime As String) DbConnector.getInstance.beginTransaction() Dim returnCode As Long Try Dim readCount As Long Dim downloadCount As Long '' JVOpen:総ダウンロードファイル数 Dim lastFileTimestamp As String '' JVOpen: 最新ファイルのタイムスタンプ゚ Const BUFFER_SIZE As Long = 110000 ''JVRead:データ格納バッファサイズ Const FILE_NAME_SIZE As Integer = 256 ''JVRead:ファイル名サイズ Dim buffer As String ''JVRead:データ格納バッファ Dim fileName As String ''JVRead:ダウンロードファイル名 returnCode = TopForm.jvLink.JVOpen(dataSpec, fromTime, jvOption, readCount, downloadCount, lastFileTimestamp) If returnCode <> 0 Then MsgBox("JVOpenエラー:" & returnCode) Else MsgBox("戻り値 : " & returnCode & vbCrLf & "読み込みファイル数 : " & readCount & vbCrLf & "ダウンロードファイル数 : " & downloadCount & vbCrLf & "タイムスタンプ : " & lastFileTimestamp) If readCount > 0 Then Do buffer = New String(vbNullChar, BUFFER_SIZE) fileName = New String(vbNullChar, FILE_NAME_SIZE) returnCode = TopForm.jvLink.JVRead(buffer, BUFFER_SIZE, fileName) Select Case returnCode Case 0 ' 全ファイル読み込み終了 Exit Do Case -1 ' ファイル切り替わり Case -3 ' ダウンロード中 Case -201 ' Init されてない MsgBox("JVInit が行われていません。") Exit Do Case -203 ' Open されてない MsgBox("JVOpen が行われていません。") Exit Do Case -503 ' ファイルがない MsgBox(fileName & "が存在しません。") Exit Do Case Is > 0 ' 正常読み込み insert(buffer) End Select Loop While (1) End If End If Catch ex As Exception DbConnector.getInstance.rollback(ex.ToString, True) Exit Sub End Try returnCode = TopForm.jvLink.JVClose() If returnCode <> 0 Then DbConnector.getInstance.rollback("ReturnCode:" & returnCode, True) MsgBox("JVClse エラー:" & returnCode) End If DbConnector.getInstance.commit() End Sub '読み込んだバッファを受け取ってレコードを挿入するためのメソッド Private Sub insert(buffer As String) Dim recordSpec As String = Mid(buffer, 1, 2) Select Case recordSpec Case "TK" '特別登録 tokuumaData.SetData(buffer) Dim paramList As ArrayList = createTokubetsuTorokuParamList() DbEditor.getInstance.insert(TableConstants.TABLE_NAME_TOKUBETSU_TOROKU, paramList) End Select End Sub '特別登録のパラメータリストを生成するためのメソッド Public Function createTokubetsuTorokuParamList() As ArrayList Dim paramList As New ArrayList setAllParams(paramList, "record_spec", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.head.RecordSpec)) setAllParams(paramList, "data_flag", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1, RTrim(tokuumaData.head.DataKubun)) setAllParams(paramList, "make_date", MySql.Data.MySqlClient.MySqlDbType.VarChar, 8, RTrim((tokuumaData.head.MakeDate.Year & tokuumaData.head.MakeDate.Month & tokuumaData.head.MakeDate.Day))) setAllParams(paramList, "year", MySql.Data.MySqlClient.MySqlDbType.VarChar, 4, RTrim(tokuumaData.id.Year)) setAllParams(paramList, "month_day", MySql.Data.MySqlClient.MySqlDbType.VarChar, 4, RTrim(tokuumaData.id.MonthDay)) setAllParams(paramList, "place_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.id.JyoCD)) setAllParams(paramList, "hold_count", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.id.Kaiji)) setAllParams(paramList, "hold_day", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.id.Nichiji)) setAllParams(paramList, "race_number", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.id.RaceNum)) setAllParams(paramList, "week_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1, RTrim(tokuumaData.RaceInfo.YoubiCD)) setAllParams(paramList, "race_name_main", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30, RTrim(tokuumaData.RaceInfo.Hondai)) setAllParams(paramList, "race_name_sub", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30, RTrim(tokuumaData.RaceInfo.Fukudai)) setAllParams(paramList, "race_name_alias", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30, RTrim(tokuumaData.RaceInfo.Kakko)) setAllParams(paramList, "race_name_main_eng", MySql.Data.MySqlClient.MySqlDbType.VarChar, 120, RTrim(tokuumaData.RaceInfo.HondaiEng)) setAllParams(paramList, "race_name_short_a", MySql.Data.MySqlClient.MySqlDbType.VarChar, 10, RTrim(tokuumaData.RaceInfo.Ryakusyo10)) setAllParams(paramList, "race_name_short_b", MySql.Data.MySqlClient.MySqlDbType.VarChar, 6, RTrim(tokuumaData.RaceInfo.Ryakusyo6)) setAllParams(paramList, "race_name_short_c", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.RaceInfo.Ryakusyo3)) setAllParams(paramList, "race_name_group", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1, RTrim(tokuumaData.RaceInfo.Kubun)) Dim sbHorseName As New Text.StringBuilder Dim sbBloodNumber As New Text.StringBuilder For c = 0 To tokuumaData.TokuUmaInfo.Length - 1 Dim _horseName As String = RTrim(tokuumaData.TokuUmaInfo(c).Bamei) Dim _bloodNumber As String = RTrim(tokuumaData.TokuUmaInfo(c).KettoNum) If 0 < _horseName.Length Then sbHorseName.Append(_horseName) sbHorseName.Append(",") sbBloodNumber.Append(_bloodNumber) sbBloodNumber.Append(",") End If Next sbHorseName.Remove(sbHorseName.Length - 1, 1) sbBloodNumber.Remove(sbBloodNumber.Length - 1, 1) setAllParams(paramList, "horse_name", MySql.Data.MySqlClient.MySqlDbType.Text, 3000, sbHorseName.ToString) setAllParams(paramList, "blood_number", MySql.Data.MySqlClient.MySqlDbType.Text, 3000, sbBloodNumber.ToString) setAllParams(paramList, "grade_count", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.RaceInfo.Nkai)) setAllParams(paramList, "grade_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1, RTrim(tokuumaData.GradeCD)) setAllParams(paramList, "race_type_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.JyokenInfo.SyubetuCD)) setAllParams(paramList, "race_mark_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.KigoCD)) setAllParams(paramList, "weight_type_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 1, RTrim(tokuumaData.JyokenInfo.JyuryoCD)) setAllParams(paramList, "race_condition_code_age_2", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.JyokenCD(0))) setAllParams(paramList, "race_condition_code_age_3", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.JyokenCD(1))) setAllParams(paramList, "race_condition_code_age_4", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.JyokenCD(2))) setAllParams(paramList, "race_condition_code_age_5", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.JyokenCD(3))) setAllParams(paramList, "race_condition_code_young", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.JyokenInfo.JyokenCD(4))) setAllParams(paramList, "distance", MySql.Data.MySqlClient.MySqlDbType.VarChar, 4, RTrim(tokuumaData.Kyori)) setAllParams(paramList, "track_code", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.TrackCD)) setAllParams(paramList, "course_group", MySql.Data.MySqlClient.MySqlDbType.VarChar, 2, RTrim(tokuumaData.CourseKubunCD)) setAllParams(paramList, "handicap_date", MySql.Data.MySqlClient.MySqlDbType.VarChar, 8, RTrim((tokuumaData.HandiDate.Year & tokuumaData.HandiDate.Month & tokuumaData.HandiDate.Day))) setAllParams(paramList, "entry_number", MySql.Data.MySqlClient.MySqlDbType.VarChar, 3, RTrim(tokuumaData.TorokuTosu)) Return paramList End Function End Class
⑥実行結果
レコード挿入後、tokubetsu_torokuテーブルを参照するとレコードが挿入されていることが確認できます。JV-Dataは全て固定長なので取り出した時データの末尾に余分なスペースが入っていますが、このサンプルではRTrim()でその余白を削除してあります。
それからゼロ詰めの項目も多いので、挿入する際に予め先頭のゼロを取っておくと後で扱いやすくなると思います。
基礎プログラム~JVデータ挿入編①~
今回はダミーデータではなく実際にJRA-VANのデータ(特別登録情報)をダウンロードしレコードに挿入するまでのプログラムを書きます。
予め言っておくと、僕は元々Java専門でVBに関してはほぼ初心者なのでコードの書き方に関してはあまり参考にしないでください。
それから、本記事はJV-Linkの動作確認やMySQL.Dataの参照追加が完了していることを前提に進めていきます。
①仕様書を開く
JRA-VAN DAta Lab. SDK Ver4.4.1>ドキュメント内にあるJV-Data441.pdfを開いて7ページ目の「特別登録」の表を見ながら進めていきます。
②テーブル情報の定義
Public Class TableConstants '「特別登録」のテーブル名 Public Const TABLE_NAME_TOKUBETSU_TOROKU As String = "tokubetsu_toroku" '「特別登録」テーブルのCREATE文 Public Const CREATE_TOKUBETSU_TOROKU As String = "CREATE TABLE IF NOT EXISTS " & TABLE_NAME_TOKUBETSU_TOROKU & " (id int(10) NOT NULL AUTO_INCREMENT ,record_spec varchar(2) ,data_group varchar(1) ,make_date varchar(8) ,year varchar(4) ,month_day varchar(4) ,place_code varchar(2) ,hold_count varchar(2) ,hold_day varchar(2) ,race_number varchar(2) ,week_code varchar(1) ,race_name_main varchar(60) ,race_name_sub varchar(60) ,race_name_alias varchar(60) ,race_name_main_eng varchar(120) ,race_name_short_a varchar(10) ,race_name_short_b varchar(6) ,race_name_short_c varchar(3) ,race_name_group varchar(20) ,grade_count varchar(12) ,grade_code varchar(6) ,race_type_code varchar(2) ,race_mark_code varchar(3) ,weight_type_code varchar(1) ,race_condition_code_age_2 varchar(3) ,race_condition_code_age_3 varchar(3) ,race_condition_code_age_4 varchar(3) ,race_condition_code_age_5 varchar(3) ,race_condition_code_young varchar(3) ,distance varchar(4) ,track_code varchar(2) ,course_group varchar(2) ,handicap_date varchar(8) ,registration_number varchar(3) ,blood_number text ,horse_name text ,PRIMARY KEY (id)) " '「特別登録」テーブルのDROP TABLE文 Public Const DROP_TOKUBETSU_TOROKU As String = "DROP TABLE IF EXISTS " & TABLE_NAME_TOKUBETSU_TOROKU End Class
仕様書の表を見ながらカラム名を自分で決めて、varchar()内の最大文字数はバイトの欄と説明の欄を参考に設定します。
これは使わなそうだなという項目(競走名副題欧字とか)は無駄になるのでカラム自体を作りません。
インデックスに関してはとりあえずスルーしていますが、仕様書のキー欄に○が付いている項目を設定しておくのが無難かと思います。
③DbConnectorクラスの作成
データベースの接続やトランザクション関連のメソッドをまとめたクラスを作成します。Imports MySql.Data.MySqlClient Public Class DbConnector Private sqlBuilder = New MySqlConnectionStringBuilder() Private connection As MySqlConnection = Nothing Private command As MySqlCommand = Nothing Private trans As MySqlTransaction = Nothing Private Shared ReadOnly instance As New DbConnector Public Shared Function getInstance() As DbConnector Return instance End Function Private Sub New() End Sub Public Function getConnection() As MySqlConnection If IsNothing(connection) Then connection = New MySqlConnection() End If Return connection End Function Public Function getCommand() As MySqlCommand If IsNothing(command) Then command = New MySqlCommand() End If Return command End Function 'データベースに接続するためのメソッド Public Sub open() sqlBuilder.Server = "localhost" sqlBuilder.Port = 3306 sqlBuilder.UserID = "root" sqlBuilder.Password = "****" sqlBuilder.Database = "test" sqlBuilder.CharacterSet = "sjis" connection = New MySqlConnection() connection.ConnectionString = sqlBuilder.ToString() connection.Open() End Sub 'データベースを切断するためのメソッド Public Sub close() connection.Close() End Sub Public Sub executeQuery() command.ExecuteNonQuery() End Sub Public Sub beginTransaction() trans = DbConnector.getInstance.connection.BeginTransaction() End Sub Public Sub rollback(ex As String, showMessage As Boolean) If IsNothing(trans) = False Then trans.Rollback() If showMessage Then MsgBox("ロールバックしました。") End If End If End Sub Public Sub commit() If IsNothing(trans) = False Then trans.Commit() End If End Sub End Class
④DbEditorクラスの作成
実際にクエリを実行してDBを操作するためのメソッドをまとめたクラスです。Imports MySql.Data.MySqlClient Public Class DbEditor Private Shared ReadOnly instance As New DbEditor Public Shared Function getInstance() As DbEditor Return instance End Function Private Sub New() End Sub 'CREATE TABLEを実行するためのメソッド Public Sub createTable() Dim command As MySqlCommand = DbConnector.getInstance.getCommand() command.Connection = DbConnector.getInstance.getConnection() command.CommandType = CommandType.Text command.CommandText = TableConstants.CREATE_TOKUBETSU_TOROKU command.Parameters.Clear() DbConnector.getInstance.executeQuery() End Sub 'DROP TABLEを実行するためのメソッド Public Sub dropTable() Dim command As MySqlCommand = DbConnector.getInstance.getCommand() command.Connection = DbConnector.getInstance.getConnection() command.CommandType = CommandType.Text command.CommandText = TableConstants.DROP_TOKUBETSU_TOROKU command.Parameters.Clear() DbConnector.getInstance.executeQuery() End Sub 'INSERTを実行するためのメソッド Public Function insert(tableName As String, params As ArrayList) As Boolean Try Dim command As New MySqlCommand command.Connection = DbConnector.getInstance.getConnection() command.CommandType = CommandType.Text Dim query As String = createInsertStatement(tableName, params) command.CommandText = query command.Parameters.Clear() For num = 0 To params.Count - 1 Dim item As DbParams = params(num) command.Parameters.Add("@" + item.columnName, item.dataType, item.maxLength).Value = item.value Next command.ExecuteNonQuery() Catch ex As MySqlException Return False End Try Return True End Function 'INSERT文を生成するためのメソッド Private Function createInsertStatement(tableName As String, params As ArrayList) As String Dim ret As String = "" Dim query As New Text.StringBuilder query.Append("INSERT INTO " & tableName & "(") For Each item As DbParams In params query.Append(item.columnName) query.Append(",") Next removeLast(params.Count, query) query.Append(") VALUES (") For Each item As DbParams In params query.Append("@") query.Append(item.columnName) query.Append(",") Next removeLast(params.Count, query) query.Append(")") ret = query.ToString Return ret End Function 'StringBuilderの末尾を削除するためのメソッド Private Sub removeLast(number As Integer, target As Text.StringBuilder) If 0 < number Then target.Remove(target.Length - 1, 1) End If End Sub End Class
続く
基礎プログラム~レコード挿入編~
Visual StudioとMySQLの接続が確認できたら今度は実際にレコードを挿入してみます。
テーブルの作成
サーバーエクスプローラー→作成済みのDB→Tablesを右クリック→Create Table任意のカラムを設定後、×で閉じるとテーブル名の入力を求められます。
フォームの作成
まずは新しいWindowsフォームアプリケーションを作成して、ボタンを2つ配置して↓のようなフォームを用意します。あとはそれぞれのボタンを押した時にDB接続、レコード挿入するようコードを書いていきます。
サンプルコード
※Openメソッド内のPasswordなどはMySQL Serverインストール時に設定したものに変える必要があります。Imports MySql.Data.MySqlClient Public Class Form1 Dim builder = New MySqlConnectionStringBuilder() Dim con As New MySqlConnection Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 'DB接続 Open() End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 'レコード挿入 Insert() End Sub Public Sub Open() builder.Server = "localhost" builder.Port = 3306 builder.UserID = "root" builder.Password = "****" builder.Database = "test" builder.CharacterSet = "sjis" Dim conStr = builder.ToString() con.ConnectionString = conStr con.Open() MsgBox("接続成功") End Sub Public Function Insert() As Boolean Dim trans As MySqlTransaction = Nothing trans = con.BeginTransaction() Try Dim command As New MySqlCommand command.Connection = con command.CommandType = CommandType.Text Dim sb As New System.Text.StringBuilder sb.Append("INSERT INTO horse (name) VALUES (@name)") command.CommandText = sb.ToString command.Parameters.Clear() command.Parameters.Add("@name", MySqlDbType.VarChar, 9).Value = "Buchiko" command.ExecuteNonQuery() Catch ex As MySqlException trans.Rollback() MsgBox("ERROR") Return False Finally End Try trans.Commit() MsgBox("レコードを挿入しました。") Return True End Function Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed con.Close() End Sub End Class
実行結果
デバッグの開始→DB接続ボタンをクリック→データ挿入ボタンをクリックという順に操作していくと実際にレコードが挿入されます。↓の画像は、確認のためにサーバーエクスプローラー→Tables→対象のテーブルを右クリック→データの取得でレコードを参照している画面です。
開発環境導入~データベース編~
データベースはMySQLを使用します。
この記事ではVisual StudioとMySQLを接続する方法を書いていきます。
①「MySQL Connector/Net」のインストール
http://dev.mysql.com/downloads/connector/net/
右下のダウンロードボタンを押して入手します。
②「MySQL ServerとMySQL for Visual Studio」をインストール
http://dev.mysql.com/downloads/installer/
「MySQL for Visual Studio」をクリック→下の(mysql-installer-community-5.7.10.0.msi)をダウンロード
インストーラーを立ち上げたらこの2つ(MySQL ServerとMySQL for Visual Studioを選択します。
MySQL Serverの設定はこの記事を参考にさせていただきました。
MySQL Server 5.6 を Windows にインストールする手順 | WEB ARCH LABO
③Visual Studioを立ち上げて実際にMySQLと接続する
インストールの完了後、Visual Studioを再起動して「サーバーエクスプローラー」→「データ接続」を右クリック→接続の追加をクリックすると↓のダイアログが表示されます。
データソースの項目に「MySQL Database」があればOK。選択して続行します。
MySQLを選択後、↓のダイアログが出るので各項目を入力してOK。
データベースを作成しますか?と聞かれるので「はい」。
これで簡単にサーバーエクスプローラーからDBを操作したりテーブルの確認をすることができるようになります。
開発環境導入~IDE編~
開発環境
言語はVB、IDEは「Visual Studio Community(無料)」を使います。
何故VBなのか?
DataLab対応ソフトの開発にはいくつか対応言語がある中で何故VBを選んだのかと言うと、最初にプログラミングパーツ(http://jra-van.jp/dlb/sdv/pgm.html)を見た時にVB以外のプログラムは2007年辺りのモノしかなく「こんな古くて大丈夫?」と不安になったからというだけです。
Javaしか触ったことがなかったので、近い印象を持っているC#で作りたかったのですがC#版のSDKは提供しているのにサンプルやプログラミングパーツは提供していないという残念な扱われ方だったため諦めた経緯があります。
Visual Studioの入手先
Visual Studioはこちらからダウンロードできます。
https://www.microsoft.com/ja-jp/dev/products/community.aspx
マシン購入
予想ソフト開発のためにデスクトップPCを新調しました。
ブログを通して可能な限り開発過程を記していこうと思います。
ちなみにスペックはこんな感じ。
パソコン
【OS】 Windows10 Pro 64bit版
【CPU】 Core i5-6400
【メモリ】8GB
【SSD/HDD】SSD 250GB / HDD 1TB
¥80,600
遠隔操作ができれば外でノートパソコンを広げて作業が可能なのと、競馬場にいてもタブレット端末で予想ソフトを使えるなどの利点もありPro版を選択しました。
更にデータベースをガリガリ使うのでSSDを選択。
液晶ディスプレイ
BenQ 23.8インチワイド
¥20,621
ノートパソコンだと出馬表は画面サイズの問題で前2走までしか表示できませんでした。
これだけ大きければ情報を無理に詰め込む必要がなくなります。