' ********************************************************
' ■ MySQL データベースアクセス
' ********************************************************
Imports System.Data.Odbc

Module MyModule

' ********************************************************
' MySQL / System.Data.Odbc
' ********************************************************
Sub Main()

	Dim myConnectString As String = _
		"Driver={MySQL ODBC 5.1 Driver};" + _
		"SERVER=localhost;" + _
		"DATABASE=lightbox;" + _
		"UID=root;" + _
		"PWD=password"

	Dim myCon As New OdbcConnection()
	myCon.ConnectionString = myConnectString
	myCon.Open()

	Dim myQuery As String = _
		"SELECT 社員マスタ.*,DATE_FORMAT(生年月日,'%Y-%m-%d') as 誕生日" _
		+ " from 社員マスタ"
	Dim myCommand As New OdbcCommand()
	myCommand.CommandText = myQuery
	myCommand.Connection = myCon

	Dim myReader As OdbcDataReader
	myReader = myCommand.ExecuteReader()

	Do While myReader.Read()

		' 文字列
		Console.Write(GetValue(myReader,"社員コード") + " : ")
		Console.Write(GetValue(myReader,"氏名") + " : ")

		' 整数
		Console.Write(GetValue(myReader,"給与") + " : ")

		' 日付
		Console.Write(GetValue(myReader,"作成日") + " : ")
		Console.Write(GetValue(myReader,"更新日") + " : ")
		Console.Write(GetValue(myReader,"生年月日") + " : ")
		Console.Write(GetValue(myReader,"誕生日"))
		Console.WriteLine()

	Loop

	myReader.Close()

	myQuery = "update 社員マスタ set 生年月日 = '1982/01/01'" _
		+ " where 社員コード = '0002'"
	Execute( myCon, myQuery )

	myCon.Close()

	myReader.Dispose()
	myCon.Dispose()

End Sub

' ********************************************************
' 列データ取得
' ********************************************************
Function GetValue(ByVal odr As OdbcDataReader, _
	ByVal strName As String) As String

	Dim ret As String = ""
	Dim fld As Integer = 0

	fld = odr.GetOrdinal(strName)
	If odr.IsDBNull(fld) Then
		ret = ""
	Else
		ret = odr.GetValue(fld).ToString()
	End If

	Return ret

End Function

' ********************************************************
' 更新処理
' ********************************************************
Function Execute(ByVal cn As OdbcConnection, _
	ByVal SQL As String) As Integer

		Dim ret As Integer
		Dim execCommand As OdbcCommand = New OdbcCommand()

		execCommand.CommandText = SQL
		execCommand.Connection = cn
		Try
			ret = execCommand.ExecuteNonQuery()
		Catch ex As Exception
			Console.WriteLine( ex.Message )
		End Try
		execCommand.Dispose()

		Return ret

End Function

End Module